Restore Gene V6.59 – Updated 26th March, 2017

This is a technical summary of Version 6 of Restore Gene, a simple two-script framework, one T-SQL and one PowerShell for optimising SQL database restores. They can used to automate the generation of database restore scripts to perform 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 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 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

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.


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.

rg1

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.

rg2

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.

rg3

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

rg4

Override With Moves

rg6

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.

rg7

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

rg8

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.

restoregene_v6_p7

All sp_RestoreGene Parameters & Defaults

All parameters are optional and have a default value.

Parameter Default Value Description
@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
@WithRecovery 0 Recover database
@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.

rg911

The screenshot below shows the commands executes by ps_RestoreGene as a result of the above call.

rg912

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.

rg913

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.

rg914

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.

ps_RestoreGene_NotInit2

 

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.

Parameter Default Value Description
-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
-DBName Mandatory Database name
-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 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:

 

 



Categories: Restore Gene

17 replies

  1. Thank you for this awesome script.
    I am curious one parameter if you can help me.
    Restore gene created a script for all backups like full,diff or log but i am only interested Full backup scripts by using parameter, is there any way i can use any parameter to get only full backup results.

    Like

  2. Great script, I love it. But I can’t find a parameter to set database in SINGLE_USER WITH ROLLBACK IMMEDIATE to restore over an online database.

    Like

  3. Paul, first off – thank you! I’ve been using this tool for years in its T-SQL form and it is wonderful. I owe you a meal, sir.
    Enhancement request – a bit parameter to exclude VDI backups (device_type = 7).

    current workaround for above ER:

    –AND device_type IN (7,102,2,9) –original line
    AND device_type IN (102,2,9) –modified line

    Edge case bug report: If two databases have names that differ only between a hyphen and an underscore ( my_database and my-database ) there is a collision in the RAISERROR variable names in the generated T-SQL.

    Enhancement request – a bit parameter to disable RAISERROR variables/usage in the generated T-SQL.

    Like

  4. Hi Lars,
    The DECLARE @msg variable and RAISERROR code have been completely removed in V6.57, they were causing other issues and a few people complained about them so good bye to it all.

    There are 4 new parameters for different device types, all defaulted to ON (include), if you set @IncludeDeviceType7 = 0 it should meet your requirements.

    Thanks for the good suggestions and pointing out the bug.
    Paul

    Like

  5. If I run restore with move the script generates wrong path (using azure blob storage). MSSQL12_testperfdb.
    Database default location = C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\

    exec [sp_RestoreGene] @BlobCredential = ‘xxxxxxxxx’, @database = ‘perfdb’ , @targetdatabase = ‘testperfdb’, @WithRecovery = ‘1’

    RESTORE DATABASE [testperfdb] FROM URL = ‘https://xxxxxxxxxxxxx.blob.core.windows.net/s-xxx-xxx-mssqlserver/Perfdb_ada8c5cf819641afae34751dc966e13f_20170126012356-06.bak’ WITH CREDENTIAL = ‘xxxxxx’, FILE = 1, NORECOVERY, STATS=10 , MOVE N’Perfdb’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL12_testperfdb.mdf’ , MOVE N’Perfdb_log’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL12_testperfdb.ldf’

    RESTORE LOG [testperfdb] FROM URL = ‘https://xxxxxxxxxxx.blob.core.windows.net/s-xxx-xxx-mssqlserver/Perfdb_ada8c5cf819641afae34751dc966e13f_20170126013856-06.log’ WITH NORECOVERY, CREDENTIAL = ‘xxxxxxx’, FILE = 1 ,STOPAT = ‘2017-01-31 03:15:36’

    Is the issue that I haven´t changed default location?

    Like

    • Hi Magnus,
      I wasn’t sure whether this was a bug or not never having tested the ‘Target Database’ parameter used in conjunction with Azure backup storage. I can’t reproduce the error though, this was a test just run:

      backup database workspace
      to url = ‘https://paulbrewertest.blob.core.windows.net/sqlbackuptest/workspace.bak’
      with credential = ‘sqlbackup’, stats=10, compression, init
      go

      exec dbo.sp_RestoreGene
      @Database = ‘workspace’
      ,@TargetDatabase = ‘workspace_test’
      ,@WithRecovery = 1
      ,@WithCHECKDB = 1
      ,@WithReplace = 1
      ,@BlobCredential = ‘sqlbackup’
      GO

      This was the script it produced which worked perfectly and restored the database:

      RESTORE DATABASE [workspace_test] FROM URL = ‘https://paulbrewertest.blob.core.windows.net/sqlbackuptest/workspace.bak’ WITH CREDENTIAL = ‘sqlbackup’, REPLACE, FILE = 1, NORECOVERY, STATS=10 , MOVE N’workspace_data’ TO ‘C:\Share\Data\workspace_data_workspace_test.mdf’ , MOVE N’workspace_log’ TO ‘C:\Share\Logs\\workspace_workspace_test.ldf’ ,MOVE N’workspace_log2′ TO N’C:\Share\Logs\\workspace_2_workspace_test.ldf’ ,MOVE N’workspace1 ‘ TO N’C:\Share\Data\LUN1\workspace_f1_workspace_test.ndf’ ,MOVE N’workspace2 ‘ TO N’C:\Share\Data\LUN2\workspace_f2_workspace_test.ndf’ ,MOVE N’workspace3 ‘ TO N’C:\Share\Data\LUN3\workspace_f3_workspace_test.ndf’ ,MOVE N’workspace4 ‘ TO N’C:\Share\Data\LUN4\workspace_f4_workspace_test.ndf’ ,MOVE N’workspace5 ‘ TO N’C:\Share\Data\LUN5\workspace_f5_workspace_test.ndf’ ,MOVE N’workspace6 ‘ TO N’C:\Share\Data\LUN6\workspace_f6_workspace_test.ndf’ ,MOVE N’WorkspaceFS’ TO N’C:\share\filestream\workspace_fs_workspace_test’
      RESTORE DATABASE [workspace_test] WITH RECOVERY
      DBCC CHECKDB(‘workspace_test’) WITH NO_INFOMSGS, ALL_ERRORMSGS

      Could you try upgrading the procedure to the latest version 6.58, it looks like you are using an old version.

      Thanks and regards
      Paul

      Like

    • Hey Magnus,

      I haven’t had a chance to test the latest version nor your particular case though I think that this is due to you not specifying  @WithMoveDataFiles and @WithMoveLogFile values and is unrelated to the fact that you’re using the blob storage feature. If I just issue the command

      RESTORE DATABASE AdventureWorks2012 FROM DISK = ‘Z:\SQLServerBackups\AdventureWorks2012.bak

      then I’d expect that this database would be restored using your default directories. Try specifying the additional move parameters.

      Like

  6. Is it possible to have the results saved to an UNC path directly after a backup or emailed?
    I´m using managed backup on SQL2014 to backup to Azure Blob storage.

    Like

    • Will look at it over the weekend, thanks

      Like

      • Hey there,

        Check out sp_ScheduleBackups which is part of the SQL Fairy Backup tools http://www.sqlfairy.com.au/downloads/ . The tools include a stored proc which will email a current DR restore script (generated by Restore Gene) to a specified address (will also configure SQL mail if necessary).

        The sp_ScheduleBackups stored proc will automagically generate and schedule Agent jobs to backup databases in a weekly full, daily diff and periodic transaction log (I recommend every 15 minutes) backup regime. A step is added to the generated backup jobs to email a current (post backup) DR restore script to a specified email address following each backup. This ensures that you’ll always have a current DR restore script at hand should the worst happen and your MSDB database isn’t available.

        SQL Fairy Backup Tools also includes usp_RestoreDB to restore databases from one server to another (assuming a central backup location) and also usp_AutoMirror to automagically mirror databases between servers, all using the power of Paul’s brilliant RestreGene. Thanks Paul.

        Liked by 1 person

Trackbacks

  1. Restore Gene History – Paul Brewer
  2. SQL Fairy

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: