Database Restore Automation

This post describes a two script framework, one T-SQL and one PoSh, for automating SQL database restores. It can help to initialize database mirroring and availability groups, execute test restores to verify production backup file integrity, implement a basic log shipping solution and to perform side by side upgrades. No changes should be necessary to either script, save then call them passing parameters to suit the purpose.

sp_LogShippingLight

A stored procedure ‘sp_LogShippingLight’ is provided that generates a T-SQL restore script. The procedure should be created on the primary server where backups were taken, parameters are available offering various restore options and overrides. A T-SQL RESTORE script is constructed and returned to the caller, it is not executed. Run without parameters it builds and returns a restore script for all user databases to the current point in time. If supplying parameters, specify only those required IE:


DECLARE @RC int

EXECUTE @RC = [master].[dbo].[sp_LogShippingLight]
   @Database = 'workspace'
  --,@WithMoveDataFiles
  --,@WithMoveLogFile
  --,@FromFileFullUNC
  --,@FromFileDiffUNC
  --,@FromFileLogUNC
  --,@StopAt
  --,@StandbyMode
  --,@IncludeSystemDBs
  ,@WithRecovery = 1
  ,@WithCHECKDB = 1;
GO

Below is a description of all parameters and their default values. If a STOPAT date time parameter is supplied with a value previous to the last full backup, the necessary full, differential and log backups are identified from database backup history and an optimized restore script is constructed to that point in time.

Parameter
Example Description
@Database workspace Restrict restore script generated to a specific database, defaults to all user databases
@WithMoveDataFiles x:\datafilesNew\ Overrides WITH   MOVE for data file folder, defaults to actual
@WithMoveLogFile y:\logfilesNew\ Overrides WITH   MOVE for log file folder, defaults to actual
@FromFileFullUNC \\backups\full\ UNC path to full backup file, defaults to actual drive and folder
@FromFileDiffUNC \\backups\diff\ UNC path to differential backup file, defaults to actual drive and folder
@FromFileLogUNC \\backups\log\ UNC path to log backup files, defaults to actual drive and folder
@StopAt 23/12/2012    12:31:00 Stop at a specific date time, defaults to current (datetime2)
@StandbyMode 0   or 1 Leave database in Standby (1) or No Recovery (0), defaults to 0.
@IncludeSystemDBs 0   or 1 If not restricting to a specific user database, include restore commands for system databases? Defaults to 0 = No
@WithRecovery 0   or 1 Include WITH   RECOVERY, defaults to 0 = No
@WithCHECKDB 0   or 1 Include CHECKDB, defaults to 0 = No

The T-SQL code below does not display properly in HTML but it does parse and compile in SQL Server Versions 2005 or higher. Double click to highlight then paste into query analyser and run to create the procedure sp_LogShippingLight. The entire procedure is effectively a single SELECT statement making repeated calls to a CTE based on backup history. Most of the functionality in the SQL Server Management Studio Restore Database Task wizard is available with the command line interface offering automation possibilities.

The procedure needs permissions to query backup history tables in msdb and data file details in the master database, it constructs and returns T-SQL RESTORE commands only, it does not run them. It wasn’t built with performance in mind and can be quite resource intensive if you are keeping years of backup history in msdb. Use procedures msdb.dbo.sp_delete_database_backuphistory or msdb.dbo.sp_delete_backuphistory to tidy history beyond what is required.


USE [master] 
GO 
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'sp_LogShippingLight') 
EXEC ('CREATE PROC dbo.sp_LogShippingLight AS SELECT ''stub version, to be replaced''') 
GO 
    
    
/********************************************************************************************* 
Log Shipping Light v2.01 (2013-11-05) 
(C) 2012, Paul Brewer 
    
Feedback: paulbrewer@yahoo.co.uk 
Updates: http://paulbrewer.wordpress.com/2013/10/12/database-restore-automation/
    
This procedure queries msdb database backup history and database file details in master.
It builds and returns RESTORE DATABASE commands as it's result set, it does not execute the commands.
    
Usage examples: 
    
EXEC sp_LogShippingLight; 
Generates RESTORE commands for all USER databases
Using actual backup files 
To existing file locations 
To Current time
Does not include WITH RECOVERY or CHECKDB 
    
EXEC sp_LogShippingLight @Database = 'db_workspace', @StopAt = '2012-12-23 12:01:00.000', @StandbyMode = 1;
Generates RESTORE commands for a specific database 
From the most recent full backup + most recent differential + transaction log backups before to STOPAT. 
Databases is left in STANDBY 
Restores to default file locations 
Rrom default backup file. 
    
EXEC sp_LogShippingLight @Database = 'db_workspace', @StopAt = '2012-12-23 12:31:00.000', @WithMoveDataFiles = 'd:\data\', @WithMoveLogFile  = 'e:\logs\' 
Includes WITH MOVE Overrides for data & log files in constructed restore commands. 

EXEC sp_LogShippingLight @Database = 'db_workspace', @WithRecovery = 1, @WithCHECKDB;
Includes a final WITH RECOVERY and CHECKDB in returned restore 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 - Cope with 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 - Andrew Guerin feedback, modified WHERE Device_Type IN (102,2) 
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 WITH RECOVERY/CHECKDB to 21 characters
                            + Include database name in output
*********************************************************************************************/ 
    
ALTER PROC [dbo].[sp_LogShippingLight] 
( 
    @Database SYSNAME = NULL, 
    @WithMoveDataFiles VARCHAR(2000) = NULL, 
    @WithMoveLogFile  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 
) 
AS 
BEGIN 
    
SET NOCOUNT ON; 
    
IF ISNULL(@StopAt,'') = '' 
SET @StopAt = GETDATE(); 
    
-------------------------------------------------------------- 
-- 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 
    ,backupfinishdate 
    ,physical_device_name 
    ,position 
) 
AS 
( 
-------------------------------------------------------------- 
-- CTE1 Full backup (most current or immediately before @StopAt if supplied) 
-------------------------------------------------------------- 
    
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_finish_date AS backupfinishdate 
    ,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 
    database_name 
    ,MAX(backup_finish_date) backup_finish_date 
FROM msdb.dbo.backupset a 
JOIN msdb.dbo.backupmediafamily b 
ON a.media_set_id = b.media_set_id 
WHERE a.[type] = 'D' 
--  AND b.[Device_Type] = 2 
AND Device_Type IN (102,2) 
AND a.is_copy_only = 0 
AND a.backup_finish_date <= ISNULL(@StopAt,a.backup_finish_date) 
GROUP BY database_name 
) x 
ON x.database_name = bs.database_name 
AND x.backup_finish_date = bs.backup_finish_date 
    
JOIN msdb.dbo.backupmediafamily mf 
ON mf.media_set_id = bs.media_set_id 
AND mf.family_sequence_number Between bs.first_family_number And bs.last_family_number 
    
WHERE bs.type = 'D' 
AND mf.physical_device_name NOT IN ('Nul', 'Nul:') 
    
-------------------------------------------------------------- 
-- CTE1 Differential backup, most current immediately before @StopAt 
-------------------------------------------------------------- 
UNION 
    
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_finish_date AS backupfinishdate 
    ,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 
    database_name 
    ,MAX(backup_finish_date) backup_finish_date 
FROM msdb.dbo.backupset a 
JOIN msdb.dbo.backupmediafamily b 
ON a.media_set_id = b.media_set_id 
WHERE a.[type] = 'I' 
--  AND b.[Device_Type] = 2 
AND Device_Type IN (102,2) 
AND a.is_copy_only = 0 
AND a.backup_finish_date <= ISNULL(@StopAt,GETDATE()) 
GROUP BY database_name 
) x 
ON x.database_name = bs.database_name 
AND x.backup_finish_date = bs.backup_finish_date 
    
JOIN msdb.dbo.backupmediafamily mf 
ON mf.media_set_id = bs.media_set_id 
AND mf.family_sequence_number Between bs.first_family_number And bs.last_family_number 
    
WHERE bs.type = 'I' 
AND mf.physical_device_name NOT IN ('Nul', 'Nul:') 
AND bs.backup_finish_date <= ISNULL(@StopAt,GETDATE()) 
    
-------------------------------------------------------------- 
-- CTE1 Log file backups after 1st full backup before @STOPAT 
-------------------------------------------------------------- 
UNION 
    
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_finish_date as backupfinishdate 
    ,mf.physical_device_name 
    ,bs.position 
    
FROM msdb.dbo.backupset bs 
    
INNER JOIN sys.databases d 
ON bs.database_name = d.name 
    
JOIN msdb.dbo.backupmediafamily mf 
ON mf.media_set_id = bs.media_set_id 
AND mf.family_sequence_number Between bs.first_family_number And bs.last_family_number 
    
LEFT OUTER JOIN 
( 
    SELECT 
    database_name 
    ,MAX(backup_finish_date) backup_finish_date 
    FROM msdb.dbo.backupset a 
    JOIN msdb.dbo.backupmediafamily b 
    ON a.media_set_id = b.media_set_id 
    WHERE a.[type] = 'D' 
    --  AND b.[Device_Type] = 2 
    AND Device_Type IN (102,2) 
    AND a.is_copy_only = 0 
    AND a.backup_finish_date <= ISNULL(@StopAt,a.backup_finish_date) 
    GROUP BY database_name 
) y 
ON bs.database_name = y.Database_name 
    
LEFT OUTER JOIN 
( 
SELECT 
    database_name 
    ,MIN(backup_finish_date) backup_finish_date 
FROM msdb.dbo.backupset a 
JOIN msdb.dbo.backupmediafamily b 
ON a.media_set_id = b.media_set_id 
WHERE a.[type] = 'D' 
--  AND b.[Device_Type] = 2 
AND Device_Type IN (102,2) 
    
AND a.is_copy_only = 0 
AND a.backup_finish_date > ISNULL(@StopAt,'1 Jan, 1900') 
GROUP BY database_name 
) z 
ON bs.database_name = z.database_name 
    
WHERE bs.backup_finish_date > y.backup_finish_date 
AND bs.backup_finish_date < ISNULL(z.backup_finish_date,GETDATE()) 
AND mf.physical_device_name NOT IN ('Nul', 'Nul:') 
AND bs.type = 'L' 
--  AND b.[Device_Type] = 2 
AND Device_Type IN (102,2) 
), 
   
-------------------------------------------------------------- 
-- CTE2 Optionally, striped backup file details 
-------------------------------------------------------------- 
   
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 
) 
    
-------------------------------------------------------------- 
-- Results, T-SQL RESTORE commands, below are based on CTE's above 
-------------------------------------------------------------- 
    
SELECT
    a.Command AS TSQL, 
    CONVERT(nvarchar(30), a.backupfinishdate, 126) 
    AS BackupDate, 
    a.BackupDevice, 
    a.Last_LSN,
    a.Database_Name 
FROM
( 
    
-------------------------------------------------------------- 
-- Most recent full backup 
-------------------------------------------------------------- 
    
SELECT
    ';SELECT ''' + 'RESTORE_FULL'' AS STEP' + ';RESTORE DATABASE [' + d.[name] + ']' + SPACE(1) + 
    'FROM DISK = N' + '''' + 
    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) + 
        
    -- 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 REPLACE, 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 + '_ROLLBACK_UNDO.bak ' + '''' + ',' END + SPACE(1) + 
        
    'STATS=10,' + SPACE(1) + 
    '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 + '''' AS Command, 
    1 AS Sequence, 
    d.name AS database_name, 
    CTE.physical_device_name AS BackupDevice, 
    CTE.backupfinishdate, 
    CTE.backup_size, 
    CTE.Last_LSN 
    
FROM sys.databases d 
JOIN 
( 
SELECT 
    DB_NAME(mf.database_id) AS name 
    ,mf.Physical_Name 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 
    ,mf.Physical_Name PhysicalName 
    ,mf.Name AS LogicalName 
FROM sys.master_files mf 
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 
    ';SELECT ''' + 'RESTORE_DIFF'' AS STEP' + ';RESTORE DATABASE [' + d.[name] + ']' + SPACE(1) + 
    'FROM DISK = N' + '''' + 
    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) + 
        
    -- 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 REPLACE, 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 + '_ROLLBACK_UNDO.bak ' + ''''  + ',' END + SPACE(1) + 
        
    'STATS=10,' + SPACE(1) + 
    '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 + '''' AS Command, 
    32769/2 AS Sequence, 
    d.name AS database_name, 
    CTE.physical_device_name AS BackupDevice, 
    CTE.backupfinishdate, 
    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 
   
JOIN 
( 
SELECT 
    DB_NAME(mf.database_id) AS name 
    ,mf.Physical_Name 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 
    ,mf.Physical_Name PhysicalName 
    ,mf.Name AS LogicalName 
FROM sys.master_files mf 
WHERE type_desc = 'LOG' 
) y 
ON d.name = y.name 
    
JOIN 
( 
SELECT 
    database_name, 
    Last_LSN, 
    backupfinishdate 
FROM CTE 
WHERE [Type] = 'D' 
) z 
ON CTE.database_name = z.database_name 
    
WHERE CTE.[type] = 'I' 
AND CTE.backupfinishdate > z.backupfinishdate -- Differential backup was after selected full backup 
AND CTE.Last_LSN > z.Last_LSN -- Differential Last LSN > Full Last LSN 
AND CTE.backupfinishdate < @StopAt 
AND CTE.family_sequence_number = 1 
    
-------------------------------------------------------------- 
UNION -- Log backups taken since most recent full or diff 
-------------------------------------------------------------- 
    
SELECT 
    ';SELECT ''' + 'RESTORE_LOG'' AS STEP' + ';RESTORE LOG [' + d.[name] + ']' + SPACE(1) + 
    'FROM DISK = N' + '''' + --CTE.physical_device_name + '''' + SPACE(1) + 
    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 + '''' + 
        
    -- 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 + '_ROLLBACK_UNDO.bak ' + ''''  + ',' END + SPACE(1) + 
        
    CASE CTE.has_backup_checksums WHEN 1 THEN ' CHECKSUM, ' ELSE ' ' END + 
        
    + 'FILE = ' + CAST(CTE.Position AS VARCHAR(5)) + 
    ' ,STOPAT = ' + '''' + CONVERT(VARCHAR(21),@StopAt,120) + '''' + 
    ',MOVE N' + '''' + x2.LogicalName + '''' + ' TO ' + 
    '''' + 
    CASE ISNULL(@WithMoveDataFiles,'Actual') 
    WHEN 'Actual' THEN x2.PhysicalName 
    ELSE @WithMoveDataFiles + SUBSTRING(x2.PhysicalName,LEN(x2.PhysicalName) - CHARINDEX('\',REVERSE(x2.PhysicalName),1) + 2,CHARINDEX('\',REVERSE(x2.PhysicalName),1) + 1) 
    END + '''' + ',' + SPACE(1) + 
        
    'MOVE N' + '''' + y1.LogicalName + '''' + ' TO ' + 
    '''' + 
    CASE ISNULL(@WithMoveLogFile ,'Actual') 
    WHEN 'Actual' THEN y1.PhysicalName 
    ELSE @WithMoveLogFile  + SUBSTRING(y1.PhysicalName,LEN(y1.PhysicalName) - CHARINDEX('\',REVERSE(y1.PhysicalName),1) + 2,CHARINDEX('\',REVERSE(y1.PhysicalName),1) + 1) 
    END + '''' 
    AS Command, 
    32769 AS Sequence, 
    d.name AS database_name, 
    CTE.physical_device_name AS BackupDevice, 
    CTE.backupfinishdate, 
    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 
    
LEFT OUTER JOIN  -- Next full backup after STOPAT 
( 
SELECT 
    database_name,  
    MIN(BackupFinishDate) AS backup_finish_date 
FROM CTE 
WHERE type = 'D' 
AND backupfinishdate > @StopAt 
GROUP BY database_name 
    
) x 
ON x.database_name = CTE.database_name 
    
LEFT OUTER JOIN -- Highest differential backup date 
( 
SELECT  
    database_name,  
    max(backupfinishdate) AS backupfinishdate 
FROM CTE 
WHERE CTE.type = 'I' 
AND CTE.backupfinishdate < @StopAt 
GROUP BY database_name 
) y 
ON y.database_name = CTE.database_name 
    
LEFT OUTER JOIN -- First log file after STOPAT 
( 
SELECT  
    database_name,  
    min(backupfinishdate) AS backupfinishdate 
FROM CTE 
WHERE CTE.type = 'L' 
AND backupfinishdate > @StopAt 
GROUP BY database_name 
) z 
ON z.database_name = CTE.database_name 
    
JOIN 
( 
SELECT 
    database_name, 
    MAX(Last_LSN) AS Last_LSN 
FROM CTE 
WHERE CTE.backupfinishdate < ISNULL(@StopAt,GETDATE()) 
AND CTE.Type IN ('D','I') 
GROUP BY database_name 
) x1 
ON CTE.database_name = x1.database_name 
    
JOIN 
( 
SELECT 
    DB_NAME(mf.database_id) AS name 
    ,mf.Physical_Name 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 
    
JOIN 
( 
SELECT 
    DB_NAME(mf.database_id) AS name, type_desc 
    ,mf.Physical_Name PhysicalName 
    ,mf.Name AS LogicalName 
FROM sys.master_files mf 
WHERE type_desc = 'LOG' 
) y1 
ON d.name = y1.name 
    
WHERE CTE.[type] = 'L' 
AND CTE.backupfinishdate <= ISNULL(x.backup_finish_date,'31 Dec, 2199') -- Less than next full backup 
AND CTE.backupfinishdate >= ISNULL(y.backupfinishdate, CTE.backupfinishdate) --Great than or equal to last differential backup 
AND CTE.backupfinishdate <= ISNULL(z.backupfinishdate, CTE.backupfinishdate) -- Less than or equal to last file file in recovery chain (IE Log Backup datetime might be after STOPAT) 
AND CTE.family_sequence_number = 1 
    
    
-------------------------------------------------------------- 
UNION -- Restore WITH RECOVERY 
-------------------------------------------------------------- 
SELECT 
    ';SELECT ''' + 'RESTORE_RECOVERY'' AS STEP' + ';RESTORE DATABASE [' + d.[name] + ']' + SPACE(1) + 'WITH RECOVERY' AS Command, 
    32771 AS Sequence, 
    d.name AS database_name, 
    '' AS BackupDevice, 
    GETDATE() AS backupfinishdate, 
    CTE.backup_size, 
    '999999999999999999998' 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 
    ';SELECT ''' + 'DBCC_CHECKDB'' AS STEP' + ';DBCC CHECKDB(' + '''' + d.[name] + '''' + ') WITH NO_INFOMSGS IF @@ERROR > 0 PRINT N''CONSISTENCY PROBLEMS IN DATABASE : ' + d.name + ''' ELSE PRINT N''CONSISTENCY GOOD IN DATABASE : ' + d.name + '''' AS Command, 
    32772 AS Sequence, 
    d.name AS database_name, 
    '' AS BackupDevice, 
    DATEADD(minute,1,GETDATE()) AS backupfinishdate, 
    CTE.backup_size, 
    '999999999999999999999' 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 -- WITH MOVE secondary data files, allows for up to 32769/2 file groups 
-------------------------------------------------------------- 
SELECT 
    ', MOVE N' + '''' + b.name + '''' + ' TO N' + 
    '''' + 
    CASE ISNULL(@WithMoveDataFiles,'Actual') 
    WHEN 'Actual' THEN b.physical_name 
    ELSE @WithMoveDataFiles + SUBSTRING(b.Physical_Name,LEN(b.Physical_Name) - CHARINDEX('\',REVERSE(b.Physical_Name),1) + 2,CHARINDEX('\',REVERSE(b.Physical_Name),1) + 1) 
    END + '''', 
    b.file_id AS Sequence, 
    DB_NAME(b.database_id) AS database_name, 
    'SECONDARY FULL' AS BackupDevice, 
    CTE.backupfinishdate, 
    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 = 'ROWS' 
AND b.file_id > 2 
AND @WithMoveDataFiles IS NOT NULL
-------------------------------------------------------------- 
) a 
-------------------------------------------------------------- 
    
WHERE a.database_name = ISNULL(@database,a.database_name) 
AND (@IncludeSystemDBs = 1 OR a.database_name NOT IN('master','model','msdb')) 
    
ORDER BY
    database_name, 
    sequence
   
    
END

 

ps_LogShippingLight

The companion PowerShell script ‘ps_LogShippingLight’ can be scheduled as a SQL Agent job to automate restores on a standby server, called from a command prompt or by an SSIS package to refresh source data. As with the procedure, no changes should be necessary to the script, save then call it passing parameters. The PowerShell script calls the T-SQL procedure and executes the restore commands on a standby server using the UNC path of the backup folders to override the FROM FILE backup path. It accepts all the stored procedure parameters and the following additional ones to support incremental restores in a log shipping type configuration. Assuming the script was saved as x:\PS\ps_LogShippingLight.ps1

 X:\PS\ps_LogShippingLight.ps1 -DBName "workspace" -PrimaryServer "PBV02\I08A" -StandbyServer "PBV02\I08B" -WithMoveDataFiles "x:\SQL08\I08B\Data\" -WithMoveLogFile  "x:\SQL08\I08B\Logs\" -FromFileFullUNC "\\pbv02\Backups\FULL\" -FromFileDiffUNC "\\pbv02\Backups\DIFF\" -FromFileLogUNC "\\pbv02\Backups\LOG\" -Initialize "1"  -WithRecovery "1" -WithCHECKDB "1" 

An escape character ` is needed if a path parameter contains $, IE “\\s1-sql\backups\S1-SQL`$I08\db_workspace\FULL\” . $ is a special character in PowerShell so the escape is required.

Parameter Example Description
$PrimaryServer PBV02\I08A Primary server name, required, backup source
$StandbyServer PBV02\I08B Standby server name, required, restore  target. This can be the same as $PrimaryServer for DR.
$RestoreLog X:\Backups\Log1.csv Records the last LSN restored, defaults to ‘backup folder . database name.csv’
$Initialize 0 or 1 Continue restoring only new diff and log backups (No) or start again from full (Yes). Defaults to Yes.
$KillConnections 0 or 1 Kill blocking users (1) or end restore (0), defaults to 1

The PowerShell script below should be saved as a .PS1 file then called passing parameters as necessary. It is the driver script for stored procedure sp_LogShippingLight, it calls the procedure on a primary server to get a restore script which it executes on the standby server. If Initialize = No , it checks a csv file it maintains to see which backups have already been restored and only restores any unprocessed new differential/log backups.

The SQLPS module is a dependency, it’s installed automatically with SQL Server 2012 when the remote server administration feature is enabled but if you are using SQL Server 2008 then this module needs to be made available for import. Another prerequisite is for Execution Policy to be set to Unrestricted.


# Updates: http://paulbrewer.wordpress.com/2013/10/12/database-restore-automation/
# Version 2.0
# ==============================================================================
# PARAMETERS AND DEFAULTS
# ==============================================================================
Param($DBName, $WithMoveDataFiles, $WithMoveLogFile ,$FromFileFullUNC ,$FromFileDiffUNC ,$FromFileLogUNC ,$StopAt ,$StandBy ,$WithRecovery ,$WithCHECKDB , $PrimaryServer , $StandbyServer , $RestoreLog ,$Initialize ,$KillConnections )
  
# DEFAULTS
if ($FromFileDiffUNC -eq $null) {$FromFileDiffUNC = $FromFileFullUNC}
if ($FromFileLogUNC-eq $null) {$FromFileLogUNC= $FromFileFullUNC}
if ($StopAt -eq $null) {$StopAt = Get-Date -Format s}
if ($StandBy -eq $null){$StandBy = 1}
if ($WithRecovery -eq $null){$WithRecovery = 0}
if ($WithCHECKDB -eq $null){$WithCHECKDB = 0}
  
#PoSh VARIABLES
if ($Initialize -eq $null) {$Initialize = 1}
if ($KillConnections -eq $null) {$KillConnections = 1}
if ($RestoreLog -eq $null) {$RestoreLog = $FromFileFullUNC + $DBName + ".csv"}
  
# REPORT RUNTIME VALUES
"EXECUTION - RUNTIME PARAMETERS" | Out-Default
"DBName  = " + $DBName | Out-Default
"WithMoveDataFiles = " + $WithMoveDataFiles | Out-Default
"WithMoveLogFile = " + $WithMoveLogFile | Out-Default
"FromFileFullUNC = " + $FromFileFullUNC | Out-Default
"FromFileDiffUNC = " + $FromFileDiffUNC | Out-Default
"FromFileLogUNC= " + $FromFileLogUNC | Out-Default
"StopAt = " + $StopAt  | Out-Default
"StandBy = " + $StandBy  | Out-Default
"WithRecovery = " + $WithRecovery  | Out-Default
"WithCHECKDB = " + $WithCHECKDB  | Out-Default
"PrimaryServer = " + $PrimaryServer  | Out-Default
"StandbyServer = " + $StandbyServer  | Out-Default
"RestoreLog = " + $RestoreLog  | Out-Default
"Initialize = " + $Initialize  | Out-Default
"KillConnections = " + $KillConnections  | Out-Default
" " | Out-Default
  
# ==============================================================================
# INITIALIZE AND VALIDATE PARAMETERS
# ==============================================================================
#Snapin for the Invoke-SQLCmd cmdlet
Add-PSSnapin SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue
Import-Module SQLPS -DisableNameChecking
Set-Location -Path Alias:
#
if ($PrimaryServer -eq $null -or $StandbyServer -eq $null -or $DBName -eq $null)
{throw "BOTH PRIMARY AND STANDBY SERVER NAMES ARE REQUIRED PARAMETERS AS IS DATABASE NAME"}
  
  
if ($KillConnections -eq 0)
{
  
    $activeconnections = "SELECT * FROM sys.sysprocesses WHERE dbid = DB_ID('" + $DBName + "')"
    $check = Invoke-Sqlcmd -Query $activeconnections -Database "master" -ServerInstance $StandbyServer
    if ($check.spid -ne $null)
    {throw "ACTIVE CONNECTIONS ARE BLOCKING RESTORE COMMANDS ON THE STANDBY SERVER"}
}
  
if ($FromFileFullUNC -eq $null)
{throw "UNC PATH TO PRIMARY BACKUP FOLDER IS REQUIRED"}
  
# ==============================================================================
# GENERATES T-SQL RESTORE COMMANDS DESERIALIZING SECONDARY FILES
# ==============================================================================
# With Overrides of data log file locations during restore
if ($WithMoveDataFiles -ne $null -and $WithMoveLogFile -ne $null) 
{ 
    $LogShippingLight_EXEC = "EXEC dbo.sp_LogShippingLight @Database = '" + $DBName + "', @WithMoveDataFiles = '" + $WithMoveDataFiles + "', @WithMoveLogFile = '" + $WithMoveLogFile + "', @FromFileFullUNC = '" + $FromFileFullUNC + "', @FromFileDiffUNC = '" + $FromFileDiffUNC + "', @FromFileLogUNC= '" + $FromFileLogUNC+ "', @StopAt = '" + $StopAt + "', @StandbyMode = '" + $Standby +"', @WithRecovery = '" + $WithRecovery +"', @WithCHECKDB = '" + $WithCHECKDB +"'" 
} 
else 
{ 
    # Without Overrides of data log file locations during restore 
    $LogShippingLight_EXEC = "EXEC dbo.sp_LogShippingLight @Database = '" + $DBName + "', @FromFileFullUNC = '" + $FromFileFullUNC + "', @FromFileDiffUNC = '" + $FromFileDiffUNC + "', @FromFileLogUNC= '" + $FromFileLogUNC+ "', @StopAt = '" + $StopAt + "', @StandbyMode = '" + $Standby +"', @WithRecovery = '" + $WithRecovery +"', @WithCHECKDB = '" + $WithCHECKDB +"'" 
} 

"EXECUTING SQL PROCEDURE > " 
$LogShippingLight_EXEC 
"< EXECUTING SQL PROCEDURE " 
 
$LogShippingLight_Results = Invoke-SQLCmd -Query $LogShippingLight_EXEC -QueryTimeout 6000 -Database "msdb" -ServerInstance $PrimaryServer
#$LogShippingLight_Results | FL
 
if ($LogShippingLight_Results -ne $null)
{
  
    foreach ($ndffile in $LogShippingLight_Results)
    {
        if ($ndffile.BackupDevice -eq "SECONDARY FULL")
        {if ($ndffiles -notcontains $ndffile.TSQL) {$ndffiles = $ndffiles + $ndffile.TSQL}}
    }
  
    #$ndffiles | Out-Default
  
    $LogShippingLight_Results = $LogShippingLight_Results | Where-Object {$_.BackupDevice -ne "SECONDARY FULL" -and $_.BackupDevice -ne "SECONDARY DIFF"}
    $hash=@{}
    foreach ($command in $LogShippingLight_Results)
    {
        if ({$command.TSQL -contains "*RESTORE*"} -and {$command.TSQL -notcontains "*WITH RECOVERY*"}) {$hash.Add($command.Last_LSN,($command.TSQL + $ndffiles))}
 #       else {$hash.Add($command.BackupDate,$command.TSQL)}
    }
    $pendingcmds = $hash.GetEnumerator() | Sort-Object -Property key
}
else #database has no backups
{throw "NO BACKUP FILES FOUND FOR INSTANCE " + $PrimaryServer + ", DATABASE - " + $DBNAME + ", CANNOT RESTORE TO INSTANCE " +  $StandbyServer}
  
# ==============================================================================
#  FUNCTION TO KILL BLOCKING CONNECTIONS
# ==============================================================================
function f_killconnections($DBName)
{
    $KillQueryConstructor = "SELECT ';KILL ' + CAST(spid AS VARCHAR(4)) + '' FROM sys.sysprocesses WHERE dbid = DB_ID('" + $DBName + "')"
    $KillCommands = Invoke-Sqlcmd  -QueryTimeout 6000 -Query $KillQueryConstructor -Database "master" -ServerInstance $StandbyServer
    foreach ($KillCommand in $KillCommands)
    {
        if ($KillCommand -ne $NULL)
        {
            $KillCommand[0] | Out-Default
            $Result = Invoke-Sqlcmd -Query $KillCommand[0] -Database "master" -ServerInstance $StandbyServer
            $Result | Out-Default
        }
    }
}
  
# ==============================================================================
# IF INITIALIZE = YES RESTORE LAST FULL, MOST RECENT DIFFERENTIAL AND LOG BACKUPS
# ==============================================================================
 
If ($Initialize -eq 1)
{
    $hash1=@{}
    foreach ($pendingcmd in $pendingcmds)
    {
        "RESTORE IN PROGESS FOR LSN - " + $pendingcmd.key + $pendingcmd.value | Out-Default
        # Run RESTORE
        if ($KillConnections -eq 1) {f_killconnections -DBName $DBName}
        try {Invoke-SQLCmd -QueryTimeout 6000 -Query $pendingcmd.value -Database "master" -ServerInstance $StandbyServer -verbose}
        catch
        {throw "EXECUTION RUNTIME ERROR "}
        "-------------------------------------------------------------------------------"  | Out-Default
  
        # hash table log for csv
        sleep -Seconds 1
        $hash1.Add($Pendingcmd.name,$pendingcmd.value)
    }
  
    # Log restore commands to csv
    If (Test-Path $RestoreLog) {Remove-Item $RestoreLog}
    foreach ($Restore in ($hash1.GetEnumerator() | Sort-Object Name))
    {
        # Definiton of a new 'Log Record' object
        $RestoreLogObject = @{
        Last_LSN = $Restore.Name
        RestoreCommand = $Restore.value
        RestoreStopAt = $StopAt}
  
        If (Test-Path $RestoreLog)
        {
            $JustData = New-Object PSObject -Property $RestoreLogObject | ConvertTo-Csv -NoTypeInformation
            $JustData[1] | Add-Content -Path Microsoft.PowerShell.Core\FileSystem::$RestoreLog -Encoding UTF8
        }
        else {New-Object PSObject -Property $RestoreLogObject | Export-Csv -Path Microsoft.PowerShell.Core\FileSystem::$RestoreLog -Encoding UTF8 -NoTypeInformation}
    }
}
  
# ==============================================================================
# IF INITIALIZE = NO RESTORE RECENT DIFFERENTIAL AND LOG BACKUPS
# ==============================================================================
If ($Initialize -eq 0)
{
    $RestoreCmdsAlreadyRun = Import-Csv -Path Microsoft.PowerShell.Core\FileSystem::$RestoreLog
 
    if ($RestoreCmdsAlreadyRun.RestoreStopAt -gt $StopAt)
    {
        throw "STOPAT PARAMETER IS EARLIER THAN A PREVIOUS RUN " | Out-Default
        break
    }
  
    if (($RestoreCmdsAlreadyRun.RestoreCommand -like "*DBCC*") -or ($RestoreCmdsAlreadyRun.RestoreCommand -like "*RESTORE_RECOVERY*"))
    {
        throw "RECOVERY HAS RUN, NO FURTH RESTORES PERMITTED" | Out-Default
        break
    }
 
    $hash2=@{}
    foreach ($pendingcmd in $pendingcmds)
    {
        if (($pendingcmd.key -ge $RestoreCmdsAlreadyRun.Last_LSN) -and  ($pendingcmd.value -ne $RestoreCmdsAlreadyRun.RestoreCommand))
        {
            "RESTORE IN PROGESS FOR LSN - " + $pendingcmd.key + $pendingcmd.value | Out-Default
            # Run RESTORE
            if ($KillConnections -eq 1) {f_killconnections -DBName $DBName}
            try {Invoke-SQLCmd -QueryTimeout 6000 -Query $pendingcmd.value -Database "master" -ServerInstance $StandbyServer -verbose}
            catch
            {
                throw "ERROR RUNNING QUERY "
            }
            "-------------------------------------------------------------------------------"  | Out-Default
  
            # hash table log for csv
            sleep -Seconds 1
            $hash2.Add($Pendingcmd.name,$pendingcmd.value)
        }
    }
 
#    clutters output, unnecessary
#    if ($pendingcmd.value = $RestoreCmdsAlreadyRun.Last_LSN)
#    {
#        "NO NEW LOG OR DIFFERENTIAL BACKUPS" | Out-Default
#        "-------------------------------------------------------------------------------"  | Out-Default
#    }   
 
  
    # Log restore commands to csv
    If (Test-Path $RestoreLog) {Remove-Item $RestoreLog}
    foreach ($Restore in ($hash2.GetEnumerator() | Sort-Object Name))
    {
        # Definiton of a new 'Log Record' object
        $RestoreLogObject = @{
        Last_LSN = $Restore.Name
        RestoreCommand = $Restore.value
        RestoreStopAt = $StopAt}
  
        If (Test-Path $RestoreLog)
        {
            $JustData = New-Object PSObject -Property $RestoreLogObject | ConvertTo-Csv -NoTypeInformation
            $JustData[1] | Add-Content -Path Microsoft.PowerShell.Core\FileSystem::$RestoreLog -Encoding UTF8
        }
        else {New-Object PSObject -Property $RestoreLogObject | Export-Csv -Path Microsoft.PowerShell.Core\FileSystem::$RestoreLog -Encoding UTF8 -NoTypeInformation}
    }
}

In a log shipping, rather than recovery situation, the PoSh script might be executed as below:


X:\PS\ps_LogShippingLight.ps1 -DBName "workspace" -PrimaryServer "PBV02\I08A" -StandbyServer "PBV02\I08B" -WithMoveDataFiles "x:\SQL08\I08B\Data\" -WithMoveLogFile  "x:\SQL08\I08B\Logs\" -FromFileFullUNC "\\pbv02\Backups\FULL\" -FromFileDiffUNC "\\pbv02\Backups\DIFF\" -FromFileLogUNC "\\pbv02\Backups\LOG\" -Initialize "1"
while (1 -eq 1)
{
    X:\PS\ps_LogShippingLight.ps1 -DBName "workspace" -PrimaryServer "PBV02\I08A" -StandbyServer "PBV02\I08B" -WithMoveDataFiles "x:\SQL08\I08B\Data\" -WithMoveLogFile  "x:\SQL08\I08B\Logs\" -FromFileFullUNC "\\pbv02\Backups\FULL\" -FromFileDiffUNC "\\pbv02\Backups\DIFF\" -FromFileLogUNC "\\pbv02\Backups\LOG\" -Initialize "0"
    Start-Sleep -s 1800
}

Conclusion

This PoSh script and SQL stored procedure might meet certain business requirements as they are now. Log ship type incremental restores to a standby for ETL source data is possible as is the automated validation of production backups, restoring and running CHECKDB to a standby. They might also form part of a larger overall solution such as reinitializing Availability Groups, database mirroring, replication. They have limitations and challenges, such as those listed below, but they do offer more SQL Server restore automation possibilities than anything else available free (but with no warranty) on the internet that I can find. Version 3 will include a change to exclude the distribution database and start the log recovery chain earlier. A trappable ‘Log Backup is too earlier’ error is preferable to an unmanagable ‘Missing Backup Log File’ in the restore log(s) script returned, log backups that start after a concurrent full backup and finish before it are currently excluded.

  • In log shipping mode, whenever a full backup is taken on the primary, it will be restored to the standby by this framework. If the primary database is huge, this may be sub-optimal. Strictly speaking, only new log / diff backups are required after the initial full backup is restored to the standby
  • Some of the features available in the RESTORE DATABASE command, such as those related to Service Broker, Replication and Change Data Capture, are not available in this framework. Additional commands may have to be run after WITH RECOVERY to bring the database into the desired state.
  • SQL Agent security permissions to run the PoSh script, the log shipping light stored procedure on the primary and the restore commands on standby have been the most common problems. Also, making the SQLPS. module available to the script at runtime can be a challenge.
  • The logging in the PoSh script isn’t great, ‘out-default’ & ‘invoke-sql -verbose’
  • The log shipping Light PoSh script doesn’t copy backup files to the standby server like the default SQL Server Log Shipping feature so it isn’t a good idea for DR purposes.
  • The @WithMoveDataFiles override for database files only offers 1 destination folder. If a database has multiple .ndf’s in different folders, they can only be move to 1 folder on the standby. If folder structures match on the primary and standby servers then no override is required and database files will restore on the standby to the same drive/folder names as on the primary.
  • If a database was upgraded from SQL Server 2000 then additional WITH DATA PURITY checks may be required but are not included in the DBCC CHECKDB command generated.


Categories: PowerShell, Restore

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: