I think it is not the first time you hear that how important is to have a restore script which helps you in DR (Disaster Recovery) scenarios. In a DR scenario one of your main enemy is the time.
You have to figure out what to be done. When you decide that restore is the way to go you have to make the appropriate backup files ready (paying attention to the restore sequence) to restore then start the restore process.
Starting this process in the GUI (Management Studio) is time consuming even if you need to restore only one database. Using a restore script could be faster but only if you don’t need to write the whole script from scratch. In my opinion the best way is to have a well working restore script generator.
I tried to find a good one in the past more times. Since I did not find a script which meets fully my requirements I decided to write my own script, but not from scratch. I used the good parts, ideas from other scripts I found. My Restore Script Generator (sp_RestoreScriptGen) is inspired by Tibor Karaszi (sqlblog | blog)
sp_RestoreScriptGen generates the restore script from the backup files. It supports databases which have more data files. You can choose different destination folder for data files and log file, you can also change the database name.
Supported WITH options: REPLACE, STOPAT, RECOVERY (1), NORECOVERY (0), CHECKSUM (if available).
Only backup files with ONE backup set is supported at the moment!
Further features: Instant File Initialization check, Orphan user check, Restore Time Measure option
@database_name = N'',
@data_dest = N'',
@log_dest = N'',
@recovery = 'NORECOVERY',
@stopat = '',
@replace = 0,
@checkIFI = 1,
@restoretime = 1
Here is the stored procedure: sp_SQLApprenticeDOTnet_SQL2008R2_RestoreScriptGen
I really hope that you can find this stored procedure useful. Don’t hesitate to contact me (robertATsqlapprenticeDOTnet) in case of having any questions or remarks.