Introduction

This article describes Version 6 of Restore Gene, a simple two-script framework, one T-SQL and one PowerShell for optimising 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, the scripts can help to initialize database mirroring, replication and availability groups, multiple standby server databases can be restored concurrently using simultaneous invocations.

The stored procedure generates the required restore scripts and is a useful tool by itself; in disaster recovery situations, it can construct a restore script to a given point in time in seconds. Running it without any parameters generates a restore script for all user databases on an instance to the most recent point in time. The companion PowerShell driver script is to automate the database restore process. It calls the stored procedure on the primary to generate the required restore script which it then executes on a standby server. The PoSh script can only process one database at a time, database name is a required parameter, but it can be called multiple times simultaneously for different standby databases.

No changes should be necessary to either the PoSh script or the Stored Procedure. Just create them 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 procedures results then paste and run. The PoSh drive script can be invoked from a SQL Agent job step or any PoSh shell.

Version 6 Feature Summary

The features below were added between Version 3.0 to 6.0, many thanks for the suggestions and contributions from:

• Support for Backup Forking and Fix for Parameter Sniffing (L.Sarro)
• Brent Ozar ULTD – Generated a lot of interest and suggestions.
• Support for Azure backup file locations and a generic string replacement feature. (http://www.sqlfairy.com.au)
• Support for concurrent restores of the same database to different standby’s (Kiran)
• Support for file stream files, with move overrides (Kiran)
• Support for a CSV list of databases to restore (Stephen T for suggestion, thanks to Ola Hallengren for permission to use a code snippet)
• Support for SQL Server Versions from 2005 to 2016 (B. McLaren)
• Support for restoring a database to the same server, with a new name (M.Norkett)
• Fix for multiple log files with move, J.Peablles
• Fix for mirrored backups, C.Richardson
• Mod to log file LSN selection, include if matches diff, R.Devries
• Bug fix to kill commands executed by ps_RestoreGene, Rod
• Improvements to output and option to drop databases after the restore, J.Lee
• Set Single User parameter option, Hakan Ekman
• Option to exclude Differential and Log backups in restore script, Mehedi Amin
• Backup device type exclusion parameters and remove all message variables/raise error messages, Lars Rasmussen

Many other people have contributed, offered encouragement and feedback over the years, much appreciated.

User Guide Technical Environment

This user guide was written using a virtual environment consisting of two default SQL Server 2016 instances on two Windows Server 2012 servers, the primary server is named PB31 and the standby server named PB33. Two full recovery mode user databases exist on the primary, SQL_HeartBeat and SQL_Hekaton_HeartBeat, these are backed up using the Ole Hallengren maintenance suite the schedule was:

• FULL – Every 12 hours between 00:00 and 23:59:59
• DIFF – Every 6 hours between 00:30 and 23:59
• LOG – Every 1 hour between 00:15 and 23:59

The backup jobs and this user guide were left running and were written over the course of a few days.

Restore Generator T-SQL Stored Procedure

The simplest way to start using Restore Gene is to call it without parameters from a Query Analyser command line. This will return a restore script for all user databases to the most recent point in time possible using the appropriate full, differential and log backups. If CHECKSUM was specified in the backup command it will be used in the restore command too, the database will be left in NO RECOVERY state, see the full parameter list for all the default settings.

Restore All User Databases to Latest Possible Recovery Point

The restore script created by this call to Restore Gene will not execute as the most recent backup was not a tail of the log. The WITH REPLACE parameter was not supplied and the default is no so the restore command would generate an error when executed and fail.

Restore All User Databases with RECOVERY, REPLACE, CHECKDB

If no tail of the log backup was taken, use the WITH REPLACE parameter, in the example below the databases are recovered after the restore then CHECKDB is run.

Restore a Specific User Database to a Point in Time

To recover a specific database to a specific point in time, supply restore gene with the parameters shown below. In this example Restore Gene was run on November 19th restoring to a time on November 18th.

Restore a database with a Backup Fork Point LSN

Restore Gene is backup LSN fork point aware, given a scenario where a database is backed up as below:

• 13:00 Full Backup
• 14:00 Log Backup
• 15:00 Log Backup

And at 15:30, a restore is run with Stop At 13:30 then subsequently another Log Backup is taken at 16:00.

At 16:30 the system crashes (again), Restore Gene will construct a recovery using the following backups:

• 13:00 Full Backup
• 14:00 Log Backup (to 13:30 LSN fork Point)
• 16:00 Log Backup.

Restore a Specific User Database WITH MOVE

There’s potentially an issue with Restore Gene constructing restore script WITH MOVE statements from sys.master_files, if a database file exists when restore gene runs but did not exist when the backup was taken then the restore script does not reflect the database files at the point the backup was taken. If the @SuppressWithMoves parameter setting is the default ON and there are won’t be any problems with the restore scripts, if the parameter is switched off then potentially the restore execution will fail. This will only be an issue if additional database files are added after the backup and parameter @SuppressWithMoves = 0.

There are five optional parameters relating to WITH MOVE statements, 2 for display purposes and 3 to override the target folders when restoring data, log and file stream files. The SQL_Hekaton_HeartBeat database used in the examples below contains a secondary data file and a file stream data file, the first example displays the existing file details and the second example moves the files.

Show With Moves

Override With Moves

Restore a Specific User Database FROM FILE Overrides

If the restore script created on the primary server is intended for execution on a standby server the FROM FILE parameters will replace the backup folder path with a new path which could be a UNC share. The example below creates a restore script that can be run on the standby, the \\pb31\MSSQL$\Backups\ path is a hidden share on the primary accessible from the standby server.

Restore All User Databases to a Standby FROM FILE Overrides

The Ole Hallengren backup solution creates a sub folder for each database and backup type, the Restore Gene scan and replace feature can be used to construct restore scripts for multiple database as shown below

Restore from Azure Blob Storage

If a backup is taken to Azure blob storage (URL) then restore gene will detect it and incorporate FROM URL in the restore script it generates. The same credentials used for the backup can be passed as a parameter to restore gene and this information is then included in the restore script constructed.

All sp_RestoreGene Parameters & Defaults

All parameters are optional and have a default value.

Restore Generator PowerShell Script

The PowerShell script calls sp_RestoreGene on the Primary Server and executes the restore script it constructs on the Standby Server. The -LogShippingInitialize parameter default setting is True.

• True – Identify and the required full, differential and log backups from the primary server and execute the restore script on the Standby Server.
• False – Identify new log backups with a higher LSN than last full/differential/log backups used during initialization.

The example call below restores database ‘SQL_HeartBeat’ to a standby server with initialization.

The screenshot below shows a second call made to ps_RestoreGene with initialization set to no, this will only select new log backups taken on the primary.

The diagram below illustrates the process flow, backups on the primary, ps_RestoreGene executes sp_RestoreGene on the primary and executes the restore scripts on the standby.

All ps_RestoreGene Parameters and Defaults

Restore Log, Primary Server, Standby Server and Database Name are mandatory, all other parameters are optional and have a default value shown in the table below.

Restore Gene Limitations

• Supports a maximum of 10 stripped backup files.
• Is not compatible with SQL Server 2000 and lower versions.
• A single backup fork point LSN has been tested.
• There are considerations using the WITH MOVE override parameters, when database files have changed since the backup was taken specifically.
• If the backed up databases contain multiple files in different folders, and the standby server folder structure differs, the @WithMove parameters offer only one target folder for each type (data, log, file stream).

License

SQL Server Restore Gene is licensed under the MIT license, a popular and widely used open source license.

Copyright (c) 2016 Paul Brewer

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the “Software”), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions: