SQLServerCentral Article

Which Databases are Being Backed Up - Another Look


I was reading the article Every DBA Should Know Which Databases are be Backed Up by Leo Peysakhovich. I agree with Leo, that most of the time when we need a backup of database, we noticed that we don't have backup of that database or we have a very old set of backups. To avoid this kind of situation, I have written a view based on system tables which allows me to know when the last time a database was backed up and which database have never been backed up.

Step 1: create  a view in master or any user database using below script.

Create View Last_Backup
Select a.name,Backup_Date from master.dbo.sysdatabases a
left join 
            (select database_name,cmax(backup_finish_date) backup_date
            from msdb.dbo.backupset where backup_finish_date <= getdate()
            group by database_name)  B
on  a.name = b.database_name

Step 2.1: Once this view is ready, to know when databases are last time backed up

Select * from Last_Backup order by 2 desc, 1
Name                               backup_date
-------------------                ---------------------------
SBD_EMIS_VIEWS                     2004-05-25 02:03:11.000  
PILOT_VIEWS                        2004-05-25 02:02:54.000  
PILOT_USER                         2004-05-25 2:02:53.000   
PILOT_SYSTEM                       2004-05-25 02:02:50.000 
PILOT_DATA_FRAME                   2004-05-25 02:02:46.000

Above result shows when database was last backed up.

Step 2.2: To know the databases that were never backed up

Select * from Last_Backup       where backup_date is null
Name                                 backup_date
-------------------                  --------------------------
CG_EMSP                              NULL
CG_FMO_ARCHIVE                       NULL  

Above result shows that two databases were never backed up.

Step 3: Add these missing databases into appropriate backup maintenance plan.

Step 4: We can schedule a job to send email on daily basis to know the backup status of all databases. Attached is the script for the same.


By spending few minutes to know which database was not backed up and update backup plan on regular basis, we can save data & our life.


2 (1)




2 (1)