“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()))

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