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.

Filed under: Backups, Dynamic SQL, Microsoft SQL Server, SQLServerPedia Syndication Tagged: backups, dynamic sql, language sql, microsoft sql server