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
as
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
go
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.
Conclusion
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.
