BackUp Status Script

This time its all about a script a backup script.Today early morning i asked to get BackUp details for some of DB’s we have, usually they ask me about the last BackUp taken so its easy for me to tell him. But this time he needs all the history of BackUp’s for some reason.So i know that iformation is stored somewhere is System databases so started goggling for it  and i found it on mssqltips.com and the script is written by Thomas LaRock(aka SQLRockStar) and i modified this script according to my use by taking his prior permission i modified his script and add the details about the restoration of BackUp’s and location of BackUp’s basically i added information about the Restorations of BackUp’s, so here is my modified script about BackUp status which returns some very useful information about BackUp’s and restore.

-- Script Returns BackUp Status and corresponding Restore Status for all Databases BackUps

DECLARE @DBNAME VARCHAR(100)

SET @DBNAME=NULL  -- Default NULL(All Databses)

select 'BackUp Name'=BS.name,

'User Name'=BS.user_name,

'Start Date'=BS.backup_start_date,

'Finish Date'=BS.backup_finish_date,

'Backup Type'=Case when BS.type='D' then 'FULL Backup'

              when BS.type='L' then 'Transaction Log Backup'

              when BS.type='I' then 'Differential Backup' end

,'Backup Size MB'=floor(((BS.backup_size/1024)/1024))

,'DbName'=BS.database_name

,'Server Name'=BS.server_name

,MF.physical_device_name

,'IS Ever Restored'=case when BS.backup_set_id in 

(select backup_set_id from msdb.dbo.restorehistory)

 then 'Yes' else 'No' end

,'Destination Db'

=isnull(RH.destination_database_name,'Yet Not Restored From This BackUpSet')

,'Restore Path'

=isnull(min(RF.destination_phys_name),'Yet Not Restored From This BackUpSet')

,'restore Type'=isnull(CASE WHEN RH.restore_type = 'D' THEN 'Database'

              WHEN RH.restore_type = 'F' THEN 'File'

              WHEN RH.restore_type = 'G' THEN 'Filegroup'

              WHEN RH.restore_type = 'I' THEN 'Differential'

              WHEN RH.restore_type = 'L' THEN 'Log'

              WHEN RH.restore_type = 'V' THEN 'Verifyonly'

              WHEN RH.restore_type = 'R' THEN 'Revert'

              ELSE RH.restore_type 

             END ,'Yet Not')

,Rh.restore_date,'Restore By'=isnull(RH.user_name,'No One')

,'Time Taken'

=cast(datediff(ss,BS.backup_start_date,BS.backup_finish_date)/3600 as varchar(10))

+' Hours, ' + 

cast(datediff(ss,BS.backup_start_date,BS.backup_finish_date)/60 as varchar(10))

+ ' Minutes, ' + 

cast(datediff(ss,BS.backup_start_date,BS.backup_finish_date)%60 as varchar(10)) 

+' Seconds'

from msdb..backupset BS 

JOIN msdb..backupmediafamily MF 

on BS.media_set_id=MF.media_set_id

left outer join msdb..restorehistory RH 

on BS.backup_set_id =RH.backup_set_id

left outer join msdb..restorefile RF 

on RF.restore_history_id=Rh.restore_history_id

where BS.database_name = isnull(@DBNAME,BS.database_name)

group by BS.name,BS.user_name,BS.backup_start_date,BS.backup_finish_date,

BS.TYPE,BS.backup_size,BS.database_name,BS.server_name

,MF.physical_device_name,BS.backup_set_id,RH.destination_database_name

,RH.restore_type,Rh.restore_date,RH.user_name

Here this scripts take a Database name as parameter and is you don’t supply that parameter that by default it shoes information about all databases

Here what this script returns :

• 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

So in this way this script return some useful information regarding BackUp’s and Restoration of backup’s its really make sense if you schedule this script to send through mail to get updates about your BackUp’s and Restore information so its really a good idea if we send this information in mail so in next blog post i will write how to send this information using
e-mail to direct in your inbox through Database nail in SQL Server.

SO if you have any suggestions regarding this script and do let me now or if you found any other method then do post here any kind of suggestions are welcomed

Thanks….

If you like this article,  Subscribe in a reader or Subscribe by Email. Show your support by sharing this article with your friends through the services given below.