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 */

WHILE EXISTS (SELECT * FROM #DB_Simple) BEGIN

	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;

	END;

DROP TABLE #DB_Simple;

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

Summary

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.

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.
e.g.:

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

DECLARE db_cursor CURSOR STATIC FOR
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
WHILE @@FETCH_STATUS = 0
BEGIN
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
END;

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!

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

David Dennis

August 19, 2023 at 21:17

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.
e.g.:

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

DECLARE db_cursor CURSOR STATIC FOR
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
WHILE @@FETCH_STATUS = 0
BEGIN
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
END;

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!

Make SQL Server Database Restores Easy With sp_DatabaseRestore

Your New Favorite Tool

What do you need to do today?

Summary

Subscribe for Updates

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

Leave a Comment Cancel reply