This stored procedure creates the script to restore your database with the information existing in [msdb] database.
It helps you by finding the last FULL backup, the last DIFFERENTIAL backup and all the TRANSACTION LOG backups needed.
It's quite comfortable when you are doing so many differential or log backups. I hope you enjoy it!!!
- Of course, this script is AS IS, and there's no warranty, etc ...
- The database name is implicit. You have to create the stored procedure in each database you want to script the backup. This change
is due to the SELECT on SYS.DATABASE_FILES to get the NAME and PHYSICAL_NAME of the database, that you must be in the database.
- The script gets the backup information from msdb database. You should be carefull that in your maintenance plan you should
clean the msdb's history with a retention of more days than between full backups. If you do a full backup every weekend,
the history retention in msdb should be higher, for example 15 days, to allow the query to get the full backup information.
- @Days: how many days back in the records you want to list backups look for. Must be bigger than the days between full backups. By default set to 20 (old enought I think)
- @WithMove: 1 or 0; 1=include a "move xx to yy" statement.
- @WithStats: 1 or 0; 1=include a "STATS=1" statement
It is not case sensitive unless your collation is.
- EXEC dbo.CreateRestoreScript
- EXEC dbo.CreateRestoreScript @Days=20, @WithMove=1, @WithStats=1
- EXEC dbo.CreateRestoreScript @Days=30, @WithMove=0, @WithStats=0
Scripting it with Powershell to save the result in a file:
&"sqlcmd" ("-d", "myDatabase", "-Q", "EXEC dbo.CreateRestoreScript") | Set-Content (Join-Path "C:\Temp\" "restoreScript_$((get-date).ToString("yyyyMMdd_HHmmss")).sql")