This article describes a simple, home-grown, two-script framework, one T-SQL and one PowerShell (PoSh), for automating SQL database restores. I've called the framework 'Restore Gene' (short for Restore Generator), and you can use it to automate the generation of scripts that will perform a variety of database restore operations, such as a full recovery, a restore to a point in time, or stepping through a recovery sequence in STANDBY mode, using different STOPAT times. It will also generate the DBCC CHECKDB command to execute against the restored database to verify backup file integrity. In addition, the scripts can help to initialize database mirroring and availability groups or implement a basic log shipping solution.

The Restore Gene stored procedure generates the required restore scripts, including the DBCC CHECKDB command, if required. It is a useful tool, by itself; in disaster recovery situations, it can construct a restore script, in seconds. It optionally includes override options for name of the target database and WITH MOVE overrides for data, log file locations, which might help recover lost data by restoring a temporary version of a database to the same instance.

I built the companion PowerShell driver script to automate the database restore process. It calls the stored procedure on the primary server, to generate the required script, then it runs the restore commands on a standby server. You can invoke the PoSh script from from a SQL Agent job step, an SSIS package or any PoSh shell.

No changes should be necessary to either the PoSh script or the stored procedure. Just create them, and then call them, passing parameters to suit your purpose. They are a work in progress, but my goal is that, together, they will provide a robust, flexible, comprehensive and efficient SQL Server restore framework, and one that rivals the GUI Restore Task wizard in SQL Server Management Studio. The Restore Gene command line interface offers automation possibilities not available via the Management Studio GUI. For example, dynamic calls that query current backup history can be saved and invoked as part of other processes.

What's new in the latest version (v3.4)

This is an ongoing effort to provide a 'best of breed' free community tool; a feature rich, robust and flexible database restore framework. It started life as a simple T-SQL query that identified the backup files needed to recover to a given point in time, but over time I've added new features and expanded the framework to incorporate the PoSh driver script.

There have been a few versions of Restore Gene over the last 18 months, with Version 3.4 being the most recent and most stable. Following is a summary of recently-added features, including those added in the very latest version.

sp_RestoreGene – Stored Procedure

• V3.4 - Remove TRY CATCH blocks from RESTORE LOG commands
• V3.32 – Parameterize WITH REPLACE, safety check that a tail of the log backup exists
• V3.32 – Add error handling and logging
• V3.32 – Allow for restored standby/target database name override
• Include CHECKSUM where possible, in the restore script
• Backward compatibility (to SQL Server 2005), compressed backup size issue
• Cater for multiple (< 11) stripped backup files
• Allow override of database data and log file locations
• Allow override of backup file location
• Include a final WITH RECOVERY and optionally DBCC CHECKDB
• Include a WITH STANDBY option

ps_RestoreGene – PowerShell Script

• V3.4 - Terminates recovery chain sequence on first error
• V3.32 – Use 'Last Restore LSN' for incremental restores of new log files, for log shipping ignore new full and diff backups
• V3.32 – Additional error handling and improved (xml) logging
• 'Kill Blocking User' option in the PoSh driver script

The tests developed and used for each version have together formed an ever expanding regression test suite, and V3.4 contains no bugs of which I am aware. The framework is being use operationally by a few people, the feedback and suggestions are very useful and appreciated.

Generating Database Restore Scripts with the sp_RestoreGene Stored Procedure

The stored procedure, sp_RestoreGene, generates and returns a T-SQL restore script. It interrogates msdb to find the latest Full and Differential database backups then the whole string of log backups, which should include a tail log backup.

Simply create the procedure on the primary server, where the backups were taken. On execution, the procedure constructs the T-SQL RESTORE script and returns it to the caller, it isn't executed. To run the generated restore script, simply copy the TSQL column in the results it returns then paste it into a query window.

Parameters are available offering various restore options and overrides. Calling the procedure without any parameters generates a script that will restore all user databases on an instance to the current point in time. Alternatively, we can simply override certain parameter values, as required, leaving the others at their defaults. For example, Listing 1 will generate a script that will restore only the workspace database and then run a DBCC CHECKDB on the restored copy (note that subsequently dropping the restored database is currently a manual step).

USE [master]
GO
DECLARE @RC INT
EXECUTE @RC = [dbo].[sp_RestoreGene]
@Database = 'workspace',
@WithRecovery = 1,
@WithCHECKDB = 1,
@Log_Reference = 'Incident X'
GO

Listing 1: Calling sp_RestoreGene

Listing 2, below, shows the restore script generated by running Listing 1 (the T-SQL commands generated and returned by the stored procedure have been reformatted for presentation purposes). Note that sp_RestoreGene handles automatically the restore of striped as well as single file backups. In this example, Listing 1 didn't supply a @StopAt parameter value, so it has defaulted to current date / time.

; DECLARE @msg_workspace VARCHAR(1000)
; SET @msg_workspace = 'Incident X';
RAISERROR (@msg_workspace,0,0) WITH LOG;
RESTORE DATABASE [workspace]
FROM DISK = N'E:\SQL12\Backups\\workspace_A.bak' ,
DISK = N'E:\SQL12\Backups\\workspace_B.bak'
WITH FILE = 2,CHECKSUM, NORECOVERY, STATS=10,
MOVE N'workspace_data' TO 'E:\SQL12\Data\\workspace_data.mdf',
MOVE N'workspace_log' TO 'E:\SQL12\Logs\\workspace.ldf'

; SET @msg_workspace = 'Incident X';
RAISERROR (@msg_workspace,0,0) WITH LOG;
RESTORE DATABASE [workspace]
FROM DISK = N'E:\SQL12\Backups\\workspace_DiffA_1.bak' ,
DISK = N'E:\SQL12\Backups\\workspace_DiffB_1.bak'
WITH FILE = 2,CHECKSUM, NORECOVERY, STATS=10,
MOVE N'workspace_data' TO 'E:\SQL12\Data\\workspace_data.mdf',
MOVE N'workspace_log' TO 'E:\SQL12\Logs\\workspace.ldf'
; SET @msg_workspace = 'Incident X';
RAISERROR (@msg_workspace,0,0) WITH LOG;
RESTORE LOG [workspace]
FROM DISK = N'E:\SQL12\Backups\\workspace_LogA_2.trn',
DISK = N'E:\SQL12\Backups\\workspace_LogB_2.trn'
WITH NORECOVERY,  CHECKSUM, FILE = 2 ,STOPAT = '2014-08-08 05:32:21' ,
MOVE N'workspace_data' TO 'E:\SQL12\Data\\workspace_data.mdf',
MOVE N'workspace_log' TO 'E:\SQL12\Logs\\workspace.ldf';
; SET @msg_workspace = 'Incident X';
RAISERROR (@msg_workspace,0,0) WITH LOG;
RESTORE LOG [workspace]
FROM DISK = N'E:\SQL12\Backups\\workspace_LogA_2.trn',
DISK = N'E:\SQL12\Backups\\workspace_LogB_2.trn'
WITH NORECOVERY,  CHECKSUM, FILE = 2 ,STOPAT = '2014-08-08 05:32:21' ,
MOVE N'workspace_data' TO 'E:\SQL12\Data\\workspace_data.mdf',
MOVE N'workspace_log' TO 'E:\SQL12\Logs\\workspace.ldf';
; SET @msg_workspace = 'Incident X';
RAISERROR (@msg_workspace,0,0) WITH LOG;
RESTORE DATABASE [workspace] WITH RECOVERY
; SET @msg_workspace = 'Incident X';
RAISERROR (@msg_workspace,0,0) WITH LOG;
DBCC CHECKDB('workspace') WITH NO_INFOMSGS, ALL_ERRORMSGS

sp_RestoreGene Parameters

Table 1 provides a description of all the parameters that the sp_RestoreGene stored procedure accepts, and their default values. The 'PoSh Script Parameters' listed at the end of the table default sensibly and should be used only by the companion PoSh script, ps_RestoreGene.ps1.

Table 1: sp_RestoreGene parameters

Using the StopAt Parameter

If we supply a value for the @StopAt parameter that is prior to the last full backup, then the stored procedure searches the database backup history, identifies the necessary full, differential and log backups from and constructs an optimized restore script to that point in time. Figure 1 illustrates the process of backup file selection.

Figure 1: Backup file selection

sp_RestoreGene Examples

Following are three example calls to, and results from, the sp_RestoreGene stored procedure, illustrating cases where it might prove useful in speeding up manual disaster recovery.

• Example 1 – Generated script will restore all user databases to the most recent backup, WITH RECOVERY, and run DBCC CHECKDB on each restored database. Assumes no tail log backup exists so uses WITH REPLACE.
• Example 2 – Restore a specific database in STANDBY mode, to a specific point in time, on the same instance. Override the target restore database name and data / log file locations.
• Example 3 – Restore a specific database to the current / closest point in time. Assumes a tail of log backup exists

Example 1 – Restore and recover all users database to most recent backups

Table 2 shows the backup history on the instance on which I executed the sp_RestoreGene stored procedure, for this example.

Table 2: User database backup history

Listing 3, below, shows the call to sp_RestoreGene that will return a script to restore all user database from the most recent backups. The assumption below is that no tail of log backups exist so @WithReplace is set to 1. Since we don't supply an @StopAt date / time, the procedure will default to the current date / time.

It's important when calling the procedure to pass just the parameters you want to affect; don't pass NULL parameters.

USE [master]
GO
DECLARE @RC int
EXECUTE @RC = [dbo].[sp_RestoreGene]
@WithRecovery = 1
,@WithCHECKDB = 1
,@WithReplace = 1
,@Log_Reference = 'Example 1 - Recover all user databases';
GO

; DECLARE @msg_workspace VARCHAR(1000)
; SET @msg_workspace = 'Example 1 - Recover all user databases';
RAISERROR (@msg_workspace,0,0) WITH LOG;
RESTORE DATABASE [workspace]
FROM DISK = N'E:\SQL12\Backups\workspace_A.bak' ,
DISK = N'E:\SQL12\Backups\workspace_B.bak'
WITH REPLACE, FILE = 1,CHECKSUM, NORECOVERY, STATS=10,
MOVE N'workspace_data' TO 'E:\SQL12\Data\workspace_data.mdf',
MOVE N'workspace_log' TO 'E:\SQL12\Logs\workspace.ldf'
; SET @msg_workspace = 'Example 1 - Recover all user databases';
RAISERROR (@msg_workspace,0,0) WITH LOG;
RESTORE LOG [workspace]
FROM DISK = N'E:\SQL12\Backups\workspace_LogA_1.trn',
DISK = N'E:\SQL12\Backups\workspace_LogB_1.trn'
WITH NORECOVERY,  CHECKSUM, FILE = 1 ,STOPAT = '2014-07-13 15:17:53' ,
MOVE N'workspace_data' TO 'E:\SQL12\Data\workspace_data.mdf',
MOVE N'workspace_log' TO 'E:\SQL12\Logs\workspace.ldf';
; SET @msg_workspace = 'Example 1 - Recover all user databases';
RAISERROR (@msg_workspace,0,0) WITH LOG;
RESTORE DATABASE [workspace] WITH RECOVERY
; SET @msg_workspace = 'Example 1 - Recover all user databases';
RAISERROR (@msg_workspace,0,0) WITH LOG;
DBCC CHECKDB('workspace') WITH NO_INFOMSGS, ALL_ERRORMSGS
; DECLARE @msg_workspace1 VARCHAR(1000)
; SET @msg_workspace1 = 'Example 1 - Recover all user databases';
RAISERROR (@msg_workspace1,0,0) WITH LOG;
RESTORE DATABASE [workspace1]
FROM DISK = N'E:\SQL12\Backup\sworkspace1_A.bak' ,
DISK = N'E:\SQL12\Backups\workspace1_B.bak'
WITH REPLACE, FILE = 1,CHECKSUM, NORECOVERY, STATS=10,
MOVE N'workspace1_data' TO 'E:\SQL12\Data\workspace1_data.mdf',
MOVE N'workspace1_log' TO 'E:\SQL12\Logs\workspace1.ldf'
; SET @msg_workspace1 = 'Example 1 - Recover all user databases';
RAISERROR (@msg_workspace1,0,0) WITH LOG;
RESTORE LOG [workspace1]
FROM DISK = N'E:\SQL12\Backups\workspace1_LogA_1.trn',
DISK = N'E:\SQL12\Backups\workspace1_LogB_1.trn' WITH NORECOVERY,  CHECKSUM, FILE = 1 ,STOPAT = '2014-07-13 15:17:53' ,
MOVE N'workspace1_data' TO 'E:\SQL12\Data\workspace1_data.mdf',
MOVE N'workspace1_log' TO 'E:\SQL12\Logs\workspace1.ldf';
; SET @msg_workspace1 = 'Example 1 - Recover all user databases';
RAISERROR (@msg_workspace1,0,0) WITH LOG;
RESTORE DATABASE [workspace1] WITH RECOVERY
; SET @msg_workspace1 = 'Example 1 - Recover all user databases';
RAISERROR (@msg_workspace1,0,0) WITH LOG;
DBCC CHECKDB('workspace1') WITH NO_INFOMSGS, ALL_ERRORMSGS

Example 2 – Point-in-time database restore using STANDBY mode

Table 3 shows the backup history on the instance on which I executed the sp_RestoreGene stored procedure, for this example.

Table 3: User database backup History

The call to sp_RestoreGene below constructs a RESTORE script to restore the workspace database to 16:20:40. The restore is to the same instance and so we rename the restored database to workspace_recovery and also override the data and log file locations. It assumes a tail of log backup exists so does not use WITH REPLACE.

USE [master]
DECLARE @RC int
EXECUTE @RC = [dbo].[sp_RestoreGene]
@Database = 'workspace'
,@TargetDatabase = 'workspace_recovery'
,@WithMoveDataFiles = 'E:\SQL12\temp\'
,@WithMoveLogFile = 'E:\SQL12\temp\'
,@StopAt = '13 Jul 2014 16:20:40:000'
,@StandbyMode = 1
,@Log_Reference = 'Example 2 - User database with overrides'
GO

Listing 5: Call to sp_RestoreGene for point-in-time restore in STANDBY mode

Listing 6 below shows the restore commands generated and returned by sp_RestoreGene, reformatted for easier reading.

; DECLARE @msg_workspace VARCHAR(1000)
; SET @msg_workspace = 'Example 2 - User database with overrides';
RAISERROR (@msg_workspace,0,0) WITH LOG;
RESTORE DATABASE [workspace_recovery]
FROM DISK = N'E:\SQL12\Backups\\workspace_A.bak' ,
DISK = N'E:\SQL12\Backups\\workspace_B.bak'
WITH FILE = 1,CHECKSUM, STANDBY =N'E:\SQL12\Backups\\workspace_ROLLBACK_UNDO.bak ', STATS=10
, MOVE N'workspace_data' TO 'E:\SQL12\temp\workspace_data.mdf'
, MOVE N'workspace_log' TO 'E:\SQL12\temp\workspace.ldf'
, MOVE N'workspace1    ' TO N'E:\SQL12\temp\workspace_f1.ndf'
, MOVE N'workspace2    ' TO N'E:\SQL12\temp\workspace_f2.ndf'
, MOVE N'workspace3    ' TO N'E:\SQL12\temp\workspace_f3.ndf'
, MOVE N'workspace4    ' TO N'E:\SQL12\temp\workspace_f4.ndf'
, MOVE N'workspace5    ' TO N'E:\SQL12\temp\workspace_f5.ndf'
, MOVE N'workspace6    ' TO N'E:\SQL12\temp\workspace_f6.ndf'
; SET @msg_workspace = 'Example 2 - User database with overrides';
RAISERROR (@msg_workspace,0,0) WITH LOG;
RESTORE DATABASE [workspace_recovery]
FROM DISK = N'E:\SQL12\Backups\\workspace_DiffA_1.bak' ,
DISK = N'E:\SQL12\Backups\\workspace_DiffB_1.bak'
WITH FILE = 1,CHECKSUM, STANDBY =N'E:\SQL12\Backups\\workspace_ROLLBACK_UNDO.bak ', STATS=10
, MOVE N'workspace_data' TO 'E:\SQL12\temp\workspace_data.mdf'
, MOVE N'workspace_log' TO 'E:\SQL12\temp\workspace.ldf'
; SET @msg_workspace = 'Example 2 - User database with overrides';
RAISERROR (@msg_workspace,0,0) WITH LOG;
TRY RESTORE LOG [workspace_recovery]
FROM DISK = N'E:\SQL12\Backups\\workspace_LogA_4.trn',
DISK = N'E:\SQL12\Backups\\workspace_LogB_4.trn'
WITH STANDBY =N'E:\SQL12\Backups\\workspace_ROLLBACK_UNDO.bak ', CHECKSUM, FILE = 1 ,STOPAT = '2014-07-13 16:20:40'
,MOVE N'workspace_data' TO 'E:\SQL12\temp\workspace_data.mdf'
,MOVE N'workspace_log' TO 'E:\SQL12\temp\workspace.ldf';
; SET @msg_workspace = 'Example 2 - User database with overrides';
RAISERROR (@msg_workspace,0,0) WITH LOG;
RESTORE LOG [workspace_recovery]
FROM DISK = N'E:\SQL12\Backups\\workspace_LogA_1.trn',
DISK = N'E:\SQL12\Backups\\workspace_LogB_1.trn'
WITH STANDBY =N'E:\SQL12\Backups\\workspace_ROLLBACK_UNDO.bak ',
CHECKSUM, FILE = 2 ,STOPAT = '2014-07-13 16:20:40' ,
MOVE N'workspace_data' TO 'E:\SQL12\temp\workspace_data.mdf',
MOVE N'workspace_log' TO 'E:\SQL12\temp\workspace.ldf';

Example 3 – Restore specific database to most recent point in time

The example call shown in Listing 7 was made against the same backup history shown in Table 3. The intent is simply to restore the workspace database to the most recent point in time (no @StopAt parameter is supplied), assuming the existence of a tail log backup.

DECLARE @RC int
EXECUTE @RC = [dbo].[sp_RestoreGene]
@Database = 'workspace'
,@WithRecovery = 1
,@Log_Reference = 'Example 3 - To current time'
GO

Listing 7: Call to sp_RestoreGene to restore a specific database to most recent point in time

Listing 8 shows the restore commands generated and returned by sp_RestoreGene, reformatted for easier reading.

; DECLARE @msg_workspace VARCHAR(1000)
; SET @msg_workspace = 'Example 3 - To current time';
RAISERROR (@msg_workspace,0,0) WITH LOG;
RESTORE DATABASE [workspace]
FROM DISK = N'E:\SQL12\Backups\workspace_A.bak' ,
DISK = N'E:\SQL12\Backups\workspace_B.bak'
WITH FILE = 2,CHECKSUM, NORECOVERY, STATS=10,
MOVE N'workspace_data' TO 'E:\SQL12\Data\workspace_data.mdf',
MOVE N'workspace_log' TO 'E:\SQL12\Logs\workspace.ldf'
; SET @msg_workspace = 'Example 3 - To current time';
RAISERROR (@msg_workspace,0,0) WITH LOG;
RESTORE DATABASE [workspace]
FROM DISK = N'E:\SQL12\Backups\workspace_DiffA_1.bak' ,
DISK = N'E:\SQL12\Backups\workspace_DiffB_1.bak'
WITH FILE = 2,CHECKSUM, NORECOVERY, STATS=10,
MOVE N'workspace_data' TO 'E:\SQL12\Data\workspace_data.mdf',
MOVE N'workspace_log' TO 'E:\SQL12\Logs\workspace.ldf'
; SET @msg_workspace = 'Example 3 - To current time';
RAISERROR (@msg_workspace,0,0) WITH LOG;
RESTORE LOG [workspace]
FROM DISK = N'E:\SQL12\Backups\workspace_LogA_2.trn',
DISK = N'E:\SQL12\Backups\workspace_LogB_2.trn'
WITH NORECOVERY, CHECKSUM, FILE = 2 ,STOPAT = '2014-07-13 16:56:10' ,
MOVE N'workspace_data' TO 'E:\SQL12\Data\workspace_data.mdf',
MOVE N'workspace_log' TO 'E:\SQL12\Logs\workspace.ldf';
; SET @msg_workspace = 'Example 3 - To current time';
RAISERROR (@msg_workspace,0,0) WITH LOG;
RESTORE DATABASE [workspace] WITH RECOVERY

Database Restore Automation using the ps_RestoreGene PowerShell Script

The ps_RestoreGene PowerShell script is the driver script for the sp_RestoreGene stored procedure. We can run the PowerShell script as a SQL Agent job to automate restores on a standby server, it can be called from a PoSh shell or by an SSIS package. It calls the procedure on the primary server to get a restore script which it executes on the standby server. It uses the existing backup history in the primary's msdb.dbo.backupset table to identify the backup files required (the backup files need to be in a network share that is accessible to the standby). The PoSh script can only process one database at a time, database name is a required parameter.

Simply by switching a parameter value, $LogShippingInitialize, we can also use the script to automate log shipping. In this mode, the script checks an xml restore log file that it maintains to find the last LSN restored, and then only processes new transaction log backups.

As with the procedure, no changes should be necessary to the script; just save it as a .PS1 file, and then call it, passing parameters as necessary.

ps_RestoreGene Parameters

The ps_RestoreGene script accepts the same parameters as the stored procedure, plus additional ones to support incremental restores in the log shipping configuration. Table 4 summarizes the parameters accepted by ps_RestoreGene, in addition to those available for the stored procedure, as described in Table 1.

Table 4: Additional parameters for ps_RestoreGene

Note that if a path parameter contains "$", then you'll need to use an escape character, `, is needed because $ is a reserved character in PowerShell. For example: "\\s1-sql\backups\S1-SQL`$I08\db_workspace\FULL\".

Using the LogShippingInitialize Parameter

When we execute the PoSh script using -LogShippingInitialize "1", we are operating in full recovery mode. However, simply by switching the value of this parameter to "0" (off), we can switch to log shipping mode, and subsequent full or differential backups are ignored, as illustrated in Figure 2.

Figure 2: Automating Log shipping

Example: Log Shipping using ps_RestoreGene

This example shows how to use the PoSh driver script to automate log shipping. As described earlier, the PoSh script calls the sp_RestoreGene stored procedure on the primary server to generate the script and then runs it on the target server.

This first call to the ps_RestoreGene script will be in normal restore mode (LogShippingInitialize "1") to restore the database in STANDBY mode to the Standby server, and subsequent calls will use the log shipping mode (LogShippingInitialize "0") to log ship log backups with an LSN higher than the previous log restored.

Listing 9 shows the call to perform the initial restore. It assumes the script was saved as E:\PS\ps_RestoreGene.ps1. Since we specify no -StopAt parameter, we are restoring the most current backups starting from the most recent Full database backup.

cls
 
E:\PS\ps_RestoreGene.ps1 `
-LogShippingInitialize  "1" `
-DBName "workspace" `
-PrimaryServer "PBV001" `
-StandbyServer "PBV002" `
-FromFileFullUNC "\\pbv001\Backups\" `
-RestoreLog "E:\PS\Log_PBV001_PBV002_workspace.xml" `
-WithReplace "1" `
-StandBy "1" `
-Log_Reference "PoSh Example 1" 

Listing 9: Executing ps_RestoreGene with LogShippingInitialize "1"

Listing 10 below shows the generated result.

-----------------------------------------
RestoreGene Batch Execution Starting
-----------------------------------------
Executing SQL Command -  + EXEC dbo.sp_RestoreGene  @LogShippingVariableDeclare = 0, @Database = 'workspace',@Log_Reference = 'PoSh Example 1', @TargetDatabase = 'workspace',@FromFileFullUNC = '\\pbv001\Backups\', @FromFileDiffUNC = '\\pbv001\Backups\', @FromFileLogUNC= '\\pbv001\Backups\', @StopAt = '2014-08-08T05:56:33', @StandbyMode = '1', @WithReplace = '1', @WithRecovery = '0', @WithCHECKDB = '0'
-----------------------------------------
Executing SQL Command -  + ;DECLARE @msg_workspace VARCHAR(1000) ; SET @msg_workspace = 'PoSh Example 1';  RAISERROR (@msg_workspace,0,0) WITH LOG;RESTORE DATABASE [workspace] FROM DISK = N'\\pbv001\Backups\workspace_A.bak' , DISK = N'\\pbv001\Backups\workspace_B.bak' WITH REPLACE, FILE = 2,CHECKSUM, STANDBY =N'\\pbv001\Backups\\workspace_ROLLBACK_UNDO.bak ', STATS=10, MOVE N'workspace_data' TO 'E:\SQL12\Data\\workspace_data.mdf', MOVE N'workspace_log' TO 'E:\SQL12\Logs\\workspace.ldf'
-----------------------------------------
Executing SQL Command -  + ;DECLARE @msg_workspace VARCHAR(1000) ; SET @msg_workspace = 'PoSh Example 1';  RAISERROR (@msg_workspace,0,0) WITH LOG;RESTORE DATABASE [workspace] FROM DISK = N'\\pbv001\Backups\workspace_DiffA_1.bak' , DISK = N'\\pbv001\Backups\workspace_DiffB_1.bak' WITH FILE = 2,CHECKSUM, STANDBY =N'\\pbv001\Backups\\workspace_ROLLBACK_UNDO.bak ', STATS=10, MOVE N'workspace_data' TO 'E:\SQL12\Data\\workspace_data.mdf', MOVE N'workspace_log' TO 'E:\SQL12\Logs\\workspace.ldf'
-----------------------------------------
Executing SQL Command -  + ;DECLARE @msg_workspace VARCHAR(1000) ; SET @msg_workspace = 'PoSh Example 1';  RAISERROR (@msg_workspace,0,0) WITH LOG;RESTORE LOG [workspace]FROM DISK = N'\\pbv001\Backups\workspace_LogA_2.trn', DISK = N'\\pbv001\Backups\workspace_LogB_2.trn' WITH STANDBY =N'\\pbv001\Backups\\workspace_ROLLBACK_UNDO.bak ',  CHECKSUM, FILE = 2 ,STOPAT = '2014-08-08 05:56:33' ,MOVE N'workspace_data' TO 'E:\SQL12\Data\\workspace_data.mdf',  MOVE N'workspace_log' TO 'E:\SQL12\Logs\\workspace.ldf';
-----------------------------------------
RestoreGene Batch Execution Complete

The call in Listing 11 below was made after a new log backup had been taken. It finds and restores any new log file backup only, to reach the most current possible point in time possible. The Last LSN is recorded in the XML restore log file and passed to the stored procedure sp_RestoreGene. It uses it to filter the RESTORE script generated to include only log files with a higher starting LSN. You could also use ascending -StopAt points in time to use this incremental log restore process to recover lost, even historic, data.

cls
E:\PS\ps_RestoreGene.ps1 `
-LogShippingInitialize  "0" `
-DBName "workspace" `
-PrimaryServer "PBV001" `
-StandbyServer "PBV002" `
-FromFileFullUNC "\\pbv001\Backups\" `
-RestoreLog "E:\PS\Log_PBV001_PBV002_workspace.xml" `
-StandBy "1" `
-Log_Reference "PoSh Example 2"

Listing 11: Executing ps_RestoreGene with LogShippingInitialize "0"

Listing 12 below shows the generated result.

#Output window – pasted for display
-----------------------------------------
RestoreGene Batch Execution Starting
-----------------------------------------
Executing SQL Command -  + EXEC dbo.sp_RestoreGene  @LogShippingVariableDeclare = 0, @LogShippingLastLSN = '47000000121600001', @LogShippingStartTime= '2014-08-08T05:56:33',@Database = 'workspace',@Log_Reference = 'PoSh Example 2', @TargetDatabase = 'workspace',@FromFileFullUNC = '\\pbv001\Backups\', @FromFileDiffUNC = '\\pbv001\Backups\', @FromFileLogUNC= '\\pbv001\Backups\', @StopAt = '2014-08-08T05:58:04', @StandbyMode = '1', @WithReplace = '0' , @WithRecovery = '0', @WithCHECKDB = '0'
-----------------------------------------
Executing SQL Command -  + ;DECLARE @msg_workspace VARCHAR(1000) ; SET @msg_workspace = 'PoSh Example 2';  RAISERROR (@msg_workspace,0,0) WITH LOG;RESTORE LOG [workspace]FROM DISK = N'\\pbv001\Backups\workspace_LogA_2.trn', DISK = N'\\pbv001\Backups\workspace_LogB_2.trn' WITH STANDBY =N'\\pbv001\Backups\\workspace_ROLLBACK_UNDO.bak ',  CHECKSUM, FILE = 3 ,STOPAT = '2014-08-08 05:58:04' ,MOVE N'workspace_data' TO 'E:\SQL12\Data\\workspace_data.mdf',  MOVE N'workspace_log' TO 'E:\SQL12\Logs\\workspace.ldf';
-----------------------------------------
RestoreGene Batch Execution Complete

Current Limitations

There are limitations with this framework, such as the following:

• The ps_LogShippingLight PoSh script doesn't copy backup files to the standby server like the default SQL Server Log Shipping feature. To achieve similar redundancy, you could mirror the backups.
• The @WithMoveDataFiles override in sp_LogShippingLight, for database files, only offers one folder. If a database has multiple secondary (.ndf) data files, in different folders, then the script will move them all into a single folder. If folder structures match on the primary and standby servers then no override is required and .ndf files will be restored to the same drives / folders.
• The DBCC CHECKDB command generated does not include any WITH DATA PURITY checks, which may be required if a database was upgraded from SQL Server 2000

Summary

I've tried to build a simple framework that will offer a solution to many common SQL Server restore automation challenges, and significantly speed up the production of restore scripts for manual disaster recovery.

I'm already working on a prototype for v4.0, which will include a management console with commands and options for configuring, validating and executing database restores (see http://paulbrewer.wordpress.com/2014/07/19/database-restore-automation-v4-0-prototype-management-console/). 

I'd love to hear your feedback, and suggestions for further improvements! 

Useful References

• http://michlstechblog.info/blog/powershell-some-basic-xml-handling-with-powershell-and-net/
• http://www.sqlservercentral.com/blogs/robert_davis/2013/01/04/day-3-of-31-days-of-disaster-recovery-determining-files-to-restore-database/
• http://www.sqlservercentral.com/articles/Backups/93224/
• http://sqlserverpedia.com/wiki/Restore_With_Standby
• http://sqlblog.com/blogs/adam_machanic/archive/2012/03/22/released-who-is-active-v11-11.aspx
• http://www.sqlservercentral.com/blogs/robert_davis/2013/01/12/day-11-of-31-days-of-disaster-converting-lsn-formats/
• http://technet.microsoft.com/en-us/sqlserver/ff977043.aspx