Database Restore Automation using T-SQL and PowerShell (V3.32)

This article describes a simple, home-grown, two-script ‘Restore Gene’ framework, one T-SQL and one PowerShell (PoSh), for automating SQL database restores. You can use them to automate the generation of database restore scripts that will perform a variety of 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 although a drop database command is not included. In addition, the scripts can help to initialize database mirroring and availability groups or implement a basic log shipping solution

The 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. Running it without any parameters generates a restore script for all user databases on an instance to the current point in time. It optionally includes override options for database name (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, to generate the required script, then it runs the restore commands on a standby server. It will perform the tasks using the existing backup history in the primary 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.

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. The T-SQL script can be invoked from query analyser, copy the TSQL column in the results it returns then paste and run. The PoSh drive script can be invoked from a SQL Agent job step, an SSIS package or any PoSh shell.

They are a work in progress, but my goal is that together 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 command line interfaces offers automation possibilities not available via the GUI, 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.32)?
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, gradually new features were added, including:

sp_RestoreGene – Stored Procedure

  •  V3.32 – Parameterize WITH REPLACE, safety check that a tail of the log backup exists
  • 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 over ride of database data and log file locations
  • Allow override of backup file location
  • V3.32 –  Add error handling and logging
  • V3.32 – Allow for restored standby/target database name override
  • Include a final WITH RECOVERY and optionally DBCC CHECKDB
  • Include a WITH STANDBY option

ps_RestoreGene – PowerShell Script
• V3.32 – Use ‘Last Restore LSN’ for incremental restores of new log files, for log shipping ignore new full and diff backups
• ‘Kill Blocking User’ option in the PoSh driver script
• V3.32 – Additional error handling and improved (xml) logging

There have been a few versions of this stored procedure and PoSh driver script over the last 18 months, Version 3.32 is the most recent and most stable. The tests developed and used for each version have together formed an ever expanding regression test suite, there are no bugs that I am aware of in V3.32. The framework is being use operationally by a few people, the feedback and suggestions are very useful and appreciated. Please click here to see a few examples of the framework in action.

The sp_RestoreGene Stored Procedure – Click here for the source code

The stored procedure sp_RestoreGene generates and returns a T-SQL restore script by interrogating msdb to find the latest Full and Diff backups then the whole string of Logs that should include a tail log backup. Create the procedure on the primary server where the backups were taken. Parameters are available offering various restore options and overrides. Based on these parameters, the procedure constructs the T-SQL RESTORE script and returns to the caller, it isn’t executed. Calling the procedure without parameters returns a script that will return all user databases 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 restore only the workspace database and then run a DBCC CHECKDB on the restored copy.


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 shows the output from running Listing 1. The T-SQL commands generated and returned by the stored procedure have been reformed for presentation purposes. Note that it handles automatically the restore of striped as well as single file backups. No ‘StopAt’ parameter was supplied so the value has been 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_1.bak' ,
         DISK = N'E:\SQL12\Backups\\workspace_2.bak'
  WITH FILE = 9,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_Diff1.bak' ,
        DISK = N'E:\SQL12\Backups\\workspace_Diff2.bak'
  WITH REPLACE, FILE = 30,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;
BEGIN TRY
    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 = 9 ,STOPAT = '2014-06-11 00:13:32',
          MOVE N'workspace_data' TO 'E:\SQL12\Data\\workspace_data.mdf',
          MOVE N'workspace_log' TO 'E:\SQL12\Logs\\workspace.ldf';
END TRY
BEGIN CATCH
    SET @msg_workspace = ERROR_MESSAGE();
    RAISERROR (@msg_workspace,0,0)
END CATCH;

;
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
IF @@ERROR > 0
    PRINT N'CONSISTENCY PROBLEMS IN DATABASE : workspace'
ELSE
    PRINT N'CONSISTENCY GOOD IN DATABASE : workspace'

Listing 2: Example RESTORE script

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_LogShippingLight.ps1.

Parameter Example Description
@Database ‘workspace’ Restore a specific database. Defaults to all user databases
@TargetDatabase ‘workspace_copy1′ Allows override of restored database name, only possible if working with a specific database, defaults to actual database name
@WithMoveDataFiles ‘x:\datafilesNew\’ Overrides WITH MOVE for data file folder, defaults to the actual data file paths
@WithMoveLogFile ‘y:\logfilesNew\’ Overrides WITH MOVE for log file folder, defaults to the actual log file path  on the assumption the standby matches the primary.
@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 ’02 Jun 2014 17:51:21:623′ 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′ Iinclude 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 No, only possible in conjunction WithRecovery = Yes
@WithReplace ’0′ or ’1′ If WITH REPLACE is required, such as when no tail log backup exists. Defaults to No
@LogReference ‘Incident XYZ’ Recorded in SQL Error Log. Additional messages are logged if the restore operation fails
@LogShippingStartTime PoSh Script Parameter Used by to skip subsequent FULL & DIFF backups when log shipping
@LogShippingVariableDeclare PoSh Script Parameter Used to suppress DECLARE @MSG in result set
@LogShippingLastLSN PoSh Script Parameter Used to filter results sent, exclude log backups already restored

Table 1: sp_RestoreGene parameters

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.

RestoreOverView8

Figure 1 : Backup File Selection

It should be noted that if years of backup history are stored on the primary then the stored procedure will take a long time to finish. Delete old history using stored procedures dbo.sp_delete_database_backuphistory and dbo.sp_delete_backuphistory.

Click here for more examples

The ps_RestoreGene PowerShell Script - Click here for the source code

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. Alternatively, simply by switching a parameter value $LogShippingInitialize, we can 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.

The script accepts the same parameters as the stored procedure, plus additional ones to support incremental restores in this log shipping configuration. 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.

Assuming the script was saved as E:\PS\ps_LogShippingLight.ps1, then Listing 3 & 4 demonstrate two calls to the script. The first call executes the PoSh script using -LogShippingInitialize “1″, because no -StopAt parameter was supplied we are restoring the most current backups starting from the most recent Full. A subsequent call is made with the -LogShippingInitialize parameter switching to “0″ (off). This is log shipping the current most log backups with an LSN higher than the previous log restored. 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  "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" 

#Output window – pasted for display
==============================================================================
Information : Executing SQL Command - EXEC dbo.sp_RestoreGene  @LogShippingVariableDecla
re = 0, @Database = 'workspace',@Log_Reference = 'PoSh Example 1', @TargetDatabase = 'wo
rkspace',@FromFileFullUNC = '\\pbv001\Backups\', @FromFileDiffUNC = '\\pbv001\Backups\',
 @FromFileLogUNC= '\\pbv001\Backups\', @StopAt = '2014-07-13T13:42:04', @StandbyMode = '
1', @WithReplace = '1', @WithRecovery = '0', @WithCHECKDB = '0'
==============================================================================
Information : Executing SQL Command - ;DECLARE @msg_workspace VARCHAR(1000) ; SET @msg_w
orkspace = '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\w
orkspace_B.bak' WITH REPLACE, FILE = 1,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'
VERBOSE: PoSh Example 1
VERBOSE: 34 percent processed.
VERBOSE: 68 percent processed.
VERBOSE: 100 percent processed.
VERBOSE: Processed 320 pages for database 'workspace', file 'workspace_data' on file 1.
VERBOSE: Processed 8 pages for database 'workspace', file 'workspace1    ' on file 1.
VERBOSE: Processed 8 pages for database 'workspace', file 'workspace2    ' on file 1.
VERBOSE: Processed 8 pages for database 'workspace', file 'workspace3    ' on file 1.
VERBOSE: Processed 8 pages for database 'workspace', file 'workspace4    ' on file 1.
VERBOSE: Processed 8 pages for database 'workspace', file 'workspace5    ' on file 1.
VERBOSE: Processed 8 pages for database 'workspace', file 'workspace6    ' on file 1.
VERBOSE: Processed 7 pages for database 'workspace', file 'workspace_log' on file 1.
VERBOSE: RESTORE DATABASE successfully processed 375 pages in 0.080 seconds (36.572 MB/se
c).
==============================================================================
Information : Executing SQL Command - ;DECLARE @msg_workspace VARCHAR(1000) ; SET @msg_w
orkspace = 'PoSh Example 1';  RAISERROR (@msg_workspace,0,0) WITH LOG;BEGIN TRY RESTORE
LOG [workspace] FROM DISK = N'\\pbv001\Backups\workspace_LogA_1.trn', DISK = N'\\pbv001\
Backups\workspace_LogB_1.trn' WITH STANDBY =N'\\pbv001\Backups\\workspace_ROLLBACK_UNDO.
bak ',  CHECKSUM, FILE = 1 ,STOPAT = '2014-07-13 13:42:04' ,MOVE N'workspace_data' TO 'E
:\SQL12\Data\\workspace_data.mdf',  MOVE N'workspace_log' TO 'E:\SQL12\Logs\\workspace.l
df'; END TRY BEGIN CATCH SET @msg_workspace = ERROR_MESSAGE(); RAISERROR (@msg_workspace
,0,0) END CATCH;
VERBOSE: PoSh Example 1
VERBOSE: Processed 0 pages for database 'workspace', file 'workspace_data' on file 1.
VERBOSE: Processed 0 pages for database 'workspace', file 'workspace1    ' on file 1.
VERBOSE: Processed 0 pages for database 'workspace', file 'workspace2    ' on file 1.
VERBOSE: Processed 0 pages for database 'workspace', file 'workspace3    ' on file 1.
VERBOSE: Processed 0 pages for database 'workspace', file 'workspace4    ' on file 1.
VERBOSE: Processed 0 pages for database 'workspace', file 'workspace5    ' on file 1.
VERBOSE: Processed 0 pages for database 'workspace', file 'workspace6    ' on file 1.
VERBOSE: Processed 11 pages for database 'workspace', file 'workspace_log' on file 1.
VERBOSE: This backup set contains records that were logged before the designated point in
 time. The database is being left in the restoring state so that more roll forward can be
 performed.
VERBOSE: RESTORE LOG successfully processed 11 pages in 0.016 seconds (5.126 MB/sec).

Listing 3: Executing ps_RestoreGene – LogShippingInitialize Yes

 

The call below in Listing 4 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. 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.

 

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"

#Output window – pasted for display
==============================================================================
Information : Executing SQL Command - EXEC dbo.sp_RestoreGene  @LogShippingVariableDecla
re = 0, @LogShippingLastLSN = '46000000085600001', @LogShippingStartTime= '2014-07-13T13
:42:04',@Database = 'workspace',@Log_Reference = 'PoSh Example 2', @TargetDatabase = 'wo
rkspace',@FromFileFullUNC = '\\pbv001\Backups\', @FromFileDiffUNC = '\\pbv001\Backups\',
 @FromFileLogUNC= '\\pbv001\Backups\', @StopAt = '2014-07-13T13:43:29', @StandbyMode = '
1', @WithReplace = '0' , @WithRecovery = '0', @WithCHECKDB = '0'
==============================================================================
Information : Executing SQL Command - ;DECLARE @msg_workspace VARCHAR(1000) ; SET @msg_w
orkspace = 'PoSh Example 2';  RAISERROR (@msg_workspace,0,0) WITH LOG;BEGIN TRY 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 ',  FILE = 1 ,STOPAT = '2014-07-13 13:43:29' ,MOVE N'workspace_data' TO 'E:\SQL12\Da
ta\\workspace_data.mdf',  MOVE N'workspace_log' TO 'E:\SQL12\Logs\\workspace.ldf'; END T
RY BEGIN CATCH SET @msg_workspace = ERROR_MESSAGE(); RAISERROR (@msg_workspace,0,0) END
CATCH;
VERBOSE: PoSh Example 2
VERBOSE: Processed 0 pages for database 'workspace', file 'workspace_data' on file 1.
VERBOSE: Processed 0 pages for database 'workspace', file 'workspace1    ' on file 1.
VERBOSE: Processed 0 pages for database 'workspace', file 'workspace2    ' on file 1.
VERBOSE: Processed 0 pages for database 'workspace', file 'workspace3    ' on file 1.
VERBOSE: Processed 0 pages for database 'workspace', file 'workspace4    ' on file 1.
VERBOSE: Processed 0 pages for database 'workspace', file 'workspace5    ' on file 1.
VERBOSE: Processed 0 pages for database 'workspace', file 'workspace6    ' on file 1.
VERBOSE: Processed 2 pages for database 'workspace', file 'workspace_log' on file 1.
VERBOSE: This backup set contains records that were logged before the designated point in
 time. The database is being left in the restoring state so that more roll forward can be
 performed.
VERBOSE: RESTORE LOG successfully processed 2 pages in 0.021 seconds (0.558 MB/sec).

Listing 4: Executing ps_RestoreGene – LogShippingInitialize No

When $LogShippingInitialize =  No(0), then the procedure is log shipping and subsequent full or differential backups are ignored, as illustrated in Figure 2.

RestoreOverView7

Figure 2: Automating Log shipping

Table 2 summarizes the additional parameter accepted by ps_LogShippingLight, in addition to those available for the stored procedure described in Table 1.

Parameter Example Description
$PrimaryServer PBV001 Required – Primary server name
$StandbyServer PBV002 Required – Standby server name
$DBName AdventureWorks2012 Required – Database Name
$FromFileFullUNC \\PBV001\`$SQLBackups\ Required – UNC Path to backup folder
$FromFileDiffUNC Optional – Defaults to $FromFileFullUNC
$FromFileLogUNC Optional – Defaults to $FromFileFullUNC
$RestoreLog X:\SQLBackups\Log1.xml Required – XML log file path and name
$LogShippingInitialize 0 or 1 Incremental log backups only = No (0) or Restart from full (1) = Yes. Defaults to 1
$KillConnections 0 or 1 Kill blocking users (1) or end restore (0). Defaults to 1

Table 2: Additional parameters for ps_RestoreGene

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

 

Current Limitations
There are limitations with this framework, such as the following:
• The ps_RestoreGene 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_RestoreGene, 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 default 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 offer a simple framework that provides a solution to many common SQL Server restore automation challenges, and will significantly speed up the production of manual disaster recovery restore scripts. I’d love to hear your feedback, and suggestions for further improvements!

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

 

 



Categories: PowerShell, Restore

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: