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)

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: 5975 | Views in the last 30 days: 32
 
Related Articles
FORUM

DDL trigger for RESTORE DATABSE

DDL trigger for RESTORE DATABSE

SCRIPT

Script to Restore Entire database from a directory

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

FORUM

restoring a databse with change logs

restoring a databse with change logs

SCRIPT

Restore All Backups

SCRIPT TO RESTORE BACKUP FOR ALL DATABASES, PLACED IN FOLDER CALLED ORIGIN_FOLDER, WITH THE INITIAL...

ARTICLE

Powershell Database Backup Script

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

Tags
backup / restore    
restore    
t-sql    
 
Contribute

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
Editor, SQLServerCentral.com

Already a member? Jump in:

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