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 v6.58 (2016-12-03)
(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
*     
********************************************************************************************/ 
                               
ALTER PROC [dbo].[sp_RestoreGene]
(
    @Database VARCHAR(2000) = 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
    @LogShippingStartTime DATETIME = NULL,
    @LogShippingLastLSN VARCHAR(25) = NULL,
	-- Parameters for PowerShell script use
    @BlobCredential VARCHAR(255) = NULL, --Credential used for Azure blob access 
    @RestoreScriptReplaceThis NVARCHAR(255) = NULL,
    @RestoreScriptWithThis NVARCHAR(255) = NULL,
--	@Log_Reference VARCHAR (250) = NULL,
    @SuppressWithMove BIT = 1,
    @PivotWithMove BIT = 0,
    @RestoreScriptOnly BIT = 0,
    @DropDatabaseAfterRestore BIT = 0,
	@SetSingleUser BIT = 0,
	@ExcludeDiffAndLogBackups BIT = 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(2000);
    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 @Log_Reference_ VARCHAR (250);
    --SET @Log_Reference_ = @Log_Reference;
    --DECLARE @LogShippingVariableDeclare_ BIT;
    --SET @LogShippingVariableDeclare_ = @LogShippingVariableDeclare;
    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_ BIT;
	SET @ExcludeDiffAndLogBackups_ = @ExcludeDiffAndLogBackups;
                     
    -- Defaults Recovery Point Times
    IF ISNULL(@StopAt_,'') = ''
        SET @StopAt_ = GETDATE(); 
                     
    IF ISNULL(@LogShippingStartTime_,'') = ''
        SET @LogShippingStartTime_ = @StopAt_;
                     
    -- Default written to the SQL Server Error Log
    --IF ISNULL(@Log_Reference_,'') = ''
    --    SET @Log_Reference_ = 'sp_RestoreGene - Recovery Step - ';     
                     
    -- 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..#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;

	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
      
    -----------------------------------------------------------------------------------------------------------------------------------------
    -- 'SELECT DATABASES' code snippet used below with permission from the ola hallengren - https://ola.hallengren.com/
    --=======================================================================================================================================
      
      
    -- Backup forking support         
    SELECT rf1.database_name, MAX(database_backup_lsn) database_backup_lsn, MAX(fork_point_lsn) fork_point_lsn, MAX(last_lsn) last_lsn
    INTO #BackupFork
    FROM msdb.dbo.backupset rf1
    INNER JOIN
    (
        SELECT database_name, MAX(backup_finish_date) AS backup_finish_date
        FROM msdb.dbo.backupset
        WHERE backup_start_date <= @LogShippingStartTime_
        AND fork_point_lsn IS NULL
        GROUP BY database_name
    ) rf2
        ON rf1.database_name = rf2.database_name
                
    WHERE fork_point_lsn IS NOT NULL
    AND rf1.backup_start_date <= @LogShippingStartTime_
    AND rf1.backup_finish_date > rf2.backup_finish_date
    GROUP BY rf1.database_name;
                
    CREATE INDEX IDX_BackupFork ON #BackupFork(database_name);
                
                     
    --------------------------------------------------------------
    -- CTE1 Full Backup UNION Differential Backup UNION Log Backup
    --------------------------------------------------------------
    WITH CTE
    (
         database_name
        ,current_compatibility_level
        ,last_lsn
        ,current_is_read_only
        ,current_state_desc
        ,current_recovery_model_desc
        ,has_backup_checksums
        ,backup_size
        ,[type]
        ,backupmediasetid
        ,family_sequence_number
        ,backupstartdate
        ,physical_device_name
        ,position
    )
    AS
    ( 
                     
        --------------------------------------------------------------
        -- Full backup - Most current immediately before @LogShippingStartTime_
        SELECT
            bs.database_name
            ,d.[compatibility_level] AS current_compatibility_level
            ,bs.last_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
            ,x.backup_start_date AS backupstartdate
            ,mf.physical_device_name
            ,bs.position
        FROM msdb.dbo.backupset bs 
                     
        INNER JOIN sys.databases d
            ON bs.database_name = d.name
                     
        INNER JOIN
        (
            SELECT
                a.database_name
                ,MAX(a.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
                
            LEFT OUTER JOIN #BackupFork c
                ON c.database_name = a.database_name
                
            WHERE a.[type] = 'D'
            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_               
            -- Recovery Forking
            AND ISNULL(c.fork_point_lsn,a.database_backup_lsn) >= a.database_backup_lsn
            AND ((c.fork_point_lsn IS NOT NULL AND a.database_backup_lsn < c.database_backup_lsn) OR c.fork_point_lsn IS NULL) 
                
            GROUP BY a.database_name
                
        ) x
            ON x.database_name = bs.database_name
            AND x.backup_start_date = bs.backup_start_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:') 
                     
        UNION
                     
        --------------------------------------------------------------
        -- Differential backup, most current immediately before @StopAt_
        SELECT
            bs.database_name
            ,d.[compatibility_level] AS current_compatibility_level
            ,bs.last_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
            ,x.backup_start_date AS backupstartdate
            ,mf.physical_device_name
            ,bs.position
        FROM msdb.dbo.backupset bs 
                     
        INNER JOIN sys.databases d
               
        ON bs.database_name = d.name
               
        INNER JOIN  -- Last Full in recovery fork before STOPAT
        (
            SELECT
                a.database_name
                ,MAX(a.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
                
            LEFT OUTER JOIN #BackupFork c
                ON c.database_name = a.database_name
                
            WHERE a.[type] = 'D'
            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_
                
            -- Recovery Forking
            AND ISNULL(c.fork_point_lsn,a.database_backup_lsn) >= a.database_backup_lsn
            AND ((c.fork_point_lsn IS NOT NULL AND a.database_backup_lsn < c.database_backup_lsn) OR c.fork_point_lsn IS NULL) 
                
            GROUP BY a.database_name
        ) y
            ON y.database_name = bs.database_name 
                
        INNER JOIN -- Last Diff before STOPAT
        (
            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
                
            LEFT OUTER JOIN #BackupFork c
                ON c.database_name = a.database_name
                
            WHERE a.[type] = 'I'
            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())
            AND a.database_backup_lsn = ISNULL(c.database_backup_lsn,a.database_backup_lsn )
            AND a.last_lsn <= ISNULL(c.fork_point_lsn, a.last_lsn)
            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 
                     
        WHERE bs.type = 'I'
        AND mf.physical_device_name NOT IN ('Nul', 'Nul:')
        AND mf.mirror = 0
        AND x.backup_start_date > y.backup_start_date
        AND @StopAt_ = @LogShippingStartTime_
                     
        UNION
                     
        --------------------------------------------------------------
        -- Log file backups - after 1st full / before @StopAt_
        SELECT
            bs.database_name
            ,d.[compatibility_level] AS current_compatibility_level
            ,bs.last_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_start_date as backupstartdate
            ,mf.physical_device_name
            ,bs.position
                     
        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 -- After the selected full backup
        (
            SELECT
                a.database_name
                ,MAX(a.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
                
            LEFT OUTER JOIN #BackupFork c
                ON c.database_name = a.database_name
                
            WHERE a.[type] = 'D'
            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_
                
            -- Recovery Forking
            AND ISNULL(c.fork_point_lsn,a.database_backup_lsn) >= a.database_backup_lsn
            AND ((c.fork_point_lsn IS NOT NULL AND a.database_backup_lsn < c.database_backup_lsn) OR c.fork_point_lsn IS NULL) 
                
            GROUP BY a.database_name
        ) y
            ON bs.database_name = y.database_name 
                
        LEFT OUTER JOIN #BackupFork r
            ON r.database_name = bs.database_name
                
        LEFT OUTER JOIN -- Select the first log file after STOPAT
        (
            SELECT DISTINCT x.database_name, database_backup_lsn, 
            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 b.mirror = 0
                AND device_type IN (7,102,2,9)
                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
            AND bs.database_backup_lsn = ISNULL(r.database_backup_lsn,bs.database_backup_lsn)
                
        WHERE bs.backup_start_date <= x.last_Log_After_StopAt -- Include 1st log after stop at
--        AND bs.backup_start_date >= y.backup_start_date -- After last full backup start date
        AND bs.backup_start_date > y.backup_start_date -- After last full backup start date
        AND bs.database_backup_lsn = ISNULL(r.database_backup_lsn,bs.database_backup_lsn) -- Recovery Fork
        AND (bs.last_lsn <= ISNULL(r.fork_point_lsn,bs.last_lsn) OR bs.fork_point_lsn = r.fork_point_lsn)
                
        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)
    )
                     
    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
    )
    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,
        Stripe10.physical_device_name  AS S10_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
    ) 
                     
    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.backupstartdate, 126)
        AS BackupDate,
        a.BackupDevice,
        a.last_lsn,
        a.database_name ,
        --ROW_NUMBER() OVER(ORDER BY database_name, Sequence, last_lsn) AS SortSequence
        ROW_NUMBER() OVER(ORDER BY database_name, Sequence, a.backupstartdate) 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 + 
                     
        ' 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.backupstartdate,
        #CTE.backup_size,
        #CTE.last_lsn 
                     
    FROM sys.databases d 
                     
    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 
                     
    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 
                     
    JOIN #Stripes
        ON #Stripes.database_name = d.name
        AND #Stripes.backupmediasetid = #CTE.backupmediasetid
        AND #Stripes.last_lsn = #CTE.last_lsn 
                     
    WHERE #CTE.[type] = 'D'
    AND #CTE.family_sequence_number = 1 
                     
    --------------------------------------------------------------
    -- 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 + 
                     
        ' 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.backupstartdate,
        #CTE.backup_size,
        #CTE.last_lsn 
                     
    FROM sys.databases d 
                     
    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 
                     
    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 
                     
    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
        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 
                     
    WHERE #CTE.[type] = 'I'
    AND #CTE.family_sequence_number = 1 
	AND @ExcludeDiffAndLogBackups_ = 0
                     
    --------------------------------------------------------------
    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 @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.backupstartdate,
        #CTE.backup_size,
        #CTE.last_lsn 
                     
    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
    ) x2
    ON d.name = x2.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
        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'
    ) y1
        ON d.name = y1.name 
                     
    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
                    
                     
    WHERE #CTE.[type] = 'L'
    AND #CTE.family_sequence_number = 1 
    AND #CTE.last_lsn >= ISNULL(after_diff.last_lsn,'0')
	AND @ExcludeDiffAndLogBackups_ = 0
                     
    ----------------------------------------------------------------
    --UNION -- Declare @msg_ variable
    ----------------------------------------------------------------
    --SELECT
    --   'DECLARE @msg_' + REPLACE(REPLACE(REPLACE(d.name,' ','_'),'.','_'),'-','_') + ' VARCHAR(1000)' AS Command,
    --    -1 AS Sequence,
    --    d.name AS database_name,
    --    '' AS BackupDevice,
    --    GETDATE() AS backupstartdate,
    --    #CTE.backup_size,
    --    '0' AS last_lsn 
                     
    --FROM sys.databases d 
                     
    --JOIN #CTE
    --ON #CTE.database_name = d.name 
                     
    --WHERE #CTE.[type] = 'D'
    --AND @LogShippingVariableDeclare_ = 1 

	    --------------------------------------------------------------
    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 backupstartdate,
        #CTE.backup_size,
        '0' AS last_lsn 
                     
    FROM sys.databases d 
                     
    JOIN #CTE
    ON #CTE.database_name = d.name 
                     
    WHERE #CTE.[type] = 'D'
    AND @SetSingleUser_ = 1
                     
    --------------------------------------------------------------
    UNION -- Restore WITH RECOVERY
    --------------------------------------------------------------
    SELECT
       --'SET @msg_' + REPLACE(REPLACE(REPLACE(d.name,' ','_'),'.','_'),'-','_') + ' = ' + '''' + @Log_Reference_  + ' WITH RECOVERY ' + '''' + ';  RAISERROR (@msg_' + REPLACE(REPLACE(REPLACE(d.name,' ','_'),'.','_'),'-','_') + ',0,0) WITH LOG' + ';' + 
	   '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 backupstartdate,
        #CTE.backup_size,
        '999999999999999999997' AS last_lsn 
                     
    FROM sys.databases d 
                     
    JOIN #CTE
    ON #CTE.database_name = d.name 
                     
    WHERE #CTE.[type] = 'D'
    AND @WithRecovery_ = 1 
                     
    --------------------------------------------------------------
    UNION -- CHECKDB
    --------------------------------------------------------------
    SELECT
        --'SET @msg_' + REPLACE(REPLACE(REPLACE(d.name,' ','_'),'.','_'),'-','_') + ' = ' + '''' + @Log_Reference_  + ' CHECKDB ' + '''' + ';  RAISERROR (@msg_' + REPLACE(REPLACE(REPLACE(d.name,' ','_'),'.','_'),'-','_') + ',0,0) WITH LOG' + ';' +
		'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 backupstartdate,
        #CTE.backup_size,
        '999999999999999999998' AS last_lsn 
                     
    FROM sys.databases d 
                     
    JOIN #CTE
    ON #CTE.database_name = d.name 
                     
    WHERE #CTE.[type] = 'D'
    AND @WithCHECKDB_ = 1
    AND @WithRecovery_ = 1 
 
                     
    --------------------------------------------------------------
    UNION -- Drop database after restore
    --------------------------------------------------------------
    SELECT
       --'SET @msg_' + REPLACE(REPLACE(REPLACE(d.name,' ','_'),'.','_'),'-','_') + ' = ' + '''' + @Log_Reference_  + ' DROP DATABASE ' + '''' + ';  RAISERROR (@msg_' + REPLACE(REPLACE(REPLACE(d.name,' ','_'),'.','_'),'-','_') + ',0,0) WITH LOG' + ';' +
	   '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 backupstartdate,
        #CTE.backup_size,
        '999999999999999999999' AS last_lsn 
                     
    FROM sys.databases d 
                     
    JOIN #CTE
    ON #CTE.database_name = d.name 
                     
    WHERE #CTE.[type] = 'D'
    AND @DropDatabaseAfterRestore_ = 1 
                                 
    --------------------------------------------------------------
    UNION -- WITH MOVE secondary data files, allows for up to 32769/2 file groups
    --------------------------------------------------------------
    SELECT
        '	,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.backupstartdate,
        #CTE.backup_size,
        #CTE.last_lsn 
                     
    FROM sys.master_files b
    INNER JOIN #CTE
    ON #CTE.database_name = DB_NAME(b.database_id) 
                     
    WHERE #CTE.[type] = 'D'
    AND b.type_desc IN ('ROWS','FILESTREAM','LOG')
    AND b.file_id > 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.last_lsn, 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.last_lsn, 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

73 replies

  1. 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

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

    Like

  3. 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

  4. 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

  5. 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

  6. 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

  7. How do you handle FULLTEXT index folders?

    Liked by 1 person

  8. 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

  9. 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

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

    Like

  11. 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

  12. 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

  13. 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

  14. 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

  15. 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

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

    Like

  17. 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

  18. 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

  19. 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

  20. 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

  21. 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

  22. 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

  23. HI Paul,

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

    Cheers,
    Kiran

    Like

  24. 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

  25. 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

  26. 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

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

    Like

  28. Thanks Paul, this is awesome 🙂

    Like

  29. 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

  30. 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

  31. 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

  32. 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

  33. 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

  34. 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

  35. 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

  36. 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

Trackbacks

  1. Automatically restore SQL Server database from file from another server | XL-UAT
  2. SQL Fairy

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: