dbo.sp_RestoreScript.sql

/*
Original link: https://sqlundercover.com/2017/06/29/undercover-toolbox-sp_restorescript-a-painless-way-to-generate-sql-server-database-restore-scripts


sp_RestoreScript 1.0
Written By David Fowler
29 June 2017
Generate a set of backup commands to restore a database(s) to a specified time                                                               
 
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)
 
    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