Generating a restore script

8

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.

8 thoughts on “Generating a restore script

  1. Paul Brewer says:

    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

    • 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!

  2. Paul Brewer says:

    Regarding the dependency on msdb, not CHECKDB 🙂

  3. Peter says:

    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.

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

      • Peter says:

        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

      • Peter says:

        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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,614 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: