sp_DatabaseRestore: Open Source Database Restore Stored Procedure

Welcome to the sp_DatabaseRestore documentation page.

We were looking for a script that restores a database including the transaction log chain using the backup files in a folder. We also wanted it to be able to continue applying transaction log backups. In other words, we were looking for a custom Log Shipping solution. We could roll our own, but why reinvent the wheel if something already exists? We tried a couple of scripts, but GregWhiteDBA‘s DatabaseRestore stored procedure fit our needs and didn’t need a lot of modifications.

sp_DatabaseRestore is an MIT-licensed open-source stored procedure that restores backups that were created using Ola Hallengren‘s DatabaseBackup solution. Thanks GregWhiteDBA for making it open source! If you aren’t using Ola’s solution for your database backups, you could modify DatabaseRestore to support native backups or another backup solution. That exercise is left to the reader.

The following modifications were made to DatabaseRestore:

  • Removed code that assumed that the FULL backups are copy-only backups
  • Removed @BackupPath parameter and added @BackupPathFull and @BackupPathLog in case the files are stored in different base paths
  • Removed @LogToTable parameter as it wasn’t used in the code
  • Added @RunRecovery and @ContinueLogs in case the user needed to restore more LOG backups
  • Changed the data types of the input parameters to match system data types or to use the smallest data type
  • Added columns to the table variables that store the output of RESTORE FILELISTONLY and RESTORE HEADERONLY since SQL Server 2016 has more columns in the output
  • Added code to read the LSN information from the LOG backup and compare it to the LSN from the newest FULL backup so that it doesn’t fail when it tries to restore a LOG backup that is too early
  • Added code to read the LSN information from the restored database and compare it to the LSN from the LOG backups when @ContinueLogs = 1 so that it can figure out which LOG file to restore and not throw an error for the LOG backups that were already restored
  • Cleaned up the code to have consistency between upper and lower cases of variables

DatabaseRestore may fail if you are not using SQL Server 2016 due to the extra columns that were added to @Headers and @FileListParameters to support the new columns returned by RESTORE HEADERONLY and RESTORE FILELISTONLY commands.  It’s a simple enough fix but is not included in our code at this time. 

INPUT PARAMETERS

  • @Database NVARCHAR(128) – name of the source database
  • @RestoreDatabaseName NVARCHAR(128), default=NULL – name of the restored database, can leave off or set to NULL if the restored name will be the source database’s name
  • @BackupPathFull NVARCHAR(MAX) – full path with ending backslash where the FULL backups are stored
  • @BackupPathLog NVARCHAR(MAX) – full path with ending backslash where the LOG backups are stored
  • @MoveFiles BIT, default=0 – whether or not you want to use a different location for the database files than what was used on the source server, leave off or set to 0 if using the same path as the source
  • @MoveDataDrive NVARCHAR(260), default=NULL – new location for the data file(s), used when @MoveFiles=1
  • @MoveLogDrive NVARCHAR(260), default=NULL – new location for the log file, used when @MoveFiles=1
  • @TestRestore BIT, default=0 – whether or not you are testing restores, if set to 1 then it drops the database at the end
  • @RunCheckDB BIT, default=0 – whether or not you want it to run DBCC CHECKDB after the restore, it assumes you are using Ola’s DatabaseIntegrityCheck stored procedure
  • @ContinueLogs, default=0 – whether or not you are continuing to restore logs after the database has already been restored without recovering it
  • @RunRecovery BIT, default=0 – whether or not to recover the database (RESTORE DATABASE WITH RECOVERY so that it is now usable)
  • @StopAt, NVARCHAR(14) null – if you want to stop restoring logs as of a certain time, you can pass in a date/time pass in a date time like ‘20170508201501’. This doesn’t use the StopAt command for restores – it simply doesn’t restore transaction logs that have this date/time in it.

SAMPLE CALLS

Restore the newest FULL backup and all LOG backups and then recover the database so that it can be used:

Restore the newest FULL backup and all LOG backups but keep it in the NORECOVERY state so that more LOG backups can be applied:

Restore more LOG backups but keep it in the NORECOVERY state so that more LOG backups can be applied:

Restore more LOG backups, if any, and then recover the database so that it can be used:

To get your hands on DatabaseRestore, download our free First Responder Kit.

For Support

When you have questions about how the tools work, talk with the community in the #FirstResponderKit Slack channel. If you need a free invite, hit SQLslack.com. Be patient – it’s staffed with volunteers who have day jobs. When you find a bug or want something changed, read the contributing.md file.

Previous Post
4 Free Things to Do with a Slow SQL Server
Next Post
New White Paper: How to Build an Always On Availability Group in Google Compute Engine
Menu
{"cart_token":"","hash":"","cart_data":""}