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)

Backup & Restoration Script

By Ashish Gilhotra,

As a SQL Server DBA, many time we takes backup's of our database and restore these backup's as a part of our dail maintanance plan or recovery plan. Sometimes we used to take backup's everyday and some times we forgot to take backup's of databses, so i just get into that thing as write a script for that which tells you about all the databases backup's and relatively there restore information, this script provides you useful information like backup date,how long it takes to run,who takes backup, backup type, size, is ever restored or not, restoration dat and some other very useful information for backup's and restoration process... so here we go with the script

This script takes Databse Name as a paramter by default it is NULL and script throws results for all of the databses but if you want to see the details for any specific database the provide Databse Name with this script.

Here are the little description of columns which we get from this script...


BS_Name = Name of BackUp
BS_UserName = Name of User from which BackUp had taken.
backup_start_date = Start Date Time of BackUp
backup_end_date = End Date Time of BackUp
BackUp Type = Type of BackUp
BackUp Size = Size of BackUp File
Dbname = Name of database
ServerName = Name of Server
Physical_Device_Name = name of Drive or Device where backup stored
Is Ever Restored = is BackUp Ever Restore(Yes/No)
Destination Db = Name of Database Restored By BackUp
Restore Path = Path from database restored
Restore type = Type of restoration
restore_date = Date when Database Restored
restore by = User by which database in restored
Time Taken = Total time taken to take Database BackUp


Hope this script will be useful for mant peoples who like to see the backup's and restoration details for their Databases.

Total article views: 6490 | Views in the last 30 days: 6
Related Articles




Script to Restore Entire database from a directory

Script to restore the entire databases from the backup directory just by passing the backup director...


Generate RESTORE DATABASE Script without Using CMDSHELL

Use this script to generate RESTORE DATABASE scripts for all .BAK backup files from backup location ...


restoring a databse with change logs

restoring a databse with change logs


Restore script for Ola Hallengren backup

Create a restore script based on backup files in a directory

backup and restore