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)

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.

Brent says: and this week during Google Cloud Next, you’ll learn about the Google Compute Engine project where Tara used this script. Stay tuned…

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

74 Comments. Leave new

  • wow that’s great, I developed this originally to test restores and run dbcc check db’s off SAN and now its in the first responder kit. I am humbled 🙂

    I shall be checking out the changes.

    Reply
  • This SP needs a lot of love to become production quality. I suppose this is some sort of experiment on the OSS community — i.e. how quickly can this get some shape.

    Reply
  • Paul Holmes
    March 7, 2017 2:59 pm

    Awesome project!

    My two cents… Given that the standard restore command defaults to RECOVERY, I’d argue that the default for @RunRecovery should align and be 1

    Then again #ImnotarealDBA 😉

    Reply
    • I don’t like defaulting to WITH RECOVERY as it doesn’t allow you continue applying transaction logs. So if you do need to apply more transaction logs, you are stuck with restoring the full backup again. The reason why we were looking for a stored procedure like this is so that we could use custom log shipping, where you are adding more transaction logs. So in this specific instance, WITH NORECOVERY makes more sense than WITH RECOVERY. It might make more sense once the white paper where I used DatabaseRestore gets released later this week. 😉

      Reply
      • Paul Holmes
        March 7, 2017 7:09 pm

        Ah ok Tara, I think I’m with you now – am I right in thinking then that it’s a simple one-step to take a DB restored with NORECOVERY, into RECOVERY? So defaulting the way you have done makes total sense now.

        #WhyImNotARealDBA

        Reply
        • To move a database that is in recovery into a usable state, it’s just a simple RESTORE DATABASE DbNameGoesHere WITH RECOVERY command. You don’t even have to specify a file. DatabaseRestore can do this for you if you use the parameter, but it’s super simple to do yourself via the RESTORE DATABASE command.

          Reply
          • Paul Holmes
            March 7, 2017 7:20 pm

            Gotcha – makes complete sense now. Have the default allow both outcomes – more logs or recovery – rather than just one.

            I’m going to let our production DBAs know about this proc. Looks like a good “get me out of sh!t *fast*” option. 🙂

  • I think It is good to have an option to either generate restore scripts or execute.

    Reply
  • Wow…I’ll definitely check this one out. I have written a restore procedure on my own about a year ago….wanted to open source it a long time ago but didn’t manage to get that done yet sadly. Back then I was quite puzzled that there seemed to be nothing already developed which satisfied my needs. Then Paul Brewer came along with Restore Gene and now you offer a solution as well…great! This looks a lot like most of the things I have done myself. Perhaps I will get rid of my one procedure and hopefully find the time to make enhancements to either your procedure or Paul Brewer’s one. However I haven’t had a close look on either yet….there’s just to much to be done 😉

    Reply
  • I’ll have to figure out why I get these errors, above and below the restore.

    Msg 213, Level 16, State 7, Line 1
    Column name or number of supplied values does not match table definition.
    Msg 3013, Level 16, State 1, Line 1
    RESTORE FILELIST is terminating abnormally.

    Msg 213, Level 16, State 7, Line 1
    Column name or number of supplied values does not match table definition.
    Msg 3013, Level 16, State 1, Line 1
    RESTORE HEADERONLY is terminating abnormally.

    Reply
    • Hi Allen, it’s due to the code being written for SQL Server 2016 which contains new columns in the output of RESTORE FILELISTONLY and RESTORE HEADERONLY. Run those two RESTORE commands on your server and see which columns you need to remove from the two respective temp tables. Once the extra columns are removed, it should work without errors.

      Reply
  • Bryan Maynard
    March 8, 2017 10:38 am

    Are there plans to have this support read-only standby? That is my primary use of log shipping at the moment for our reporting servers. I haven’t had a chance to use this yet but it sounds great!

    Reply
  • I’ve used quite a few of these and tried to tailor them to the Ola routines. I really like this one. You may want to look into the LSN variables and all of the casts to bigint. I was getting errors sine my LSNs were bigger than a bigint so I changed all of the casts to numeric(25,0), which I believe is the definition of the LSN (?) and it worked great.

    Thanks for the work and to Greg for the original routine.

    Reply
    • Jeff Rosenberg
      March 10, 2017 6:02 am

      Yeah, I ran into same problem, not sure why the casts to bigint were in there. I’ve submitted a pull request to change that to numeric(25, 0), so hopefully that will get accepted into the proc.

      Reply
  • Hi, Thanks for great script.
    I modified it to add an option to restore as STAND BY state and change LSN data type from BIG INT to Numeric (22,0) because of some LSN is too big, bigger than BIGINT as my case.

    Reply
  • So, no love for differential backup / restores? Or am I missing something?

    Reply
    • It’s not that I don’t like differentials, it’s just that I didn’t need it for the white paper I was writing that needed a restore solution. I was using the KISS principle. The solution is open source so feel free to add a contribution to add support for differentials.

      Reply
  • Not wanting to bash but feel the frustration of some of the others. The email from Brent was so enticing, then we come to find that we leave the page without a solution to the problem everyone is looking on the internet who uses Ola’s scripts, i.e. I backup using Ola script, my server just died. How do I restore to the latest point in time! Or even better, pick a time. There are so many pages and solutions out there on the internet but no on has nailed it. I even suggest it’s the biggest argument against using Ola’s scripts as he only provides one half of the story. He doesn’t provide the most important part… the restore!
    Again, I realize this is not what you set out to achieve and this is not a complaint (honestly) but I thought you would like to know the huge amount of frustration out there and if you wanted to set yourself another SQL goal / project you would become a hero!

    Reply
  • Hi, Thanks for sharing this great script.
    I am a little but confused about @Version parameter. Is it the version of SQL Server ?

    Reply
    • Erik Darling
      May 23, 2017 5:36 am

      It’s the version of the script, so people can easily tell if they’re on the newest version.

      Reply
  • Hi everyone, can you please advice how secure is to use xp_cmdshell option in this procedure ? To be honest, I am not fond of enabling it on production instances.

    Reply
    • I’m not fond of enabling it on production instances either because there are security implications to it. But in my mind, this stored procedure would be running on a non-production server or one that isn’t yet production.
      But if you don’t want to use xp_cmdshell, then you can’t use this stored procedure. There are other ways of doing this task, such as Powershell.

      Reply
  • Dumb question, but does this ignore Diff backups, then? It can only restore from Full and then Logs?

    Reply
    • Erik Darling
      July 10, 2017 8:01 am

      Current versions of the script support restoring Diffs. The original version was just Fulls and Logs.

      Reply
  • Richard Garrett
    July 10, 2017 7:54 am

    I would like to see a feature added on the restore scripts that will support restoring a database from production to a test or dev server. It would need to maintain existing permissions on the destination server for the database by scripting them out beforehand then applying them to the database afterwards and then fixing any broken logins.

    I developed a script years ago that does this by generating a basic restore script for the database along with permissions. I then have to copy the resulting script file and run it in a new window. The server name is part of the input and it checks to make sure the database exists on the server and will generate a server name check as part of the output so you don’t accidentally run the file on the wrong server and mess up permissions. My script does not do T-logs, PIT recovery, or many of the other options this one does though.

    Reply
    • Erik Darling
      July 10, 2017 8:01 am

      If you’d like to suggest features, the best place to do it is our GitHub repo. You can even contribute to the project, since it’s all open source.

      Thanks!

      Reply
  • Is it possible to point this stored procedure to Azure Blob in order to restore files?

    Reply
    • If you can access Azure Blob as files, then yes. Otherwise, you have to download them as files first. This stored proc is just a fancy way of running RESTORE DATABASE/LOG. Whatever limitations RESTORE has, this has it too.

      Reply
  • Quick question. I’m testing on a system (SQL 2008 R2) and I’m using debug level 1 which I’m reading as only printing the commands but it is doing the restore also. Am I reading it wrong, or is this the intended functionality?

    Reply
    • Jaime Thomson
      November 1, 2017 1:05 pm

      nvm, I see the following in the code.
      IF @Debug IN (0, 1)
      EXECUTE sys.sp_executesql @sql;
      This is the intended functionality

      Reply
  • zeropointforce
    November 15, 2017 2:36 pm

    Hello,

    I just downloaded the First Responder zip file, unfortunately I do not see where the sp_DatabaseRestore sql script is located inside the zip file nor is it installed as an sp in my DBA maintenance database. All the other procs were installed without issue. Also, a warning is generated regarding this call: master.dbo.sp_DatabaseRestore. I hope it is not an occurrence of me overlooking it. because I need new glasses.

    Reply
  • Msg 2812, Level 16, State 62, Line 1
    Could not find stored procedure ‘dbo.DatabaseRestore’.

    Iam getting the above error while executing the “EXEC dbo.DatabaseRestore ” please help me

    Reply
  • this is waaste of trying not working for me, if you have any clear document how to use or video please provide me the link

    Reply
    • yes i do agree

      Reply
      • It’s funny that ton and jin have the same IP address. Coincidence? I think not. 😉

        “not working for me” doesn’t allow us to help you. Post an error or describe the issue. If you find any issues with the code, feel free to submit an issue and then submit a fix for it in Github.

        Reply
  • Ronaldg leeschools.net
    December 22, 2017 9:54 am

    Trying this out on the CMS I intend to test restores on. SQL 2016 with the Ola Scripts for 2016.
    I resolved all the xp_cmshel errors etc. But this script gives me errors around CommandExecute.
    I thought I read you modified Ola stuff but can’t find that info again. So guess I imagined it. 🙂
    Errors:
    Getting default data drive for @MoveDataDrive
    Getting default log drive for @@MoveLogDrive
    @ContinueLogs set to 0
    Msg 50000, Level 16, State 1, Procedure CommandExecute, Line 72 [Batch Start Line 2]
    The value for the parameter @Command is not supported.
    ===============
    USE [master]
    GO

    DECLARE @return_value int,
    @VersionDate datetime

    EXEC @return_value = [dbo].[sp_DatabaseRestore]
    @BackupPathFull = N’\\isbackups\m$\PS855\SQL1\9143-855PRCL$9143V855PRSQL1\HRPRD\FULL\’,
    @BackupPathDiff = N’\\isbackups\m$\PS855\SQL1\9143-855PRCL$9143V855PRSQL1\HRPRD\DIFF\’,
    @BackupPathLog = N’\\isbackups\m$\PS855\SQL1\9143-855PRCL$9143V855PRSQL1\HRPRD\LOG\’,
    @RestoreDiff = 1,
    @RunRecovery = 1,
    @VersionDate = @VersionDate OUTPUT

    SELECT @VersionDate as N’@VersionDate’

    SELECT ‘Return Value’ = @return_value

    GO

    Reply
  • I have Ola’s script backing up about 20+ databases. I would like to restore all of them once a month in a separate MSSQL instance.

    How can I restore all databases found in a folder?

    Reply
    • Ronald Garlit
      October 4, 2018 6:14 am

      I have SQL Agent jobs setup on my one of my CMS boxes to specifically test Last nights production backups and the lastest DIFF and TRN log files during the day. In my case I set @TestRestore = 0 and recover and run queries in other steps. With the file step deleting the database. All results go to a log folder with an email sending me the link to the report. Oh yes. One of my steps is to run a database integrity check after the restore. 🙂

      sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q “EXEC [dbo].[sp_DatabaseRestore] @Database = ‘HRPRD’, @BackupPathFull = N’\\lcsd.local\backup\ApplicationServices\855PRSQL1\9143-855PRCL$9143V855PRSQL1\HRPRD\FULL\’, @BackupPathDiff = N’\\lcsd.local\backup\ApplicationServices\855PRSQL1\9143-855PRCL$9143V855PRSQL1\HRPRD\DIFF\’, @BackupPathLog = N’\\lcsd.local\backup\ApplicationServices\855PRSQL1\9143-855PRCL$9143V855PRSQL1\HRPRD\LOG\’, @RestoreDiff = 1, @ContinueLogs = 0, @RunRecovery = 0, @Debug = 0, @TestRestore = 0” -b

      Reply
  • Thank you for making this available. Works a treat apart from a small snag. When you restore with @ContinueLogs = 1 but only specify @BackupPathLog then nothing gets restored.

    This is the call I am using:

    exec MaintenanceSolution.dbo.sp_DatabaseRestore
    @ContinueLogs = 1
    , @Database = ‘db1’
    , @RestoreDatabaseName = ‘db1-copy’
    , @BackupPathLog = ‘f:\Backup\’
    , @Debug = 1
    ;

    The sp_DatabaseRestore procedure has initialisation code for @HeadersSQL and @DatabaseLastLSN wrapped inside IF @BackupPathFill IS NOT NULL … which looks like a bug.

    It can be fixed by moving @HeadersSQL init code below #Headers table definition and moving @DatabaseLastLSN init code under the IF block for @BackupPathFill IS NOT NULL.

    Reply
  • Thanks for this great solution.
    Unfortunately I get: “No backups for “database xxx” found in “\\xxx\xxx$\…” as soon as I add the “@StopAt = ‘2019-03-27T04:05:20’ Parameter. When I comment this Parameter, everything works fine, except that only the 1st logfile after the full backup gets restored – and not all log Backups in the same backup Directory.
    Also tried with the date Format in the scripts example: @StopAt = ”20192703040520”, but I get same error. What am I doing wrong ? Name of Backup files (trn) is like this: “tools_backup_2019_03_27_110112_9746376.trn”

    Help appreciated :-).

    Reply
  • chiemezie Ukaumunna
    April 1, 2019 1:15 pm

    I am trying to restore multiple Databases, Seem not to work, getting this error: must pass parameter number 2 and subsequent parameters as ‘@name = value’

    EXEC dbo.sp_DatabaseRestore
    @Database= ‘DB1’, ‘DB2’, ‘DB3’ ,
    @BackupPathFull = ‘\\xxxxxxxxx\’,
    @ContinueLogs = 0,
    @RunRecovery = 1;

    Reply
  • Franklin Onuoha
    July 3, 2019 11:52 am

    Are there any parameter that prints the restore script out without running it.

    so i can review what full, diff and log files are being restored before restoring

    Reply
  • The database restore job failed with the following error while trying to restore an existing database. (Microsoft SQL Server 2017 (RTM-CU11) (KB4462262) )

    Exclusive access could not be obtained because the database is in use. [SQLSTATE 42000] (Error 3101) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013) Outcome: Failed [SQLSTATE 01000] (Message 50000) Duration: 00:00:20 [SQLSTATE 01000] (Message 50000) Date and time: 2019-07-16 11:17:50 [SQLSTATE 01000] (Message 50000) [SQLSTATE 01000] (Message 50000) Date and time: 2019-07-16 11:17:50 [SQLSTATE 01000] (Message 50000) Command: RESTORE DATABASE [XXX] WITH RECOVERY [SQLSTATE 01000] (Message 50000) The database is already fully recovered. [SQLSTATE 42000] (Error 3153) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013) Outcome: Failed [SQLSTATE 01000] (Message 50000) Duration: 00:00:00 [SQLSTATE 01000] (Message 50000) Date and time: 2019-07-16 11:17:50 [SQLSTATE 01000] (Message 50000) [SQLSTATE 01000] (Message 50000). The step failed.

    Reply
  • Thank so much for this script, am currently using it on my dev/Staging environment. am wondering if i wanna use another different database name for the restore, pls how does that work instead of overwriting the current database.

    Reply
  • Anyway the Tool can be used to restore to a standby database? So the database can be read and log ship files can continue to be added to the database?

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}