Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Tuesday, 7 May 2013

How to Monitor SQL Server Backups ?

If SQL Server database backups are too much critical, then how can I verify that database backups are being backup as per backup policy.
What all should be monitored for SQL Server Database Backup considering Disaster recovery in mind.
  1. Ensure that Databases are backing up as per plan
  2. What all databases which are not getting backup up
  3. Database backups are good and can be recovered in case restore ins required.
Let’s build solution for these three critical item for ensuring GOOD SQL Database Backups.
How can I monitor that all Databases are backing up as per plan ?
Here is  a quick script which will let you know
  • what all databases are being backup
  • When those backup happened
  • Backup window – Backup Start Time and Backup End Time
  • What Type of backup is being taken ?
  • Size of Database backup
  • Location, where backup has been performed
Scripts:

SELECT 
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
   bs.database_name, 
   bs.backup_start_date, 
   bs.backup_finish_date,
   bs.expiration_date,
   CASE bs.type 
       WHEN 'D' THEN 'Full Database' 
       WHEN 'L' THEN 'Log' 
       WHEN 'I' THEN 'Differential' 
       WHEN 'F' THEN 'File Level' 
       WHEN 'G' THEN 'File Level Differential' 
       WHEN 'P' THEN 'Partial' 
       WHEN 'Q' THEN 'Differential partial'       
   END AS backup_type, 
   convert(varchar,cast(bs.backup_size/1024/1024 as money),10) as 'Backup Size in MB',
   bmf.logical_device_name, 
   bmf.physical_device_name,  
   bs.name AS backupset_name,
   bs.description
FROM   msdb.dbo.backupmediafamily  bmf
   INNER JOIN msdb.dbo.backupset bs ON bmf.media_set_id = bs.media_set_id 
WHERE  (CONVERT(datetime, bs.backup_start_date, 102) >= GETDATE() - 2) 
and bs.type='D'
ORDER BY    bs.database_name,  bs.backup_finish_date desc


Screentshot: