Archive

Archive for the ‘Backup and Recovery’ Category

Quick and easy way to automate SQL Express database backups

August 6, 2015 Leave a comment

One of free SQL Server Express limitations is the lack of SQL Server Agent and, consequently, Maintenance Plans as such.

The easy way to automate regular database tasks, such as database backup would be to use internal windows scheduler and just a simple “ms dos”-like batch file.

First, the backup stored procedure has to be created in [master] database.


CREATE PROCEDURE Sp_dbbackup_user_and_system_databases

AS

BEGIN

DECLARE @db_bkp_location NVARCHAR(50)

DECLARE @today NVARCHAR(10)

DECLARE @bkpsql NVARCHAR(500)

DECLARE @db_name NVARCHAR(50)

DECLARE bkpcur CURSOR FOR

SELECT NAME

FROM   sys.databases

WHERE  NAME NOT IN ( ‘tempdb’ )

ORDER  BY NAME

SET @db_bkp_location = ‘E:\DBBackup’

SET @today = CONVERT(VARCHAR, Getdate(), 112)

SET @today = Substring(@today, 1, 4) + ‘_’

+ Substring(@today, 5, 2) + ‘_’

+ Substring(@today, 7, 2)

SET @bkpsql = 

SET @db_name = 

OPEN bkpcur

FETCH bkpcur INTO @db_name

WHILE @@fetch_status = 0

BEGIN

SET @bkpsql = ‘BACKUP DATABASE ‘ + @db_name + ‘ TO  DISK = ‘

+ ”” + @db_bkp_location + ‘\’ + @db_name + ‘_’ +

@today

+ ‘.bak’ + ””

+ ‘ WITH NOFORMAT, INIT,  NAME = ‘ + ””

+ @db_name + ‘-‘ + @today + ‘-Full Database Backup’

+ ”” + ‘, SKIP, NOREWIND, NOUNLOAD’;

–print @bkpsql

EXECUTE Sp_executesql

@bkpsql

FETCH bkpcur INTO @db_name

END

CLOSE bkpcur

DEALLOCATE bkpcur

END

Now the batch file

@echo off

sqlcmd -S .\SQLEXPRESS -E -Q “EXEC master..sp_DBBackup_User_and_System_Databases”

Now what’s left is just to schedule it with standard windows scheduler.

Start -> All Programs -> Accessories -> System Tools -> Scheduled Tasks -> Add Scheduled Tasks, plug in the batch file name, pick up the recurrence you want and it’s good to go. Like it was one of those Maintenance Plan tasks or just plain SQL Agent jobs we’re all used to.

Advertisements

Script for point-in-time recovery

June 15, 2011 Leave a comment

  

This is great script for a point-in-time recovery. Big ‘Thank You’ to Henning Frettem

(and Michael Valentine Jones, SQLTeam.com as, apparently, the original script author)

 

/*
Generate Point-in-time restore script, given only DBName and Point-in-time timestamp
Works only on SQL2008 databases!!

Script for generating restore script for full backup originally created by Michael
Valentine Jones, SQLTeam.com

The rest is created by Henning Frettem, http://www.thefirstsql.com
*/
SET NOCOUNT ON
GO

DECLARE
  
@DBName VARCHAR(200) = ‘MyDB’,
  
@PointInTime DATETIME = ‘2010-08-09 09:10:00’,
  
@Filename VARCHAR(200),
  
@tab VARCHAR(1) = CHAR(9),
  
@cr VARCHAR(2) = CHAR(13)+CHAR(10),
  
@Full_BackupStartDate DATETIME,
  
@Diff_BackupStartDate DATETIME,
  
@Log_BackupStartDate DATETIME,
  
@SQL NVARCHAR(MAX) =

BEGIN TRY
  
–> Performing some checks
  
IF NOT EXISTS (SELECT 1 FROM msdb.dbo.backupset WHERE database_name = @DBName AND TYPE = ‘D’ AND backup_start_date <= @PointInTime)
    
RAISERROR(N’No full backup exists prior to the specified PointInTime’, 16, 1)
  
ELSE IF NOT EXISTS (SELECT 1 FROM msdb.dbo.backupset WHERE database_name = @DBName AND TYPE = ‘L’ AND backup_start_date > @PointInTime)
    
RAISERROR(N’No backup of the log exist after the specified PointInTime’, 16, 1)

  –> Getting the filename and the date of the last full backup prior to the PointInTime
  
SELECT TOP 1
    
@Filename = b.physical_device_name,
    
@Full_BackupStartDate = backup_start_date
  
FROM msdb.dbo.backupset a
    
INNER JOIN msdb.dbo.backupmediafamily b
      
ON a.media_set_id = b.media_set_id
  
WHERE a.database_name = @DBName
    
AND a.TYPE = ‘D’
    
AND a.backup_start_date <= @PointInTime
  
ORDER BY a.backup_start_date DESC

  –> Create temp-tables for file header information
  
DECLARE @header TABLE (
    
BackupName NVARCHAR(128),
    
BackupDescription NVARCHAR(255),
    
BackupType smallint,
    
ExpirationDate DATETIME,
    
Compressed bit,
    
Position smallint,
    
DeviceType tinyint,
    
UserName NVARCHAR(128),
    
ServerName NVARCHAR(128),
    
DatabaseName NVARCHAR(128),
    
DatabaseVersion INT,
    
DatabaseCreationDate DATETIME,
    
BackupSize numeric(20,0),
    
FirstLSN numeric(25,0),
    
LastLSN numeric(25,0),
    
CheckpointLSN numeric(25,0),
    
DatabaseBackupLSN numeric(25,0),
    
BackupStartDate DATETIME,
    
BackupFinishDate DATETIME,
    
SortOrder smallint,
    
CodePage smallint,
    
UnicodeLocaleId INT,
    
UnicodeComparisonStyle INT,
    
CompatibilityLevel tinyint,
    
SoftwareVendorId INT,
    
SoftwareVersionMajor INT,
    
SoftwareVersionMinor INT,
    
SoftwareVersionBuild INT,
    
MachineName NVARCHAR(128),
    
Flags  INT,
    
BindingID UNIQUEIDENTIFIER,
    
RecoveryForkID UNIQUEIDENTIFIER,
    
Collation NVARCHAR(128),
    
FamilyGUID UNIQUEIDENTIFIER,
    
HasBulkLoggedData bit,
    
IsSnapshot bit,
    
IsReadOnly bit,
    
IsSingleUser bit,
    
HasBackupChecksums bit,
    
IsDamaged bit,
    
BeginsLogChain bit,
    
HasIncompleteMetaData bit,
    
IsForceOffline bit,
    
IsCopyOnly bit,
    
FirstRecoveryForkID UNIQUEIDENTIFIER,
    
ForkPointLSN numeric(25,0) NULL,
    
RecoveryModel NVARCHAR(60),
    
DifferentialBaseLSN numeric(25,0) NULL,
    
DifferentialBaseGUID UNIQUEIDENTIFIER,
    
BackupTypeDescription NVARCHAR(60),
    
BackupSetGUID UNIQUEIDENTIFIER NULL,
    
CompressedBackupSize bigint,
    
Seq INT NOT NULL IDENTITY(1,1)
    )

  –> Create temp-table for db file information
  
DECLARE @filelist TABLE  (
    
LogicalName NVARCHAR(128),
    
PhysicalName NVARCHAR(260),
    
TYPE CHAR(1),
    
FileGroupName NVARCHAR(128),
    
Size numeric(20,0),
    
MaxSize numeric(20,0),
    
FileID bigint,
    
CreateLSN numeric(25,0),
    
DropLSN numeric(25,0) NULL,
    
UniqueID UNIQUEIDENTIFIER,
    
ReadOnlyLSN numeric(25,0) NULL,
    
ReadWriteLSN numeric(25,0) NULL,
    
BackupSizeInBytes bigint,
    
SourceBlockSize INT,
    
FileGroupID INT,
    
LogGroupGUID UNIQUEIDENTIFIER NULL,
    
DifferentialBaseLSN numeric(25,0) NULL,
    
DifferentialBaseGUID UNIQUEIDENTIFIER,
    
IsReadOnly bit,
    
IsPresent bit,
    
TDEThummbprint VARBINARY(32),
    
Seq INT NOT NULL IDENTITY(1,1)
    )

  –> Get header and filelist information from the backup file
  
INSERT INTO @header
  
EXEC (‘RESTORE HeaderOnly FROM DISK = ”’ + @Filename + ””)

  INSERT INTO @filelist
  
EXEC (‘RESTORE FilelistOnly FROM DISK = ”’ + @Filename + ””)

  –> Generate the full backup restore script
  
SELECT
    
@SQL = @SQL +
      
CASE
        
WHEN a.Seq = 1 THEN
          
@cr + ‘RESTORE DATABASE [‘ + c.DatabaseName + ‘]’ +
          
@cr + ‘FROM DISK =’ + @cr + @tab + ”” +
          
@Filename + ”” + @cr + ‘WITH’
        
ELSE
      
END
      
+ @cr + @tab + ‘MOVE ”’ + a.LogicalName + ”’ TO ”’ + a.PhysicalName + ”’,’
      
+
      
CASE
        
WHEN a.Seq = b.Seq THEN
          
@cr + @tab + ‘REPLACE, STATS = 5, NORECOVERY’
        
ELSE
      
END
  FROM
    
@filelist a
    
CROSS JOIN
      
(SELECT Seq = MAX(b1.Seq) FROM @filelist b1 ) b
    
CROSS JOIN
      
(SELECT DatabaseName = MAX(c1.DatabaseName) FROM @header c1) c
  
ORDER BY
    
a.Seq

  SELECT @SQL = @SQL + @cr + ‘GO’ + @cr + @cr

  –> Restore the last differential backup if it exists
  
SELECT TOP 1
    
@SQL = @SQL + ‘RESTORE DATABASE [‘ + @DBName + ‘] FROM DISK = ”’ + b.physical_device_name + ”’ WITH NORECOVERY’ + @cr + ‘GO’,
    
@Diff_BackupStartDate = a.backup_start_date
  
FROM msdb.dbo.backupset a
    
INNER JOIN msdb.dbo.backupmediafamily b
      
ON a.media_set_id = b.media_set_id
  
WHERE a.database_name = @DBName
    
AND a.TYPE = ‘I’
    
AND a.backup_start_date > @Full_BackupStartDate
    
AND a.backup_start_date < @PointInTime
  
ORDER BY a.backup_start_date DESC

  IF @Diff_BackupStartDate IS NULL
    
SET @Diff_BackupStartDate = @Full_BackupStartDate

  –> Generate all log restores except the last one
  
SELECT
    
@SQL = @SQL + ‘RESTORE LOG [‘ + @DBName + ‘] FROM DISK = ”’ + b.physical_device_name + ”’ WITH NORECOVERY’ + @cr + ‘GO’ + @cr,
    
@Log_BackupStartDate = a.backup_start_date
  
FROM msdb.dbo.backupset a
    
INNER JOIN msdb.dbo.backupmediafamily b
      
ON a.media_set_id = b.media_set_id
  
WHERE a.database_name = @DBName
    
AND a.TYPE = ‘L’
    
AND a.backup_start_date > @Diff_BackupStartDate
    
AND a.backup_start_date < @PointInTime
  
ORDER BY a.backup_start_date

  –> Generate last log restore script with the stopat command and recovery
  
SELECT TOP 1
    
@SQL = @SQL + ‘RESTORE LOG [‘ + @DBName + ‘] FROM DISK = ”’ + b.physical_device_name + ”’
      WITH RECOVERY, STOPAT = ”’
+ CONVERT(VARCHAR(20), @PointInTime, 120) + ”” + @cr + ‘GO’ + @cr
  
FROM msdb.dbo.backupset a
    
INNER JOIN msdb.dbo.backupmediafamily b
      
ON a.media_set_id = b.media_set_id
  
WHERE a.database_name = @DBName
    
AND a.TYPE = ‘L’
    
AND a.backup_start_date > @PointInTime
  
ORDER BY a.backup_start_date ASC

  PRINT @SQL

END TRY
BEGIN CATCH
  
PRINT ERROR_MESSAGE()
END CATCH

Categories: Backup and Recovery

Changing your default settings for any new database is just compulsory

June 2, 2011 2 comments

What a statement, eh? All over a sudden and out of the blue. Never bothered to alter any default settings for a to-be-created database myself and haven’t heard others doing that on a large scale. However, after hitting this article by Glenn Berry, (which references couple of other related posts One | Two by Michelle Ufford and Kimberly Tripp) I realized this is serious. And I’m very serial about this! (“South Park” is sometimes retarded and disgusting, especially that first episode of 15th season, but in 9 out of 10 – it’s brilliant!)

In short, to summarize what the guys already said: excessive number of transaction log file extents, – Virtual Log Files, will lead to lengthy restore/recovery times. As simple as that. Besides, you want to save on execution time and server workload when having multiple Transaction Log File extents following one another during “heavy” DML ops. In addition to that, having multiple small VLFs in Transaction Log File will make it unnecessary big in size, which eventually will require its truncate and shrink. Too much hassle for such a silly reason as not-really-suitable default settings, which are very easy to change.

Hence, right after the new installation of SQL Server, go and change the model db datafile and transaction log file settings from default to… whatever you feel like after reading those two articles, but the main point is – CHANGE IT!

GUI-way:

Script-way:

USE [master];
ALTER DATABASE [MyNewDB] MODIFY FILE ( NAME = N'MyNewDB_Log', FILEGROWTH = 65536KB );

Stuck Database Backup

June 2, 2011 Leave a comment

Recently I came across very peculiar problem with database backup. It starts with this error message:

System.Data.SqlClient.SqlError: Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed. (Microsoft.SqlServer.Smo)

It stalls and after it stalls and the relevant session is killed, the entire kill/rollback transaction gets stalled too. But here is what happened in details and chronological order.

 

IMPORTANT NOTE: IF YOU HAVE A SIMILAR SITUATION, PLEASE DO NOT FOLLOW THE STEPS TILL YOU REVIEW THE ENTIRE POST, ESPECIALLY THE CONCLUSION. THIS MIGHT SAVE YOU A LOT OF TIME AND HASSLE OF RESTARTING SQL SERVER SERVICE, ESPECIALLY IN PRODUCTION ENVIRONMENTS

After checking backup jobs across multiple servers, one looked wrong:

Started investigating and found that the backup is stuck. (script found here, by Nitansh Agarwal)

SELECT session_id AS SPID,
command,
a.TEXT AS Query,
start_time,
percent_complete,
DATEADD(second,estimated_completion_time/1000, GETDATE()) AS estimated_completion_time
FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command IN ('BACKUP DATABASE','RESTORE DATABASE');

percent_complete was zero, meanwhile start_time went back to months ago…

I stopped the job execution through Job Activity Monitor

but then again, it still remained “running” at the background, which became obvious after querying sys.sysprocesses

Finally, I killed it with kill <spid>;

BUT IT NEVER WENT AWAY EVEN AFTER THIS!

After digging i-net for some time I came across probably the most useful post from Microsoft Connect: Killed/Rollback

Should you encounter this situation, I highly encourage you to go through this article carefully – it does worth every second to review it, just go from BOTTOM to TOP. It basically confirms that cases when database backup stucks running at the background are random, not reproducible yet very possible across SQL Server 2000-2008 product family, no matter what edition, platform (32- or 64-bit), SPs or CUs are installed. And the only solution is… Right, – bounce the instance. (sorry, Oracle notation again, silly me, – restart the service).

So, here is the service restarted, the transaction is gone but the subsequent attempt to run the very same backup apparently stalls again…

percent_complete is 0 for more than 2.5 hours – lame…

Then a bit of further research, and this post comes up. The same story as mine, with the only exception that I don’t have any error messages. Then transaction log file truncation it is.

I will have to kill the backup process again, which, in turn, will stuck and thus another ins… service restart will be required, but that’s a small price to pay for fixing non-going backup situation.

——- waited till the maintenance window later on in the evening ——–

… and, moving a bit ahead, – it didn’t work again …

So, I killed the process, bounced the service, made sure the process is not there anymore and thus there is no checkpoint waiting for that stuck backup to complete. This way, it’s a green line to proceed with transaction log file truncate and then, which, hopefully, will make backups runnable once again.

Nope.

After truncating the TLog with
DBCC SHRINKFILE (N'ECOMGEN_log' , 0, TRUNCATEONLY);

transaction log file did truncate successful but running backup as the next thing
BACKUP DATABASE [ECOMLIVE] TO  DISK = N'D:\Backups\ECOMLIVE\ECOMLIVE.bak'
WITH NOFORMAT, NOINIT,  NAME = N'ECOMLIVE-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 1
GO

GOT STUCK AGAIN!!!
SELECT *
FROM SYS.sysprocesses
WHERE SPID = (SELECT session_id
FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command IN ('BACKUP DATABASE','RESTORE DATABASE'))

Interesting! I missed this thing last time

Well, back to google again… MSSEARCH wait type

And here is the article

BINGO!

Full-Text Catalogs. After stopping SQL Server Full-Text Search service,

the backup kicked-in right away!

Well, the main question still remains – is it a bug, which spans across 2000-2005-2008 with no regards to edition, version, service pack or cumulative update, or such a special feature? 😉

Anyhow, any subsequent backup attempts taken with full-text search service running inevitably failed. So, the question remained – what needs to be taken care of to make backups running robustly again. This post suggested full-text catalogs rebuild would solve the problem, and it really did!

First, I rebuilt the full-text catalog
USE [ECOMLIVE];
ALTER FULLTEXT CATALOG FT_ITEMKEYWORDS_LIVE REBUILD WITH ACCENT_SENSITIVITY=OFF;

and it didn’t help – backups where still stuck. So, I dropped and re-created the catalog.

DROP FULLTEXT CATALOG [FT_ITEMKEYWORDS_LIVE];

–after issuing drop catalog, transaction log file backup has to be taken before creating a new one
BACKUP LOG ECOMLIVE TO DISK = N’D:\Backups\ECOMLIVE\ECOMLIVE_201106022230.trn’ WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD,  STATS = 10;

CREATE FULLTEXT CATALOG [FT_ITEMKEYWORDS_LIVE]
IN PATH N’D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\ITEMKEYWORDS’
WITH ACCENT_SENSITIVITY = OFF
AUTHORIZATION
[dbo];

That did it. After this point backups got back to running normally.

CONCLUSION

Should you experience the similar situation, with MSSEARCH wait type, start with looking at full-text search. Shut the full-text search service down and see whether the backup which is stuck now starts running.

SELECT session_id AS SPID,
command,
a.TEXT AS Query,
percent_complete
FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command IN ('BACKUP DATABASE','RESTORE DATABASE')

If it does, drop and re-create the catalog(s). Don’t bother rebuilding them, it didn’t help in my situation, probably won’t help in yours too. Drop and re-build works better 🙂
DROP FULLTEXT CATALOG [FT_ITEMKEYWORDS_LIVE];

–after issuing drop catalog, transaction log file backup has to be taken before creating a new one
BACKUP LOG ECOMLIVE TO DISK = N’D:\Backups\ECOMLIVE\ECOMLIVE_201106022230.trn’ WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD,  STATS = 10;

CREATE FULLTEXT CATALOG [FT_ITEMKEYWORDS_LIVE]
IN PATH N’D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\ITEMKEYWORDS’
WITH ACCENT_SENSITIVITY = OFF
AUTHORIZATION
[dbo];

If this doesn’t help, your best bet would be bouncing the SQL Server service and take it from there, mostly by talking to Microsoft Support.

SQL Server 2005 Backup Compression

May 12, 2011 Leave a comment

Well… not “out of the box” as in SQL Server 2008. But that could be done with a script.

The problem though is that comparable to “native” SS2008 compression, compressing database and transaction log files in SS2005 would take times more CPU, I/O and storage and, as a result, would take much more time to run. This is because SS2008 performs compression before writting to disk, whereas here, in SS2005, all files will be written to disk first, then compressed.

Nevertheless, there might be certain cituations where using archiving of database and transaction log backups might come handy.

Any archivator, which supports archiving of large-enough files could be used for this. I use 7-zip for that purpose – it’s free, fast and . Here are two other free archivators: IZArc, PeaZip

Here, you need to enable xp_cmdshell which runs OS commands from withing SS Scheduled Job, not forgetting to disable it after the execution.

Bold marks the custom directories and file names.

EXEC sp_configure ‘show advanced options’, 1
RECONFIGURE
EXEC sp_configure ‘xp_cmdshell’, 1
RECONFIGURE

DECLARE @cmd VARCHAR(1000)
DECLARE @db_bkp_files_dir varchar(100)
DECLARE @archive_destination_dir varchar(100)
DECLARE @archive_name varchar(100)
DECLARE @7z_path varchar(100)
set @archive_destination_dir = ‘D:\Backup_test’ –destination dir
set @7z_path = ‘”C:\Program Files\7-Zip
set @db_bkp_files_dir = ‘D:\Backup_test’ –db backup files origin 
set @archive_name = ‘DBFullBackup_‘ + convert(varchar, getdate(), 112) + substring(replace(convert(varchar, getdate(), 108), ‘:’, ”), 1, 4)
SET @cmd = @7z_path + ‘\7z” a -t7z -mx5 -ms=off ‘ + @archive_destination_dir + ‘\’ + @archive_name + ‘.7z ‘ +  @db_bkp_files_dir + ‘\*’ 
–print @cmd
EXEC xp_cmdshell @cmd

EXEC sp_configure ‘show advanced options’, 0
RECONFIGURE