Restore Me, Please

Our shop has a stored procedure to restore databases that takes care of all the ugly details of file moves and the like.  You supply a target_database_name and a backup_file and it does the rest.  No worrying about which paths to move your data and log files to on each server.  It's even supposed to distinguish between native and Red-Gate backups and do the appropriate magic.


A couple years ago, we had a rock-star DBA "optimize" the restore procedure, and it has only worked intermittently ever since.  We've all taken to using the native backup syntax so we don't depend on "the procedure."  However, we occasionally get a compressed database we need to restore (.sqb), and I recently ran through a painful restore that I thought I'd share to give a quick overview of restoring backups that have been compressed with Red-Gate SQL Backup.

After making a good-faith effort to restore with "the procedure," I grabbed version 6.4 of the Red-Gate SQB Converter so I could extract the .SQB file into its constituent .BAK files.  Not entirely related, but we tend to have 15 .BAK files wrapped up in each .SQB file.  It helps backup performance, but can be a killer on MemToLeave Virtual Address Space (VAS) on 32-bit servers.

In this particular case, it wouldn’t convert the file because it wanted a password.  This is often an indication that the converter version is older than the SQL backup version that created the .sqb file, but I'm sure it could have other meanings, too (like needing a password?).

Since I couldn't get my .bak files out of the .sqb file, I had to crack open "the procedure" and scarf its Red-Gate syntax to do the restore.  Other than the additional Red-Gate syntax, the process is the same we would use with native tools…

1.       Restore filelistonly to get your logical filenames

2.       Determine the location to place your .mdf and .ldf files on the server

3.       Restore your database with move

Here is the relevant syntax…

1.       Get your logical filenames with this:

EXEC master..sqlbackup N'-SQL "restore filelistonly from disk = ''path_to_your_sqb_file.sqb''"'

2.       Get the default file locations for your server (if you can create a database, you should have permissions to write restored database files there, too):

CREATE DATABASE bob

EXEC bob..sp_helpfile

DROP DATABASE bob

3.  Once you have your logical filenames, and the paths to where you want to move them, you can run the restore…

EXEC master..sqlbackup N'-SQL "restore database your_database from disk = ''path_to_your_sqb_file.sqb'' with move ''data_file'' to ''path_to_mdf_file.mdf'', move ''log_file'' to ''path_to_your_log_file.ldf''"'

I'm still a big fan of Red-Gate SQL Backup, I just don't listen to rock any more.