Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin.
Search for scripts directly from SSMS, and instantly access any saved scripts in your
SSC briefcase from the favorites tab.
Download now (direct download link)
Thank this author by sharing:
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")
Scripts used to validate the backups information as below
1) Database Backups for all databases F...
This script provides very useful information about database backup and restoration.
Learn how to write a script for regular database backups using Powershell and SMO.
xmla script for automate ssas database backup
Script to Check the Database Backup duration of entire instances