Building a SQL Restore Script When Your Backup Runs

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.

-- available at http://databasehealth.com/shared-scripts/


-- Created by Steve Stedman
--    http://SteveStedman.com
--    http://StedmanSolutions.com
--    http://DatabaseHealth.com
--    twitter:   @SqlEmt
--    email:  Steve@StedmanSolutions.com
-- 

-- to use the procecure, first run the whole script to create it, then 
--    uncomment the following line and replace database name with the name 
--    of your database. Do not include square brackets on the database name.
--   NOTICE: the actual script may need to be modified for updated paths or 
--     filenames.
--  examples
--EXECUTE [DBHealthHistory]..[dbhLogChain] 'DBHealthHistory';
---- or
--EXECUTE [DBHealthHistory]..[dbhLogChain] 'DBHealthHistory', 'display';
---- or 
--EXECUTE [DBHealthHistory]..[dbhLogChain] 'DBHealthHistory', 'FileInBackupDir';


-- to use the FileInBackupDir option you need to enable the 'Ole Automation Procedures'
-- uncomment and execute the following 8 lines.
--sp_configure 'show advanced options', 1; 
--GO 
--RECONFIGURE; 
--GO 
--sp_configure 'Ole Automation Procedures', 1; 
--GO 
--RECONFIGURE; 
--GO 

use DBHealthHistory; -- replace this with master, or your dba type database.

-- in case it exists and you need to drop it.
IF OBJECT_ID('dbhLogChain', 'P') IS NOT NULL
BEGIN
	DROP PROCEDURE [dbo].[dbhLogChain];
END
GO



CREATE PROCEDURE dbhLogChain @dbName VARCHAR(2048), 
						     @outputSqlScript VARCHAR(20) = 'display'
-- database name should be quoted with no square brackets
AS
BEGIN
-- ============================================================================
-- Copyright (c) 2015-2017 Steve Stedman 
--    http://DatabaseHealth.com
--    twitter:   @SqlEmt
--    email:  Steve@StedmanSolutions.com
-- ============================================================================

	SET NOCOUNT ON;


	IF LOWER(@dbName) = 'all_databases'
	BEGIN
		DECLARE @DatabaseID as INT;
		DECLARE @DatabaseName as NVARCHAR(50);
 		DECLARE @DatabaseCursor as CURSOR;
 		SET @DatabaseCursor = CURSOR FOR
		SELECT name, database_id
		  FROM sys.databases 
		  WHERE name not in ('tempdb');
 
		OPEN @DatabaseCursor;
		FETCH NEXT FROM @DatabaseCursor INTO @DatabaseName, @DatabaseID;
 
		WHILE @@FETCH_STATUS = 0
		BEGIN
			EXECUTE [DBHealthHistory]..[dbhLogChain] @DatabaseName, 'FileInBackupDir';
			FETCH NEXT FROM @DatabaseCursor INTO @DatabaseName, @DatabaseID;
		END
 
		CLOSE @DatabaseCursor;
		DEALLOCATE @DatabaseCursor;
	END
	ELSE
	BEGIN
		DECLARE @outputBuffer AS VARCHAR(MAX) = '';
		DECLARE @mostRecentBackupFile AS VARCHAR(MAX) = '';
		DECLARE @maxDifferential AS DATETIME;
		DECLARE @maxFull AS DATETIME;

		--Use to enter extra comments
		SET @outputBuffer += 'RAISERROR (''Caution running this script will overwrite the database [' + @dbName + '].'', 20, 1) WITH LOG; ' + char(13) + char(10);
		SET @outputBuffer += '--Make sure to change restore file paths if needed' + char(13) + char(10);
		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);

		SELECT TOP 1 @maxFull = bs.backup_finish_date
		  FROM msdb.dbo.backupset bs WITH (NOLOCK)
		 INNER JOIN msdb.dbo.backupmediafamily bmf WITH (NOLOCK)   
			ON bs.media_set_id = bmf.media_set_id
		 WHERE bmf.physical_device_name <> 'NUL' -- special case to deal with Veeam
		   AND NULLIF(bmf.physical_device_name, '') is not null
		   AND database_name = @dbName
		   AND bs.is_copy_only = 0
		   AND bs.is_snapshot = 0
		   AND bs.type = 'D' -- Full Database
		 ORDER by backup_set_id desc;  -- DatabaseHealth

		SELECT TOP 1 @maxDifferential = bs.backup_finish_date
		  FROM msdb.dbo.backupset bs WITH (NOLOCK)
		 INNER JOIN msdb.dbo.backupmediafamily bmf WITH (NOLOCK)   
			ON bs.media_set_id = bmf.media_set_id
		 WHERE bmf.physical_device_name <> 'NUL' -- special case to deal with Veeam
		   AND NULLIF(bmf.physical_device_name, '') is not null
		   AND database_name = @dbName
		   AND bs.is_copy_only = 0
		   AND bs.is_snapshot = 0
		   AND bs.type = 'I' -- Differential
		 ORDER by backup_set_id desc;  -- DatabaseHealth

		 IF @maxFull > @maxDifferential OR @maxDifferential is NULL
		 BEGIN
			SET @maxDifferential = @maxFull;
		 END

		SET @outputBuffer += 'use [master];' + char(13) + char(10);
		SET @outputBuffer += '--ALTER DATABASE ' + QUOTENAME(@dbName) + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;'  + char(13) + char(10);

		SELECT TOP 1 @outputBuffer += '--RESTORE DATABASE ' + QUOTENAME(database_name) + 
			   ' FROM DISK = ''' + bmf.physical_device_name + ''' WITH <<<<<MOVES>>>>>--REPLACE, NORECOVERY, STATS = 1;'  + char(13) + char(10),
			   @mostRecentBackupFile = bmf.physical_device_name
		  FROM msdb.dbo.backupset bs WITH (NOLOCK)
		 INNER JOIN msdb.dbo.backupmediafamily bmf WITH (NOLOCK)   
			ON bs.media_set_id = bmf.media_set_id
		 WHERE bmf.physical_device_name <> 'NUL' -- special case to deal with Veeam
		   AND NULLIF(bmf.physical_device_name, '') is not null
		   AND database_name = @dbName
		   AND bs.is_copy_only = 0
		   AND bs.is_snapshot = 0
		   AND bs.type = 'D' -- full database
		 ORDER by backup_set_id desc;  

		IF NULLIF(@mostRecentBackupFile, '') IS NOT NULL
		BEGIN
		
			DECLARE @moves AS VARCHAR(MAX);
			SET @moves = char(13) + char(10);
			DECLARE @Table TABLE 
			(
				[LogicalName] varchar(128),
				[PhysicalName] varchar(128), 
				[Type] varchar, 
				[FileGroupName] varchar(128), 
				[Size] varchar(128), 
				[MaxSize] varchar(128), 
				[FileId] varchar(128), 
				[CreateLSN] varchar(128), 
				[DropLSN] varchar(128), 
				[UniqueId] varchar(128), 
				[ReadOnlyLSN] varchar(128), 
				[ReadWriteLSN] varchar(128), 
				[BackupSizeInBytes] varchar(128), 
				[SourceBlockSize] varchar(128), 
				[FileGroupId] varchar(128), 
				[LogGroupGUID] varchar(128), 
				[DifferentialBaseLSN] varchar(128), 
				[DifferentialBaseGUID] varchar(128), 
				[IsReadOnly] varchar(128), 
				[IsPresent] varchar(128), 
				[TDEThumbprint] varchar(128)
				--,[SnapshotUrl] nvarchar(360)  --- ONLY SQL 2016 or NEWER
			);
		
			INSERT INTO @table
			EXEC('RESTORE FILELISTONLY  FROM DISK=''' + @mostRecentBackupFile  + '''   ');

			SELECT @moves = @moves + '--MOVE ''' + LogicalName + ''' TO ''' + PhysicalName + ''',' + char(13) + char(10) from @Table;

			SET @outputBuffer = REPLACE(@outputBuffer, '<<<<<MOVES>>>>>', @moves);

			SELECT TOP 1 @outputBuffer += '--RESTORE DATABASE ' + QUOTENAME(database_name) + 
				   ' FROM DISK = ''' + bmf.physical_device_name + ''' WITH NORECOVERY, STATS = 1; -- Differential'  + char(13) + char(10),
				   @mostRecentBackupFile = bmf.physical_device_name
			  FROM msdb.dbo.backupset bs WITH (NOLOCK)
			 INNER JOIN msdb.dbo.backupmediafamily bmf WITH (NOLOCK)   
				ON bs.media_set_id = bmf.media_set_id
			 WHERE bmf.physical_device_name <> 'NUL' -- special case to deal with Veeam
			   AND NULLIF(bmf.physical_device_name, '') is not null
			   AND database_name = @dbName
			   AND bs.backup_start_date > @maxFull
			   AND bs.is_copy_only = 0
			   AND bs.is_snapshot = 0
			   AND bs.type = 'I' -- differential
			 ORDER by backup_set_id DESC;  

			 SELECT TOP 1 @mostRecentBackupFile = bmf.physical_device_name
			  FROM msdb.dbo.backupset bs WITH (NOLOCK)
			 INNER JOIN msdb.dbo.backupmediafamily bmf WITH (NOLOCK)   
				ON bs.media_set_id = bmf.media_set_id
			 WHERE bmf.physical_device_name <> 'NUL' -- special case to deal with Veeam
			   AND NULLIF(bmf.physical_device_name, '') is not null
			   AND database_name = @dbName
			   AND bs.backup_start_date > @maxDifferential
			   AND bs.is_copy_only = 0
			   AND bs.is_snapshot = 0
			   AND bs.type = 'L' -- log backup
			 ORDER by backup_set_id DESC;  

			SELECT @outputBuffer += '--RESTORE DATABASE ' + QUOTENAME(database_name) + 
				   ' FROM DISK = ''' + bmf.physical_device_name + ''' WITH NORECOVERY, STATS = 10; -- Log Backup'  + char(13) + char(10)
			  FROM msdb.dbo.backupset bs WITH (NOLOCK)
			 INNER JOIN msdb.dbo.backupmediafamily bmf WITH (NOLOCK)   
				ON bs.media_set_id = bmf.media_set_id
			 WHERE bmf.physical_device_name <> 'NUL' -- special case to deal with Veeam
			   AND NULLIF(bmf.physical_device_name, '') is not null
			   AND database_name = @dbName
			   AND bs.backup_start_date > @maxDifferential
			   AND bs.is_copy_only = 0
			   AND bs.is_snapshot = 0
			   AND bs.type = 'L' -- log backup
			 ORDER by backup_set_id ASC;  

			SET @outputBuffer += '--RESTORE DATABASE ' + QUOTENAME(@dbName) + ' WITH RECOVERY;'  + char(13) + char(10);
	
			SET @outputSqlScript = LOWER(@outputSqlScript);
			IF @outputSqlScript = 'display'
			BEGIN
				PRINT @outputBuffer;
			END

			IF @outputSqlScript = 'fileinbackupdir'
			BEGIN
				DECLARE @ole INTEGER;
				DECLARE @fileID INTEGER;
		
				SET @mostRecentBackupFile = @mostRecentBackupFile + '.restore_script.sql';
				--print @mostRecentBackupFile;
			
				EXECUTE sp_OACreate 'Scripting.FileSystemObject', @ole OUT;
				EXECUTE sp_OAMethod @ole, 'OpenTextFile', @fileID OUT, @mostRecentBackupFile, 2, 1;
				EXECUTE sp_OAMethod @fileID, 'WriteLine', Null, @outputBuffer;
				EXECUTE sp_OADestroy @fileID;
				EXECUTE sp_OADestroy @ole;
			END
		END
	END
END
GO
EXECUTE [DBHealthHistory]..[dbhLogChain] 'ALL_DATABASES', 'FileInBackupDir';

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!