Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Jason Carter

Jason Carter has spent most of his career as a .NET developer, with time spent as a development manager, accidental DBA, and most recently a full-time DBA. Having worked with large databases as a developer, he found great interest in tuning, tweaking, and making databases run faster. With the support of his wife, he gave up his managerial duties, jumped the development ship and dove head first into his new career as a Database Administrator.

Restore Script from Backup Directory – Modified

As a DBA your job is to protect the data, whether that be from corruption, attack, developers or any other host of unknown afflictions. While I was not involved in the day to day backup or recovery while acting as an Accidental DBA (handled by an MSP), nor do I handle those duties in my current role as an actual DBA (handled by Storage Team) I am very aware of the needs of a solid strategy for backup AND recovery.

As I was tuning my next presentation which will introduce the uninformed to Ola Hallengren’s portfolio of free utilities I realized that teaching new or aspiring DBAs about the importance of a backup plan is reckless unless you also tell them about the importance of the more important, a recovery plan. I recalled having read a write-up by Greg Robidoux at MSSQLTips.com for a script to automatically generate a recovery script, based off a folder full of backups.

Once I looked at the requirements Greg lays out in his script I saw that it would not work as-is with Ola’s solution, so I modified it to do just that.

Now, as a warning, there is nothing earth shattering here, just a simple rework of a great solution for your toolbox. If you use Ola’s tools, maybe you can add this as an item in your tool box, as always, all feedback is greatly appreciated.

 

USE Master;
GO 
SET NOCOUNT ON

/**
	Variable declaration
**/
DECLARE @dbName sysname
		, @backupPath NVARCHAR(500)
		, @cmd NVARCHAR(500)
		, @lastFullBackup NVARCHAR(500)
		, @lastDiffBackup NVARCHAR(500)
		, @backupFile NVARCHAR(500)

DECLARE	@fileList TABLE (backupFile NVARCHAR(255))
DECLARE @directoryList TABLE (backupFile NVARCHAR(255))

/**
	Initialize variables
**/
SET @dbName = 'AdventureWorks2012'
SET @backupPath = 'C:\Backup'
/* Match that of Olas output */
SET @backupPath = @backupPath + '\' + @@SERVERNAME + '\' + @dbName + '\'

/**
	Get List of Files
**/
SET @cmd = 'DIR /s /b /O D ' + @backupPath
INSERT INTO @fileList(backupFile) EXEC master.sys.xp_cmdshell @cmd

/**
	Find latest full backup
**/
SELECT @lastFullBackup = MAX(backupFile) 
FROM @fileList 
WHERE backupFile LIKE '%' + @@SERVERNAME + '_' + @dbName + '_FULL_%.bak'

SET @cmd = 'RESTORE DATABASE [' + @dbName + '] FROM DISK = ''' 
       + @backupPath + @lastFullBackup + ''' WITH NORECOVERY, REPLACE'
PRINT @cmd

/**
	Find latest diff backup
**/
SELECT @lastDiffBackup = MAX(backupFile) 
FROM @fileList 
WHERE 
	backupFile  LIKE '%' + @@SERVERNAME + '_' + @dbName + '_DIFF_%.bak'
	AND backupFile>@lastFullBackup

/**
	check to make sure there is a diff backup
**/
IF @lastDiffBackup IS NOT NULL
	BEGIN
	   SET @cmd = 'RESTORE DATABASE [' + @dbName + '] FROM DISK = ''' 
		   + @backupPath + @lastDiffBackup + ''' WITH NORECOVERY'
	   PRINT @cmd
	   SET @lastFullBackup = @lastDiffBackup
	END

/**
	check for log backups
**/
DECLARE backupFiles CURSOR FOR 
   SELECT backupFile 
   FROM @fileList
   WHERE 
	backupFile LIKE  '%' + @@SERVERNAME + '_' + @dbName + '_LOG_%.trn'
	AND backupFile> @lastFullBackup

OPEN backupFiles 

/**
	Loop through all the files for the database 
**/
FETCH NEXT FROM backupFiles INTO @backupFile 

WHILE @@FETCH_STATUS = 0 
	BEGIN 
	   SET @cmd = 'RESTORE LOG [' + @dbName + '] FROM DISK = ''' 
		   + @backupPath + @backupFile + ''' WITH NORECOVERY'
	   PRINT @cmd
	   FETCH NEXT FROM backupFiles INTO @backupFile 
	END

CLOSE backupFiles 
DEALLOCATE backupFiles 

/**
	put database in a useable state
**/
SET @cmd = 'RESTORE DATABASE [' + @dbName + '] WITH RECOVERY'
PRINT @cmd

 

Comments

Leave a comment on the original post [jason-carter.net, opens in a new window]

Loading comments...