No database backup

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
								dbs.[name] AS DatabaseName 
								FROM    master.sys.databases dbs
								where dbs.database_id <> 2  
								AND dbs.[name] NOT IN
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
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()))

Read More

SQL Server - Predict SQL BACKUP DATABASE finish time with sys.dm_exec_requests

SQL Server – Restore Database Estimated Finish Time

SQL Server - Display restore history for a single database

SQL Server - Display sql backup history for a single database

SQL Server - Move MDF/LDF files to a designated path

SQL Server - BackUp All Databases

Author: Jack Vamvas (


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on No database backup | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer