Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
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: 4501 | Views in the last 30 days: 14
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 / restore    
sql server 7    

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones