Restore Gene V6.70 – Updated 24th June, 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
  • Case sensitive database collection compatibility, Mick Pollock SQLFairy

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

Download stored procedure here –

Download PowerShell script here –

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.


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.

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
@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.

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).


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.


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

21 replies

  1. Hi Paul,

    I love restore gene and we are using it in the company I work for a few weeks now (as a new best practice). I have 2 questions though

    1. Would it be possible for sp_restoregene to work on something like a “file/folder” mode? this is, prepare the list of backups out of files instead of basing the whole work from msdb.dbo.backupset? I think that would be a terrific addition. I’m imagining a situation where you may want to use restore gene for your disaster recovery plan (if a server goes down) and since you have lost your server you have no access to the msdb database either so restore gene is automatically not an option anymore. I guess you could argue that you should also backup your system DBs as a best practice (and msdb included) but again, the recovery process would involve using a standard T-SQL RESTORE DATABASE for msdb and only then use restore gene for the rest, which makes it a bit less straightforward. Having said that, even with the msdb.dbo.backupset pre-requisite. sp_restoregene rocks!

    2. I have noticed (I don’t know if you have seen it too) that Brent Ozar has released their own “DatabaseRestore” stored proc as part of the “SQL Server Download Pack from Brent Ozar Unlimited”. Have you used it? are you familiar with pros/cons of that sp vs. sp_restoregene?

    Thanks again for all the work and for providing this fantastic stored procedure under the MIT license and making it available to the SQL Server community. I’m pretty sure I speak for many when I said we really appreciate that!



    • Hi Martin,
      Thanks very much for the feedback, it’s great to hear the procedure is proving useful, it took ages to develop.

      What I do is run sp_RestoreGene as a SQL Agent Job step after the backup job step to create a restore script for the database(s) just backed up. Set the Restore Gene parameters to restore to the most recent point in time, set the SQL Agent job step advanced properties to output to a file in the backup folder, with overwrite. Then you always have a restore script ready if the server crashes.

      The Brent Ozar script reads backup files from disk and creates a restore script for you, that’s what you are suggesting isn’t it? At the start of Restore Gene I decided to use the backup history in msdb for a couple of reasons

      1. Didn’t want to define a temporary table for restore filelistonly as this is liable to change from version to version. The Brent Ozar script may not work with all versions of SQL Server.

      2. Silly reason but I wanted the entire procedure encapsulated in a single query, there were performance problems though (pointed out by other users) so that goal went out the window and it was optimised in various smaller queries.

      The Brent Ozar script restore looks quite new, Restore Gene is quite old. Over the years I’ve tried to add every feature suggested (often people actually supplied the code) and fix every bug. There are so many ways things can go wrong with restore scripts such as:

      . Backup fork points
      . Multiple log files
      . Performance when 100’s are databases need restoring
      . different backup file types
      . Different versions of SQL Server (2005 to most recent)
      . Use CHECKSUM where possible and other best practices
      . Optimise scripts using most recent differential where possible then logs

      The list is endless and all have been fixed in Restore Gene, Brent and his team may find they have a mountain of challenges in front of them 🙂


      Liked by 1 person

      • Thanks Paul, your idea about integrating the backup/restore activity via SQL Agent Jobs sounds interesting. I’m going to explore the possibility of using that one too.

        And I totally agree, an automation process such as this for such a sensitive task as Restoring a database MUST be bullet-proof. One of the reasons I like your solution, as you say, is it has been battle tested and many people have used it in the past.

        I’m all good with early adopters, but when it comes to disaster recovery and production environments I suddenly become a very conservative guy… hehehe.

        Again, thanks for all you do with RG.

        BTW. Is there a way to receive a notification if a new version of this SP is released? I would love to be notified (as opposed to checking this page from time to time… which I can entirely forget!).

        Liked by 2 people

        • Hi Martin,
          If you subscribe to my blog you should get an update in future when new posts are created. Whenever a new version of Restore Gene is released I will post a new blog with details.

          Updates are fairly infrequent now, they are typically made when bugs are spotted and, touch wood, its been quiet for a while now.

          Thanks and best wishes


  2. 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.


    • Will look at it over the weekend, thanks


      • Hey there,

        Check out sp_ScheduleBackups which is part of the SQL Fairy Backup tools . 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

  3. 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 = ‘’ 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 = ‘’ WITH NORECOVERY, CREDENTIAL = ‘xxxxxxx’, FILE = 1 ,STOPAT = ‘2017-01-31 03:15:36’

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


    • 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 = ‘’
      with credential = ‘sqlbackup’, stats=10, compression, init

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

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

      RESTORE DATABASE [workspace_test] FROM URL = ‘’ 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’

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

      Thanks and regards


    • 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.


  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.


  5. 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.


  6. 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.


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



  1. Restore Gene History – Paul Brewer
  2. SQL Fairy
%d bloggers like this: