asked
checking backups
How do you check if the database backups are running in every database on the server?
I do something very similar to what @TRAD and @Fatherjack have already mentioned, so I don't want to repeat their answers (both of which get a +1 from me). Here are a few other ideas, though, to fill out the list. If you have at least one SQL Server 2008 instance (even locally), you could register a central management server with all of your instances and do a one-off check of latest backups for each server group all at once. This would definitely be _in addition to_ a regular, automated check like @TRAD mentioned, but it's a handy thing to have when the boss comes by and asks how we're doing on this front. For a script to check your backup status, here's something off the top of my head. There are places where it could be improved, I'm sure, but I don't have access to the scripts I actually use or to a SQL Server instance with enough data to make tuning judgments: select @@SERVERNAME as ServerName, d.name as DatabaseName, d.recovery_model_desc as RecoveryModel, d.create_date as DatabaseCreationDate, MAX(bsFull.backup_finish_date) as LatestFullBackup, MAX(bsLog.backup_finish_date) as LatestLogBackup, case when MAX(bsFull.backup_finish_date) > d.create_date then 'True' else 'False' end as CurrentFullBackupExists, case when d.recovery_model_desc = 'SIMPLE' then 'N/A' when MAX(bsLog.backup_finish_date) > d.create_date then 'True' else 'False' end as CurrentLogBackupExists from sys.databases d left outer join msdb.dbo.backupset bsFull on d.name = bsFull.database_name and bsFull.type = 'D' left outer join msdb.dbo.backupset bsLog on d.name = bsLog.database_name and bsLog.type = 'L' group by d.name, d.recovery_model_desc, d.create_date order by d.name I include the database creation date because, if you re-deploy a database without deleting the backup statistics, you don't really have a full database backup in place--you have a database backup of the previous database. Another thing is to check how you're taking the backups. If you're using a third-party tool, they're usually good about indicating when jobs fail and you can filter down on those or have them send automated e-mails. If you're using maintenance plans, check the SQL Server Agent log, and have it notify you whenever the job fails. Also check the maintenance plan to make sure all of the databases you want to back up actually are getting backed up. The script above can help you do that, and you could probably add a couple of filters and turn it into an extra e-mail alert in case the first layers miss something (like if you specify the databases to back up but a new one gets added or somebody turns off one of your backup jobs and forgets to turn it back on).
I would be remiss if I did not add that you should also prune your backup sets over time. That will make queries to retrieve this information faster, and you could archive the old information if you ever need it. We typically hold on to backup set information for roughly 30 days. This can still be a pretty good-sized amount of information, but before I started this, there were servers that had backup sets going back 2 years, and that's a lot more!
The way I do it is have a script that gets my list of databases from the sysdatabases in the system database and then checks against backupset table MSDB to see if they have had a full backup within a set period of time. I have a pretty non sophisticated way all this happens, stores the results into tables and then a nifty report that is emailed to me each morning to view and make sure everything is as it should be. It works very well and is free.
Don't forget that having backups done is very different from having a backup that will restore successfully. Make sure that part of your backup routine is the test your restore process. Either a fixed percentage of the backups done or if you can, all of them. If the backup is corrupt or their are other issues that are missed by just seeing a backup complete message.
Ah yes, I should have mentioned that as well. We have an entire other process to validate our backs that included restoring them from on online backup system Tivoli and performs restores. It is a sampling of databases and not a full restore of each db each day, that would be a bit labor intensive.
