Welcome to this article describing 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 your purpose.

sp_LogShippingLight

A stored procedure 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, comment out those not 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

ps_LogShippingLight

The companion PowerShell script 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 which can be the primary server for disaster recovery.
$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

sp_LogShippingLight

The T-SQL code below compiles in SQL Server Versions 2005 or higher. 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.

Known issues:

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.

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.00 (2013-10-15)
(C) 2012, Paul Brewer
 
Feedback: <a href="mailto:paulbrewer@yahoo.co.uk">mailto:paulbrewer@yahoo.co.uk</a>
Updates: <a href="http://paulbrewer.wordpress.com/2013/10/12/database-restore-automation/">http://paulbrewer.wordpress.com/2013/10/12/database-restore-automation/</a>
 
This procedure has a companion PowerShell driver script called ps_LogShippingLight
It is designed to gather backup information and return restore commands which are then executed on a standby server by the PoSh script.
 
Usage examples:
 
sp_LogShippingLight
No parameters = Generates RESTORE commands for all USER databases, from actual backup files to existing file locations to most current time, consistency checks, CHECKSUM where possible
 
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 left in STANDBY
Ignores COPY_ONLY backups, restores to default file locations from default backup file.
 
sp_LogShippingLight @Database = 'db_workspace', @StopAt = '2012-12-23 12:31:00.000', @WithMoveDataFiles = 'c:\temp\', @WithMoveLogFile  = 'c:\temp\' , @FromFileFullUNC = 'c:\backup\'
Overrides data file folder, log file folder and backup file folder.
Generates RESTORE commands for a specific database from most recent full backup, most recent differential + transaction log backups before STOPAT.
Ignores COPY_ONLY backups, includes WITH MOVE to simulate a restore to a test environment with different folder mapping.
 
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 for stepping through log restores with a readable database
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
*********************************************************************************************/ 
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
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,
    '99999999999999998' 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,
    '99999999999999999' 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
--------------------------------------------------------------
) 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 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 +"'"
}
 
$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}
    }
}

Log Shipping Light Example

The test lab consists of 2 SQL Server 2008 R2 instances on the same virtual server. This is obviously not a perquisite and the Posh script could be run from anywhere.

Server = PBV01

Instance = I08A

Database = workspace (primary)
Data File = x:\SQL08\I08A\Data
Log File = x:\SQL08\I08A\Logs
Full Backup = X:\SQL08\I08A\Backups\FULL
Differential Backup = X:\SQL08\I08A\Backups\DIFF
Log Backup = X:\SQL08\I08A\Backups\LOG

Instance = I08B

Database = workspace (standby)
Data File = x:\SQL08\I08B\Data
Log File = x:\SQL08\I08B\Logs

Full Recovery with CHECKDB The output below shows an invocation of the ps_LogShippingLight.ps1 script and the commands it executed on the standby server. The restores start from the last full backup (-Initialize “1″), the most recent differential then outstanding log backups and includes recovery and CHECKDB.

PS C:\Users\Administrator> 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"
 
EXECUTION - RUNTIME PARAMETERS
DBName  = workspace
WithMoveDataFiles = x:\SQL08\I08B\Data\
WithMoveLogFile = x:\SQL08\I08B\Logs\
FromFileFullUNC = \\pbv02\Backups\FULL\
FromFileDiffUNC = \\pbv02\Backups\DIFF\
FromFileLogUNC= \\pbv02\Backups\LOG\
StopAt = 2013-10-18T22:33:58
StandBy = 1
WithRecovery = 1
WithCHECKDB = 1
PrimaryServer = PBV02\I08A
StandbyServer = PBV02\I08B
RestoreLog = \\pbv02\Backups\FULL\workspace.csv
Initialize = 1
KillConnections = 1
 
RESTORE IN PROGESS FOR LSN - 26000000048000001;SELECT 'RESTORE_FULL' AS STEP;RESTORE DATABASE [workspace] FROM DISK = N
'\\pbv02\Backups\FULL\workspace_1.bak' , DISK = N'\\pbv02\Backups\FULL\workspace_2.bak'WITH REPLACE, FILE = 2,CHECKSUM,
 STANDBY =N'\\pbv02\Backups\FULL\\workspace_ROLLBACK_UNDO.bak ', STATS=10, MOVE N'workspace_data' TO 'x:\SQL08\I08B\Dat
a\workspace_data.mdf', MOVE N'workspace_log' TO 'x:\SQL08\I08B\Logs\workspace.ldf'
 
STEP
----
RESTORE_FULL
VERBOSE: 71 percent processed.
VERBOSE: 100 percent processed.
VERBOSE: Processed 176 pages for database 'workspace', file 'workspace_data' on file 2.
VERBOSE: Processed 2 pages for database 'workspace', file 'workspace_log' on file 2.
VERBOSE: RESTORE DATABASE successfully processed 178 pages in 0.469 seconds (2.965 MB/sec).
-------------------------------------------------------------------------------
RESTORE IN PROGESS FOR LSN - 26000000061600001;SELECT 'RESTORE_DIFF' AS STEP;RESTORE DATABASE [workspace] FROM DISK = N
'\\pbv02\Backups\DIFF\workspace_Diff15.bak' , DISK = N'\\pbv02\Backups\DIFF\workspace_Diff25.bak'WITH REPLACE, FILE = 2
,CHECKSUM, STANDBY =N'\\pbv02\Backups\FULL\\workspace_ROLLBACK_UNDO.bak ', STATS=10, MOVE N'workspace_data' TO 'x:\SQL0
8\I08B\Data\workspace_data.mdf', MOVE N'workspace_log' TO 'x:\SQL08\I08B\Logs\workspace.ldf'
RESTORE_DIFF
VERBOSE: 100 percent processed.
VERBOSE: Processed 48 pages for database 'workspace', file 'workspace_data' on file 2.
VERBOSE: Processed 2 pages for database 'workspace', file 'workspace_log' on file 2.
VERBOSE: RESTORE DATABASE successfully processed 50 pages in 0.365 seconds (1.070 MB/sec).
-------------------------------------------------------------------------------
RESTORE IN PROGESS FOR LSN - 26000000062400001;SELECT 'RESTORE_LOG' AS STEP;RESTORE LOG [workspace] FROM DISK = N'\\pbv
02\Backups\LOG\workspace_LogA_6.trn', DISK = N'\\pbv02\Backups\LOG\workspace_LogB_6.trn' WITH STANDBY =N'\\pbv02\Backup
s\FULL\\workspace_ROLLBACK_UNDO.bak ',  CHECKSUM, FILE = 2 ,STOPAT = '2013-10-18 22:33:58',MOVE N'workspace_data' TO 'x
:\SQL08\I08B\Data\workspace_data.mdf', MOVE N'workspace_log' TO 'x:\SQL08\I08B\Logs\workspace.ldf'
RESTORE_LOG
VERBOSE: Processed 0 pages for database 'workspace', file 'workspace_data' on file 2.
VERBOSE: Processed 3 pages for database 'workspace', file 'workspace_log' on file 2.
VERBOSE: This backup set contains records that were logged before the designated point in time. The database is being le
ft in the restoring state so that more roll forward can be performed.
VERBOSE: RESTORE LOG successfully processed 3 pages in 0.215 seconds (0.109 MB/sec).
-------------------------------------------------------------------------------
RESTORE IN PROGESS FOR LSN - 26000000064000001;SELECT 'RESTORE_LOG' AS STEP;RESTORE LOG [workspace] FROM DISK = N'\\pbv
02\Backups\LOG\workspace_LogA_7.trn', DISK = N'\\pbv02\Backups\LOG\workspace_LogB_7.trn' WITH STANDBY =N'\\pbv02\Backup
s\FULL\\workspace_ROLLBACK_UNDO.bak ',  CHECKSUM, FILE = 2 ,STOPAT = '2013-10-18 22:33:58',MOVE N'workspace_data' TO 'x
:\SQL08\I08B\Data\workspace_data.mdf', MOVE N'workspace_log' TO 'x:\SQL08\I08B\Logs\workspace.ldf'
RESTORE_LOG
VERBOSE: Processed 0 pages for database 'workspace', file 'workspace_data' on file 2.
VERBOSE: Processed 1 pages for database 'workspace', file 'workspace_log' on file 2.
VERBOSE: This backup set contains records that were logged before the designated point in time. The database is being le
ft in the restoring state so that more roll forward can be performed.
VERBOSE: RESTORE LOG successfully processed 1 pages in 0.119 seconds (0.065 MB/sec).
-------------------------------------------------------------------------------
RESTORE IN PROGESS FOR LSN - 26000000065600001;SELECT 'RESTORE_LOG' AS STEP;RESTORE LOG [workspace] FROM DISK = N'\\pbv
02\Backups\LOG\workspace_LogA_8.trn', DISK = N'\\pbv02\Backups\LOG\workspace_LogB_8.trn' WITH STANDBY =N'\\pbv02\Backup
s\FULL\\workspace_ROLLBACK_UNDO.bak ',  CHECKSUM, FILE = 2 ,STOPAT = '2013-10-18 22:33:58',MOVE N'workspace_data' TO 'x
:\SQL08\I08B\Data\workspace_data.mdf', MOVE N'workspace_log' TO 'x:\SQL08\I08B\Logs\workspace.ldf'
RESTORE_LOG
VERBOSE: Processed 0 pages for database 'workspace', file 'workspace_data' on file 2.
VERBOSE: Processed 1 pages for database 'workspace', file 'workspace_log' on file 2.
VERBOSE: This backup set contains records that were logged before the designated point in time. The database is being le
ft in the restoring state so that more roll forward can be performed.
VERBOSE: RESTORE LOG successfully processed 1 pages in 0.122 seconds (0.064 MB/sec).
-------------------------------------------------------------------------------
RESTORE IN PROGESS FOR LSN - 99999999999999998;SELECT 'RESTORE_RECOVERY' AS STEP;RESTORE DATABASE [workspace] WITH RECO
VERY
RESTORE_RECOVERY
VERBOSE: RESTORE DATABASE successfully processed 0 pages in 3.543 seconds (0.000 MB/sec).
-------------------------------------------------------------------------------
RESTORE IN PROGESS FOR LSN - 99999999999999999;SELECT 'DBCC_CHECKDB' AS STEP;DBCC CHECKDB('workspace') WITH NO_INFOMSGS
 IF @@ERROR > 0 PRINT N'CONSISTENCY PROBLEMS IN DATABASE : workspace' ELSE PRINT N'CONSISTENCY GOOD IN DATABASE : works
pace'
DBCC_CHECKDB
VERBOSE: CONSISTENCY GOOD IN DATABASE : workspace
-------------------------------------------------------------------------------

The PoSh script above was run after the T-SQL Lab Setup script below had completed on the primary server. It’s deliberately obtuse and complex to test the constructed restore script integrity, multiple generations of stripped backup files are created.

-------------------------------------------------
-- Log Shipping Light Example
-- Create Primary Database
-------------------------------------------------
:CONNECT VCTSDBA05
:SETVAR DataDrive "C:\Share\"
:SETVAR LogDrive "C:\Share\"
:SETVAR DatabaseName "workspace"
:SETVAR BackupFull "C:\Share"
:SETVAR BackupDiff "C:\Share"
:SETVAR BackupLog "C:\Share"
 
IF DATABASEPROPERTYEX(N'$(databasename)','Status') IS NOT NULL
BEGIN
    ALTER DATABASE $(DatabaseName) SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE $(DatabaseName)
END;
 
CREATE DATABASE $(DatabaseName)
ON
(
    NAME = $(DatabaseName)_data,
    FILENAME = N'$(DataDrive)\$(DatabaseName)_data.mdf',
    SIZE = 10,
    MAXSIZE = 500,
    FILEGROWTH = 5
)
LOG ON
(
    NAME = $(DatabaseName)_log,
    FILENAME = N'$(LogDrive)\$(DatabaseName).ldf',
    SIZE = 5MB,
    MAXSIZE = 5000MB,
    FILEGROWTH = 5MB
) ;
GO
 
-------------------------------------------------
-- Create Table
USE $(DatabaseName);
 
IF OBJECT_ID('dbo.HeartBeat','U') IS NOT NULL
    DROP TABLE dbo.HeartBeat;
 
CREATE TABLE [dbo].[HeartBeat](
[GUID_PK] [uniqueidentifier] NOT NULL,
[CreateDate] [datetime] NULL,
[CreateServer] [nvarchar](50) NULL,
[RandomNbr] [int] NULL,
CONSTRAINT [PK_HeartBeat] PRIMARY KEY CLUSTERED
(
[GUID_PK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
 
ALTER TABLE [dbo].[HeartBeat] ADD CONSTRAINT [DF_HeartBeat_GUID_PK] DEFAULT (newsequentialid()) FOR [GUID_PK];
 
ALTER TABLE [dbo].[HeartBeat] ADD CONSTRAINT [DF_HeartBeat_CreateDate] DEFAULT (getdate()) FOR [CreateDate];
 
ALTER TABLE [dbo].[HeartBeat] ADD CONSTRAINT [DF_HeartBeat_CreateServer] DEFAULT (@@servername) FOR [CreateServer];
 
-------------------------------------------------
-- Easily tracked activity and backups
--:SETVAR DatabaseName "workspace"
--:SETVAR BackupFull "X:\SQL08\I08A\Backups\FULL\"
--:SETVAR BackupDiff "X:\SQL08\I08A\Backups\DIFF\"
--:SETVAR BackupLog "X:\SQL08\I08A\Backups\LOG\"
 
DECLARE @FileGeneration INT =1
WHILE @FileGeneration < 3 -- ((@FileGeneration - 1) * @ExecutionTime) + Restore Runtime) = Total Test Runtime
BEGIN  -------------------------------------------------   -- Backup database to 2 striped files. First generation FILE = 1   
DECLARE @nSQL NVARCHAR(1000);  
SELECT @nSQL = N'
   BACKUP DATABASE $(DatabaseName)    
   TO DISK = ' + '''' + '$(BackupFull)\$(DatabaseName)_1.bak' + '''' + ', 
   DISK = ' + '''' + '$(BackupFull)\$(DatabaseName)_2.bak' + '''' + '     
   WITH CHECKSUM,COMPRESSION' + CASE @FileGeneration WHEN 1 THEN ',INIT;' ELSE ';' END    
   EXEC sp_executesql @nSQL;  
   --PRINT @nSQL  
   -------------------------------------------------  
   -- Insert data, log & differential backups. To 2 striped files, file gen# will be @FileGeneration  
   SET NOCOUNT ON;    
   DECLARE @Now DATETIME = GETDATE(); 
   DECLARE @ExecutionTime INT = 2 -- Minutes  
   DECLARE @x INT = 0;    
   DECLARE @y AS DATETIME2;   
   DECLARE @z VARCHAR(200);   
   WHILE @Now > DATEADD(minute,-2,GETDATE())
    BEGIN
        WAITFOR DELAY '00:00:010'; --10 Second Delay
        INSERT INTO [dbo].[HeartBeat]  ([RandomNbr]) VALUES (ROUND((RAND() * 100),0));
 
        SET @x = @x + 1
        SELECT @nSQL = N'
        BACKUP LOG $(DatabaseName)
        TO DISK = ' + '''' + '$(BackupLog)\$(DatabaseName)_LogA_' + CAST(@x AS VARCHAR(3)) + '.trn' + '''' + ',
        DISK = ' + '''' + '$(BackupLog)\$(DatabaseName)_LogB_' + CAST(@x AS VARCHAR(3)) + '.trn' + '''' + '
        WITH CHECKSUM,COMPRESSION' + CASE @FileGeneration WHEN 1 THEN ', INIT;' ELSE ';' END
        EXEC sp_executesql @nSQL;
        --PRINT @nSQL
 
        WAITFOR DELAY '00:00:005';
        INSERT INTO [dbo].[HeartBeat]  ([RandomNbr]) VALUES (ROUND((RAND() * 100),0));
 
        IF @x = 5 -- Random differential backup to test restore script optimization
        BEGIN
 
            SELECT @nSQL = N'
            BACKUP DATABASE $(DatabaseName)
            TO DISK = ' + '''' + '$(BackupDiff)\$(DatabaseName)_Diff1' + CAST(@x AS VARCHAR(3)) + '.bak' + '''' + ',
            DISK = ' + '''' + '$(BackupDiff)\$(DatabaseName)_Diff2' + CAST(@x AS VARCHAR(3)) + '.bak' + '''' + '
            WITH DIFFERENTIAL, CHECKSUM,COMPRESSION' + CASE @FileGeneration WHEN 1 THEN ', INIT;' ELSE ';' END
            EXEC sp_executesql @nSQL;
            --PRINT @nSQL
 
        END
 
        SET @y = GETDATE();
        SET @z = 'Log = ' + CAST(@x AS VARCHAR(3)) + ' - STOPAT = ' + CAST(@y AS VARCHAR(19));
        RAISERROR (@z,0,0);
    END;
 
    SET @FileGeneration = @FileGeneration + 1
END
 
GO

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 integrity validation of production backups, restoring with recovery 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.

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.
If multiple database backups finish in the same second, the framework errors.

Database Restore Automation

sp_LogShippingLight

ps_LogShippingLight

Log Shipping Light Example

Conclusion

Rate

Share

Categories

Share

Rate

Database Restore Automation

sp_LogShippingLight

ps_LogShippingLight

Log Shipping Light Example

Conclusion

Rate

Share

Categories

Share

Rate

Related content

How to Generate a Restore Script

Backup Strategy in Azure SQL?

Scrubbing Personally Identifiable Information

Restoring the SQL Server Master Database Even Without a Backup

Display All Errors Fired in a Restore Procedure