Generating a restore script

August 18, 2014 by Kenneth Fisher

In order to speed up our backups on a large database our team decided to stripe the backup files. In case you weren’t aware of this particular backup feature it his means that a single backup is written to multiple files which can dramatically speed up your backups and restores. Unfortunately in this partiular case it also meant that our script that automatically generated restore commands broke. And of course I was asked to correct it. First thing I did was to tweet to #sqlhelp and I recieved a number of great scripts. Unfortunately none were exactly what I needed so I started merging and modifying and building my own. I was about a third of the way done when I happend to be reading dba.stackexchange.com and ran across a link to a restore command generator called sp_RestoreScriptGenie by Paul Brewer and based on a script by Robert Davis(b/t).

Edit:

One of the big benefits of putting something like this in a blog is that sometimes the author turns up and helps you out. In this particular case Paul commented (see below) with the latest version of this SP and in fact he even has a powershell version! Here is the link to the latest version of his scripts.

Among other things it has the following features:

It will generate the most recent restore script for all user databases if you don’t pass in a parameter.
Multi-file backup files are support for FULL, DIFF and LOG backups.
Flag to include scripts for the system databases.
Option to pass in a single database name and generate the restore for just that database.
Generate the scripts to restore to a specific time.
Flag to modify the script to leave the database in standby mode.
Parameters to modify the data, log and backup directories.

Now it isn’t perfect:

In the discussion they mention changing the “Device_Type” from = 2 to 7.
I couldn’t find a version more recent than early 2013 so I’m not sure if it is still supported.
The scripts are generated from msdb so it has limited usefulness in a DR situation.
It can only handle 10 files per backup (if you are using more than 10 files for the backup you may have additional problems).
It automatically includes a CHECKDB at the end of each restore. Really a good thing but I would rather be able to turn it off if I need to.

As you can see I consider it a very good script generator given the items on the problem list are fairly minor and the positives are pretty cool. I believe we will be using it in our office and I have added it to my Free Scripts page in case you want to use it too.

Category: Backups, Dynamic SQL, Microsoft SQL Server, SQLServerPedia Syndication | Tags: backups, dynamic sql, language sql, microsoft sql server

9 thoughts on “Generating a restore script”

Paul Brewer says:

August 19, 2014 at 1:38 AM

Hi Ken,
The latest version of the script is here – http://paulbrewer.wordpress.com/2014/08/15/restore-gene-version-3-free-public-framework-technical-article/

In the latest version you can include CHECKDB or not, it’s a parameter option.

Regarding the dependency on CHECKDB ..
This solution isn’t going to work if you lose msdb. To mitigate this, you could add a final step to any SQL Agent backup jobs and call sp_RestoreGene for the DB that has just been backed up. Outputting to a text file would create and save the restore scripts ready, before the disaster.

The new version was release August 2014.

I’m really pleased you are exploring it.
Thanks and regards
Paul

Kenneth Fisher says:

August 19, 2014 at 6:14 AM

Even with the dependency on msdb the script is absolutely perfect for our needs. I’m looking forward to going over the new version.

Honestly we are doing some sort of hardware level backup (if I understand it correctly) for DR. We mostly need RESTORE commands for operation recoveries. Someone deleted a table, we need to move the DB from one location to another, etc.

Thanks again!

Reply

Paul Brewer says:

August 19, 2014 at 1:45 AM

Regarding the dependency on msdb, not CHECKDB 🙂

Peter says:

August 7, 2015 at 6:23 AM

This is really disappointing. I’ve been using the old version to output the commands to a file using SQLCMD. Now the updated version adds columns, which makes the output file useless.

Reverting to old version now. Would be good if Paul could offer advice but his website isn’t allowing me to post a comment.

Kenneth Fisher says:

August 7, 2015 at 7:31 AM

SQLCMD commands are you using to export the output? You should be able to restrict it to just the columns you are interested in.

Reply
- Peter says:
  
  August 7, 2015 at 9:30 AM
  
  Thanks for your reply Kenneth, it would be great if you could point me in the right direction, I’m using: sqlcmd -d master -Q “Exec sp_RestoreScriptGenie @database = ‘DATABASEName'” -o\\servername\InstanceName\Database\RestoreScript.txt
  
  with the old version, which works fine. What do I need to change to only output the first column when using the new version?
  
  Thanks
  Peter
  
  Reply
  - Kenneth Fisher says:
    
    August 7, 2015 at 9:53 AM
    
    Put this in a .sql file:
```
CREATE TABLE #RestoreCommands (id INT NOT NULL IDENTITY (1,1),TSQL varchar(8000), BackupDate datetime, BackupDevice varchar(8000), Last_LSN char(30), Database_Name sysname, SortSequence int)

INSERT INTO #RestoreCommands
EXEC [dbo].[sp_RestoreGene]
    @Database = 'DATABASENAME'

SELECT TSQL FROM #RestoreCommands
```
    Then change your command to
    
    sqlcmd -d master -i “filename.sql” -o….
    
    Here is a reference for running files in sqlcmd just in case: https://msdn.microsoft.com/en-us/library/ms170572.aspx?f=255&MSPPError=-2147217396
- Peter says:
  
  August 10, 2015 at 4:00 AM
  
  Fantastic Kenneth thank you, I appreciate your help.
  
  One last thing – any idea how I can get rid of those (seemingly pointless) @msg and Raiserror statements? I can’t think of how they would ever be useful.
  
  Reply