06 December,2013 by Jack Vamvas
“No database backup” – These are the words as a DBA you never want to utter (or even think). Almost everything can be forgiven – but a database that’s never been backed up or an invalid backup can cause blood pressure to rise . Believe me , I’ve seen this situation in a Production environment and it’s not pleasant
What checks can you build in to the daily checks , which give you a better chance of ensuring a backup is taken ?
As part of the daily checks build in a procedure that checks for databases that have no backup in the last week. This query lists databases catalogued but with no record of a backup in the last week.
There are very large environments with databases in the multi-terabyte range where a daily or weekly backup is impractical. You may need to make adjustments on the script to account for databases that schedule for once a month.
--Databases with no full backup in the last 7 days SELECT dbs.[name] AS DatabaseName FROM master.sys.databases dbs where dbs.database_id <> 2 AND dbs.[name] NOT IN (SELECT bus.database_name as 'name' FROM msdb.dbo.backupset bus INNER JOIN msdb.dbo.backupmediafamily bume ON bus.media_set_id = bume.media_set_id WHERE dbs.database_id <> 2 AND dbs.is_in_standby = 0 AND dbs.source_database_id IS NULL AND dbs.[state] <> 1 AND bus.backup_finish_date >= DATEADD(d, -7, GETDATE()))