SQL Server Blog

Make SQL Server Database Restores Easy With sp_DatabaseRestore

If you’ve read the Straight Path blog for any period of time, you’ve probably noticed more than a few posts about database backups.

You may have also noticed we’re big fans of the Ola Hallengren Maintenance Solution for backups, which creates stored procedures and jobs to handle your regular backups. These scripts and jobs are highly recommended not only because they are incredibly flexible but they are also darn near universal (at least as far as native backups go) because most SQL Server DBAs have used them at one time or another.

If you’re using the Ola Hallengren backup, that’s great! Now, let me ask you an important question:

What will you do NOT IFBUT WHEN you have to restore from those backups?

I’ve found the answer to this question isn’t nearly as universal. Maybe you will use some scripts found off an internet post from many years ago. Or perhaps a script that generates a zillion other scripts. Or maybe a lovingly crafted custom solution. Or you might even just go through Management Studio and use the GUI, right-click, “Tasks”, “Restore”.

There’s an easier way, and the unbelievable news is you may have it already installed on your SQL Server instances.

Your New Favorite Tool

If you’ve used the Ola Hallengren solution then chances are you’ve used some other free software like sp_Blitz, right? Well, inside the First Responder Kit that includes sp_Blitz is a stored procedure named “sp_DatabaseRestore” which makes restoring from Ola Hallengren backups very, very easy.

To be clear, we didn’t make this stored procedure; we just use it a lot.

All you need to do to execute this stored procedure is pass the parameters for the file locations of your Full, Differential, and/or Log backups. Easy as that you can restore the most recent copy of any database with one command. Here’s an example: suppose I have a database named “TheDatabase” on a server named “TheServer” that is being backed up to X:\BACKUP. It’s in Full recovery, and I want to restore the most recent backups, including the differential and log files on the same server. Just run this:

EXEC dbo.sp_DatabaseRestore 
	@Database = 'TheDatabase', 
	@BackupPathFull = 'X:\BACKUP\TheServer\TheDatabase\FULL\', 
	@BackupPathDiff = 'X:\BACKUP\TheServer\TheDatabase\DIFF\', 
	@BackupPathLog = 'X:\BACKUP\TheServer\TheDatabase\LOG\', 
	@RestoreDiff = 1,
	@ContinueLogs = 1, 
	@RunRecovery = 1;

One script, and you’re done! Let’s take a quick look at these parameters:

  • @Database – this is the name of the source database you are restoring. sp_DatabaseRestore will look for databases with this name in the folders specified that follow the Ola Hallengren default naming convention.
  • @BackupPathFull – where your full backups are. sp_DatabaseRestore will look for the most recent file and restore it with NO RECOVERY to apply additional backups.
  • @BackupPathDiff – where your differential backups are. sp_DatabaseRestore will look for the most recent file since the latest full backup and restore it with NO RECOVERY to apply additional backups.
  • @BackupPathLog – where your log backups are. sp_DatabaseRestore will look for the most recent files since the latest full backup and restore them in order with NO RECOVERY to apply additional backups.
  • @RestoreDiff = 1 – this tells sp_DatabaseRestore you want to restore differential backups after the full backup is restored. If you don’t then set it to 0 or just don’t use the parameter at all because the default is 0.
  • @ContinueLogs = 1 this tells sp_DatabaseRestore you want to restore log backups after the full and possibly differential backups are restored. If you don’t then just set it to 0 or don’t use the parameter at all because the default is 0.
  • @RunRecovery = 1 – this tells sp_DatabaseRestore you want your database set to RECOVERY after all backups are restored, bringing it out of recovering status and allowing it to be queried. The default is 0.

But wait – there’s more! There are many other helpful parameters you can use depending on what else you may need from a particular restore. For instance:

  • Need to restore the database as a different name? Check out the @RestoreDatabaseName parameter to rename the restored database.
  • Need to restore the files to different locations? Try @MoveFiles = 1, along with @MoveDataDrive and @MoveLogDrive to specify the locations.
  • Need to stop at a point in time? Pass a numeric 14-character string to @StopAt of the desired date and time with the syntax of ‘YYYYMMDDHHMMSS’.
  • Want those log backups to restore faster? Use @OnlyLogsAfter with a similar 14-character string that indicates a time a few minutes before that latest full or differential happened. Without using this parameter, sp_DatabaseRestore will attempt to read and restore all the log backups in your LOG folder.

And if you want to see even more ways to use this great stored procedure, there’s also the @Help parameter to reveal some more use case examples.

EXEC dbo.sp_DatabaseRestore 
	@Help = 1

What do you need to do today?

In addition to its simplicity, another nifty thing about sp_DatabaseRestore is that you can use it for many scenarios. Personally, I’ve already used it with clients to quickly work through:

  • Actual disasters including restoring to a point in time
  • Disaster Recovery testing
  • SQL Agent jobs for restoring Production databases to non-production environments
  • Setting up Availability Groups
  • Setting up log shipping

Heck, you can even code it in your favorite kind of loop to restore a bunch of databases. For example, here’s a simple way to restore all the databases in the Simple recovery model on your instance.

DECLARE @SQL NVARCHAR(1000), @DatabaseName NVARCHAR(50) 

SELECT [Name] AS DatabaseName
INTO #DB_Simple
FROM sys.databases
WHERE database_id > 4 /* only user databases */
AND recovery_model = 3; /* databases in simple recovery model */


	SET @DatabaseName = (SELECT TOP 1 DatabaseName from #DB_Simple ORDER BY DatabaseName);

	SET @SQL = 'EXEC dbo.sp_DatabaseRestore 
	  @Database = ''' + @DatabaseName + ''', 
	  @BackupPathFull = '' X:\BACKUP\TheServer\' + @DatabaseName + '\FULL\'',
	  @ContinueLogs = 0, 
	  @RunRecovery = 1;

	EXEC sp_executesql @SQL;

	DELETE FROM #DB_Simple WHERE DatabaseName = @DatabaseName;



Really, the possibilities of how to use this wonderful stored procedure are only limited by your imagination.


You really need to be restoring your SQL databases with some regularity, if only to practice your Disaster Recovery plans and make sure your databases backups are usable. If you haven’t found an easy way to restore SQL Server databases, we highly suggest you at least try the free and community-supported sp_DatabaseRestore to make your life easier.

Article by Jeff Iannucci
Jeff loves to help others save time. Whether improving query performance or reducing downtime for maintenance tasks or migrations, he’s not only passionate about discovering ways to save our most valuable resource, but also about sharing what he’s learned. Starting as an accidental DBA in 1998, Jeff has worked extensively with SQL Server database design, administration, and T-SQL development. He appreciates any opportunity to share his knowledge with the community, and has been presenting at user groups and writing blog posts since 2018. He was chosen as an IDERA ACE for 2020, and in 2021 became an author for Pluralsight. When not resolving database issues, he enjoys watching Indycar racing, attending Arizona Diamondbacks and Phoenix Suns games, and cooking Italian food for his wife and four children.

Subscribe for Updates


1 thought on “Make SQL Server Database Restores Easy With sp_DatabaseRestore”

  1. Thanks Jeff.
    I’m both delighted, and saddened by this revelation.
    Delighted because this sounds like an awesome tool.
    Saddened because:
    1. I’ve used “pieces” of the First Responder Kit on numerous occasions, but never discovered this stored procedure.
    2. I inherited this production environment from someone else, and we used custom backup scripts, instead of using Ola’s.
    3. I’ve spent untold hours developing a similar tool (in an SSMS Template) – but have never turned it into a Stored Procedure for several operational reasons (starting with: corporate policy makes it difficult to deploy stored procedures into our environment).

    But this gives me some ammo to both: start using Ola’s maintenance scripts, AND get the First Responder Kit deployed enterprise-wide.

    THANK YOU for this “Revelation”.

    One “style” note on your loop above (and a “debate” I continually have with my colleagues):
    Using a temp table and a “while exists” loop with “select top (1) / delete from table approach for this decidedly “non-set-based” task, as opposed to a static cursor.


    SELECT [Name] AS DatabaseName
    FROM sys.databases
    WHERE database_id > 4 /* only user databases */
    AND recovery_model = 3; /* databases in simple recovery model */

    OPEN db_cursor

    FETCH NEXT FROM db_cursor INTO @DatabaseName
    SET @SQL = ‘EXEC dbo.sp_DatabaseRestore
    @Database = ”’ + @DatabaseName + ”’,
    @BackupPathFull = ” X:\BACKUP\TheServer\’ + @DatabaseName + ‘\FULL\”,
    @ContinueLogs = 0,
    @RunRecovery = 1;

    EXEC sp_executesql @SQL;

    FETCH NEXT FROM db_cursor INTO @DatabaseName

    CLOSE db_cursor;
    DEALLOCATE db_cursor;

    (Also note my use of DECLARE @SQL = NVARCHAR(MAX): I’ve never found a good reason to NOT use “MAX” for a VARIABLE declaration, and I HAVE run into truncation problems when building dynamic SQL. Not an issue in THIS case, but if you’re building a “restore database …. with move … on a partitioned database with hundreds (or thousands) of partitions and long paths … the truncation can “bite” you unexpectedly 😉 ).

    Again – THANK YOU for this great tip!


Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Share This