When was my Last Backup?

Posted: November 3, 2008 in Uncategorized

Just a quick post, as I was looking at review a client’s backup plan there was not job in SQL Server that told me about the history of the backup. This could have been for many reasons such as the backups were being done by a non-native SQL Server tool like a tape system. To find out the last time SQL Server has registered a backup and a way to see how often the databases have been backed up you can look at the tables in the MSDB database named Backupset and Backupfile. Here is a clip it that I used to tell me how often the database is being backed up.

Select
Distinct s.backup_start_date from Backupfile b

    Join backupset s on s.backup_set_id = b.backup_set_id

Where b.Logical_name like
‘MY_DATABASE_FILENAME_%’

Order
by backup_start_date Desc

There are ways that you can adjust the Query to see if it’s a log backup or a diff even a full. I would not recommend changing data in these tables. I just use this as a quick reference to how often the fulls were being done. I know that there were no Transaction Log backups on this database so I did not account for that in my Query.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s