sp_RestoreGene

Double click to highlight it all, copy / paste in a query analyser window on the primary server (SQL Server 2005 or higher) where the backup history exists.  Copy / Paste only works with Chrome or newer versions of Internet Explorer, a line feed/character return problem corrupts the T-SQL if copied using Internet Explorer 8 or lower.


USE [master] 
GO 
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'sp_RestoreGene') 
EXEC ('CREATE PROC dbo.sp_RestoreGene AS SELECT ''stub version, to be replaced''') 
GO 
                      
/*********************************************************************************************
Restore Generator v8.10 (2018-07-25)
(C) 2012, Paul Brewer
                      
Feedback: paulbrewer@yahoo.co.uk
Updates: https://paulbrewer.wordpress.com/sp_restoregene/
User Guide: https://paulbrewer.wordpress.com/2016/08/05/restoregeneversion6/
                 
This procedure queries msdb database backup history and database file details in master.
It builds and returns RESTORE DATABASE commands as its result set, it does not execute the commands.
                      
CHANGE LOG:
December 23, 2012   - V1.01 - Release
January 4, 2013     - V1.02 - LSN Checks + Bug fix to STOPAT date format
January 11, 2013    - V1.03 - SQL Server 2005 compatibility (backup compression problem) & @StandbyMode
January 14, 2013    - V1.04 - WITH COPY and up to 10 striped backup files
January 15, 2013    - V1.05 - Format of constructed restore script, enclose database name in [ ]
February 7, 2013    - V1.06 - Modified WHERE device_type IN (7,102,2,9)
May 26, 2013        - V1.07 - Various changes for PoSh Driver Script compatibility
October 14, 2013    - V1.08 - Rename parameters, more meaningful names
October 15, 2013    - V2.00 - Add 2nd CTE for striped backup files and remove repeating calls to CTE
November 5, 2013    - V2.01 - Secondary .ndf files only included if @WithMoveDataFiles NOT NULL
                            - Extended artificial LSN used for sequencing WITH RECOVERY/CHECKDB to 21 characters
                            - Include database name in results for logging in PoSh
May 23, 2014        - V3.0  - Improved logging and error handling, add 'LogShippingVariableDeclare' parameter
                                Default is Yes when called from query analyser, set to No when called by PoSh
May 30, 2014        - V3.1  - Restructured CTE, all predicates moved to the CTE
June 6, 2014        - V3.2  - Add 'LogShippingStartTime' parameter for PowerShell script.
                                To skip subsequent full and diff backups when restoring Logs in PoSh log shipping mode
                            - Add 'Target Restore Database Name' parameter
                                If restoring a specific database, allows overriding restored name
                                Probably used in conjunction with a @WithMove override of data and log files to a different folder.
                            - Add LogShippingLastLSN parameter, used to filter results sent to ps_LogShippingLight
June 10, 2014       - V3.3  - Standardize Log Shipping Parameter Names & make &msg variable declare unique
June 27, 2014       - V3.31 - Diff restore bug fix
July 13, 2014       - V3.32 - Feedback from Tony Davis
                                Rename framework to memorable name 'RestoreGene'
                                Paramater for WITH REPLACE
August 5, 2014      - V3.4  - Remove TRY CATCH blocks from log restores and checkdb
                            - Remove CTE's,use #temp tables, suggested by S.Stresser
August 25, 2014     - V3.5  - goestock suggestion - Include device type 7 for AWS EC2 backups
August 29th, 2014   - V3.6  - Richard found and fixed a problem with variable declare where database name has a space or full stop.
July 28, 2015       - V3.7  - Luke Sarro, modifications for case sensitivity & parameter sniffing, a massive amount of work done.
August 28, 2015     - V3.8  - Luke Sarro modifications, recovery fork management. supports forking in recovery plans when constructing a restore script. 
                              Only tested where a single fork exists, IE a restore was done to a historic point in time, with recovery, then subsequent log backups are taken. 
                              When a fork exists the restore script will follow the new path for subsequent differential and log backups taken after the forking point.
                              If no rbackup forks exist, the procedure works as before.
September 02 2015   - V3.9  - SQLMongo modification to replace hyphens in database name
September 04, 2015  - V4.0  - Domingo modification, check for default backup path = ''
September 29, 2015  - V4.2  - Fixes bug identifying full backups taken after a recovery fork, has introduced code duplication to be removed later but fixes problem 
October 3, 2015     - V4.3  - Remove code duplication introduced in V4.2      
*
June 3rd, 2016      - V5.0  - Feedback from Brent Ozar related viewing spike:
                    - V5.0  - Reintroduce SQL Server 2005 compatibility - Bob McLaren - Declaring and assigning variables on same line doesn't work with SQL Server 2005.
                    - V5.0  - Override data and log file paths if overriding restored database name
                 
June 11th, 2016     - V5.1  - Azure http url restore support, mick@sqlfairy.com
                    - V5.1  - Restore Script find and replacement variables, mick@sqlfairy.com
               
June 17th, 2016     - V5.2 - Removed TRY CATCH block in log restore commands
June 26th, 2016     - V5.3 - Include FILESTREAM files in WITH MOVE, jgkiran
July 3rd, 2016      - V5.4 - New parameter for FILESTREAM files, for WITH MOVE parameter, jgkiran
July 14rd, 2016     - V5.5 - Rename rollback files when standby = 1 to allow multiple concurrent ps_RestoreGene invocations, jgkiran
        
August 8th, 2016    - V5.6 - Accepts a comma separated variable list of databases to restore, suggested Stephen T 
                            - Got permission to reuse the 'SELECT DATABASES' code snippet from ola hallengren - https://ola.hallengren.com/
August 8th, 2016    - V5.7 - Make restore to new database, secondary file names, more meaningful 
August 9th, 2016    - V5.8 - Include the WITH MOVE statements on the same results line as the corresponding RESTORE DATABASE statement, flatten the result set to simplify the PoSh driver 
August 9th, 2016    - V5.9 - New Parameter, suppress WITH MOVE statements is default, parameter allows override to include them in the script. 
     
August 13th, 2016    - V6.0
                            - Add parameter option to pivot WITH MOVE secondary files to a new result set line. Not compatible with PoSh driver, useful with large, complex databases. 
                            - Add a parameter option to include just the restore script in the result set. Not compatible with PoSh driver, useful as SQL Agent Job step output.
    
August 14th, 2016    - V6.1 - Remove parameter @UseDefaultDatabaseBackupPath , no longer used  
August 15th, 2016    - V6.2 - @TargetDatabase parameter, file rename tidy  
August 17th, 2016    - V6.3 - Edge case fix to fork point LSN
August 19th, 2016    - V6.4 - Publish SQL Server Central
September 4th, 2016  - V6.41 - PoSh Driver Last LSN minor fix. - Published SQL Server Central
September 9th, 2016  - V6.42 - Fix for multiple log files with move, J.Peablles 
September 9th, 2016  - V6.43 - Fix for mirrored backups, C.Richardson
September 9th, 2016  - V6.44 - Mod to log file LSN selection, include if matches diff, R.Devries 
September 30th, 2016 - V6.45 - Improvements to the output generated when @RestoreScriptOnly = 1, John Lee
September 30th, 2016 - V6.5  - New 'Drop Database After Restore' parameter for use with CHECKDB and automated backup verification, John Lee
  
November 5th, 2016  - V6.51  - Increase length of @Database parameter to allow for more comma seperated database names
November 5th, 2016  - V6.52  - Edge case log exclusion criteria mod, R.Devries 
November 5th, 2016  - V6.53  - New parameter option to include ALTER DATABASE @database SET SINGLE_USER WITH ROLLBACK IMMEDIATE, Hakan Ekman
November 5th, 2016  - V6.54  - New parameter option to exclude differential and log backups (presumaby for development environment refreshes), Mehedi Amin
  
November 19th, 2016 - V6.55  - Remove semi colon from start of T-SQL restore command strings, serves no purpose
November 19th, 2016 - V6.56  - Remove variable declare, log reference and RAISERROR, serves no purpose and generates an error in edge cases ( my_database and my-database ), Lars Rasmussen
November 19th, 2016 - V6.57  - Parameters options for device types to include, Lars Rasmussen 
  
December 3rd, 2016  - V6.58  - Remove TIME function from STANDBY mode file name construct, for SQL Server 2005 compatibility, Erwin Louwerse
March 26th, 2017    - V6.59  - Significant performance improvements where an instance has many databases, Simone Bizzotto
  
June 24th, 2017     - V6.70   - Case Sensitive Collation Database Compatibility - M.Pollock 
September 9th, 2017 - V6.71   - Device Type filter fix, LostInEurope01
November 11th, 2017 - V6.72   - Mod to log file selection, R.Devries
                              - Increase @Databases variable length, Bob
Febuary 14th, 2018  - V6.73   - Add escape characters to database name in SET SINGLE_USER, Rod
Febuary 16th, 2018  - V7.0    - Increase support for stripped backup files from 10 to 15, Mehedi 
Febuary 17th, 2018  - V8.0    - Extensive changes to the backup fork handling, Mike 
March 30th, 2018    - V8.04   - Code refactor
May 25th, 2018      - V8.05   - Missing exclusion of copy only backups reintroduced - Andy
August 25th, 2018   - V8.10   - Enhancements to the @ExcludeDiffAndLogBackups parameters, Bob and Chris F
                              - Change from parameter data type from bit to int
							  - 0 (default) = full, diff and logs, 1 = full only, 2 = full and diff, 3 = full and logs 
							  - Existing BIT data type functionality retained and extended by new values 2 and 3.
 
*     
********************************************************************************************/ 
                                 
ALTER PROC [dbo].[sp_RestoreGene]
(
    @Database VARCHAR(MAX) = NULL,
    @TargetDatabase SYSNAME = NULL,
    @WithMoveDataFiles VARCHAR(2000) = NULL,
    @WithMoveLogFile  VARCHAR(2000) = NULL,
    @WithMoveFileStreamFile VARCHAR(2000) = NULL,
    @FromFileFullUNC VARCHAR(2000) = NULL,
    @FromFileDiffUNC VARCHAR(2000) = NULL,
    @FromFileLogUNC VARCHAR(2000) = NULL,
    @StopAt DATETIME = NULL,
    @StandbyMode BIT = 0,
    @IncludeSystemDBs BIT = 0,
    @WithRecovery BIT = 0,
    @WithCHECKDB BIT = 0,
    @WithReplace BIT = 0,
   
    -- Parameters for PowerShell script use only
    @LogShippingStartTime DATETIME = NULL,
    @LogShippingLastLSN VARCHAR(25) = NULL,
    -- Parameters for PowerShell script use only
   
    @BlobCredential VARCHAR(255) = NULL, --Credential used for Azure blob access
    @RestoreScriptReplaceThis NVARCHAR(255) = NULL,
    @RestoreScriptWithThis NVARCHAR(255) = NULL,
    @SuppressWithMove BIT = 1,
    @PivotWithMove BIT = 0,
    @RestoreScriptOnly BIT = 0,
    @DropDatabaseAfterRestore BIT = 0,
    @SetSingleUser BIT = 0,
    @ExcludeDiffAndLogBackups INT = 0,
  
    -- Options to exclude device types
    @IncludeDeviceType7 BIT = 1,
    @IncludeDeviceType102 BIT = 1,
    @IncludeDeviceType2 BIT = 1,
    @IncludeDeviceType9 BIT = 1
)
AS
BEGIN
   
    SET NOCOUNT ON; 
   
    -- Avoid Parameter Sniffing Problems
    DECLARE @Database_ VARCHAR(MAX);
    SET @Database_ = @Database;
    DECLARE @TargetDatabase_ SYSNAME;
    SET @TargetDatabase_ = @TargetDatabase;
   
    -- Special handling for PoSh Driver
    DECLARE @WithMoveDataFiles_ VARCHAR(2000);
    IF @WithMoveDataFiles <> '' SET @WithMoveDataFiles_= @WithMoveDataFiles;
    DECLARE @WithMoveLogFile_  VARCHAR(2000);
    IF @WithMoveLogFile <> '' SET @WithMoveLogFile_= @WithMoveLogFile;
    DECLARE @WithMoveFileStreamFile_  VARCHAR(2000);
    IF @WithMoveFileStreamFile <> '' SET @WithMoveFileStreamFile_= @WithMoveFileStreamFile;
   
    DECLARE @FromFileFullUNC_ VARCHAR(2000);
    SET @FromFileFullUNC_ = @FromFileFullUNC;
    DECLARE @FromFileDiffUNC_ VARCHAR(2000);
    SET @FromFileDiffUNC_ = @FromFileDiffUNC;
    DECLARE @FromFileLogUNC_ VARCHAR(2000);
    SET @FromFileLogUNC_ = @FromFileLogUNC;
    DECLARE @StopAt_ DATETIME;
    SET @StopAt_ = @StopAt;
    DECLARE @StandbyMode_ BIT;
    SET @StandbyMode_ = @StandbyMode;
    DECLARE @IncludeSystemDBs_ BIT;
    SET @IncludeSystemDBs_ = @IncludeSystemDBs;
    DECLARE @WithRecovery_ BIT;
    SET @WithRecovery_ = @WithRecovery;
    DECLARE @WithCHECKDB_ BIT;
    SET @WithCHECKDB_ = @WithCHECKDB;
    DECLARE @WithReplace_ BIT;
    SET @WithReplace_ = @WithReplace;
    DECLARE @LogShippingStartTime_ DATETIME;
    SET @LogShippingStartTime_ = @LogShippingStartTime;
    DECLARE @LogShippingLastLSN_ VARCHAR(25);
    SET @LogShippingLastLSN_ = @LogShippingLastLSN;
    DECLARE @BlobCredential_ VARCHAR(255);
    SET @BlobCredential_ = @BlobCredential;
    DECLARE @RestoreScriptReplaceThis_ NVARCHAR(255);
    SET @RestoreScriptReplaceThis_ = @RestoreScriptReplaceThis;
    DECLARE @RestoreScriptWithThis_ NVARCHAR(255);
    SET @RestoreScriptWithThis_ = @RestoreScriptWithThis;
    DECLARE @SuppressWithMove_ BIT;
    SET @SuppressWithMove_ = @SuppressWithMove;
    DECLARE @PivotWithMove_ BIT
    SET @PivotWithMove_ = @PivotWithMove;
    DECLARE @RestoreScriptOnly_ BIT;
    SET @RestoreScriptOnly_ = @RestoreScriptOnly;
    DECLARE @DropDatabaseAfterRestore_ BIT;
    SET @DropDatabaseAfterRestore_ = @DropDatabaseAfterRestore;
    DECLARE @SetSingleUser_ BIT;
    SET @SetSingleUser_ = @SetSingleUser;
    DECLARE @ExcludeDiffAndLogBackups_ INT;
    SET @ExcludeDiffAndLogBackups_ = @ExcludeDiffAndLogBackups;
   
    -- Defaults Recovery Point Times
    IF ISNULL(@StopAt_,'') = ''
        SET @StopAt_ = GETDATE(); 
   
    IF ISNULL(@LogShippingStartTime_,'') = ''
        SET @LogShippingStartTime_ = @StopAt_;
   
    -- Allow override of restored database name only if working with a specific database
    IF @TargetDatabase_ IS NOT NULL AND @Database_ IS NULL
        SET @TargetDatabase_ = NULL;
   
    IF CHARINDEX(',',@Database_, 1) > 0
        SET @TargetDatabase_ = NULL;
   
    -- ps_LogShippingLight - Filtered Results
    IF ISNULL(@LogShippingLastLSN_,'') = ''
        SET @LogShippingLastLSN_ = '-1';
   
    -- Present WITH MOVE if override paths are supplied
    IF ISNULL(@WithMoveDataFiles,'') <> '' OR ISNULL(@WithMoveLogFile_,'') <> '' OR ISNULL(@WithMoveFileStreamFile_,'') <> '' OR ISNULL(@TargetDatabase_,'') <> ''
        SET @SuppressWithMove_ = 0
   
    -- Backup file locations defaulted to '' by ps_RestoreGene
    IF @FromFileFullUNC_ = ''
        SET @FromFileFullUNC_ = NULL;
   
    IF @FromFileDiffUNC_ = ''
        SET @FromFileDiffUNC_ = NULL;
   
    IF @FromFileLogUNC_ = ''
        SET @FromFileLogUNC_ = NULL;
   
    -- Environment Preparation
    IF OBJECT_ID('tempdb..#CTE') IS NOT NULL
        DROP TABLE #CTE;
   
    IF OBJECT_ID('tempdb..#Stripes') IS NOT NULL
        DROP TABLE #Stripes;
   
    IF OBJECT_ID('tempdb..#BackupFork') IS NOT NULL
        DROP TABLE #BackupFork;
  
    IF OBJECT_ID('tempdb..#ForkPointsCounts') IS NOT NULL
        DROP TABLE #ForkPointsCount;
          
    IF OBJECT_ID('tempdb..#RestoreGeneResults') IS NOT NULL
        DROP TABLE #RestoreGeneResults;
   
    IF OBJECT_ID('tempdb..#tmpDatabases') IS NOT NULL
        DROP TABLE #tmpDatabases;
   
    IF OBJECT_ID('tempdb..#DeviceTypes') IS NOT NULL
        DROP TABLE #DeviceTypes;
 
    IF OBJECT_ID('tempdb..#FullBackups') IS NOT NULL
        DROP TABLE #FullBackups;
           
    CREATE TABLE #DeviceTypes (device_type tinyint);
    IF @IncludeDeviceType7 = 1 INSERT INTO #DeviceTypes (device_type) SELECT 7;
    IF @IncludeDeviceType102 = 1 INSERT INTO #DeviceTypes (device_type) SELECT 102;
    IF @IncludeDeviceType2 = 1 INSERT INTO #DeviceTypes (device_type) SELECT 2;
    IF @IncludeDeviceType9 = 1 INSERT INTO #DeviceTypes (device_type) SELECT 9;
   
    --=======================================================================================================================================
    -- 'SELECT DATABASES' code snippet used below with permission from the ola hallengren - https://ola.hallengren.com/
    -----------------------------------------------------------------------------------------------------------------------------------------
    CREATE TABLE #tmpDatabases
    (
        ID int IDENTITY,
        DatabaseName nvarchar(450),
        DatabaseNameFS nvarchar(max),
        DatabaseType nvarchar(max),
        Selected bit,
        Completed bit
    );
    CREATE CLUSTERED INDEX IDX_tmpDatabases ON #tmpDatabases(DatabaseName);
   
    IF CHARINDEX(',',@Database_,1) > 0
    BEGIN
   
        DECLARE @SelectedDatabases TABLE
        (
        DatabaseName nvarchar(max),
        DatabaseType nvarchar(max),
        Selected bit
        );
   
        DECLARE @ErrorMessage nvarchar(max);
        DECLARE @Error int;
   
        ----------------------------------------------------------------------------------------------------
        --// Select databases                                                                           //--
        ----------------------------------------------------------------------------------------------------
   
        SET @Database_ = REPLACE(@Database_, ', ', ',');
   
        WITH Databases1 (StartPosition, EndPosition, DatabaseItem) AS
        (
        SELECT 1 AS StartPosition,
                ISNULL(NULLIF(CHARINDEX(',', @Database_, 1), 0), LEN(@Database_) + 1) AS EndPosition,
                SUBSTRING(@Database_, 1, ISNULL(NULLIF(CHARINDEX(',', @Database_, 1), 0), LEN(@Database_) + 1) - 1) AS DatabaseItem
        WHERE @Database_ IS NOT NULL
        UNION ALL
        SELECT CAST(EndPosition AS int) + 1 AS StartPosition,
                ISNULL(NULLIF(CHARINDEX(',', @Database_, EndPosition + 1), 0), LEN(@Database_) + 1) AS EndPosition,
                SUBSTRING(@Database_, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(',', @Database_, EndPosition + 1), 0), LEN(@Database_) + 1) - EndPosition - 1) AS DatabaseItem
        FROM Databases1
        WHERE EndPosition < LEN(@Database_) + 1
        ),
        Databases2 (DatabaseItem, Selected) AS
        (
        SELECT CASE WHEN DatabaseItem LIKE '-%' THEN RIGHT(DatabaseItem,LEN(DatabaseItem) - 1) ELSE DatabaseItem END AS DatabaseItem,
                CASE WHEN DatabaseItem LIKE '-%' THEN 0 ELSE 1 END AS Selected
        FROM Databases1
        ),
        Databases3 (DatabaseItem, DatabaseType, Selected) AS
        (
        SELECT CASE WHEN DatabaseItem IN('ALL_DATABASES','SYSTEM_DATABASES','USER_DATABASES') THEN '%' ELSE DatabaseItem END AS DatabaseItem,
                CASE WHEN DatabaseItem = 'SYSTEM_DATABASES' THEN 'S' WHEN DatabaseItem = 'USER_DATABASES' THEN 'U' ELSE NULL END AS DatabaseType,
                Selected
        FROM Databases2
        ),
        Databases4 (DatabaseName, DatabaseType, Selected) AS
        (
        SELECT CASE WHEN LEFT(DatabaseItem,1) = '[' AND RIGHT(DatabaseItem,1) = ']' THEN PARSENAME(DatabaseItem,1) ELSE DatabaseItem END AS DatabaseItem,
                DatabaseType,
                Selected
        FROM Databases3
        )
        INSERT INTO @SelectedDatabases (DatabaseName, DatabaseType, Selected)
        SELECT DatabaseName,
                DatabaseType,
                Selected
        FROM Databases4
        OPTION (MAXRECURSION 0)
   
        INSERT INTO #tmpDatabases (DatabaseName, DatabaseNameFS, DatabaseType, Selected, Completed)
        SELECT [name] AS DatabaseName,
                REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE([name],'\',''),'/',''),':',''),'*',''),'?',''),'"',''),'<',''),'>',''),'|',''),' ','') AS DatabaseNameFS,
                CASE WHEN name IN('master','msdb','model') THEN 'S' ELSE 'U' END AS DatabaseType,
                0 AS Selected,
                0 AS Completed
        FROM sys.databases
        WHERE [name] <> 'tempdb'
        AND source_database_id IS NULL
        ORDER BY [name] ASC
   
        UPDATE tmpDatabases
        SET tmpDatabases.Selected = SelectedDatabases.Selected
        FROM #tmpDatabases tmpDatabases
        INNER JOIN @SelectedDatabases SelectedDatabases
        ON tmpDatabases.DatabaseName LIKE REPLACE(SelectedDatabases.DatabaseName,'_','[_]')
        AND (tmpDatabases.DatabaseType = SelectedDatabases.DatabaseType OR SelectedDatabases.DatabaseType IS NULL)
        WHERE SelectedDatabases.Selected = 1
   
        UPDATE tmpDatabases
        SET tmpDatabases.Selected = SelectedDatabases.Selected
        FROM #tmpDatabases tmpDatabases
        INNER JOIN @SelectedDatabases SelectedDatabases
        ON tmpDatabases.DatabaseName LIKE REPLACE(SelectedDatabases.DatabaseName,'_','[_]')
        AND (tmpDatabases.DatabaseType = SelectedDatabases.DatabaseType OR SelectedDatabases.DatabaseType IS NULL)
        WHERE SelectedDatabases.Selected = 0
   
        IF @Database_ IS NULL OR NOT EXISTS(SELECT * FROM @SelectedDatabases) OR EXISTS(SELECT * FROM @SelectedDatabases WHERE DatabaseName IS NULL OR DatabaseName = '')
            BEGIN
            SET @ErrorMessage = 'The value for the parameter @Databases is not supported.' + CHAR(13) + CHAR(10) + ' '
            RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
            SET @Error = @@ERROR
        END
   
    END
 
    --=======================================================================================================================================
    -- Full Backups - Last before stop at for each database 
    SELECT
       a.database_name
      ,MAX(a.backup_finish_date) AS backup_finish_date
    INTO #FullBackups
    FROM msdb.dbo.backupset a
    JOIN msdb.dbo.backupmediafamily b
       ON a.media_set_id = b.media_set_id
   
    WHERE a.[type] = 'D'
    AND (a.database_name IN (SELECT DatabaseName FROM #tmpDatabases WHERE Selected = 1) OR @Database_ IS NULL OR a.database_name = @Database_)
    AND device_type IN (SELECT device_type FROM #DeviceTypes)
    AND a.is_copy_only = 0
    AND b.mirror = 0
    AND a.backup_start_date <= @LogShippingStartTime_
    GROUP BY a.database_name;
 
    CREATE INDEX IDX_FullBackups ON #FullBackups(database_name);
 
    --=======================================================================================================================================
    -- Backup forking (suppress differential, log file select criteria)
    SELECT bs.database_name, first_recovery_fork_guid, MIN(fork_point_lsn) AS fork_point_lsn
    INTO #BackupFork 
    FROM msdb.dbo.backupset bs
    JOIN msdb.dbo.backupmediafamily b
       ON bs.media_set_id = b.media_set_id  
    JOIN #FullBackups fb
      ON fb.database_name = bs.database_name
    WHERE fb.backup_finish_date < bs.backup_start_date
    AND bs.backup_finish_date < @LogShippingStartTime_
    AND bs.first_recovery_fork_guid <> bs.last_recovery_fork_guid
    GROUP BY bs.database_name, first_recovery_fork_guid;
 
    -- Only one fork point between the selected full backup and the stop at time supported
    SELECT database_name, COUNT(*) AS ForkPoints
    INTO #ForkPointsCount
    FROM #BackupFork rh
    GROUP BY database_name;
 
    --=======================================================================================================================================
    --------------------------------------------------------------
    -- CTE1 Full Backup 
    --------------------------------------------------------------
    WITH CTE
    (
         database_name
        ,current_compatibility_level
        ,first_lsn
        ,last_lsn
        ,fork_point_lsn
        ,current_is_read_only
        ,current_state_desc
        ,current_recovery_model_desc
        ,has_backup_checksums
        ,backup_size
        ,[type]
        ,backupmediasetid
        ,family_sequence_number
        ,backupfinishdate
        ,physical_device_name
        ,position
        ,first_recovery_fork_guid
        ,last_recovery_fork_guid
    )
    AS
    ( 
        --------------------------------------------------------------
        -- Full backup - Most recent before @LogShippingStartTime_
        SELECT
            bs.database_name
            ,d.[compatibility_level] AS current_compatibility_level
            ,bs.first_lsn
            ,bs.last_lsn
            ,bs.fork_point_lsn
            ,d.[is_read_only] AS current_is_read_only
            ,d.[state_desc] AS current_state_desc
            ,d.[recovery_model_desc] current_recovery_model_desc
            ,bs.has_backup_checksums
            ,bs.backup_size AS backup_size
            ,'D' AS [type]
            ,bs.media_set_id AS backupmediasetid
            ,mf.family_sequence_number
            ,bs.backup_finish_date AS backupfinishdate
            ,mf.physical_device_name
            ,bs.position
            ,bs.first_recovery_fork_guid
            ,bs.last_recovery_fork_guid

        FROM msdb.dbo.backupset bs 
   
        INNER JOIN sys.databases d
            ON bs.database_name = d.name
   
        INNER JOIN #FullBackups fb
            ON fb.database_name = bs.database_name
            AND fb.backup_finish_date = bs.backup_finish_date
   
        JOIN msdb.dbo.backupmediafamily mf
            ON mf.media_set_id = bs.media_set_id
            AND mf.family_sequence_number Between bs.first_family_number And bs.last_family_number      
   
        WHERE bs.type = 'D'
        AND mf.mirror = 0
        AND mf.physical_device_name NOT IN ('Nul', 'Nul:') 
		AND bs.is_copy_only = 0
   
        UNION
   
        --------------------------------------------------------------
        -- Differential backup, most current immediately before @StopAt_, suppress if backup fork points exist
        SELECT
            bs.database_name
            ,d.[compatibility_level] AS current_compatibility_level
            ,bs.first_lsn
            ,bs.last_lsn
            ,bs.fork_point_lsn
            ,d.[is_read_only] AS current_is_read_only
            ,d.[state_desc] AS current_state_desc
            ,d.[recovery_model_desc] current_recovery_model_desc
            ,bs.has_backup_checksums
            ,bs.backup_size AS backup_size
            ,'I' AS [type]
            ,bs.media_set_id AS backupmediasetid
            ,mf.family_sequence_number
            ,bs.backup_finish_date AS backupfinishdate
            ,mf.physical_device_name
            ,bs.position
            ,bs.first_recovery_fork_guid
            ,bs.last_recovery_fork_guid
        FROM msdb.dbo.backupset bs 
   
        INNER JOIN sys.databases d
          ON bs.database_name = d.name
   
        INNER JOIN #FullBackups fb
            ON fb.database_name = bs.database_name
            AND fb.backup_finish_date < bs.backup_finish_date
 
        INNER JOIN -- Last Diff before STOPAT / Suppress if backup forkpoints exist
        (
            SELECT
                a.database_name
                ,MAX(backup_start_date) backup_start_date
            FROM msdb.dbo.backupset a
            JOIN msdb.dbo.backupmediafamily b
                ON a.media_set_id = b.media_set_id
   
            WHERE a.[type] = 'I'
            AND (a.database_name IN (SELECT DatabaseName FROM #tmpDatabases WHERE Selected = 1) OR @Database_ IS NULL OR a.database_name = @Database_)
            AND device_type IN (SELECT device_type FROM #DeviceTypes)
            AND a.is_copy_only = 0
            AND b.mirror = 0
            AND a.backup_start_date <= ISNULL(@StopAt_,GETDATE())
            GROUP BY a.database_name  
        ) x
            ON x.database_name = bs.database_name
            AND x.backup_start_date = bs.backup_start_date 
   
        INNER JOIN msdb.dbo.backupmediafamily mf
            ON mf.media_set_id = bs.media_set_id
            AND mf.family_sequence_number Between bs.first_family_number And bs.last_family_number 
 
        LEFT OUTER JOIN #BackupFork bf
            ON bf.database_name = bs.database_name
 
        WHERE bs.type = 'I'
        AND mf.physical_device_name NOT IN ('Nul', 'Nul:')
        AND mf.mirror = 0
        AND @StopAt_ = @LogShippingStartTime_
        AND bf.fork_point_lsn IS NULL
   
        UNION
   
        --------------------------------------------------------------
        -- Log file backups - after 1st full / before @StopAt_
        SELECT
            bs.database_name
            ,d.[compatibility_level] AS current_compatibility_level
            ,bs.first_lsn
            ,bs.last_lsn
            ,bs.fork_point_lsn
            ,d.[is_read_only] AS current_is_read_only
            ,d.[state_desc] AS current_state_desc
            ,d.[recovery_model_desc] current_recovery_model_desc
            ,bs.has_backup_checksums
            ,bs.backup_size AS backup_size
            ,'L' AS [type]
            ,bs.media_set_id AS backupmediasetid
            ,mf.family_sequence_number
            ,bs.backup_finish_date as backupfinishdate
            ,mf.physical_device_name
            ,bs.position
            ,bs.first_recovery_fork_guid
            ,bs.last_recovery_fork_guid 
        FROM msdb.dbo.backupset bs 
   
        INNER JOIN sys.databases d
            ON bs.database_name = d.name
   
        INNER JOIN msdb.dbo.backupmediafamily mf
            ON mf.media_set_id = bs.media_set_id
            AND mf.family_sequence_number Between bs.first_family_number And bs.last_family_number 
   
        INNER JOIN #FullBackups fb
            ON bs.database_name = fb.database_name 
   
        LEFT OUTER JOIN -- Select the first log file after STOPAT
        (
            SELECT x.database_name, CASE WHEN y.last_Log_After_StopAt IS NULL THEN CONVERT(datetime, '31 Dec 2050') ELSE y.last_Log_After_StopAt END AS last_Log_After_StopAt
            FROM msdb.dbo.backupset x
            LEFT JOIN
            (
                SELECT
                    database_name
                    ,MIN(backup_start_date) last_Log_After_StopAt
                FROM msdb.dbo.backupset a
                JOIN msdb.dbo.backupmediafamily b
                ON a.media_set_id = b.media_set_id
                WHERE a.[type] = 'L'
                AND (a.database_name IN (SELECT DatabaseName FROM #tmpDatabases WHERE Selected = 1) OR @Database_ IS NULL OR a.database_name = @Database_)
                AND b.mirror = 0
                AND device_type IN (SELECT device_type FROM #DeviceTypes)
                AND a.backup_start_date > ISNULL(@StopAt_,'1 Jan, 1900')
                GROUP BY database_name
            ) y
                ON x.database_name = y.database_name
        ) x
            ON bs.database_name = x.database_name
  
        LEFT OUTER JOIN #BackupFork bf
            ON bf.database_name = bs.database_name
            AND bf.first_recovery_fork_guid = bs.first_recovery_fork_guid
   
        WHERE bs.backup_start_date <= x.last_Log_After_StopAt -- Include 1st log after stop at
        AND bs.backup_start_date >= fb.backup_finish_date -- Full backup finish
        AND bs.backup_finish_date <= @StopAt_
        AND (bs.database_name IN (SELECT DatabaseName FROM #tmpDatabases WHERE Selected = 1) OR @Database_ IS NULL OR bs.database_name = @Database_)
        AND mf.physical_device_name NOT IN ('Nul', 'Nul:')
        AND mf.mirror = 0
        AND bs.type = 'L'
        AND device_type IN (SELECT device_type FROM #DeviceTypes)
        AND (bf.fork_point_lsn > bs.first_lsn OR bf.first_recovery_fork_guid IS NULL OR bs.first_recovery_fork_guid <> bs.last_recovery_fork_guid)
    )
   
    SELECT * INTO #CTE FROM CTE;
    CREATE INDEX IDX_CTE ON #CTE(database_name);
   
    --------------------------------------------------------------
    -- CTE2 Optionally, striped backup file details
    --------------------------------------------------------------
    WITH Stripes
    (
        database_name,
        backupmediasetid,
        family_sequence_number,
        last_lsn,
        S2_pdn,
        S3_pdn,
        S4_pdn,
        S5_pdn,
        S6_pdn,
        S7_pdn,
        S8_pdn,
        S9_pdn,
        S10_pdn,
        S11_pdn,
        S12_pdn,
        S13_pdn,
        S14_pdn,
        S15_pdn
    )
    AS
    (
        SELECT
            Stripe1.database_name,
            Stripe1.backupmediasetid,
            Stripe1.family_sequence_number,
            Stripe1.last_lsn,
            Stripe2.physical_device_name AS S2_pdn,
            Stripe3.physical_device_name AS S3_pdn,
            Stripe4.physical_device_name AS S4_pdn,
            Stripe5.physical_device_name AS S5_pdn,
            Stripe6.physical_device_name AS S6_pdn,
            Stripe7.physical_device_name AS S7_pdn,
            Stripe8.physical_device_name AS S8_pdn,
            Stripe9.physical_device_name AS S9_pdn,
            Stripe11.physical_device_name AS S10_pdn,
            Stripe12.physical_device_name AS S11_pdn,
            Stripe13.physical_device_name AS S12_pdn,
            Stripe14.physical_device_name AS S13_pdn,
            Stripe15.physical_device_name AS S14_pdn,
            Stripe10.physical_device_name AS S15_pdn
        FROM #CTE AS Stripe1 
   
        LEFT OUTER JOIN #CTE AS Stripe2
            ON Stripe2.database_name = Stripe1.database_name
            AND Stripe2.backupmediasetid = Stripe1.backupmediasetid
            AND Stripe2.family_sequence_number = 2 
   
        LEFT OUTER JOIN #CTE AS Stripe3
            ON Stripe3.database_name = Stripe1.database_name
            AND Stripe3.backupmediasetid = Stripe1.backupmediasetid
            AND Stripe3.family_sequence_number = 3 
   
        LEFT OUTER JOIN #CTE AS Stripe4
            ON Stripe4.database_name = Stripe1.database_name
            AND Stripe4.backupmediasetid = Stripe1.backupmediasetid
            AND Stripe4.family_sequence_number = 4 
   
        LEFT OUTER JOIN #CTE AS Stripe5
            ON Stripe5.database_name = Stripe1.database_name
            AND Stripe5.backupmediasetid = Stripe1.backupmediasetid
            AND Stripe5.family_sequence_number = 5 
   
        LEFT OUTER JOIN #CTE AS Stripe6
            ON Stripe6.database_name = Stripe1.database_name
            AND Stripe6.backupmediasetid = Stripe1.backupmediasetid
            AND Stripe6.family_sequence_number = 6 
   
        LEFT OUTER JOIN #CTE AS Stripe7
            ON Stripe7.database_name = Stripe1.database_name
            AND Stripe7.backupmediasetid = Stripe1.backupmediasetid
            AND Stripe7.family_sequence_number = 7 
   
        LEFT OUTER JOIN #CTE AS Stripe8
            ON Stripe8.database_name = Stripe1.database_name
            AND Stripe8.backupmediasetid = Stripe1.backupmediasetid
            AND Stripe8.family_sequence_number = 8 
   
        LEFT OUTER JOIN #CTE AS Stripe9
            ON Stripe9.database_name = Stripe1.database_name
            AND Stripe9.backupmediasetid = Stripe1.backupmediasetid
            AND Stripe9.family_sequence_number = 9 
   
        LEFT OUTER JOIN #CTE AS Stripe10
            ON Stripe10.database_name = Stripe1.database_name
            AND Stripe10.backupmediasetid = Stripe1.backupmediasetid
            AND Stripe10.family_sequence_number = 10
  
        LEFT OUTER JOIN #CTE AS Stripe11
            ON Stripe11.database_name = Stripe1.database_name
            AND Stripe11.backupmediasetid = Stripe1.backupmediasetid
            AND Stripe11.family_sequence_number = 11
  
        LEFT OUTER JOIN #CTE AS Stripe12
            ON Stripe12.database_name = Stripe1.database_name
            AND Stripe12.backupmediasetid = Stripe1.backupmediasetid
            AND Stripe12.family_sequence_number = 12
  
        LEFT OUTER JOIN #CTE AS Stripe13
            ON Stripe13.database_name = Stripe1.database_name
            AND Stripe13.backupmediasetid = Stripe1.backupmediasetid
            AND Stripe13.family_sequence_number = 13
  
        LEFT OUTER JOIN #CTE AS Stripe14
            ON Stripe14.database_name = Stripe1.database_name
            AND Stripe14.backupmediasetid = Stripe1.backupmediasetid
            AND Stripe14.family_sequence_number = 14
  
        LEFT OUTER JOIN #CTE AS Stripe15
            ON Stripe15.database_name = Stripe1.database_name
            AND Stripe15.backupmediasetid = Stripe1.backupmediasetid
            AND Stripe15.family_sequence_number = 15
 
    ) 
   
    SELECT * INTO #Stripes FROM Stripes;  
   
    CREATE INDEX IDX_Stripes ON #Stripes(database_name);
   
    --------------------------------------------------------------
    -- Results, T-SQL RESTORE commands, below are based on CTE's above
    -------------------------------------------------------------- 
   
    SELECT
        CASE WHEN @RestoreScriptReplaceThis_ IS NOT NULL AND @RestoreScriptWithThis_ IS NOT NULL THEN REPLACE(a.Command, @RestoreScriptReplaceThis_, @RestoreScriptWithThis_) ELSE a.Command END AS TSQL,  --Allow substitution of a path
        CONVERT(nvarchar(30), a.backupfinishdate, 126)
        AS BackupDate,
        a.BackupDevice,
        a.first_lsn,
        a.last_lsn,
        a.fork_point_lsn,
        a.first_recovery_fork_guid,
        a.last_recovery_fork_guid,
        a.database_name ,
        ROW_NUMBER() OVER(ORDER BY database_name, Sequence, a.backupfinishdate) AS SortSequence
    INTO #RestoreGeneResults
    FROM
    ( 
   
    --------------------------------------------------------------
    -- Most recent full backup
    -------------------------------------------------------------- 
   
    SELECT
 
        'RESTORE DATABASE [' + CASE ISNULL(@TargetDatabase_,'') WHEN '' THEN d.[name] ELSE @TargetDatabase_ END + ']' + SPACE(1) + CHAR(13) + 
   
        CASE WHEN #CTE.physical_device_name LIKE 'http%' THEN  ' FROM URL = ' ELSE ' FROM DISK = N' END + '''' + --Support restore from blob storage in Azure
   
        CASE ISNULL(@FromFileFullUNC_,'Actual')
        WHEN 'Actual' THEN #CTE.physical_device_name
        ELSE @FromFileFullUNC_ + SUBSTRING(#CTE.physical_device_name,LEN(#CTE.physical_device_name) - CHARINDEX('\',REVERSE(#CTE.physical_device_name),1) + 2,CHARINDEX('\',REVERSE(#CTE.physical_device_name),1) + 1)
        END + '''' + SPACE(1) + CHAR(13) + 
   
        -- Striped backup files
        CASE ISNULL(#Stripes.S2_pdn,'')
        WHEN '' THEN ''
        ELSE  ', DISK = N' + '''' + CASE ISNULL(@FromFileFullUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S2_pdn ELSE @FromFileFullUNC_ + SUBSTRING(#Stripes.S2_pdn,LEN(#Stripes.S2_pdn) - CHARINDEX('\',REVERSE(#Stripes.S2_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S2_pdn),1) + 1) END + ''''
        END + 
   
        CASE ISNULL(#Stripes.S3_pdn,'')
        WHEN '' THEN ''
        ELSE  ', DISK = N' + '''' + CASE ISNULL(@FromFileFullUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S3_pdn ELSE @FromFileFullUNC_ + SUBSTRING(#Stripes.S3_pdn,LEN(#Stripes.S3_pdn) - CHARINDEX('\',REVERSE(#Stripes.S3_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S3_pdn),1) + 1) END + ''''
        END + 
   
        CASE ISNULL(#Stripes.S4_pdn,'')
        WHEN '' THEN ''
        ELSE  ', DISK = N' + '''' + CASE ISNULL(@FromFileFullUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S4_pdn ELSE @FromFileFullUNC_ + SUBSTRING(#Stripes.S4_pdn,LEN(#Stripes.S4_pdn) - CHARINDEX('\',REVERSE(#Stripes.S4_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S4_pdn),1) + 1) END + ''''
        END + 
   
        CASE ISNULL(#Stripes.S5_pdn,'')
        WHEN '' THEN ''
        ELSE  ', DISK = N' + '''' + CASE ISNULL(@FromFileFullUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S5_pdn ELSE @FromFileFullUNC_ + SUBSTRING(#Stripes.S5_pdn,LEN(#Stripes.S5_pdn) - CHARINDEX('\',REVERSE(#Stripes.S5_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S5_pdn),1) + 1) END + ''''
        END + 
   
        CASE ISNULL(#Stripes.S6_pdn,'')
        WHEN '' THEN ''
        ELSE  ', DISK = N' + '''' + CASE ISNULL(@FromFileFullUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S6_pdn ELSE @FromFileFullUNC_ + SUBSTRING(#Stripes.S6_pdn,LEN(#Stripes.S6_pdn) - CHARINDEX('\',REVERSE(#Stripes.S6_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S6_pdn),1) + 1) END + ''''
        END + 
   
        CASE ISNULL(#Stripes.S7_pdn,'')
        WHEN '' THEN ''
        ELSE  ', DISK = N' + '''' + CASE ISNULL(@FromFileFullUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S7_pdn ELSE @FromFileFullUNC_ + SUBSTRING(#Stripes.S7_pdn,LEN(#Stripes.S7_pdn) - CHARINDEX('\',REVERSE(#Stripes.S7_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S7_pdn),1) + 1) END + ''''
        END + 
   
        CASE ISNULL(#Stripes.S8_pdn,'')
        WHEN '' THEN ''
        ELSE  ', DISK = N' + '''' + CASE ISNULL(@FromFileFullUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S8_pdn ELSE @FromFileFullUNC_ + SUBSTRING(#Stripes.S8_pdn,LEN(#Stripes.S8_pdn) - CHARINDEX('\',REVERSE(#Stripes.S8_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S8_pdn),1) + 1) END + ''''
        END + 
   
        CASE ISNULL(#Stripes.S9_pdn,'')
        WHEN '' THEN ''
        ELSE  ', DISK = N' + '''' + CASE ISNULL(@FromFileFullUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S9_pdn ELSE @FromFileFullUNC_ + SUBSTRING(#Stripes.S9_pdn,LEN(#Stripes.S9_pdn) - CHARINDEX('\',REVERSE(#Stripes.S9_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S9_pdn),1) + 1) END + ''''
        END + 
   
        CASE ISNULL(#Stripes.S10_pdn,'')
        WHEN '' THEN ''
        ELSE  ', DISK = N' + '''' + CASE ISNULL(@FromFileFullUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S10_pdn ELSE @FromFileFullUNC_ + SUBSTRING(#Stripes.S10_pdn,LEN(#Stripes.S10_pdn) - CHARINDEX('\',REVERSE(#Stripes.S10_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S10_pdn),1) + 1) END + ''''
        END + 
  
        CASE ISNULL(#Stripes.S11_pdn,'')
        WHEN '' THEN ''
        ELSE  ', DISK = N' + '''' + CASE ISNULL(@FromFileFullUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S11_pdn ELSE @FromFileFullUNC_ + SUBSTRING(#Stripes.S11_pdn,LEN(#Stripes.S11_pdn) - CHARINDEX('\',REVERSE(#Stripes.S11_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S11_pdn),1) + 1) END + ''''
        END + 
  
        CASE ISNULL(#Stripes.S12_pdn,'')
        WHEN '' THEN ''
        ELSE  ', DISK = N' + '''' + CASE ISNULL(@FromFileFullUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S12_pdn ELSE @FromFileFullUNC_ + SUBSTRING(#Stripes.S12_pdn,LEN(#Stripes.S12_pdn) - CHARINDEX('\',REVERSE(#Stripes.S12_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S12_pdn),1) + 1) END + ''''
        END + 
  
        CASE ISNULL(#Stripes.S13_pdn,'')
        WHEN '' THEN ''
        ELSE  ', DISK = N' + '''' + CASE ISNULL(@FromFileFullUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S13_pdn ELSE @FromFileFullUNC_ + SUBSTRING(#Stripes.S13_pdn,LEN(#Stripes.S13_pdn) - CHARINDEX('\',REVERSE(#Stripes.S13_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S13_pdn),1) + 1) END + ''''
        END + 
  
        CASE ISNULL(#Stripes.S14_pdn,'')
        WHEN '' THEN ''
        ELSE  ', DISK = N' + '''' + CASE ISNULL(@FromFileFullUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S14_pdn ELSE @FromFileFullUNC_ + SUBSTRING(#Stripes.S14_pdn,LEN(#Stripes.S14_pdn) - CHARINDEX('\',REVERSE(#Stripes.S14_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S14_pdn),1) + 1) END + ''''
        END + 
  
        CASE ISNULL(#Stripes.S15_pdn,'')
        WHEN '' THEN ''
        ELSE  ', DISK = N' + '''' + CASE ISNULL(@FromFileFullUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S15_pdn ELSE @FromFileFullUNC_ + SUBSTRING(#Stripes.S15_pdn,LEN(#Stripes.S15_pdn) - CHARINDEX('\',REVERSE(#Stripes.S15_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S15_pdn),1) + 1) END + ''''
        END + 
        
        ' WITH ' + 
    
        CASE WHEN #CTE.physical_device_name LIKE 'http%' AND @BlobCredential_ IS NOT NULL THEN ' CREDENTIAL = ''' + @BlobCredential_ + ''', ' ELSE '' END +
   
        CASE ISNULL(@WithReplace_,0) WHEN 1 THEN 'REPLACE, ' ELSE '' END   + 'FILE = ' + CAST(#CTE.position AS VARCHAR(5)) + ',' +
        CASE #CTE.has_backup_checksums WHEN 1 THEN 'CHECKSUM, ' ELSE ' ' END + 
   
        CASE @StandbyMode_ WHEN 0 THEN 'NORECOVERY,' ELSE 'STANDBY =N' + '''' + ISNULL(@FromFileFullUNC_,SUBSTRING(#CTE.physical_device_name,1,LEN(#CTE.physical_device_name) - CHARINDEX('\',REVERSE(#CTE.physical_device_name)))) + '\' + d.name + '_' + REPLACE(REPLACE(SUBSTRING(CONVERT(VARCHAR(24),GETDATE(),127),12,12),':',''),'.','') + '_ROLLBACK_UNDO.bak ' + '''' + ',' END + SPACE(1) + 
   
        'STATS=10' + SPACE(1) +
   
        CASE @SuppressWithMove_ WHEN 0 THEN
   
            ', MOVE N' + '''' + x.LogicalName + '''' + ' TO ' +
            '''' +
            CASE ISNULL(@WithMoveDataFiles_,'Actual')
            WHEN 'Actual' THEN x.PhysicalName
            ELSE @WithMoveDataFiles_ + SUBSTRING(x.PhysicalName,LEN(x.PhysicalName) - CHARINDEX('\',REVERSE(x.PhysicalName),1) + 2,CHARINDEX('\',REVERSE(x.PhysicalName),1) + 1)
            END + '''' + SPACE(1) + 
   
            ', MOVE N' + '''' + y.LogicalName + '''' + ' TO ' +
            '''' +
            CASE ISNULL(@WithMoveLogFile_ ,'Actual')
            WHEN 'Actual' THEN y.PhysicalName
            ELSE @WithMoveLogFile_  + SUBSTRING(y.PhysicalName,LEN(y.PhysicalName) - CHARINDEX('\',REVERSE(y.PhysicalName),1) + 2,CHARINDEX('\',REVERSE(y.PhysicalName),1) + 1)
            END + ''''
        ELSE ' '
        END 
   
        AS Command,
        1 AS Sequence,
        d.name AS database_name,
        #CTE.physical_device_name AS BackupDevice,
        #CTE.backupfinishdate,
        #CTE.backup_size,
        #CTE.first_lsn,
        #CTE.last_lsn,
        #CTE.fork_point_lsn,
        #CTE.first_recovery_fork_guid,
        #CTE.last_recovery_fork_guid 
   
    FROM sys.databases d 
   
    INNER JOIN
    (
        SELECT
            DB_NAME(mf.database_id) AS name
            ,CASE ISNULL(@TargetDatabase_,'') WHEN '' THEN mf.Physical_Name ELSE SUBSTRING(mf.Physical_Name,1,CASE CHARINDEX('.',mf.Physical_Name) WHEN 0 THEN LEN(mf.Physical_Name) ELSE CHARINDEX('.',mf.Physical_Name) - 1 END) + '_' + @TargetDatabase_ + '.mdf' END  AS PhysicalName
            ,mf.Name AS LogicalName
        FROM sys.master_files mf
        WHERE type_desc = 'ROWS'
        AND mf.file_id = 1
    ) x
        ON d.name = x.name 
   
    INNER JOIN
    (
        SELECT
            DB_NAME(mf.database_id) AS name, type_desc
            ,CASE ISNULL(@TargetDatabase_,'') WHEN '' THEN mf.Physical_Name ELSE SUBSTRING(mf.Physical_Name,1,CASE CHARINDEX('.',mf.Physical_Name) WHEN 0 THEN LEN(mf.Physical_Name) ELSE CHARINDEX('.',mf.Physical_Name) - 1 END) +  '_' + @TargetDatabase_ + '.ldf' END  AS PhysicalName
            ,mf.Name AS LogicalName
        FROM sys.master_files mf
 
        -- Fix for multiple log files/with moves
        INNER JOIN
        (
            SELECT database_id, MIN(file_id) AS logfile_id
            FROM sys.master_files
            WHERE type_desc = 'LOG'
            GROUP BY database_id
        ) logfileid
            on logfileid.database_id = mf.database_id
            AND logfileid.logfile_id = mf.file_id
        WHERE type_desc = 'LOG'
    ) y
        ON d.name = y.name 
   
    LEFT OUTER JOIN #CTE
        ON #CTE.database_name = d.name
        AND #CTE.family_sequence_number = 1 
 
    INNER JOIN #Stripes
        ON #Stripes.database_name = d.name
        AND #Stripes.backupmediasetid = #CTE.backupmediasetid
        AND #Stripes.last_lsn = #CTE.last_lsn 
 
    LEFT OUTER JOIN #ForkPointsCount fpc
        ON fpc.database_name = #CTE.database_name
   
    WHERE #CTE.[type] = 'D'
    AND #CTE.family_sequence_number = 1 
    AND ISNULL(fpc.ForkPoints,0) < 2
   
    --------------------------------------------------------------
    -- Most recent differential backup
    --------------------------------------------------------------
    UNION 
   
    SELECT
 
        'RESTORE DATABASE [' + CASE ISNULL(@TargetDatabase_,'') WHEN '' THEN d.[name] ELSE @TargetDatabase_ END + ']' + SPACE(1) + CHAR(13) +
        CASE WHEN #CTE.physical_device_name LIKE 'http%' THEN ' FROM URL = N' ELSE ' FROM DISK = N' END + '''' +
   
        CASE ISNULL(@FromFileDiffUNC_,'Actual')
        WHEN 'Actual' THEN #CTE.physical_device_name
        ELSE @FromFileDiffUNC_ + SUBSTRING(#CTE.physical_device_name,LEN(#CTE.physical_device_name) - CHARINDEX('\',REVERSE(#CTE.physical_device_name),1) + 2,CHARINDEX('\',REVERSE(#CTE.physical_device_name),1) + 1)
        END + '''' + SPACE(1) + CHAR(13) + 
   
        -- Striped backup files
        CASE ISNULL(#Stripes.S2_pdn,'')
        WHEN '' THEN ''
        ELSE  ', DISK = N' + '''' + CASE ISNULL(@FromFileDiffUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S2_pdn ELSE @FromFileDiffUNC_ + SUBSTRING(#Stripes.S2_pdn,LEN(#Stripes.S2_pdn) - CHARINDEX('\',REVERSE(#Stripes.S2_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S2_pdn),1) + 1) END + ''''
        END + 
   
        CASE ISNULL(#Stripes.S3_pdn,'')
        WHEN '' THEN ''
        ELSE  ', DISK = N' + '''' + CASE ISNULL(@FromFileDiffUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S3_pdn ELSE @FromFileDiffUNC_ + SUBSTRING(#Stripes.S3_pdn,LEN(#Stripes.S3_pdn) - CHARINDEX('\',REVERSE(#Stripes.S3_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S3_pdn),1) + 1) END + ''''
        END + 
   
        CASE ISNULL(#Stripes.S4_pdn,'')
        WHEN '' THEN ''
        ELSE  ', DISK = N' + '''' + CASE ISNULL(@FromFileDiffUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S4_pdn ELSE @FromFileDiffUNC_ + SUBSTRING(#Stripes.S4_pdn,LEN(#Stripes.S4_pdn) - CHARINDEX('\',REVERSE(#Stripes.S4_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S4_pdn),1) + 1) END + ''''
        END + 
   
        CASE ISNULL(#Stripes.S5_pdn,'')
        WHEN '' THEN ''
        ELSE  ', DISK = N' + '''' + CASE ISNULL(@FromFileDiffUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S5_pdn ELSE @FromFileDiffUNC_ + SUBSTRING(#Stripes.S5_pdn,LEN(#Stripes.S5_pdn) - CHARINDEX('\',REVERSE(#Stripes.S5_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S5_pdn),1) + 1) END + ''''
        END + 
   
        CASE ISNULL(#Stripes.S6_pdn,'')
        WHEN '' THEN ''
        ELSE  ', DISK = N' + '''' + CASE ISNULL(@FromFileDiffUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S6_pdn ELSE @FromFileDiffUNC_ + SUBSTRING(#Stripes.S6_pdn,LEN(#Stripes.S6_pdn) - CHARINDEX('\',REVERSE(#Stripes.S6_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S6_pdn),1) + 1) END + ''''
        END + 
   
        CASE ISNULL(#Stripes.S7_pdn,'')
        WHEN '' THEN ''
        ELSE  ', DISK = N' + '''' + CASE ISNULL(@FromFileDiffUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S7_pdn ELSE @FromFileDiffUNC_ + SUBSTRING(#Stripes.S7_pdn,LEN(#Stripes.S7_pdn) - CHARINDEX('\',REVERSE(#Stripes.S7_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S7_pdn),1) + 1) END + ''''
        END + 
   
        CASE ISNULL(#Stripes.S8_pdn,'')
        WHEN '' THEN ''
        ELSE  ', DISK = N' + '''' + CASE ISNULL(@FromFileDiffUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S8_pdn ELSE @FromFileDiffUNC_ + SUBSTRING(#Stripes.S8_pdn,LEN(#Stripes.S8_pdn) - CHARINDEX('\',REVERSE(#Stripes.S8_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S8_pdn),1) + 1) END + ''''
        END + 
   
        CASE ISNULL(#Stripes.S9_pdn,'')
        WHEN '' THEN ''
        ELSE  ', DISK = N' + '''' + CASE ISNULL(@FromFileDiffUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S9_pdn ELSE @FromFileDiffUNC_ + SUBSTRING(#Stripes.S9_pdn,LEN(#Stripes.S9_pdn) - CHARINDEX('\',REVERSE(#Stripes.S9_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S9_pdn),1) + 1) END + ''''
        END + 
   
        CASE ISNULL(#Stripes.S10_pdn,'')
        WHEN '' THEN ''
        ELSE  ', DISK = N' + '''' + CASE ISNULL(@FromFileDiffUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S10_pdn ELSE @FromFileDiffUNC_ + SUBSTRING(#Stripes.S10_pdn,LEN(#Stripes.S10_pdn) - CHARINDEX('\',REVERSE(#Stripes.S10_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S10_pdn),1) + 1) END + ''''
        END + 
   
        CASE ISNULL(#Stripes.S11_pdn,'')
        WHEN '' THEN ''
        ELSE  ', DISK = N' + '''' + CASE ISNULL(@FromFileDiffUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S11_pdn ELSE @FromFileDiffUNC_ + SUBSTRING(#Stripes.S11_pdn,LEN(#Stripes.S11_pdn) - CHARINDEX('\',REVERSE(#Stripes.S11_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S11_pdn),1) + 1) END + ''''
        END + 
  
        CASE ISNULL(#Stripes.S12_pdn,'')
        WHEN '' THEN ''
        ELSE  ', DISK = N' + '''' + CASE ISNULL(@FromFileDiffUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S12_pdn ELSE @FromFileDiffUNC_ + SUBSTRING(#Stripes.S12_pdn,LEN(#Stripes.S12_pdn) - CHARINDEX('\',REVERSE(#Stripes.S12_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S12_pdn),1) + 1) END + ''''
        END + 
  
        CASE ISNULL(#Stripes.S13_pdn,'')
        WHEN '' THEN ''
        ELSE  ', DISK = N' + '''' + CASE ISNULL(@FromFileDiffUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S13_pdn ELSE @FromFileDiffUNC_ + SUBSTRING(#Stripes.S13_pdn,LEN(#Stripes.S13_pdn) - CHARINDEX('\',REVERSE(#Stripes.S13_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S13_pdn),1) + 1) END + ''''
        END + 
  
        CASE ISNULL(#Stripes.S14_pdn,'')
        WHEN '' THEN ''
        ELSE  ', DISK = N' + '''' + CASE ISNULL(@FromFileDiffUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S14_pdn ELSE @FromFileDiffUNC_ + SUBSTRING(#Stripes.S14_pdn,LEN(#Stripes.S14_pdn) - CHARINDEX('\',REVERSE(#Stripes.S14_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S14_pdn),1) + 1) END + ''''
        END + 
  
        CASE ISNULL(#Stripes.S15_pdn,'')
        WHEN '' THEN ''
        ELSE  ', DISK = N' + '''' + CASE ISNULL(@FromFileDiffUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S15_pdn ELSE @FromFileDiffUNC_ + SUBSTRING(#Stripes.S15_pdn,LEN(#Stripes.S15_pdn) - CHARINDEX('\',REVERSE(#Stripes.S15_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S15_pdn),1) + 1) END + ''''
        END + 
  
        ' WITH FILE = ' + CAST(#CTE.position AS VARCHAR(5)) + ',' +
        CASE #CTE.has_backup_checksums WHEN 1 THEN 'CHECKSUM, ' ELSE ' ' END + 
   
        CASE WHEN #CTE.physical_device_name LIKE 'http%' AND @BlobCredential_ IS NOT NULL THEN ' CREDENTIAL = ''' + @BlobCredential_ + ''', ' ELSE '' END +
   
        CASE @StandbyMode_ WHEN 0 THEN 'NORECOVERY,' ELSE 'STANDBY =N' + '''' + ISNULL(@FromFileFullUNC_,SUBSTRING(#CTE.physical_device_name,1,LEN(#CTE.physical_device_name) - CHARINDEX('\',REVERSE(#CTE.physical_device_name)))) + '\' + d.name + '_' + REPLACE(REPLACE(SUBSTRING(CONVERT(VARCHAR(24),GETDATE(),127),12,12),':',''),'.','') + '_ROLLBACK_UNDO.bak ' + ''''  + ',' END + SPACE(1) + 
   
        'STATS=10' + SPACE(1) 
   
        AS Command,
        750000 AS Sequence,
        d.name AS database_name,
        #CTE.physical_device_name AS BackupDevice,
        #CTE.backupfinishdate,
        #CTE.backup_size,
        #CTE.first_lsn,
        #CTE.last_lsn,
        #CTE.fork_point_lsn, 
        #CTE.first_recovery_fork_guid,
        #CTE.last_recovery_fork_guid 
 
   
    FROM sys.databases d 
   
    INNER JOIN #CTE
      ON #CTE.database_name = d.name
      AND #CTE.family_sequence_number = 1 
 
    LEFT OUTER JOIN #Stripes
    ON #Stripes.database_name = d.name
      AND #Stripes.backupmediasetid = #CTE.backupmediasetid
      AND #Stripes.last_lsn = #CTE.last_lsn 
 
    LEFT OUTER JOIN #ForkPointsCount fpc
        ON fpc.database_name = #CTE.database_name
   
    WHERE #CTE.[type] = 'I'
    AND #CTE.family_sequence_number = 1
    AND @ExcludeDiffAndLogBackups_ IN (0,2)
    AND ISNULL(fpc.ForkPoints,0) < 2
       
    --------------------------------------------------------------
    UNION -- Log backups taken since most recent full or diff
    -------------------------------------------------------------- 
   
    SELECT
 
        'RESTORE LOG [' + CASE ISNULL(@TargetDatabase_,'') WHEN '' THEN d.[name] ELSE @TargetDatabase_ END + ']' + SPACE(1) + CHAR(13) + 
   
        CASE WHEN #CTE.physical_device_name LIKE 'http%' THEN ' FROM URL = ' ELSE ' FROM DISK = N' END + '''' +
   
        CASE ISNULL(@FromFileLogUNC_,'Actual')
        WHEN 'Actual' THEN #CTE.physical_device_name
        ELSE @FromFileLogUNC_ + SUBSTRING(#CTE.physical_device_name,LEN(#CTE.physical_device_name) - CHARINDEX('\',REVERSE(#CTE.physical_device_name),1) + 2,CHARINDEX('\',REVERSE(#CTE.physical_device_name),1) + 1)
        END + '''' + CHAR(13) + 
   
        -- Striped backup files
        CASE ISNULL(#Stripes.S2_pdn,'')
        WHEN '' THEN ''
        ELSE  ', DISK = N' + '''' + CASE ISNULL(@FromFileLogUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S2_pdn ELSE @FromFileLogUNC_ + SUBSTRING(#Stripes.S2_pdn,LEN(#Stripes.S2_pdn) - CHARINDEX('\',REVERSE(#Stripes.S2_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S2_pdn),1) + 1) END + ''''
        END + 
   
        CASE ISNULL(#Stripes.S3_pdn,'')
        WHEN '' THEN ''
        ELSE  ', DISK = N' + '''' + CASE ISNULL(@FromFileLogUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S3_pdn ELSE @FromFileLogUNC_ + SUBSTRING(#Stripes.S3_pdn,LEN(#Stripes.S3_pdn) - CHARINDEX('\',REVERSE(#Stripes.S3_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S3_pdn),1) + 1) END + ''''
        END + 
   
        CASE ISNULL(#Stripes.S4_pdn,'')
        WHEN '' THEN ''
        ELSE  ', DISK = N' + '''' + CASE ISNULL(@FromFileLogUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S4_pdn ELSE @FromFileLogUNC_ + SUBSTRING(#Stripes.S4_pdn,LEN(#Stripes.S4_pdn) - CHARINDEX('\',REVERSE(#Stripes.S4_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S4_pdn),1) + 1) END + ''''
        END + 
   
        CASE ISNULL(#Stripes.S5_pdn,'')
        WHEN '' THEN ''
        ELSE  ', DISK = N' + '''' + CASE ISNULL(@FromFileLogUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S5_pdn ELSE @FromFileLogUNC_ + SUBSTRING(#Stripes.S5_pdn,LEN(#Stripes.S5_pdn) - CHARINDEX('\',REVERSE(#Stripes.S5_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S5_pdn),1) + 1) END + ''''
        END + 
   
        CASE ISNULL(#Stripes.S6_pdn,'')
        WHEN '' THEN ''
        ELSE  ', DISK = N' + '''' + CASE ISNULL(@FromFileLogUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S6_pdn ELSE @FromFileLogUNC_ + SUBSTRING(#Stripes.S6_pdn,LEN(#Stripes.S6_pdn) - CHARINDEX('\',REVERSE(#Stripes.S6_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S6_pdn),1) + 1) END + ''''
        END + 
   
        CASE ISNULL(#Stripes.S7_pdn,'')
        WHEN '' THEN ''
        ELSE  ', DISK = N' + '''' + CASE ISNULL(@FromFileLogUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S7_pdn ELSE @FromFileLogUNC_ + SUBSTRING(#Stripes.S7_pdn,LEN(#Stripes.S7_pdn) - CHARINDEX('\',REVERSE(#Stripes.S7_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S7_pdn),1) + 1) END + ''''
        END + 
   
        CASE ISNULL(#Stripes.S8_pdn,'')
        WHEN '' THEN ''
        ELSE  ', DISK = N' + '''' + CASE ISNULL(@FromFileLogUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S8_pdn ELSE @FromFileLogUNC_ + SUBSTRING(#Stripes.S8_pdn,LEN(#Stripes.S8_pdn) - CHARINDEX('\',REVERSE(#Stripes.S8_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S8_pdn),1) + 1) END + ''''
        END + 
   
        CASE ISNULL(#Stripes.S9_pdn,'')
        WHEN '' THEN ''
        ELSE  ', DISK = N' + '''' + CASE ISNULL(@FromFileLogUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S9_pdn ELSE @FromFileLogUNC_ + SUBSTRING(#Stripes.S9_pdn,LEN(#Stripes.S9_pdn) - CHARINDEX('\',REVERSE(#Stripes.S9_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S9_pdn),1) + 1) END + ''''
        END + 
   
        CASE ISNULL(#Stripes.S10_pdn,'')
        WHEN '' THEN ''
        ELSE  ', DISK = N' + '''' + CASE ISNULL(@FromFileLogUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S10_pdn ELSE @FromFileLogUNC_ + SUBSTRING(#Stripes.S10_pdn,LEN(#Stripes.S10_pdn) - CHARINDEX('\',REVERSE(#Stripes.S10_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S10_pdn),1) + 1) END + ''''
        END + 
  
        CASE ISNULL(#Stripes.S11_pdn,'')
        WHEN '' THEN ''
        ELSE  ', DISK = N' + '''' + CASE ISNULL(@FromFileLogUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S11_pdn ELSE @FromFileLogUNC_ + SUBSTRING(#Stripes.S11_pdn,LEN(#Stripes.S10_pdn) - CHARINDEX('\',REVERSE(#Stripes.S11_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S11_pdn),1) + 1) END + ''''
        END + 
  
        CASE ISNULL(#Stripes.S12_pdn,'')
        WHEN '' THEN ''
        ELSE  ', DISK = N' + '''' + CASE ISNULL(@FromFileLogUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S12_pdn ELSE @FromFileLogUNC_ + SUBSTRING(#Stripes.S12_pdn,LEN(#Stripes.S12_pdn) - CHARINDEX('\',REVERSE(#Stripes.S12_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S12_pdn),1) + 1) END + ''''
        END + 
  
        CASE ISNULL(#Stripes.S13_pdn,'')
        WHEN '' THEN ''
        ELSE  ', DISK = N' + '''' + CASE ISNULL(@FromFileLogUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S13_pdn ELSE @FromFileLogUNC_ + SUBSTRING(#Stripes.S13_pdn,LEN(#Stripes.S13_pdn) - CHARINDEX('\',REVERSE(#Stripes.S13_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S13_pdn),1) + 1) END + ''''
        END + 
  
        CASE ISNULL(#Stripes.S14_pdn,'')
        WHEN '' THEN ''
        ELSE  ', DISK = N' + '''' + CASE ISNULL(@FromFileLogUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S14_pdn ELSE @FromFileLogUNC_ + SUBSTRING(#Stripes.S14_pdn,LEN(#Stripes.S14_pdn) - CHARINDEX('\',REVERSE(#Stripes.S14_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S14_pdn),1) + 1) END + ''''
        END + 
  
        CASE ISNULL(#Stripes.S15_pdn,'')
        WHEN '' THEN ''
        ELSE  ', DISK = N' + '''' + CASE ISNULL(@FromFileLogUNC_,'Actual') WHEN 'Actual' THEN #Stripes.S15_pdn ELSE @FromFileLogUNC_ + SUBSTRING(#Stripes.S15_pdn,LEN(#Stripes.S15_pdn) - CHARINDEX('\',REVERSE(#Stripes.S15_pdn),1) + 2,CHARINDEX('\',REVERSE(#Stripes.S15_pdn),1) + 1) END + ''''
        END + 
  
        CASE @StandbyMode_ WHEN 0 THEN ' WITH NORECOVERY,' ELSE ' WITH STANDBY =N' + '''' + ISNULL(@FromFileFullUNC_,SUBSTRING(#CTE.physical_device_name,1,LEN(#CTE.physical_device_name) - CHARINDEX('\',REVERSE(#CTE.physical_device_name)))) + '\' + d.name + '_' + REPLACE(REPLACE(SUBSTRING(CONVERT(VARCHAR(24),GETDATE(),127),12,12),':',''),'.','') + '_ROLLBACK_UNDO.bak ' + ''''  + ',' END + SPACE(1) + 
   
        CASE WHEN #CTE.physical_device_name LIKE 'http%' AND @BlobCredential_ IS NOT NULL THEN ' CREDENTIAL = ''' + @BlobCredential_ + ''', ' ELSE '' END + 
   
        CASE #CTE.has_backup_checksums WHEN 1 THEN ' CHECKSUM, ' ELSE ' ' END + 
   
        + 'FILE = ' + CAST(#CTE.position AS VARCHAR(5)) +
        ' ,STOPAT = ' + '''' + CONVERT(VARCHAR(21),@StopAt_,120) + '''' 
   
        AS Command,
        1000000 AS Sequence,
        d.name AS database_name,
        #CTE.physical_device_name AS BackupDevice,
        #CTE.backupfinishdate,
        #CTE.backup_size,
        #CTE.first_lsn,
        #CTE.last_lsn,
        #CTE.fork_point_lsn,
        #CTE.first_recovery_fork_guid,
        #CTE.last_recovery_fork_guid 
          
   
    FROM sys.databases d 
     
    INNER JOIN #CTE
        ON #CTE.database_name = d.name
        AND #CTE.family_sequence_number = 1 
 
    LEFT OUTER JOIN #Stripes
        ON #Stripes.database_name = d.name
        AND #Stripes.backupmediasetid = #CTE.backupmediasetid
        AND #Stripes.last_lsn = #CTE.last_lsn 
   
    LEFT OUTER JOIN
    (
        SELECT database_name, MAX(last_lsn) last_lsn
        FROM #CTE 
        WHERE [type] = 'I'
 
        GROUP BY database_name
    ) after_diff
        ON after_diff.database_name = #CTE.database_name
 
    LEFT OUTER JOIN #ForkPointsCount fpc
        ON fpc.database_name = #CTE.database_name
 
    WHERE #CTE.[type] = 'L'
    AND #CTE.family_sequence_number = 1
    AND #CTE.last_lsn >= CASE WHEN @ExcludeDiffAndLogBackups_ = 0 THEN ISNULL(after_diff.last_lsn,'0') ELSE 0 END
    AND @ExcludeDiffAndLogBackups_ IN (0,3)

    AND ISNULL(fpc.ForkPoints,0) < 2
 
    --------------------------------------------------------------
    UNION -- SET SINGLE USER WITH ROLLBACK IMMEDIATE
    --------------------------------------------------------------
    SELECT
 
       'BEGIN TRY ALTER DATABASE [' + CASE ISNULL(@TargetDatabase_,'') WHEN '' THEN d.[name] ELSE @TargetDatabase_ END + ']' + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE END TRY BEGIN CATCH PRINT' + '''' + 'SET SINGLE USER FAILED' + '''' + ' END CATCH' AS Command,
        0 AS Sequence,
        d.name AS database_name,
        '' AS BackupDevice,
        GETDATE() AS backupfinishdate,
        #CTE.backup_size,
        '0' AS first_lsn,
        '0' AS last_lsn,
        '0' AS fork_point_lsn ,
        '00000000-0000-0000-0000-000000000000' AS first_recovery_fork_guid,
        '00000000-0000-0000-0000-000000000000' AS last_recovery_fork_guid     
 
    FROM sys.databases d 
   
    INNER JOIN #CTE
      ON #CTE.database_name = d.name 
 
    LEFT OUTER JOIN #ForkPointsCount fpc
        ON fpc.database_name = #CTE.database_name
 
    WHERE #CTE.[type] = 'D'
    AND @SetSingleUser_ = 1
    AND ISNULL(fpc.ForkPoints,0) < 2
   
    --------------------------------------------------------------
    UNION -- Restore WITH RECOVERY
    --------------------------------------------------------------
    SELECT
 
       'RESTORE DATABASE [' + CASE ISNULL(@TargetDatabase_,'') WHEN '' THEN d.[name] ELSE @TargetDatabase_ END + ']' + SPACE(1) + 'WITH RECOVERY' AS Command,
        1000001 AS Sequence,
        d.name AS database_name,
        '' AS BackupDevice,
        GETDATE() AS backupfinishdate,
        #CTE.backup_size,
        #CTE.first_lsn,
        '999999999999999999997' AS last_lsn,
        #CTE.fork_point_lsn ,
        '00000000-0000-0000-0000-000000000000' AS first_recovery_fork_guid,
        '00000000-0000-0000-0000-000000000000' AS last_recovery_fork_guid                  
   
    FROM sys.databases d 
   
    INNER JOIN #CTE
      ON #CTE.database_name = d.name 
 
    LEFT OUTER JOIN #ForkPointsCount fpc
        ON fpc.database_name = #CTE.database_name
 
    WHERE #CTE.[type] = 'D'
    AND @WithRecovery_ = 1 
    AND ISNULL(fpc.ForkPoints,0) < 2
   
    --------------------------------------------------------------
    UNION -- CHECKDB
    --------------------------------------------------------------
    SELECT
 
        'DBCC CHECKDB(' + '''' + CASE ISNULL(@TargetDatabase_,'') WHEN '' THEN d.[name] ELSE @TargetDatabase_ END + '''' + ') WITH NO_INFOMSGS, ALL_ERRORMSGS' AS Command,
        1000002 AS Sequence,
        d.name AS database_name,
        '' AS BackupDevice,
        DATEADD(minute,1,GETDATE()) AS backupfinishdate,
        #CTE.backup_size,
        #CTE.first_lsn,
        '999999999999999999998' AS last_lsn,
        #CTE.fork_point_lsn,
        '00000000-0000-0000-0000-000000000000' AS first_recovery_fork_guid,
        '00000000-0000-0000-0000-000000000000' AS last_recovery_fork_guid                      
  
    FROM sys.databases d 
   
    INNER JOIN #CTE
      ON #CTE.database_name = d.name 
 
    LEFT OUTER JOIN #ForkPointsCount fpc
        ON fpc.database_name = #CTE.database_name
 
    WHERE #CTE.[type] = 'D'
    AND @WithCHECKDB_ = 1
    AND @WithRecovery_ = 1 
    AND ISNULL(fpc.ForkPoints,0) < 2
   
    --------------------------------------------------------------
    UNION -- Drop database after restore
    --------------------------------------------------------------
    SELECT
 
        -- Comment out all commands if multiple forking points exist 
        --CASE WHEN @CommentOut = 1 THEN '  -- Multipe backup fork points detected, command suppressed -- ' ELSE '' END +
 
       'DROP DATABASE [' + CASE ISNULL(@TargetDatabase_,'') WHEN '' THEN d.[name] ELSE @TargetDatabase_ END + ']' + SPACE(1) AS Command,
        1000003 AS Sequence,
        d.name AS database_name,
        '' AS BackupDevice,
        GETDATE() AS backupfinishdate,
        #CTE.backup_size,
        #CTE.first_lsn,
        '999999999999999999999' AS last_lsn,
        #CTE.fork_point_lsn,
        '00000000-0000-0000-0000-000000000000' AS first_recovery_fork_guid,
        '00000000-0000-0000-0000-000000000000' AS last_recovery_fork_guid              
          
                   
   
    FROM sys.databases d 
   
    INNER JOIN #CTE
      ON #CTE.database_name = d.name 
 
    LEFT OUTER JOIN #ForkPointsCount fpc
        ON fpc.database_name = #CTE.database_name
 
    WHERE #CTE.[type] = 'D'
    AND @DropDatabaseAfterRestore_ = 1 
    AND ISNULL(fpc.ForkPoints,0) < 2
 
    --------------------------------------------------------------
    UNION -- RAISERROR suppress restore when multiple forkpoints exist
    --------------------------------------------------------------
    SELECT
 
       'RAISERROR (' + '''' + 'Multiple restores per performed between the selected full backup and stop at time for database ' + CASE ISNULL(@TargetDatabase_,'') WHEN '' THEN d.[name] ELSE @TargetDatabase_ END + ' - Restore Gene processing aborted.' + '''' + ',0,0);' +  + SPACE(1) AS Command,
        1 AS Sequence,
        d.name AS database_name,
        '' AS BackupDevice,
        GETDATE() AS backupfinishdate,
        #CTE.backup_size,
        '000000000000000000000' AS first_lsn,
        '999999999999999999999' AS last_lsn,
        #CTE.fork_point_lsn,
        '00000000-0000-0000-0000-000000000000' AS first_recovery_fork_guid,
        '00000000-0000-0000-0000-000000000000' AS last_recovery_fork_guid              
          
    FROM sys.databases d 
   
    INNER JOIN #CTE
      ON #CTE.database_name = d.name 
 
    LEFT OUTER JOIN #ForkPointsCount fpc
        ON fpc.database_name = #CTE.database_name
 
    WHERE #CTE.[type] = 'D'
    AND ISNULL(fpc.ForkPoints,0) > 1
 
    --------------------------------------------------------------
    UNION -- WITH MOVE secondary data files, allows for up to 32769/2 file groups
    --------------------------------------------------------------
 
    SELECT
 
        -- Comment out all commands if multiple forking points exist 
        --CASE WHEN @CommentOut = 1 THEN '  -- Multipe backup fork points detected, command suppressed -- ' ELSE '' END +
 
        '   ,MOVE N' + '''' + b.name + '''' + ' TO N' + '''' +
   
        CASE b.type_desc 
   
            WHEN 'ROWS' THEN
                CASE ISNULL(@WithMoveDataFiles_,'Actual')
                WHEN 'Actual' THEN CASE ISNULL(@TargetDatabase_,'') WHEN '' THEN b.Physical_Name ELSE SUBSTRING(b.Physical_Name,1,CASE CHARINDEX('.',b.Physical_Name) WHEN 0 THEN LEN(b.Physical_Name) ELSE CHARINDEX('.',b.Physical_Name) - 1 END) + '_' + @TargetDatabase_ + '.ndf'  END
                ELSE CASE ISNULL(@TargetDatabase_,'') WHEN '' THEN  @WithMoveDataFiles_ + SUBSTRING(b.Physical_Name,LEN(b.Physical_Name) - CHARINDEX('\',REVERSE(b.Physical_Name),1) + 2,CHARINDEX('\',REVERSE(b.Physical_Name),1) + 1)
                ELSE @WithMoveDataFiles_ + SUBSTRING (b.Physical_Name, (LEN(b.Physical_Name)-CHARINDEX('\',REVERSE(b.Physical_Name)) + 2),CHARINDEX('\',REVERSE(b.Physical_Name)) - 1 - CHARINDEX('.',REVERSE(b.Physical_Name))) +  '_' + @TargetDatabase_ + '.ndf'  END
            END 
   
            WHEN 'FILESTREAM' THEN
                CASE ISNULL(@WithMoveFileStreamFile_,'Actual')
                WHEN 'Actual' THEN CASE ISNULL(@TargetDatabase_,'') WHEN '' THEN b.Physical_Name ELSE SUBSTRING(b.Physical_Name,1,CASE CHARINDEX('.',b.Physical_Name) WHEN 0 THEN LEN(b.Physical_Name) ELSE CHARINDEX('.',b.Physical_Name) - 1 END) + '_' + @TargetDatabase_ END
                ELSE CASE ISNULL(@TargetDatabase_,'') WHEN '' THEN  @WithMoveFileStreamFile_ + SUBSTRING(b.Physical_Name,LEN(b.Physical_Name) - CHARINDEX('\',REVERSE(b.Physical_Name),1) + 2,CHARINDEX('\',REVERSE(b.Physical_Name),1) + 1)
                ELSE @WithMoveFileStreamFile_ + SUBSTRING (b.Physical_Name, (LEN(b.Physical_Name)-CHARINDEX('\',REVERSE(b.Physical_Name)) + 2),CHARINDEX('\',REVERSE(b.Physical_Name)) - 1 - CHARINDEX('.',REVERSE(b.Physical_Name))) +  '_' + @TargetDatabase_ END
            END  
   
            WHEN 'LOG' THEN
                CASE ISNULL(@WithMoveLogFile_,'Actual')
                WHEN 'Actual' THEN CASE ISNULL(@TargetDatabase_,'') WHEN '' THEN b.Physical_Name ELSE SUBSTRING(b.Physical_Name,1,CASE CHARINDEX('.',b.Physical_Name) WHEN 0 THEN LEN(b.Physical_Name) ELSE CHARINDEX('.',b.Physical_Name) - 1 END) + '_' + @TargetDatabase_ + '.ldf' END
                ELSE CASE ISNULL(@TargetDatabase_,'') WHEN '' THEN  @WithMoveLogFile_ + SUBSTRING(b.Physical_Name,LEN(b.Physical_Name) - CHARINDEX('\',REVERSE(b.Physical_Name),1) + 2,CHARINDEX('\',REVERSE(b.Physical_Name),1) + 1)
                ELSE @WithMoveLogFile_ + SUBSTRING (b.Physical_Name, (LEN(b.Physical_Name)-CHARINDEX('\',REVERSE(b.Physical_Name)) + 2),CHARINDEX('\',REVERSE(b.Physical_Name)) - 1 - CHARINDEX('.',REVERSE(b.Physical_Name))) +  '_' + @TargetDatabase_ + '.ldf'  END 
   
            END  
   
        END
   
        + '''',
        b.file_id AS Sequence,
        DB_NAME(b.database_id) AS database_name,
        'SECONDARY FULL' AS BackupDevice,
        #CTE.backupfinishdate,
        #CTE.backup_size,
        #CTE.first_lsn,
        #CTE.last_lsn,
        #CTE.fork_point_lsn,
        #CTE.first_recovery_fork_guid,
        #CTE.last_recovery_fork_guid              
              
    FROM sys.master_files b
    INNER JOIN #CTE
      ON #CTE.database_name = DB_NAME(b.database_id) 
 
    LEFT OUTER JOIN #ForkPointsCount fpc
        ON fpc.database_name = #CTE.database_name
 
    WHERE #CTE.[type] = 'D'
    AND b.type_desc IN ('ROWS','FILESTREAM','LOG')
    AND b.file_id > 2
    AND ISNULL(fpc.ForkPoints,0) < 2
   
    --------------------------------------------------------------
    ) a
    LEFT OUTER JOIN #tmpDatabases b
        ON a.database_name = b.DatabaseName
    -------------------------------------------------------------- 
   
    WHERE (@Database_ IS NULL OR b.Selected = 1 OR @Database_ = a.database_name)
    AND (@IncludeSystemDBs_ = 1 OR a.database_name NOT IN('master','model','msdb'))
    AND a.last_lsn >= @LogShippingLastLSN_;
   
    CREATE INDEX IDX_RestoreGeneResults ON #RestoreGeneResults (database_name,SortSequence,BackupDate);
   
    --------------------------------------------------------------
    -- Result Set
    IF @SuppressWithMove_ = 1
    BEGIN
        IF @RestoreScriptOnly_ = 0
        BEGIN
   
            SELECT x4.TSQL, x4.BackupDate, x4.BackupDevice, x4.first_lsn, x4.last_lsn, x4.fork_point_lsn, x4.first_recovery_fork_guid, x4.last_recovery_fork_guid, x4.database_name, x4.SortSequence
            FROM #RestoreGeneResults x4
            WHERE ISNULL(x4.BackupDevice,'') <> 'SECONDARY FULL'
            ORDER BY
                x4.database_name,
                x4.SortSequence,
                x4.BackupDate
        END
        ELSE
        BEGIN
            SELECT x4.TSQL AS [--TSQL]
            FROM #RestoreGeneResults x4
            WHERE ISNULL(x4.BackupDevice,'') <> 'SECONDARY FULL'
            ORDER BY
                x4.database_name,
                x4.SortSequence,
                x4.BackupDate
        END
    END
    ELSE
    BEGIN
        IF @PivotWithMove_ = 1
        BEGIN
   
            IF @RestoreScriptOnly_ = 0
            BEGIN
                SELECT
                    x4.TSQL, x4.BackupDate, x4.BackupDevice, x4.first_lsn, x4.last_lsn, x4.fork_point_lsn, x4.first_recovery_fork_guid, x4.last_recovery_fork_guid, x4.database_name, x4.SortSequence
                FROM #RestoreGeneResults x4
                ORDER BY
                    x4.database_name,
                    x4.SortSequence,
                    x4.BackupDate
            END
            ELSE
            BEGIN
                SELECT
                    x4.TSQL AS [--TSQL]
                FROM #RestoreGeneResults x4
                ORDER BY
                    x4.database_name,
                    x4.SortSequence,
                    x4.BackupDate
            END
   
        END
        ELSE
        BEGIN
            IF @RestoreScriptOnly_ = 0
            BEGIN
                WITH WithMoves AS
                (
                    SELECT
                        last_lsn,
                        STUFF((SELECT ' ' + TSQL FROM #RestoreGeneResults x3 WHERE x3.last_lsn = x2.last_lsn AND ISNULL(x3.BackupDevice,'') = 'SECONDARY FULL' ORDER BY x3.SortSequence FOR XML PATH, TYPE).value('.[1]', 'nvarchar(max)'), 1,1,'') AS WithoveCmds
                    FROM #RestoreGeneResults x2
                    GROUP BY last_lsn
                )
   
                SELECT
                    CASE @SuppressWithMove_ WHEN 0 THEN CASE ISNULL(x5.WithoveCmds,'') WHEN '' THEN x4.TSQL ELSE x4.TSQL + ' ' + x5.WithoveCmds END
                    ELSE x4.TSQL
                    END AS TSQL,
                    x4.BackupDate, x4.BackupDevice, x4.last_lsn, x4.database_name, x4.SortSequence
                FROM #RestoreGeneResults x4
                LEFT OUTER JOIN WithMoves x5
                    ON x4.last_lsn = x5.last_lsn
                WHERE ISNULL(x4.BackupDevice,'') <> 'SECONDARY FULL'
                ORDER BY
                    x4.database_name,
                    x4.SortSequence,
                    x4.BackupDate
            END
            ELSE
            BEGIN
                WITH WithMoves AS
                (
                    SELECT
                        last_lsn,
                        STUFF((SELECT ' ' + TSQL FROM #RestoreGeneResults x3 WHERE x3.last_lsn = x2.last_lsn AND ISNULL(x3.BackupDevice,'') = 'SECONDARY FULL' ORDER BY x3.SortSequence FOR XML PATH, TYPE).value('.[1]', 'nvarchar(max)'), 1,1,'') AS WithoveCmds
                    FROM #RestoreGeneResults x2
                    GROUP BY last_lsn
                )
   
                SELECT
                    CASE @SuppressWithMove_ WHEN 0 THEN CASE ISNULL(x5.WithoveCmds,'') WHEN '' THEN x4.TSQL ELSE x4.TSQL + ' ' + x5.WithoveCmds END
                    ELSE x4.TSQL
                    END AS [--TSQL]
                FROM #RestoreGeneResults x4
                LEFT OUTER JOIN WithMoves x5
                    ON x4.last_lsn = x5.last_lsn
                WHERE ISNULL(x4.BackupDevice,'') <> 'SECONDARY FULL'
                ORDER BY
                    x4.database_name,
                    x4.SortSequence,
                    x4.BackupDate
            END
        END
    END
END
   
GO

145 replies

  1. I have a use case where I would like to restore to the latest full and diff, but not all the logs. Is there an easy way to do this with the parameters the SP currently has? Currently to accomplish this I would have to update/modify the SP by either adding a parameter or commenting out the log restore portions, correct? Could this functionality be built into the SP?

    Ex:

    @ExcludeDiffAndLogBackups=’1′ will get the last full and @ExcludeDiffAndLogBackups=’0′ will get the last full and all logs.

    Like

    • Hi Bob, RestoreGene won’t meet your requirement at the moment with the existing parameters, I’ll add this in the next release. Thanks for the suggestion and best wishes, Paul

      Like

      • I will be interested to see how you accomplish this as there are many different ways. Once upon a time I want to say I changed parts of @ExcludeDiffAndLogBackups from being a BIT variable to an INT variable.

        Then added AND @ExcludeDiffAndLogBackups_ IN (0,2)

        and just before — Result Set

        IF @ExcludeDiffAndLogBackups_ = 2
        BEGIN
        DELETE FROM #RestoreGeneResults WHERE TSQL LIKE ‘%RESTORE LOG%’
        end

        That was simple and quick without having to increase the total lines of code and then allowed for @ExcludeDiffAndLogBackups to use 0,1,2 where 2 would be Full and Diff backups with logs excluded.
        What it didn’t account for was anyone expecting to see the SortSequence go 1,2,3 would now be 1,2 and jump to whatever was removed in the delete statement however it was still 100% functional and allowed for the least amount of change overall. At least in my eyes. Granted you are doing more work then if you were to only select what you requested upfront.

        Looking back at this again I see that rather than adding @ExcludeDiffAndLogBackups_ IN (0,2) to both lines 1081 and 1219 simply adding it to the one line allows for not having to have a DELETE on the Logs because they are not selected upfront and honors the SortSequence and only does the work that is needed.

        I will look forward to your next release and how it is implemented.

        Like

        • Thanks for the idea Chris, your approach sounds good and I’ll try and get it implemented in the next few weeks.

          A lot of RestoreGene was rewritten in version 8 specifically to simplify it, changing the bit parameter to an int sounds nice as it could be tuned to honour the existing functionality invoked when 0 and 1 values are received.

          Best wishes
          Paul

          Like

          • Hi Bob, Chris

            Enhancements to the @ExcludeDiffAndLogBackups parameters

            Change from parameter data type from bit to int

            0 (default) = full, diff and logs, 1 = full only, 2 = full and diff, 3 = full and logs

            Existing BIT data type functionality retained and extended by new values 2 and 3.

            Best regards
            Paul

            Like

  2. Number of files is not supported? for example:

    BACKUP DATABASE [Northwind] TO
    DISK = ‘C:\Northwind_file1.bak’,
    DISK = ‘D:\Northwind_file2.bak’,
    DISK = ‘E:\Northwind_file3.bak’,
    DISK = ‘F:\Northwind_file4.bak’
    WITH INIT , NOUNLOAD , NAME = ‘Northwind backup’, NOSKIP , STATS = 10, NOFORMAT

    which parameter to use to build the recovery?

    Like

  3. HI
    It seems that copy_only Backups are also included in the outputscript. I don’t have yet found it wher to correct this….

    Thank’s
    Andy

    Like

    • Hi Andy,
      Version 8.00 was a major code refactoring exercise, lots of unnecessary stuff was removed from the procedure. This check to exclude copy only backups was removed by accident, it’s been added back now in version 8.05.
      Many thanks for the feedback and best wishes
      Paul

      Like

  4. Thanks for the script,
    I am using version V7.0 of the stored procedure.

    I am running the following command, however it returns 0 rows (I corrected the exec command)
    — =============================================
    EXEC [dbo].[sp_RestoreGene]
    @Database = N’SUSDB’,
    @TargetDatabase = N’SUSDB_Test1′,
    @WithMoveDataFiles = N’H:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA’,
    @WithMoveLogFile = N’H:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA’,
    @WithMoveFileStreamFile = N’T:\WSUS_Backup’,
    @FromFileLogUNC = N’T:\WSUS_Backup\SUSDB’,
    @StandbyMode = 1,
    @WithRecovery = 0,
    @WithCHECKDB = 0,
    @STOPAT = ‘2018-03-14 08:42:37′,
    @WithReplace = 0
    GO
    — =============================================

    I checked the history table by running the following:

    select database_name,name, type, backup_start_date, backup_finish_date from msdb.dbo.backupset
    where database_name =’susdb’
    order by backup_finish_date
    GO

    — =============================================

    and it returns the following rows:
    database_name name type backup_start_date backup_finish_date
    SUSDB SUSDB_backup_2018_02_15_040502_4127893 D 2018-02-15 04:05:03.000 2018-02-15 04:05:33.000
    SUSDB SUSDB_backup_2018_02_16_040501_6887245 L 2018-02-16 04:05:01.000 2018-02-16 04:05:43.000
    SUSDB SUSDB_backup_2018_02_17_040501_4192123 L 2018-02-17 04:05:01.000 2018-02-17 04:05:06.000
    SUSDB SUSDB_backup_2018_02_18_040502_7774583 D 2018-02-18 04:05:03.000 2018-02-18 04:05:33.000
    SUSDB SUSDB_backup_2018_02_19_013504_4637961 L 2018-02-19 01:35:04.000 2018-02-19 01:35:13.000
    SUSDB SUSDB_backup_2018_02_19_040501_4440761 L 2018-02-19 04:05:01.000 2018-02-19 04:05:02.000
    SUSDB SUSDB_backup_2018_02_20_040502_4488353 D 2018-02-20 04:05:03.000 2018-02-20 04:05:33.000
    SUSDB SUSDB_backup_2018_02_21_040501_4028001 L 2018-02-21 04:05:01.000 2018-02-21 04:05:10.000
    SUSDB SUSDB_backup_2018_02_22_040502_4546654 D 2018-02-22 04:05:03.000 2018-02-22 04:05:33.000
    SUSDB SUSDB_backup_2018_02_23_040501_1432811 L 2018-02-23 04:05:01.000 2018-02-23 04:05:15.000
    SUSDB SUSDB_backup_2018_02_24_040501_9675345 L 2018-02-24 04:05:02.000 2018-02-24 04:05:07.000
    SUSDB SUSDB_backup_2018_02_25_040502_7022576 D 2018-02-25 04:05:03.000 2018-02-25 04:05:33.000
    SUSDB SUSDB_backup_2018_02_26_040501_9333934 L 2018-02-26 04:05:02.000 2018-02-26 04:05:50.000
    SUSDB SUSDB_backup_2018_02_27_040502_8944373 D 2018-02-27 04:05:03.000 2018-02-27 04:05:33.000
    SUSDB SUSDB_backup_2018_02_28_040501_5614082 L 2018-02-28 04:05:01.000 2018-02-28 04:05:11.000
    SUSDB SUSDB_backup_2018_03_01_040503_6788159 D 2018-03-01 04:05:04.000 2018-03-01 04:05:34.000
    SUSDB SUSDB_backup_2018_03_02_040501_3913949 L 2018-03-02 04:05:01.000 2018-03-02 04:05:10.000
    SUSDB SUSDB_backup_2018_03_03_040501_6257470 L 2018-03-03 04:05:01.000 2018-03-03 04:05:05.000
    SUSDB SUSDB_backup_2018_03_04_040502_6369748 D 2018-03-04 04:05:03.000 2018-03-04 04:05:33.000
    SUSDB SUSDB_backup_2018_03_05_040501_7150675 L 2018-03-05 04:05:02.000 2018-03-05 04:05:11.000
    SUSDB SUSDB_backup_2018_03_06_040502_2922258 D 2018-03-06 04:05:03.000 2018-03-06 04:05:32.000
    SUSDB SUSDB_backup_2018_03_07_040501_2460415 L 2018-03-07 04:05:01.000 2018-03-07 04:05:11.000
    SUSDB SUSDB_backup_2018_03_08_040502_1101755 D 2018-03-08 04:05:03.000 2018-03-08 04:05:32.000
    SUSDB SUSDB_backup_2018_03_09_040501_0518502 L 2018-03-09 04:05:01.000 2018-03-09 04:05:10.000
    SUSDB SUSDB_backup_2018_03_10_040501_5461780 L 2018-03-10 04:05:01.000 2018-03-10 04:05:06.000
    SUSDB SUSDB_backup_2018_03_11_040502_6551662 D 2018-03-11 04:05:03.000 2018-03-11 04:05:33.000
    SUSDB SUSDB_backup_2018_03_12_040501_5307497 L 2018-03-12 04:05:01.000 2018-03-12 04:05:10.000
    SUSDB SUSDB_backup_2018_03_13_040502_1695132 D 2018-03-13 04:05:03.000 2018-03-13 04:05:32.000
    SUSDB SUSDB_backup_2018_03_14_040501_2744958 L 2018-03-14 04:05:01.000 2018-03-14 04:05:09.000
    SUSDB SUSDB_backup_2018_03_14_094236_7501922 L 2018-03-14 09:42:37.000 2018-03-14 09:42:39.000
    SUSDB SUSDB_backup_2018_03_15_040501_2010587 D 2018-03-15 04:05:02.000 2018-03-15 04:05:32.000
    SUSDB SUSDB_backup_2018_03_16_040501_8363626 L 2018-03-16 04:05:02.000 2018-03-16 04:05:41.000
    SUSDB SUSDB_backup_2018_03_17_040501_8968769 L 2018-03-17 04:05:02.000 2018-03-17 04:05:06.000

    — =============================================

    Please advise.
    Thanks.

    Like

  5. Hi Paul: Would it be possible to add an option to insert a “go” after each restore is complete? Many thanks for your scripts. They are truly amazing.

    Like

  6. Paul, we are using your script for restores and it has been working great for us. So first off, thanks for putting this together!
    Now to my issue… we just came across an issue when multiple Full backups are taken before and after a restore occurs. It’s difficult to explain, but I’ll try:

    1. Full backup ran @ 22:18 with last_lsn of 131124000005401900001
    2. Restore done from a prior backup (before 22:18)
    3. New full backup ran @ 22:31 with last lsn of 131124000002149500001

    What we saw when we ran the script to generate the restore log chain (for another restore we had to do after 22:31), there were several log backup files that were skipped. I traced it back to lines 603 & 604 where you are getting the MAX of both the backup_start_date and last_lsn columns. In our case, those max values came from 2 different rows (max date was 22:31 backup and max lsn came from the 22:18 backup). I think this is a fairly rare scenario, but it happened to us.
    I was able to get the proper list of backup sets by adding a window function, but if you want the script backward compatible you would probably want to go a different route (probably subquery to get Max start date and then get last lsn for that backup?).
    SELECT database_name,backup_start_date,last_lsn
    FROM (
    SELECT
    a.database_name
    ,a.backup_start_date backup_start_date
    ,a.last_lsn last_lsn
    , ROW_NUMBER() OVER (ORDER BY a.backup_start_date Desc) AS RowNum
    FROM msdb.dbo.backupset a
    JOIN msdb.dbo.backupmediafamily b
    ON a.media_set_id = b.media_set_id
    WHERE a.[type] = ‘D’
    AND ( @Database_ IS NULL OR a.database_name = @Database_)
    AND device_type IN (SELECT device_type FROM #DeviceTypes)
    AND a.is_copy_only = 0
    AND b.mirror = 0
    AND a.backup_start_date <= @LogShippingStartTime_
    ) x
    WHERE RowNum = 1

    I'm hoping we never have another night like last night so we never run into this again, but thought we should share this with you in case someone else runs into a similar issue in the future.

    Like

    • Hi Mike,

      Fork points are really difficult to manage in Restore Gene as there are so many possible ‘scenarios’. Version 8 has been changed so that if there is more than one fork point LSN between the selected full backup and the @StopAt point Restore Gene raises an information message rather than generating a restore script.

      I’ve always felt the strengths of Restore Gene are it’s flexibility and automation possibilities, for really advanced DR recovery the wizard in SSMS is a better bet. If there are multiple fork points then I don’t think Restore Gene is the right tool.

      This might not completely fixes the issue you’ve described, I’ll attempt to reproduce it again later this year when things are less busy.

      Thanks and regards
      Paul

      Like

  7. Still using these awesome scripst, thanks. Got another issue. line 1182 does not escape the database name. (line 1202 is correct).

    Like

  8. Hello Paul,
    We have been using your RestoreGene since long time and it work fine with us.
    A while ago, you added a new parameter for us (November 5th, 2016 – V6.54) these was very helpful.
    This time, i have been challenging new things, recently we have increased stripes backup files from 10 to 15 which is not supported by restore gene. Each time, result generate as blank.

    At this moment , do you have any plans to add more strip files in your RestoreGene?
    Thank you for your help.

    Like

  9. Hi Paul, Thank you for such a great Automated script. is it possible to generate the restore script for all the DBs in one instance?

    Like

  10. Hi Paul,

    Just trying out your script for the first time, it is awesome – but I do have a problem I cannot solve. In the ‘Restore Database’ statement the ‘From Disk’ parameter doesn’t include the filename.

    If I include the path in the parameter @FromFileFullUNC it looks like this:
    RESTORE DATABASE db
    FROM DISK = N’T:\SQL Server\Backup\FULL\’ …

    If I exclude the @FromFileFullUNC parameter, it puts a GUID in place of the path:
    RESTORE DATABASE db
    FROM DISK = N'{4DDB5E19-0E98-4452-9258-ECE963F50CDD}5′ …

    Any help would be much appreciated.

    Cheers.

    Like

    • Hi Adrian, if you query the table msdb.dbo.backupmediafamily , is that GUID stored anywhere ? I’m a bit stumped, it’s the first time this has happened.
      Best wishes
      Paul

      Like

      • Hi Paul,

        Appreciate your reply. I looked in the msdb.dbo.backupmediafamily table I found the GUID and it was associated with a type 7 record. Not sure yet how these got there as we only do backups to disk. Anyway, adding the parameter @IncludeDeviceType7 = 0 worked like a charm.

        Thanks again,
        Adrian.

        Like

  11. Might be a good idea to incorporate some linked server functionality for centralized management compatibility. Adding a parameter to pull information from the msdb and master db’s from linked servers.

    Like

    • Hi Carlos,

      Its an interesting idea but, rather than changing sp_RestoreGene code, it would be much easier to install it on all servers then call it on remote servers from a CMS using a four part name.

      IE EXEC servername.master.dbo.sp_RestoreGene .

      Thanks and regards
      Paul

      Like

  12. Something else I’ve noticed. When a new database file is created after the latest full backup, the restore script generated will not work when specifying with move. This is because the script tries to move the new file, which did not exist in the backup. I have checked the script generated using SSMS’s restore wizard and it has the same issue. I think to fix this you can add this condition on line 1306: AND b.[differential_base_lsn] IS NOT NULL. That should filter out files that were created after the last full backup. That fixes the full backup restoration, however this doesn’t completely fix the issue since once the script gets to restoring the log backup that contains the new file creation, it will try and create it in whatever the original location was and still error out:

    Msg 3634, Level 16, State 1, Line 273
    The operating system returned the error ‘3(The system cannot find the path specified.)’ while attempting ‘CreateFileW’ on ‘C:\Somepath\Somefile.ndf’.

    I think failing at the log backup restore is better than failing at the full backup restore, though.

    Like

    • After some further poking, it looks like to get this to work you would need to find what log backup the file was created in, and append a MOVE for the file to that restore command. I’ll see if I can figure out how to do that.

      Like

      • Hi,
        The problem is the file details aren’t stored in backup history, there’s no simple way of knowing whether files have been added or removed between backups. There’s a note about this issue in the limitations documentation, it’s only an issue if you over-ride the restore file locations (IE, use WITH MOVE).
        Thanks for looking at it, cheers
        Paul

        Like

  13. I think there is an issue with the condition on line 654. I have run into a case where an extra log backup is trying to apply. I think this is due to the following time table:

    FULL backup starts at 12:30 AM
    LOG backup starts at 1 AM
    LOG backup finishes at 1:01 AM
    FULL backup finishes at 1:05 AM
    LOG backup starts at 2 AM
    LOG backup finishes at 2:01 AM

    In this case, the 1 AM LOG backup isn’t required, since the last LSN in the FULL backup is newer than the last LSN in that LOG backup. However, since only the start dates are being compared, it is included in the script. I think the fix is to just add in “AND bs.last_lsn > y.last_lsn”, and add “,MAX(last_lsn) last_lsn” after line 600.

    Like

  14. Hi Paul,

    I’m using the proc to generate restores of databases with a large number of data files and the output seems to be getting lopped off.

    For example MOVE N’Rdw_FactGenLookbackRevenue_100277′ TO N’G:\MS

    Not sure if there’s a limitation in the number of files that the proc concatenates?? I tried to set my query options at larger numbers but still no luck. Please advise. Is there a parm that I can set in the proc maybe?

    Bob

    Like

    • Hi Bob,
      You need to set the PivotWithMove parameter to 1 and the SupressWithMove parameter to 0 and it should then be Ok. The restore script will be over many rows but it’s still valid. I have this problem at work too.
      Cheers
      Paul

      Like

      • Hi Paul,
        Your quick response is much appreciated. I added the parameters but errored out on the SupressWithMove parameter. I received the “Must Declare Scalar Variable Error”. It took the PivotWithMove parameter. Here’s how I’m executing.

        USE Master
        Go

        exec
        dbo.sp_RestoreGene @database=DB List,
        @withrecovery=1,
        @WithReplace = 1,
        @PivotWithMove = 1,
        –@SupressWithMove = 0, <<<>>>
        @WithMoveDataFiles = ‘x’,
        @WithMoveLogFile = ‘y’

        Please advise,

        Regards.

        Bob

        Like

        • Spelling mistake, SuppressWithMove = 0

          Like

          • Hi Paul,

            I’m restoring some really large databases (several hundred data files each) and it’s taking very long. I need to mirror these databases after I restore them and I’m pretty sure I’ll have to apply a bunch of transaction log backups since several hours have passed. How do I generate the “second pass” of restores with just transaction log backups now that I have restored the full backups? Not sure why the restores are taking this long either? Could be al I/O I presume
            since the central backup location and database servers are geographically in the same location. Not sure if it’s a bandwidth issue. Not sure if these are good numbers or not either RESTORE DATABASE successfully processed 5607205 pages in 1642.830 seconds (26.665 MB/sec).

            Regards,

            Bob.

            Like

  15. Hi Paul,
    thanks for the latest update (6.7) – it helped a serious performance issue we were having.
    A minor heads up for you: a quick study/compare between 6.5 and 6.7 showed the device_type lookup is still in old style in 6.7 on line 641. By design?
    Thanks again for the script, the updates and continued support. 🙂
    Greg

    Like

  16. If you run sp_whoisactive , does it give you anything in the wait_type column?

    Like

  17. Hi, I used this stored procedure for weeks without any problem, but since this morning it’s not working all of a sudden. I execute it and the query is in “runnable” status for hours. Any idea?

    Like

    • Hi Sebastian, someone in Italy sent me a new version of the procedure which is apparently much faster. I haven’t had a chance to test it but will do so this weekend and get it published., it’s much quicker apparently.

      The problem sounds like a bad query plan has need generated, if there is a lot of backup history for many database this could be the problem.

      Regards
      Paul

      Like

      • Ok thanks for the quick response! I will wait for the new version.

        Executing the stored procedure was taking seconds yesterday so it’s weird. I used DBCC FREEPROCCACHE to clear the plan cache, but no success.

        Like

        • Hi Sebastien,
          V6.59 includes a changes suggested by Simone which should make a significant improvement to the performance of the procedure where a SQL instance has many databases. The change is to filter the databases more carefully at the start to avoid unnecessary work.
          Best wishes
          Paul

          Like

  18. Fabulous script! Thank you for taking the time to create and maintain this. I have a question about Availability Group databases. I’m using Ola’s backup system with a network path. I have a full weekly, a differential daily and 15 min transaction log backups. For simplicity, I have two servers S1 and S2. S1 is primary, then failover occurs and S2 becomes primary. When I run restoregene on S1, I get the following backup chain: Full, 1 diff, logs (stops when failover occurred). When I run restoregene on S2, I get nothing for databases in the AG, which I suspect is because the backup chain is broken, even though there are subsequent differential and log backups; I can see in the msdb that the backup info is being recorded. Any suggestions on how I can capture the complete backup chain (Full+diff+logs) to restore the database?

    Like

  19. Hi Paul,

    I really love this stored procedure!
    However in a SQL2005 environment v.6.57 still won’t work ;-(

    Msg 243, Level 16, State 1, Procedure sp_RestoreGene, Line 747
    Type TIME is not a defined system type.
    Msg 243, Level 16, State 1, Procedure sp_RestoreGene, Line 747
    Type TIME is not a defined system type.
    Msg 243, Level 16, State 1, Procedure sp_RestoreGene, Line 747
    Type TIME is not a defined system type.
    Msg 8155, Level 16, State 2, Procedure sp_RestoreGene, Line 747
    No column was specified for column 1 of ‘a’.
    Msg 207, Level 16, State 1, Procedure sp_RestoreGene, Line 747
    Invalid column name ‘Command’.
    Msg 207, Level 16, State 1, Procedure sp_RestoreGene, Line 747
    Invalid column name ‘Command’.

    Kind regards,
    Erwin.

    Like

    • Hi Erwin,
      The TIME function used during the construction of STANDBY file names has been replaced in V5.8, I don’t think that is compatible with SQL Server 2005 hence the problem, could you let me know if it works? I’m having problems installing SQL Server 2005 (compatibility issues) so can’t test it properly. The change doesn’t affect SQL Server 2008 or higher, these regression tests passed after making this change.
      Thanks and regards
      Paul

      Liked by 1 person

  20. Hey Paul,
    I’ve come across a couple instances where if I have a database that the FULL and LOG backup have the same BackupDate, it will wrongfully include the LOG backup in the restore chain, even if the last_lsn is lower than the last_lsn of the FULL. Changing line 620 from “AND bs.backup_start_date >= y.backup_start_date” to “AND bs.backup_start_date > y.backup_start_date” seems to fix it, but I think it would be better to use the LSNs as the comparison. I think you can do that by adding “MAX(a.last_lsn) last_lsn” after line 570, then change the comparison at line 620 to “AND bs.last_lsn >= y.last_lsn”

    Like

    • Thanks Ryan, I’ll have a look at it. There is a request for a new ‘Full Backups Only’ parameter and a fix is needed to the @DatabaseName parameter data type ( needs to be longer for big comma separated DB names lists) . Your suggestion makes 3 fixes needed which is my notional new release threshold trigger, will try and do it all this coming weekend.
      Regards
      Paul

      Like

    • Hi Ryan,
      Your suggestion has been added, I couldn’t reproduce the error but the fix doesn’t break any of the regression tests.
      Thanks and regards
      Paul

      Like

  21. Hi Paul,

    I used below command and have couple of issues:
    exec sp_RestoreGene @Database = ‘Dev1’, @TargetDatabase = ‘Dev2’, @WithReplace = 1, @WithMoveDataFiles = ‘F:\DATA\Dev2.mdf’, @WithMoveLogFile = ‘F:\LOG\Dev2_log.ldf’

    After executing the above it returned command as follows:
    ;RESTORE DATABASE [GP2_Dev2]
    FROM DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\Dev1.bak’
    WITH REPLACE, FILE = 1, NORECOVERY, STATS=10 , MOVE N’Dev1′ TO ‘F:\DATA\Dev2.mdfDev1_Dev2.mdf’ , MOVE N’Dev1_log’ TO ‘F:\LOG\Dev2_log.ldfDev1_log_Dev2.ldf’

    First issue is that the original mdf and log files are appended even though I have specified the path in the parameters.

    Second issue is after running the RESTORE sql manually (and replacing mdf and ldf paths with correct one), it does restore successfully but leaves the database in “Restoring” state and makes it inaccessible in SSMS. Do I need to specify some other parameter?

    Third issue is why the procedure is not running the restore command automatically and is only returning the sql text?

    Thanks.

    Like

  22. Not sure if this is a bug or a feature, but I’ve noticed with Line 1132 set to “AND #CTE.last_lsn > ISNULL(after_diff.last_lsn,’0′)” it returns different results for me than SSMS’s restore script generation for cases where the last LSN of the log backups is the same as the dif backup. Changing it to AND #CTE.last_lsn >= ISNULL(after_diff.last_lsn,’0′) seems to replicate SSMS’s behavior. I think this is probably due to nothing changing in any of the log backups, making them unnecessary. However, I by restoring them anyways it gives me more insight into the amount of time behind the current time the last backup was.

    Like

  23. Hi Stephen, I sent Ola Hallengren an email and asked if I minded me reusing some code in his maintenance procedures, he very kindly said OK. There is some code in his ‘Backup Database’ procedure that splits a CSV list of database names into a temporary table. That code has been added here in Version 5.6, you can supply a list of database names, separated by a comma. Thanks for the suggestion Stephen and also to Ola for permission to reuse a bit of his code.

    Like

  24. This looks like a fantastic script and I hope to use it in the future. One thing I am looking for and could not find in the documentation is an option to specify more than one user databases, say like a csv list and get the restore script only for those databases? Do you have any suggestions for that? Thanks for the awesome script!

    Like

    • Hi Stephen, if you don’t pass a database name parameter to the scrip, it will return restore scripts for all user databases. Alternatively, you would need to call it multiple times and pass database name on each call.

      It’s a nice idea, pass a comma separate list of databases, I will add that to a future release.

      Thanks and best wishes, Paul

      Like

  25. Just wanted to drop you a note and let you know that my migration from SQL 2005 to SQL 2014 was a success, and this script performed spectacularly. It made the process so much easier to script, and allowed me to focus my attention on the other important details. Thanks again to you Paul and to all contributors!

    Like

    • Thanks very much for the suggestions you made for improving the script Bob, and for this comment. I’m really pleased the script was useful and your migration went well!
      Best wishes,
      Paul

      Like

  26. Thanks Paul, this is awesome 🙂

    Like

  27. Thanks Paul, much appreciated.This is the most useful tool ever 🙂

    Like

  28. Hi Kiran,
    I can do that no problem, it involves a change to change to the PoSh driver script too. Will get it done over the weekend and let you know when complete.
    Thanks
    Paul

    Like

  29. HI Paul,

    Just tried this parameter , works like a charm, thanks for considering it, it would be beneficial if we can split this into a separate variable which will allow to move FileStream Files to a separate drive (helpfull to manage disk space on non-prod servers while restoring the databases)

    Regards,
    Kiran

    Like

  30. Thanks Paul, much appreciated.

    Like

    • Hi Kiran,
      If a database contains a FILESTREAM file, it is now included in the WITH MOVE statements, if you pass a value to the parameter @WithMoveDataFiles when calling sp_RestoreGene, the file stream file is now moved to this folder too. It’s a good sensible suggestion, many thanks.
      Paul

      Like

  31. HI Paul,

    Is there a parameter to specify for Filestream files location similar to -WithMoveDataFiles and -WithMoveLogFile ? Please advise.

    Cheers,
    Kiran

    Like

  32. Version 5.1 released today includes the following changes:

    1. If Target database name is supplied (when restoring to a database name different to the back up), a string is appended to the end of the filenames in the format : c:\sql\data\name.mdf. @TargetDatabase_ + _RestoreGene_TargetDBRenamed. This makes the filenames unique so the restore can be on the same instance using the same folders.

    2. If a backup was taken to Azure url storage, the restore script constructed includes ‘FROM URL’ rather than ‘FROM FILE’. The credentials used to take the backup can be supplied as a new parameter.

    3. Two new parameters – one for a string scan and one for replace that can optionally be applied to the restore script.

    4. The PowerShell driver script now defaults ‘With Standby’ to false.

    Thanks for the feedback, all of these changes were suggested recently.
    Paul

    Like

  33. This is an awesome solution and I thank you for sharing it with the community!

    With regards to the parameter @TargetDatabase — when I supply a new database name, the script generated does provide that new database name, however, it does not rename the data and log files, so the restore (when attempted on the same server) fails because those file names are in use. There needs to be a case statement in the building of the restore script that if the @TargetDatabase is supplied, the WITH MOVE needs to be @TargetDatabase+’.mdf’, etc.

    Also I notice in the case of databases with more than one file (multiple ndf), no script is generated to restore those. Only the mdf and log files are supported with your script.

    Thanks!

    Like

    • Hi Margaret, there are fixes for both the issues you mention, and Azure Url backups, in the next release. June 11th (when I get back from holiday and have Internet access again). Thanks and best wishes

      Like

  34. Version 5.0 includes the following suggestions:
    – Re-enabled SQL Server 2005 compatibility
    – If overriding the restored database name, physical file names changed so the new database can be restored to the same instance

    The 2nd suggestion was by Mick@SQLFairy.com who has developed a fully featured Azure compatible backup/restore solution based on Ola’s backup procedures and restore gene – http://www.sqlfairy.com.au/teh-sql-fairy-blog/

    Thanks

    Like

  35. The sproc creation fails to run on my SQL 2005 server with error, “Cannot assign a default value to a local variable.” I have a feeling the parameter sniffing fix you applied is triggering this issue. This fancy stuff is too cool for SQL 2005:
    DECLARE @Database_ SYSNAME = @Database,
    @TargetDatabase_ SYSNAME = @TargetDatabase, …

    Like

    • Thanks Bob, yes, you can’t declare and assign a variable in one statement in SQL Server 2005 can you. I’ll add that to the list of fixes for the next release too.

      I had thought the procedure was done and dusted but since Brent published the link there have been thousands of views and really good suggestions.

      Best wishes
      Paul

      Like

  36. Hi Paul,

    Great script, I have created a similar but far simpler script for creating the NORECOVERY restore scripts on DR servers. I think you might like the part to include the datafile size in MB on the same line as the MOVE statement, it makes moving things around easy.

    I have put it here – https://liamchew.wordpress.com/2016/05/31/sql-create-restore-script-from-db-backup-files/

    The part of the query is this:
    select @datafile_str = ‘,MOVE ”’ + s.name + ”’ TO ”’ + @destination_drive + RIGHT(physical_name, len(physical_name) – 1) + ””
    + ‘ –‘ + CONVERT(VARCHAR(20),CONVERT(MONEY,CONVERT(INT, (1024.0 * (size / 128.0)) / 1000.0)),1) + ‘mb’
    + CHAR(13) + CHAR(10)
    + @datafile_str
    from sys.master_files s
    join sys.databases d
    on d.database_id = s.database_id
    where d.name = @db_name
    order by size

    Regards,
    Liam

    Like

  37. Hi Paul,

    Thanks very much for this fantastic script.

    Have you considered releasing this under any license like Apache 2.0? Unfortunately our legal dept won’t let me touch it otherwise 😦

    Dan

    Like

  38. Hi Paul, just wondered if you’d had time to do any investigations into the issue I described on November 4?
    Regards
    Tiran

    Like

  39. Hi Tiran,
    There is a STOPAT parameter, if you supply a value prior to the most recent full backup, the procedure will query backup history and find the most recent full back prior to that date/time and construct a recovery plan accordingly. You can specify any value and, as long as backup history exists for that period, you should be fine.
    Best wishes
    Paul

    Like

  40. Hi Paul
    Great script but I’ve just noticed an issue since I started testing V4.3 today. I was using V3.4 originally which worked fine. However, V4.3 is not extracting the path to the full backup file (the log backups are located ok). In V3.4 the restore command generated is “RESTORE DATABASE [MEXDB_TEST] FROM DISK = N’\\\FULL\BBD-SQL01_MEXDB_FULL_20151103_210041.bak’ etc.”. In V4.3 I get “RESTORE DATABASE [MEXDB_TEST] FROM DISK = N'{AA05B80B-98EB-4F33-8585-F5AF714962FB}8′ etc.”. Can you tell me why this is happening? Note: I’m using Ola Hallengren’s SQL backup Scripts to manage my backups. Thanks

    Like

    • Hi Tiran, thanks for the feedback, I’ll try and reproduce the error at the weekend and get back to you. Best wishes, Paul

      Like

      • Hi Paul

        Thanks for looking into my previous question.

        There’s another scenario I’ve come across which I’m not sure if you’re able to support. At present I have a database for which I am doing a full backup daily and log backups hourly and retaining these for 8 days. My dilemma is that when I run your script I get commands generated to restore only the most recent full backup plus all subsequent logs or log backups up to a point in time – which is fine. However, what if I need to restore to a point prior to the most recent full backup?

        Is there a way, using your script, I could select the full backup from say 3 days ago then all the subsequent log backups up to a point in time before the next full backup?

        Thanks

        Like

  41. Version 4 released which includes the following fixes:
    – Lots of unnecessary _msg variables removed
    – Support for a single backup fork added
    – Support for default backup paths, for UNC backups there is now no need to declare them in ps_RestoreGene
    – Support for databases which a – in their name

    All feedback and suggestions most welcome, thanks
    Paul

    Like

  42. New release 3.8 supports a fork in backup history. It’s only been tested where a single fork exists, IE a restore was performed to a historic point in time, with recovery, then subsequent log backups are taken. When a fork exists the restore script will follow the new path for subsequent differential and log backups taken after the forking point. Where no fork, exists the script continues to perform as before.

    Like

  43. Great work! I’m testing it out in advance of a migration and ran into an issue with database names that contain a hyphen ‘-‘ character. The generated code uses the database name as part of a variable name, which cannot contain a hyphen. character. I’d suggest replacing any characters in database names that are not allowable in variable names with an underscore ‘_’ character.

    Like

  44. can I get rid of those set@msg statements?? What is the point in those?

    Like

  45. Version 3.7 released today with fixes for a collation (case sensitivity) issue and a forking problem with historic backups. The fixes were supplied by Luke Sarro, I wasn’t able to reproduce the problem but a full regression test shows the changes haven’t introduced any problems..
    Thanks and regards
    Paul

    Like

  46. Is there any possibility that this script could include support for databases with more than 10 backup files. One of the system I look after has the full 63 files, I’d admit a simple copy/paste and a bit of editing gets me the script I need but it would be great not having to do so.

    Like

    • I sympathise but can’t help with this, sorry. It would physically quadruple the size of the script for what must be fringe cases. I don’t think many people use even the 10 stripped files it currently supports. Best wishes

      Like

  47. How do you handle FULLTEXT index folders?

    Liked by 1 person

  48. Nice script, really useful. I was running some tests and got some unusual results. We have several data files spread on several drives and when running it I got repeated lines with same information. Is there a possibility to include a list of files and their new paths? Seems no from what I see. I’m creating a similar script, but want to include this option. I already took into consideration Peter concern, creating restore script from existing backup files, comes handy to restore older backups, not last ones, including dif and trn. Working on this possibility to create MOVE statements for different files and paths.

    Like

    • You can move all data files to a new folder during the restore via the WithMoveDataFiles parameter, only to one new folder though. Feel free to chop up my script if you think you can improve it, I won’t sue 🙂
      Thanks and regards

      Like

      • Jajaja! No, don’t worry, will not change yours. I landed here seeking for different approaches on how to handle the MOVE statement for different files on several locations. I already handle same case as yours, moving to a different folder, but all files on same one. Thanks!

        Like

  49. Paul i love your script!
    One question: It’s not possible to create the output not from the database-information but from existing backup-files from disk?

    Like

    • Hi Peter,
      I’m pleased you like the script, it took ages to develop so feedback is really appreciated.
      It does depend on backup history in the msdb database though, it can’t read details from backup files directly.
      Many thanks and best wishes
      Paul

      Like

  50. Paul, Thanks for the nice script. We have a server set to Case Sensitive collation and I had to correct several casings to be able to run the proc. Where can I contribute this change back?

    Like

    • The project has removed from GitHub, people were publishing versions of it there I wasn’t comfortable being associated with. The master copy of the procedure is, and only ever has been, published here on word press.

      Like

  51. Version 3.6 just released which includes a fix, identified and supplied, by a colleague. If database name includes a space or full stop, the msg declare at the start of each database recovery sequence was invalid.

    Like

  52. Version 3.5, just released, Includes the Device Type 7 suggestion, thanks

    Like

  53. Besides the (102,2) issue (AWS networked drives show up as Device_Type (7), which is not captured in your script; I’m running into another issue; your Database Script takes in the Backup Path for an object; but it could legitimately query the Primary server for this information using the following TSQL:

    select physical_device_name, backup_start_date, backup_finish_date
    from msdb.dbo.backupset b join msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
    where database_name = ‘MyDB’
    order by backup_finish_date DESC

    The reason this is important is that Ola Hallengren’s SQL backup Scripts (Which are highly recommended by various entities in the SQL Server world) puts each database into a folder in the following structure: BackupDriveLetter:\Backup\ServerName\DatabaseName\TypeOfBackup\FileName.(bak|sqb)

    So in my case:

    F:\Backup\SQL07\DB1\FULL\SQL07_DB1_FULL_20140817_000001.sqb

    As an added feature, can you include the ability to query what the default path for that database’s backups are based on where it stores its database? I ask because the parameter: “FromFileFullUNC” shouldn’t need to be something I provide, since SQL server knows where it’s storing databases already.

    Like

Trackbacks

  1. Automatically restore SQL Server database from file from another server | XL-UAT
  2. SQL Fairy
  3. Code I’d Hate To Live Without: T-SQL Tuesday #104 | SQL Studies

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

%d bloggers like this: