With multiple endorsements on Stack Exchange, Restore Gene is a simple two-script framework, one T-SQL and one PowerShell for optimising SQL database restores. They can be used to automate a variety of restore tasks, 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 8 Feature Summary
The features below were added between Version 3.0 to 8.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 SQLFairy
- 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
- Significant performance improvements where an instance has many databases, Simone Bizzotto
- Case sensitive database collection compatibility, Mick Pollock SQLFairy
- Device Type filter fix, LostInEurope01
- Log backup selection fix, R.Devries
- Database names variable enlarged, Bob
- Escape Database names in SET options, Rod
- Increase striped backup file support from 10 to 15, Mehedi
- Error handling in PowerShell script, checking pending restore commands, Thomas Wolf
- copy_only bug fix introduced in V8.0 code refactoring, Andy
Many other people have contributed, offered encouragement and feedback over the years, much appreciated.
Download stored procedure here – https://paulbrewer.wordpress.com/sp_restoregene/
Download PowerShell script here – https://paulbrewer.wordpress.com/ps_restoregene/
Double click to highlight the entire script, copy and paste. There are a lot of comments under each link which server as FAQ documentation.
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.
|@Database||NULL||Restore a specific database or a list of comma seperated database names, defaults to NULL which restores all user databases.|
|@TargetDatabase||NULL||Override restored database name, only possible if working with a specific database, defaults to NULL, if supplied will rename files to avoid clash if restoring to same server without WITH MOVE.|
|@WithMoveDataFiles||NULL||Overrides WITH MOVE for data file folder, defaults to the actual data file paths|
|@WithMoveLogFile||NULL||Overrides WITH MOVE for log file folder, defaults to the actual log file path|
|@WithMoveFileStreamFile||NULL||Overrides WITH MOVE for file stream file folder, defaults to the actual file stream file path|
|@FromFileFullUNC||NULL||UNC path to full backup file, defaults to actual drive and folder|
|@FromFileDiffUNC||NULL||UNC path to differential backup file, defaults to actual drive and folder|
|@FromFileLogUNC||NULL||UNC path to log backup files, defaults to actual drive and folder|
|@StopAt||Current Date Time||Stop at a specific date time, defaults to current (datetime2)|
|@StandbyMode||0||Leave database in standby mode|
|@IncludeSystemDBs||0||Include master, model and msdb database restores|
|@WithCHECKDB||0||Include a CHECKDB after recovery|
|@WithReplace||0||Replace existing database, for use when no tail log backup exists|
|@LogShippingStartTime||NULL||Only used by ps_RestoreGene|
|@LogShippingLastLSN||NULL||Only used by ps_RestoreGene|
|@BlobCredential||NULL||SQL Credential for Azure Blog Storage Account|
|@RestoreScriptReplaceThis||NULL||Restore Script String Find|
|@RestoreScriptWithThis||NULL||Restore Script String Replace|
|@SuppressWithMove||1||Exclude WITH MOVE statements from the Restore Script|
|@PivotWithMove||0||If WITH MOVE, pivot secondary files to new rows in the result set|
|@RestoreScriptOnly||0||Return just the RESTORE DATABASE commands|
|@DropDatabaseAfterRestore||0||Drops the database after the restore, use with check db|
|@SetSingleUser||0||Adds ALTER DATABASE @database SET SINGLE_USER WITH ROLLBACK IMMEDIATE|
|@ExcludeDiffAndLogBackups||0||Ignores differential and log backups in restore script|
|@IncludeDeviceType7||1||Include device type 7 backups in restore script|
|@IncludeDeviceType102||1||Include device type 102 backups in restore script|
|@IncludeDeviceType2||1||Include device type 2 backups in restore script|
|@IncludeDeviceType9||1||Include device type 9 backups in restore script|
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 the commands executes by ps_RestoreGene as a result of the above call.
The screenshot below shows a second call made to ps_RestoreGene with initialization set to no, the call was made after new log backup was taken on the primary.
The screenshot below shows the commands executes by ps_RestoreGene as a result of the above call, just the new log backup is restored followed by With Recovery and CHECKDB.
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.
|-RestoreLog||Mandatory||Unique XML log file name to record progress, EG, ‘c:\restoregene\AdventureWorks_1.xml’. This is important if running multiple executions of the script simultaneously on the same server, each log name should be unique|
|-PrimaryServer||Mandatory||Name of the primary database instance|
|-StandbyServer||Mandatory||Name of the standby database instance|
|-TargetDBName||NULL||Rename the database during restore|
|-WithReplace||0||With replace used when no tail log backup exists|
|-WithMoveDataFiles||NULL||Override data file folder|
|-WithMoveLogFile||NULL||Override log file folder|
|-WithMoveFileStreamFile||NULL||Override file stream file folder|
|-FromFileFullUNC||NULL||Shared folder containing full backups, required different primary and standby servers are specified. If path name contains a $ then a PowerShell break character is required.|
|-FromFileDiffUNC||NULL||Shared folder containing differential backups, required different primary and standby servers are specified. If path name contains a $ then a PowerShell break character is required.|
|-FromFileLogUNC||NULL||Shared folder containing log backups, required different primary and standby servers are specified. If path name contains a $ then a PowerShell break character is required.|
|-StopAt||Current Date Time||Specify point in time recovery|
|-StandBy||0||Leave database in standby mode after restore|
|-WithRecovery||0||Recover database after restore|
|-WithCHECKDB||0||Include a final CHECKDB fter restore|
|-LogShippingInitialize||0||Set to 1 for incremental restores of new log and differential backups, set to 0 to start from the appropriate full backup|
|-KillConnections||1||Kill blocking connections to the standby database before restore|
|-ConsoleFeedback||1||Set to 0 if scheduling ps_RestoreGene as a SQL agent job|
|-BlobCredential||NULL||SQL Credentials created for an Azure blog storage account, restore from URL|
|-RestoreScriptReplaceThis||NULL||Restore script scan|
|-RestoreScriptWithThis||NULL||Restore script replace|
|-DebugMode||0||Write restore commands to Restore Log rather than executing the commands on the standby server|
|-DropDatabaseAfterRestore||0||Drops the database after the restore, use with check db|
|-ExcludeDiffAndLogBackups||0||Ignores differential and log backups in restore script|
|-IncludeDeviceType7||1||Include device type 7 backups in restore script|
|-IncludeDeviceType102||1||Include device type 102 backups in restore script|
|-IncludeDeviceType2||1||Include device type 2 backups in restore script|
|-IncludeDeviceType9||1||Include device type 9 backups in restore script|
Restore Gene Limitations
- Supports a maximum of 15 stripped backup files.
- Is not compatible with SQL Server 2000 and lower versions.
- A single backup fork point LSN is supported.
- 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).
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:
Categories: Restore Gene