Rotating Header Image

SQL Databases That Have Not Had a Full Backup in x Days

So something that you should do regardless of the alerts, notifications, etc that you have setup is to just have a daily sanity check on your database backups. Sure you don’t have any failed jobs but what if the job never attempted to run in the first place like it was supposed to?

If a database fails in the enviroment and no backup is there to recover from does it make a sound?

Well no… but the users are heard for miles hahaha

screaming

Below is a nice quick sanity check showing all the dbs that have NOT been backed up for X days or have never been backed up at all. 

DECLARE @num_of_days INT

SET    
@num_of_days 

SELECT     A.nameMAX(B.backup_finish_dateAS 'LastBackupDateTime'

FROM   master.dbo.sysdatabases A WITH(NOLOCK)  LEFT OUTER JOIN

   
msdb.dbo.backupset B WITH(NOLOCKON A.name B.database_name

WHERE  (B.TYPE 'D' OR B.TYPE IS NULL)

GROUP BY A.name

HAVING (MAX(B.backup_finish_date) < GETDATE() - @num_of_days OR MAX(B.backup_finish_dateIS NULL) 

ORDER BY A.name

Enjoy!!

Post to Twitter Post to Delicious Post to Digg Post to StumbleUpon

5 Comments

  1. Thanks for the tip. Check your post as some of the code is hidden behind your categories sidebar.

  2. hmmm it doesn’t appear so on my side. Perhaps it’s a browser or screen resolution issue?

    I’ll ping some friends to give it a try but just curious… what browser/resolution are you at?

    Thanks

  3. Mitch says:

    Thanks, helpful script.

  4. […] SQL Databases That Have Not Had a Full Backup in x Days […]

  5. Wozniak engineered Apple 1, the first home computer with keyboard and TV screen as a display. While Steve Jobs took care of the creative aspect of Apple products, Wozniak was the mind behind the construction of Apple’s first computers and devices.

Leave a Reply

Twitter links powered by Tweet This v1.6.1, a WordPress plugin for Twitter.