When was the last time a full backup was taken

One of the first things you want to do as a DBA is to check when was the last time a full backup was taken for all databases you manage. For Sql Server, here is a script you can run:

set nocount on
select cast(a.name as char(30)), max(b.backup_finish_date) from master..sysdatabases a left outer join msdb..backupset b on a.name = b.database_name
where a.name not in (‘model’, ‘tempdb’, ‘pubs’, ‘northwind’, ‘adventureworks’)
group by a.name

Suppose we have a list of instances we manage, I cannot emphasize enough how convenient it is to be able to blast the above statement to all instances we manage. That way we get all full backup information on all instances in one fell swoop. Fortunately, Linchi Shea wrote a couple of utilities just for that: one in C#, one in Perl. Check ’em out here.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.