SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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)

Restore database with all necessary files

By Josep,

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")

Total article views: 4604 | Views in the last 30 days: 5
Related Articles

Database Backup Information Scripts

Scripts used to validate the backups information as below 1) Database Backups for all databases F...


Backup & Restoration Script

This script provides very useful information about database backup and restoration.


Powershell Database Backup Script

Learn how to write a script for regular database backups using Powershell and SMO.


xmla script for automate ssas database backup

xmla script for automate ssas database backup


Script to Check the Database Backup duration

Script to Check the Database Backup duration of entire instances

backup and restore    
sql server 7