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.
