Bulk Restores - response to a question on Ask SQLServerCentral

Here’s the scenario: You’re rebuilding a DR environment, and have a set of Full, Differential and transaction log backups to restore. How to script this to run with minimal interference?

The Approach

Assumptions:

• All your backups are in subdirectories named for the database, off a root directory. eg: M:\MSSQLBackup, containing directories such as M:\MSSQLBackup\MyDB1, M:\MSSQLBackup\MyDB2
• File names are of the format DBName_DateTime_Backuptype.bakortrn

The basic approach I used when scripting this a while ago was:

• create a temporary table, and populate it with the files found
• iterate through restoring all the most recent FULL backups
• iterate through restoring the most recent DIFF backups, dated after the FULL backup
• iterate through restoring all the TLog backups dated after the DIFF backup in order
• iterate through every database running RECOVERY so the database is online

The downside to this approach (and the simplistic way I’ve coded it) is that it doesn’t do all RESTOREs for one database before moving on to the next, and it runs in reverse alphabetical order.

An upside to the approach I’ve used is that there’s the opportunity for a handy “how far have I got” script…

NB: This is dirty, dirty code. I’m sure there are much better, friendlier ways of doing things, but I needed something that was SQL2000-friendly. Read the disclaimers.

Step One – Getting Backup File Details

First step: Enable xp_cmdshell

exec sp_configure 'show advanced options', 1
reconfigure with override
exec sp_configure 'xp_cmdshell', 1 -- not necessary for SQL 2000
reconfigure with override
GO

Next: create a temporary table for the processing results

IF OBJECT_ID('tempdb.dbo.BackupFiles') IS NOT NULL
  BEGIN
    DROP TABLE tempdb.dbo.BackupFiles
  END
CREATE TABLE tempdb.dbo.BackupFiles (FullFileName  VARCHAR(255), dbname VARCHAR(128) NULL, RestoreDate DATETIME NULL)

Next step: Get the files and do some tidying up

DECLARE @BackupPath varchar(250)
SELECT @BackupPath = 'M:\MSSQL\Backup'
DECLARE @SQLCmd VARCHAR(2000)
SELECT @SQLCmd = 'xp_cmdshell ''dir "' + @BackupPath + '\*.bak" /s /b'''
INSERT INTO tempdb.dbo.BackupFiles (FullFileName)
EXEC (@SQLCmd)
SELECT @SQLCmd = 'xp_cmdshell ''dir "' + @BackupPath + '\*.trn" /s /b'''
INSERT INTO tempdb.dbo.BackupFiles (FullFileName)
EXEC (@SQLCmd)

DELETE FROM tempdb.dbo.backupfiles 
WHERE fullfilename NOT LIKE @BackupPath + '%\%'
OR FullFileName IS NULL

Now, convert this data into something more useful – populating the dbname field with the first chunk of the backup filename:

UPDATE tempdb.dbo.backupfiles
SET dbname = LEFT(RIGHT(fullfilename, LEN(fullfilename)-LEN(@backuppath)), CHARINDEX('\', RIGHT(fullfilename, LEN(fullfilename)-LEN(@backuppath)))-1)

Remove anything that looks to be a backup of a system database:

DELETE FROM tempdb.dbo.backupfiles
WHERE dbname NOT IN (SELECT name FROM sysdatabases WHERE dbid > 4)

Block anything that’s relating to a database for which we don’t have a FULL backup:

UPDATE tempdb.dbo.backupfiles
SET RestoreDate = '1900-01-01 00:00'
WHERE DBName NOT IN (SELECT DISTINCT DBName FROM tempdb.dbo.backupfiles WHERE FullFileName LIKE '%full%.bak')

Restore FULL database Backups

DECLARE @DBName VARCHAR(128)
DECLARE @FullFileName VARCHAR(255)
DECLARE @RestoreCommand VARCHAR(2000)
DECLARE @KillCommand varchar(2000)

/* FULL */
WHILE 
(SELECT TOP 1 dbname
FROM tempdb.dbo.BackupFiles
WHERE fullfilename LIKE '%full.bak' AND RestoreDate IS NULL
) IS NOT NULL
  BEGIN
	SELECT @DBName = dbname, @FullFileName = MAX(fullfilename)
	FROM tempdb.dbo.BackupFiles
	WHERE FullFileName LIKE '%full.bak' AND RestoreDate IS NULL
	GROUP BY dbname
	
	/* DO STUFF */
	/* Kill existing connections */
	SELECT @KillCommand = ''
	SELECT @KillCommand = @KillCommand + 'KILL ' + convert(char(15), spid) + '   
'
	FROM sysprocesses WHERE dbid = db_id(@DBName)
	IF @KillCommand  ''
	  BEGIN
	    PRINT @KillCommand
	    EXEC (@KillCommand)
	  END
	  
	/* Restore DBs */ 
        SELECT @RestoreCommand = 'RESTORE DATABASE [' + @DBName + '] FROM DISK = ''' + @FullFileName + ''' WITH NORECOVERY,REPLACE' -- <= build your restore command here.  I've simplified this for illustrative purposes
	PRINT @RestoreCommand
	exec (@RestoreCommand)
	
        /* Now flag this backupfile as having been restored, and remove all prior-dated backup files from the temporary table */
	UPDATE TempDB.dbo.BackupFiles
	SET RestoreDate= GETDATE() WHERE dbname = @DBName AND FullFileName = @FullFileName AND RestoreDate IS NULL
	DELETE FROM TempDB.dbo.BackupFiles
	WHERE dbname = @DBName AND RestoreDate IS NULL
	AND ( (FullFileName LIKE '%full.bak') OR (FullFileName LIKE '%diff.bak') OR (FullFileName LIKE '%tlog.trn') ) 
	AND FullFileName < @FullFileName 
	
  END

Differentials

Following a similar process for the DIFF backups:

WHILE
(SELECT TOP 1 DBName
FROM tempdb.dbo.BackupFiles b
WHERE b.fullfilename LIKE '%diff.bak' AND b.DBName IN (SELECT f.DBName FROM tempdb.dbo.BackupFiles f WHERE FullFilename LIKE '%full.bak' AND RestoreDate IS NOT NULL) AND RestoreDate IS NULL
) IS NOT NULL  
  BEGIN
    SELECT @DBName = dbname, @FullFileName = MAX(FullFileName)
    FROM tempdb.dbo.BackupFiles 
    WHERE fullfilename LIKE '%diff.bak' AND DBName IN (SELECT f.DBName FROM tempdb.dbo.BackupFiles f WHERE FullFilename LIKE '%full.bak' AND RestoreDate IS NOT NULL) AND RestoreDate IS NULL
    GROUP BY DBName
    
    SELECT @RestoreCommand = 'RESTORE DATABASE ['+ @DBName + '] FROM DISK = ''' + @FullFileName + ''' WITH REPLACE,NORECOVERY'
    PRINT @RestoreCommand
    EXEC (@RestoreCommand)
    
    UPDATE TempDB.dbo.BackupFiles
    SET RestoreDate = getdate() WHERE dbname = @DBName AND FullFilename = @FullFileName AND RestoreDate IS NULL
    DELETE FROM TempDB.dbo.BackupFiles
    WHERE dbName = @DBName AND RestoreDate IS NULL AND ( (FullFileName LIKE '%full.bak') OR (FullFileName LIKE '%diff.bak'))
    DELETE FROM TempDB.dbo.BackupFiles
    WHERE dbName = @DBName AND RestoreDate IS NULL AND FullFileName LIKE '%tlog.trn' AND FullFileName < @FullFileName
  END

And now for the Transaction Logs

Aaaand again:

WHILE EXISTS (SELECT * FROM tempDB.dbo.BackupFiles WHERE RestoreDate IS NULL AND FullFileName LIKE '%tlog.trn')
  BEGIN
    SELECT @DBName = dbname, @FullFileName = MIN(FullFileName)
    FROM tempdb.dbo.BackupFiles
    WHERE FulLFileName LIKE '%tlog.trn' AND RestoreDate IS NULL
    AND DBName IN (SELECT f.DBName FROM tempdb.dbo.Backupfiles f WHERE RestoreDate IS NOT NULL AND ((FullFileName LIKE '%full.bak') OR (FulLFileName LIKE '%diff.bak') ))
    GROUP BY DBName
    
    SELECT @RestoreCommand = 'RESTORE LOG [' + @DBName + '], FROM DISK = ''' + @FullFileName + ''' WITH NORECOVERY' -- Again, Simplified.
    PRINT @RestoreCommand
    EXEC (@RestoreCommand)
    UPDATE TempDB.dbo.BackupFiles
    SET RestoreDate = getdate() WHERE dbName = @DBName AND FullFileName = @FullFileName AND RestoreDate IS NULL
  
  END

And the final RECOVERY stage

DECLARE @RecoveryCommand varchar(MAX)
SELECT @RecoveryCommand = ''
SELECT @RecoveryCommand = @RecoveryCommand + 'RESTORE DATABASE [' + b.DBName + '] WITH RECOVERY   
'
FROM (SELECT DISTINCT DBName FROM tempdb.dbo.BackupFiles WHERE RestoreDate IS NOT NULL) as b
PRINT @RecoveryCommand
EXEC (@RecoveryCommand)

Monitoring

As I mentioned above, there’s the option to monitor this process. The script, for SQL2005 and above, is:

SELECT  rh.destination_database_name ,
        MIN(rh.restore_date) AS MinStartDate ,
        MIN(bf.RestoreDate) AS MinFinishDate ,
        MAX(rh.restore_date) AS MaxStartDate ,
        MAX(bf.RestoreDate) AS MaxFinishDate ,
        MAX(cbd.NumDone) AS RestoresDone
FROM    msdb..restorehistory rh
        LEFT JOIN tempdb.dbo.BackupFiles bf ON rh.destination_database_name = bf.DBName
        LEFT JOIN ( SELECT  DBName ,
                            COUNT(*) AS NumDone
                    FROM    tempdb.dbo.BackupFiles
                    WHERE   RestoreDate IS NOT NULL
                    GROUP BY DBName
                  ) AS cbd ON bf.DBName = cbd.DBName
WHERE   rh.restore_date >= (select crdate from tempdb..sysobjects where name = 'BackupFiles')
GROUP BY rh.destination_database_name
ORDER BY 2 DESC

This shows summary information of all the database RESTOREs that have started & completed since the creation of the BackupFiles temporary table.

Caveats

Note the following:

• This script hasn’t been tested on a brand new environment – the databases all exist already.
• This script was also built for use with SQL2000 as well as SQL2005 and later; I’ve removed a couple of SQL2000-only bits.
• This script was originally built for use with Quest LightSpeed for SQL Server – I’ve changed the various @RestoreCommand definitions to show the basic idea, but haven’t actually tested it.
• Don’t use this in production. Bad things would happen.
• …particularly as there’s no error handling.
• …and it doesn’t do User / login info
• This is worth what you paid for it, ie nothing. No warranty, no liability
• This post was lashed together as a response to a Question on AskSSC

About these ads