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