UNDERCOVER TOOLBOX: sp_RestoreScript – A Painless way to generate SQL Server database restore scripts.

sp_restorescript

I’m sure we’ve all been there, we need to restore a database to 11:34am, four days ago. What’s the first step?  To go off hunting down the relevant full and differential backups and then figure out what transaction logs we need to play in and in what order.  Well here at SQL Undercover we’ve got the solution to all your restoration woes, the latest addition to the Undercover Toolbox, sp_RestoreScript.

sp_RestoreScript will do all the hard work for you, it’ll hunt down all the backup files that you need to restore your database to a specific time and present you with a nice, simple script to run based on the parameters that you give it.

Scroll down the page for examples and the source code

Prerequisites

Please note that if you’re running on a version of SQL Server prior to 2016 or have databases in a compatibility level earlier than 130, you’ll need our fn_SplitString

Parameter List

@DatabaseName
A comma delimited string containing all databases to restore DEFAULT: Current Database

@RestoreAsName
A comma delimited sting of names to restore databases as, the number of names should correspond to the number of @RestoreAsName.

@RestoreToDate
See @PointInTime for usage of @RestoreToDate. – DEFAULT: GETDATE()

@FirstLogToRestore
If the ‘LogsOnly’ restore option has been selected, this is the date of the first log to be restored.  This can’t be left NULL if ‘LogsOnly’ has been selected.  If any other option has been selected it will be ignored.

@RestoreOptions
‘ToLog’ – Script will generate statements including full, differential and log backups – DEFAULT
‘ToDiff’   Script will only include full and differential backups ‘ToFull’Script will only include a full backup
ToFull‘ Script will only include a full backup
‘LogsOnly’ – Script will only include log backups between @FirstLogToRestore and @RestoreToDate

@PointInTime  
1 – Script will restore to a point in time specified in @RestoreToDate
0 – Script will to last backup before the date\time specified in @RestoreToDate – DEFAULT

@WithMoveDataPath
WITH MOVE Path to move data files to. – DEFAULT: original path

@WithMoveLogPath
WITH MOVE Path to move log files to. – DEFAULT:@ original path

@Replace
1 – Databases will be restored WITH REPLACE
0 – Database won’t be restored WITH REPLACE – DEFAULT

@NoRecovery
1- Last file will be restored with NORECOVERY, leaving the database in ‘restoring’ state
0 – Last file will be restored with RECOVERY and the database brought online – DEFAULT

Examples

Generate a restore script for the current database


EXEC sp_RestoreScript

Generate a restore script for a specified database


EXEC sp_RestoreScript
@Databasename = 'SQLUndercover'

Generate a restore script for a multiple databases

EXEC sp_RestoreScript
@Databasename = 'SQLUndercover,SomeOtherDB'

Generate a restore script and rename a database

EXEC sp_RestoreScript
@Databasename = 'SQLUndercover',
@RestoreAsName = 'SQLUndercover_restored'

Restore a database to a point in time

EXEC sp_RestoreScript
@Databasename = 'SQLUndercover',
@RestoreToDate = '2017-06-29 13:28:59',
@PointInTime = 1

Generate a List of Restore Statements For Transaction Log Backups Between Specified Dates

EXEC sp_RestoreScript
@Databasename = 'SQLUndercover',
@FirstLogToRestore = '2017-06-20 13:00:00',
@RestoreToDate = '2017-06-29 13:00:00',
@RestoreOptions = 'LogsOnly'

Restore Databases WITH MOVE

EXEC sp_RestoreScript
@Databasename = 'SQLUndercover',
@WithMoveDataPath = 'P:\SQLData',
@WithMoveLogPath = 'Q:\SQLLogs'

Source Code


  /*                                                                                                                      

              @     ,@
             #@@   @@@
             @@@@@@@@@;
             @@@@@@@@@@
            :@@@@@@@@@@
            @@@@@@@@@@@
            @@@@@@@@@@@;
            @@@@@@@@@@@@
            @@@@@@@@@@@@
           `+@@@@@@@@@@+                                                                                                

         .@@`           #@,
     .@@@@@@@@@@@@@@@@@@@@@@@@:
   @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@        @@     @@   @@      #@   @           @
  #@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@      @@@@   @@@@  @#      #@   @           @
    ;@@@@@@@@@@@@@@@@@@@@@@@@@@'        @     @   @# @#      #@   @ #@@@   @@@@  @@@  @@@@@  @@@   @@  @   @  @@   @ @@
        .+@@@@@@@@@@@@@@@@+.            @@@@  @   @@ @#      #@   @ #@  @ @@  @  @  @  @@   @  @  @ `@  @ @  @  @  @@
       '`                  `,#           @@@@ @   @@ @#      #@   @ #@  @ @#  @ @@@@@  @    @     @  @  @ @  @@@@  @`
     ,@@@@ '@@@@@@@@@@@@@ .@@@@;           @  @   @@ @#      #@   @ #@  @ @@  @ @@     @ `  @     @  @  @@@  @     @
    #@@@@@@ @@@@@  +@@@@  +@@@@@@       @@@@   @@@@  @@@@@   `@@@@@ #@  @ #@ @@  @  @  @    @@ @  @  @   @   @  @  @
   @@@@@@@@  ,#.    `#;   @@@@@@@@'      @@     @@   @@@@@     @@,  #@  @  @@ @   @@  @@     #@    @@    @    @@   @
  ;#@@@@@@@@             @@@@@@@@@#,              @
       ,@@@@+           @@@@@+`
          .@@`        `@@@@                                          © www.sqlundercover.com
         +@@@@        @@@@@+
        @@@@@@@      @@@@@@@@#
         @@@@@@@    @@@@@@,
           :@@@@@' ;@@@@`
             `@@@@ @@@+
                @#:@@
                  @@
                  @`
                  #                                                                                                     

sp_RestoreScript 1.1
Written By David Fowler
29 June 2017
Generate a set of backup commands to restore a database(s) to a specified time         

26 July 2017
A bug with the cursor when running on versioons of SQL pre-2016 has been fixed                                                      

Parameters
==========

@DatabaseName -		A comma delimited list of databases to restore - DEFAULT: Current Database

@RestoreAsName -	A comma delimited list of name to restore databases as, the number of should correspond to the number of
					databases in @DatabaseName.  

@RestoreToDate-		See @PointInTime for useage of @RestoreToDate. - DEFAULT: GETDATE()

@FirstLogToRestore-	If 'LogsOnly' restore option has been selected, this is the date of the first log to be restored. This
					can't be left NULL if 'LogsOnly' has been selected.  If any other option has been selected it wil be ignored

@RestoreOptions-	'ToLog' - Script will generate statements including full, differential and log backups - DEFAULT
					'ToDiff'   Script will only include full and differential backups
					'ToFull'   Script will only include a full backup
					'LogsOnly' - Script will only include log backups between @FirstLogToRestore and @RestoreToDate

@PointInTime -		1 - Script will restore to a point in time specified in @RestoreToDate
					0 - Script will to last backup before the date\time specified in @RestoreToDate - DEFAULT

@WithMoveDataPath -	WITH MOVE Path to move data files to. - DEFAULT: original path

@WithMoveLogPath -	WITH MOVE Path to move log files to. - DEFAULT:@ original path

@Replace -			1 - Databases will be restored WITH REPLACE
					0 - Database won't be restored WITH REPLACE - DEFAULT

@NoRecovery -		1 - Last file will be restored with NORECOVERY, leaving the database in 'restoring' state
					0 - Last file will be restored with RECOVERY and the database brought online - DEFAULT

Full documentation and examples can be found at www.sqlundercover.com
*/
USE MASTER
GO

CREATE PROC sp_RestoreScript
(
@DatabaseName VARCHAR(3000) = NULL,
@RestoreAsName VARCHAR(3000) = NULL,
@RestoreToDate DATETIME = NULL,
@FirstLogToRestore DATETIME = NULL,
@RestoreOptions VARCHAR(11) = 'ToLog',
@PointInTime BIT = 0,
@WithMoveDataPath VARCHAR(3000) = NULL,
@WithMoveLogPath VARCHAR(3000) = NULL,
@Replace BIT = 0,
@NoRecovery BIT = 0
)

AS

BEGIN

DECLARE @WithMove VARCHAR(3000)

SET NOCOUNT ON
--Check that @RestoreOptions is a valid value
IF @RestoreOptions NOT IN ('ToLog','ToDiff','ToFull','LogsOnly')
RAISERROR (N'Invalid Restore Option specified, please use ToLog, ToDiff, ToFull or LogsOnly' , 15, 1)

--Check that both 'WithMove' parameters are either both null or both hold a value (why doens't SQL Server give us an XOR?)
IF ((@WithMoveDataPath IS NULL) AND (@WithMoveLogPath IS NOT NULL))
	OR
	((@WithMoveDataPath IS NOT NULL) AND (@WithMoveLogPath IS NULL))
RAISERROR (N'The ''WithMove'' parameters either both must be NULL or both must hold a value', 15,1) 

--set compatibility mode
DECLARE @compatibility BIT

--set compatibility to 1 if server version includes STRING_SPLIT
SELECT	@compatibility = CASE
			WHEN SERVERPROPERTY ('productversion') >= '13.0.4001.0' AND Compatibility_Level >= 130 THEN 1
			ELSE 0
		END
FROM sys.databases
WHERE name = DB_NAME()

--drop temp tables
IF  OBJECT_ID('tempdb..#BackupCommands') IS NOT NULL
	DROP TABLE #BackupCommands
CREATE TABLE #BackupCommands
(backup_start_date DATETIME, DBName VARCHAR(255), command VARCHAR(1000))

IF OBJECT_ID('tempdb..#BackupCommandsFinal') IS NOT NULL
	DROP TABLE #BackupCommandsFinal
CREATE TABLE #BackupCommandsFinal
(backup_start_date DATETIME, DBName VARCHAR(255), command VARCHAR(1000))

--remove any spaces in list of databases
SET @DatabaseName = REPLACE(@DatabaseName, ' ','')
SET @RestoreAsName = REPLACE(@RestoreAsName, ' ','')

--@PointInTime can only be true if @RestoreOptions is either 'ToLog' or 'LogsOnly'
IF (@PointInTime = 1) AND (@RestoreOptions NOT IN ('ToLog','LogsOnly'))
BEGIN
RAISERROR (N'Point in time restore is not possible with selected restore options.  @PointInTime has been changed to 0', 15,1)
SET @PointInTime = 0
END

--If @RestoreOptions is 'LogsOnly', a RestoreToDate value must be specified
IF (@RestoreOptions = 'LogsOnly') AND (@FirstLogToRestore IS NULL)
RAISERROR (N'When @RestoreOptions = LogsOnly, a @FirstLogToRestore date must be specified', 15,1) 

--Set default value for @RestoreToDate if unspecified
IF (@RestoreToDate IS NULL)
SET @RestoreToDate = GETDATE()

--Set default value for @DatabaseName if unspecified
IF (@DatabaseName IS NULL)
SET @DatabaseName = DB_NAME()

--Declare cursor containing database names
--if compatibility mode = 1 then it's safe to use STRING_SPLIT, otherwise use fn_SplitString
IF (@Compatibility = 1)
BEGIN
	--raise an error if there's a mismatch in the number of databases in @DatabaseName and @RestoreAsName
	IF ((SELECT COUNT(*) FROM  STRING_SPLIT(@DatabaseName,','))
		!= (SELECT COUNT(*) FROM  STRING_SPLIT(@RestoreAsName,',')))
		AND @RestoreAsName IS NOT NULL
	RAISERROR (N'There is a mismatch in the number of databases in @DatabaseName and @RestoreAsName', 15,1)

	DECLARE DatabaseCur CURSOR FOR
	SELECT SourceDatabase.value AS SourceDatabase,DestinationDatabase.value AS DestinationDatabase
	FROM
		(SELECT value, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) RowNumber
		FROM STRING_SPLIT(@DatabaseName,',') ) SourceDatabase
		LEFT JOIN
		(SELECT value, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) RowNumber
		FROM STRING_SPLIT(@RestoreAsName,',') ) DestinationDatabase
		ON SourceDatabase.RowNumber = DestinationDatabase.RowNumber
END
ELSE BEGIN
	--raise an error if there's a mismatch in the number of databases in @DatabaseName and @RestoreAsName
	IF ((SELECT COUNT(*) FROM  fn_SplitString(@DatabaseName,','))
		!= (SELECT COUNT(*) FROM  fn_SplitString(@RestoreAsName,',')))
		AND @RestoreAsName IS NOT NULL
	RAISERROR (N'There is a mismatch in the number of databases in @DatabaseName and @RestoreAsName', 15,1)

	DECLARE DatabaseCur CURSOR FOR
	SELECT SourceDatabase.StringElement AS SourceDatabase,DestinationDatabase.StringElement AS DestinationDatabase
	FROM
		(SELECT StringElement, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) RowNumber
		FROM fn_SplitString(@DatabaseName,',') ) SourceDatabase
		LEFT JOIN
		(SELECT StringElement, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) RowNumber
		FROM fn_SplitString(@RestoreAsName,',') ) DestinationDatabase
		ON SourceDatabase.RowNumber = DestinationDatabase.RowNumber
END

--open cursor
OPEN DatabaseCur
FETCH NEXT FROM DatabaseCur INTO @DatabaseName, @RestoreAsName

WHILE @@FETCH_STATUS = 0
BEGIN

	--Get last full backup for required timeframe
	IF (@RestoreOptions IN ('PointInTime','ToLog','ToDiff','ToFull'))
	BEGIN
		INSERT INTO #BackupCommands
		SELECT TOP 1 backupset.backup_start_date, @DatabaseName AS DBName,
					'RESTORE DATABASE ' + COALESCE(QUOTENAME(@RestoreAsName), QUOTENAME(@DatabaseName)) + ' FROM DISK = ''' + mediafamily.physical_device_name + ''' WITH NORECOVERY, FILE = ' + CAST(position AS VARCHAR) AS Command
		FROM msdb.dbo.backupset backupset
		INNER JOIN msdb.dbo.backupmediafamily mediafamily ON backupset.media_set_id = mediafamily.media_set_id
		WHERE backupset.database_name = @DatabaseName
		AND backupset.backup_start_date < @RestoreToDate
		AND backupset.type = 'D'
		ORDER BY backupset.backup_start_date DESC
	END

	--if Replace is set, add to statement
	IF @Replace = 1
	BEGIN
		UPDATE #BackupCommands
		SET command = command + ', REPLACE'
		WHERE DBName = @DatabaseName
	END

	--if WithMove parameters are set, create WITH MOVE statements
	IF (@WithMoveDataPath IS NOT NULL) AND (@WithMoveLogPath IS NOT NULL)
	BEGIN
		--generate MOVE statement
		DECLARE @WithMoveCmd VARCHAR(3000)

		SET @WithMoveCmd = ','

		SELECT @WithMoveCmd = @WithMoveCmd + STUFF((SELECT ',' + ' MOVE ''' + name + ''' TO ''' + REPLACE(physical_name,REVERSE(SUBSTRING(REVERSE(physical_name),CHARINDEX('\',REVERSE(physical_name),0), LEN(physical_name))),@WithMoveDataPath) + ''''
		FROM sys.master_files
		WHERE database_id = DB_ID(@DatabaseName)
		AND type_desc = 'ROWS'
		FOR XML PATH('')) ,1,1,'')

		SET @WithMoveCmd = @WithMoveCmd + ','

		SELECT @WithMoveCmd = @WithMoveCmd + STUFF((SELECT ',' +  'MOVE ''' + name + ''' TO ''' + REPLACE(physical_name,REVERSE(SUBSTRING(REVERSE(physical_name),CHARINDEX('\',REVERSE(physical_name),0), LEN(physical_name))),@WithMoveLogPath) + ''''
		FROM sys.master_files
		WHERE database_id = DB_ID(@DatabaseName)
		AND type_desc = 'LOG'
		FOR XML PATH('')) ,1,1,'')

		--append MOVE statement to backup command
		UPDATE #BackupCommands
		SET command = command + @WithMoveCmd
		WHERE DBName = @DatabaseName
	END

	--Get last diff for required timeframe
	IF (@RestoreOptions IN ('PointInTime','ToLog','ToDiff'))
	BEGIN
		INSERT INTO #BackupCommands
		SELECT TOP 1 backupset.backup_start_date, @DatabaseName AS DBName,
				'RESTORE DATABASE ' + COALESCE(QUOTENAME(@RestoreAsName), QUOTENAME(@DatabaseName)) + ' FROM DISK = ''' + mediafamily.physical_device_name + ''' WITH NORECOVERY, FILE = ' + CAST(position AS VARCHAR)
		FROM msdb.dbo.backupset backupset
		INNER JOIN msdb.dbo.backupmediafamily mediafamily ON backupset.media_set_id = mediafamily.media_set_id
		WHERE backupset.database_name = @DatabaseName
		AND backupset.backup_start_date >
					(SELECT backup_start_date FROM #BackupCommands)
		AND backupset.backup_start_date < @RestoreToDate
		AND backupset.type = 'I'
		ORDER BY backupset.backup_start_date DESC
	END

	--Get all log backups since last full or diff
	IF (@RestoreOptions IN ('ToLog','LogsOnly'))
	INSERT INTO #BackupCommands
	SELECT backupset.backup_start_date, @DatabaseName AS DBName,
			'RESTORE LOG ' + COALESCE(QUOTENAME(@RestoreAsName), QUOTENAME(@DatabaseName)) + ' FROM DISK = ''' + mediafamily.physical_device_name + ''' WITH NORECOVERY, FILE = ' + CAST(position AS VARCHAR)
	FROM msdb.dbo.backupset backupset
	INNER JOIN msdb.dbo.backupmediafamily mediafamily ON backupset.media_set_id = mediafamily.media_set_id
	WHERE backupset.database_name = @DatabaseName
	AND backupset.backup_start_date >
				(SELECT COALESCE(MAX(backup_start_date),@FirstLogToRestore) FROM #BackupCommands)
	AND backupset.backup_start_date < @RestoreToDate
	AND backupset.type = 'L'
	ORDER BY backupset.backup_start_date DESC

	--Get point in time if enabled
	IF (@PointInTime = 1) AND (EXISTS (SELECT * FROM #BackupCommands))
	BEGIN
		INSERT INTO #BackupCommands
		SELECT TOP 1 backupset.backup_start_date, @DatabaseName AS DBName,
				'RESTORE LOG ' + COALESCE(QUOTENAME(@RestoreAsName), QUOTENAME(@DatabaseName)) + ' FROM DISK = ''' + mediafamily.physical_device_name + ''' WITH NORECOVERY, FILE = ' + CAST(position AS VARCHAR) + ', STOPAT = ''' + CAST(@RestoreToDate AS VARCHAR) + '''' AS Command
		FROM msdb.dbo.backupset backupset
		INNER JOIN msdb.dbo.backupmediafamily mediafamily ON backupset.media_set_id = mediafamily.media_set_id
		WHERE backupset.database_name = @DatabaseName
		AND backupset.backup_start_date > @RestoreToDate
		AND backupset.type = 'L'
		ORDER BY backupset.backup_start_date
	END

	INSERT INTO #BackupCommandsFinal
	SELECT * FROM #BackupCommands

	TRUNCATE TABLE #BackupCommands

	FETCH NEXT FROM DatabaseCur INTO @DatabaseName, @RestoreAsName
END

CLOSE DatabaseCur
DEALLOCATE DatabaseCur

IF @NoRecovery = 0  --if restore with no recovery is off, remove NORECOVERY from the last restore command
BEGIN
	UPDATE #BackupCommandsFinal
	SET command = REPLACE(command,'NORECOVERY','RECOVERY')
	WHERE backup_start_date = (SELECT MAX(backup_start_date) FROM #BackupCommandsFinal)
END

SELECT backup_start_date, DBName, command
FROM #BackupCommandsFinal
ORDER BY DBName,backup_start_date

END

2 thoughts on “UNDERCOVER TOOLBOX: sp_RestoreScript – A Painless way to generate SQL Server database restore scripts.

Add yours

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: