Home › SQL Server › Building a SQL Restore Script When Your Backup Runs

Building a SQL Restore Script When Your Backup Runs

Posted on Posted in SQL Server No Comments Tagged with , , , , , , ,
Download PDF

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:

SteveStedman5
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!

Leave a Reply

Your email address will not be published. Required fields are marked *

*