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()))
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
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |