It is handy to have a SQL Restore script ready to go for every backup that you run. Here is a script that will help with that.
</p> <p>-- available at http://databasehealth.com/shared-scripts/</p> <p>-- Created by Steve Stedman<br /> -- http://SteveStedman.com<br /> -- http://StedmanSolutions.com<br /> -- http://DatabaseHealth.com<br /> -- twitter: @SqlEmt<br /> -- email: [email protected]<br /> -- </p> <p>-- to use the procecure, first run the whole script to create it, then<br /> -- uncomment the following line and replace database name with the name<br /> -- of your database. Do not include square brackets on the database name.<br /> -- NOTICE: the actual script may need to be modified for updated paths or<br /> -- filenames.<br /> -- examples<br /> --EXECUTE [DBHealthHistory]..[dbhLogChain] 'DBHealthHistory';<br /> ---- or<br /> --EXECUTE [DBHealthHistory]..[dbhLogChain] 'DBHealthHistory', 'display';<br /> ---- or<br /> --EXECUTE [DBHealthHistory]..[dbhLogChain] 'DBHealthHistory', 'FileInBackupDir';</p> <p>-- to use the FileInBackupDir option you need to enable the 'Ole Automation Procedures'<br /> -- uncomment and execute the following 8 lines.<br /> --sp_configure 'show advanced options', 1;<br /> --GO<br /> --RECONFIGURE;<br /> --GO<br /> --sp_configure 'Ole Automation Procedures', 1;<br /> --GO<br /> --RECONFIGURE;<br /> --GO </p> <p>use DBHealthHistory; -- replace this with master, or your dba type database.</p> <p>-- in case it exists and you need to drop it.<br /> IF OBJECT_ID('dbhLogChain', 'P') IS NOT NULL<br /> BEGIN<br /> DROP PROCEDURE [dbo].[dbhLogChain];<br /> END<br /> GO</p> <p>CREATE PROCEDURE dbhLogChain @dbName VARCHAR(2048),<br /> @outputSqlScript VARCHAR(20) = 'display'<br /> -- database name should be quoted with no square brackets<br /> AS<br /> BEGIN<br /> -- ============================================================================<br /> -- Copyright (c) 2015-2017 Steve Stedman<br /> -- http://DatabaseHealth.com<br /> -- twitter: @SqlEmt<br /> -- email: [email protected]<br /> -- ============================================================================</p> <p> SET NOCOUNT ON;</p> <p> IF LOWER(@dbName) = 'all_databases'<br /> BEGIN<br /> DECLARE @DatabaseID as INT;<br /> DECLARE @DatabaseName as NVARCHAR(50);<br /> DECLARE @DatabaseCursor as CURSOR;<br /> SET @DatabaseCursor = CURSOR FOR<br /> SELECT name, database_id<br /> FROM sys.databases<br /> WHERE name not in ('tempdb');</p> <p> OPEN @DatabaseCursor;<br /> FETCH NEXT FROM @DatabaseCursor INTO @DatabaseName, @DatabaseID;</p> <p> WHILE @@FETCH_STATUS = 0<br /> BEGIN<br /> EXECUTE [DBHealthHistory]..[dbhLogChain] @DatabaseName, 'FileInBackupDir';<br /> FETCH NEXT FROM @DatabaseCursor INTO @DatabaseName, @DatabaseID;<br /> END</p> <p> CLOSE @DatabaseCursor;<br /> DEALLOCATE @DatabaseCursor;<br /> END<br /> ELSE<br /> BEGIN<br /> DECLARE @outputBuffer AS VARCHAR(MAX) = '';<br /> DECLARE @mostRecentBackupFile AS VARCHAR(MAX) = '';<br /> DECLARE @maxDifferential AS DATETIME;<br /> DECLARE @maxFull AS DATETIME;</p> <p> --Use to enter extra comments<br /> SET @outputBuffer += 'RAISERROR (''Caution running this script will overwrite the database [' + @dbName + '].'', 20, 1) WITH LOG; ' + char(13) + char(10);<br /> SET @outputBuffer += '--Make sure to change restore file paths if needed' + char(13) + char(10);<br /> SET @outputBuffer += '--Consider using STOPAT to recover mid transaction log. Syntax STOPAT = ''YYYYMMDD HH:MM:SS'' Add After NORECOVERY before STATS Example NORECOVERY, STOPAT = ''20150616 15:04:31'', STATS' + char(13) + char(10);</p> <p> SELECT TOP 1 @maxFull = bs.backup_finish_date<br /> FROM msdb.dbo.backupset bs WITH (NOLOCK)<br /> INNER JOIN msdb.dbo.backupmediafamily bmf WITH (NOLOCK)<br /> ON bs.media_set_id = bmf.media_set_id<br /> WHERE bmf.physical_device_name <> 'NUL' -- special case to deal with Veeam<br /> AND NULLIF(bmf.physical_device_name, '') is not null<br /> AND database_name = @dbName<br /> AND bs.is_copy_only = 0<br /> AND bs.is_snapshot = 0<br /> AND bs.type = 'D' -- Full Database<br /> ORDER by backup_set_id desc; -- DatabaseHealth</p> <p> SELECT TOP 1 @maxDifferential = bs.backup_finish_date<br /> FROM msdb.dbo.backupset bs WITH (NOLOCK)<br /> INNER JOIN msdb.dbo.backupmediafamily bmf WITH (NOLOCK)<br /> ON bs.media_set_id = bmf.media_set_id<br /> WHERE bmf.physical_device_name <> 'NUL' -- special case to deal with Veeam<br /> AND NULLIF(bmf.physical_device_name, '') is not null<br /> AND database_name = @dbName<br /> AND bs.is_copy_only = 0<br /> AND bs.is_snapshot = 0<br /> AND bs.type = 'I' -- Differential<br /> ORDER by backup_set_id desc; -- DatabaseHealth</p> <p> IF @maxFull > @maxDifferential OR @maxDifferential is NULL<br /> BEGIN<br /> SET @maxDifferential = @maxFull;<br /> END</p> <p> SET @outputBuffer += 'use [master];' + char(13) + char(10);<br /> SET @outputBuffer += '--ALTER DATABASE ' + QUOTENAME(@dbName) + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;' + char(13) + char(10);</p> <p> SELECT TOP 1 @outputBuffer += '--RESTORE DATABASE ' + QUOTENAME(database_name) +<br /> ' FROM DISK = ''' + bmf.physical_device_name + ''' WITH <<<<<MOVES>>>>>--REPLACE, NORECOVERY, STATS = 1;' + char(13) + char(10),<br /> @mostRecentBackupFile = bmf.physical_device_name<br /> FROM msdb.dbo.backupset bs WITH (NOLOCK)<br /> INNER JOIN msdb.dbo.backupmediafamily bmf WITH (NOLOCK)<br /> ON bs.media_set_id = bmf.media_set_id<br /> WHERE bmf.physical_device_name <> 'NUL' -- special case to deal with Veeam<br /> AND NULLIF(bmf.physical_device_name, '') is not null<br /> AND database_name = @dbName<br /> AND bs.is_copy_only = 0<br /> AND bs.is_snapshot = 0<br /> AND bs.type = 'D' -- full database<br /> ORDER by backup_set_id desc; </p> <p> IF NULLIF(@mostRecentBackupFile, '') IS NOT NULL<br /> BEGIN</p> <p> DECLARE @moves AS VARCHAR(MAX);<br /> SET @moves = char(13) + char(10);<br /> DECLARE @Table TABLE<br /> (<br /> [LogicalName] varchar(128),<br /> [PhysicalName] varchar(128),<br /> [Type] varchar,<br /> [FileGroupName] varchar(128),<br /> [Size] varchar(128),<br /> [MaxSize] varchar(128),<br /> [FileId] varchar(128),<br /> [CreateLSN] varchar(128),<br /> [DropLSN] varchar(128),<br /> [UniqueId] varchar(128),<br /> [ReadOnlyLSN] varchar(128),<br /> [ReadWriteLSN] varchar(128),<br /> [BackupSizeInBytes] varchar(128),<br /> [SourceBlockSize] varchar(128),<br /> [FileGroupId] varchar(128),<br /> [LogGroupGUID] varchar(128),<br /> [DifferentialBaseLSN] varchar(128),<br /> [DifferentialBaseGUID] varchar(128),<br /> [IsReadOnly] varchar(128),<br /> [IsPresent] varchar(128),<br /> [TDEThumbprint] varchar(128)<br /> --,[SnapshotUrl] nvarchar(360) --- ONLY SQL 2016 or NEWER<br /> );</p> <p> INSERT INTO @table<br /> EXEC('RESTORE FILELISTONLY FROM DISK=''' + @mostRecentBackupFile + ''' ');</p> <p> SELECT @moves = @moves + '--MOVE ''' + LogicalName + ''' TO ''' + PhysicalName + ''',' + char(13) + char(10) from @Table;</p> <p> SET @outputBuffer = REPLACE(@outputBuffer, '<<<<<MOVES>>>>>', @moves);</p> <p> SELECT TOP 1 @outputBuffer += '--RESTORE DATABASE ' + QUOTENAME(database_name) +<br /> ' FROM DISK = ''' + bmf.physical_device_name + ''' WITH NORECOVERY, STATS = 1; -- Differential' + char(13) + char(10),<br /> @mostRecentBackupFile = bmf.physical_device_name<br /> FROM msdb.dbo.backupset bs WITH (NOLOCK)<br /> INNER JOIN msdb.dbo.backupmediafamily bmf WITH (NOLOCK)<br /> ON bs.media_set_id = bmf.media_set_id<br /> WHERE bmf.physical_device_name <> 'NUL' -- special case to deal with Veeam<br /> AND NULLIF(bmf.physical_device_name, '') is not null<br /> AND database_name = @dbName<br /> AND bs.backup_start_date > @maxFull<br /> AND bs.is_copy_only = 0<br /> AND bs.is_snapshot = 0<br /> AND bs.type = 'I' -- differential<br /> ORDER by backup_set_id DESC; </p> <p> SELECT TOP 1 @mostRecentBackupFile = bmf.physical_device_name<br /> FROM msdb.dbo.backupset bs WITH (NOLOCK)<br /> INNER JOIN msdb.dbo.backupmediafamily bmf WITH (NOLOCK)<br /> ON bs.media_set_id = bmf.media_set_id<br /> WHERE bmf.physical_device_name <> 'NUL' -- special case to deal with Veeam<br /> AND NULLIF(bmf.physical_device_name, '') is not null<br /> AND database_name = @dbName<br /> AND bs.backup_start_date > @maxDifferential<br /> AND bs.is_copy_only = 0<br /> AND bs.is_snapshot = 0<br /> AND bs.type = 'L' -- log backup<br /> ORDER by backup_set_id DESC; </p> <p> SELECT @outputBuffer += '--RESTORE DATABASE ' + QUOTENAME(database_name) +<br /> ' FROM DISK = ''' + bmf.physical_device_name + ''' WITH NORECOVERY, STATS = 10; -- Log Backup' + char(13) + char(10)<br /> FROM msdb.dbo.backupset bs WITH (NOLOCK)<br /> INNER JOIN msdb.dbo.backupmediafamily bmf WITH (NOLOCK)<br /> ON bs.media_set_id = bmf.media_set_id<br /> WHERE bmf.physical_device_name <> 'NUL' -- special case to deal with Veeam<br /> AND NULLIF(bmf.physical_device_name, '') is not null<br /> AND database_name = @dbName<br /> AND bs.backup_start_date > @maxDifferential<br /> AND bs.is_copy_only = 0<br /> AND bs.is_snapshot = 0<br /> AND bs.type = 'L' -- log backup<br /> ORDER by backup_set_id ASC; </p> <p> SET @outputBuffer += '--RESTORE DATABASE ' + QUOTENAME(@dbName) + ' WITH RECOVERY;' + char(13) + char(10);</p> <p> SET @outputSqlScript = LOWER(@outputSqlScript);<br /> IF @outputSqlScript = 'display'<br /> BEGIN<br /> PRINT @outputBuffer;<br /> END</p> <p> IF @outputSqlScript = 'fileinbackupdir'<br /> BEGIN<br /> DECLARE @ole INTEGER;<br /> DECLARE @fileID INTEGER;</p> <p> SET @mostRecentBackupFile = @mostRecentBackupFile + '.restore_script.sql';<br /> --print @mostRecentBackupFile;</p> <p> EXECUTE sp_OACreate 'Scripting.FileSystemObject', @ole OUT;<br /> EXECUTE sp_OAMethod @ole, 'OpenTextFile', @fileID OUT, @mostRecentBackupFile, 2, 1;<br /> EXECUTE sp_OAMethod @fileID, 'WriteLine', Null, @outputBuffer;<br /> EXECUTE sp_OADestroy @fileID;<br /> EXECUTE sp_OADestroy @ole;<br /> END<br /> END<br /> END<br /> END<br /> GO<br /> EXECUTE [DBHealthHistory]..[dbhLogChain] 'ALL_DATABASES', 'FileInBackupDir';<br />
SQL Server Encryption and how to use TDE
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!