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