Get Last Backup Date of Database

I’ve been working hard on my backup/restore skills lately, getting to grips with the fundamentals and making sure that I understand backup/restore best practices and running examples to make sure I’m ready in the event of a disaster. It’s a cliche to say that you don’t want to be doing this in a real life disaster when everyone is huddled around your desk, but it’s true! Tasks like tail log backups, a type of backup that backs up the log file even if it is offline or damaged, is something that you may need to run in some scenarios. A post by Paul Randal goes into depth about this. Knowing how you can run a tail of the log backup and when its possible, and even how to attach the log to a different server are crucial in disaster recovery scenarios, so it’s well worth reading and running through the demos.

Not understanding backup/restore processes can have greater repercussions applies if you have log shipping running; although full backups do not break the backup chain, log backups that are not applied to the standby server means that you can lose a database that the business rely on to run reports on through the day. If you do need to run a log backup and you do not want to break the log chain, use the COPY_ONLY option when backing up the log.

Some time ago, I wrote a detailed script that returns the database backup history using the system tables, but sometimes all you want ot know is how to cut to the chase. So I have stripped the script back (more than a little) to return the latest full backup of all databases on the instance.

--get last backup of a database
select db.name,
MAX (bck.backup_finish_date) as FinishTime
FROM MSDB.SYS.databases db
LEFT OUTER JOIN msdb.dbo.backupset bck ON bck.database_name = db.name
group by db.name

Using the left outer join to join from the sys.databases table to the sys.backupset table returns all databases that have no backups, which in most cases is far more important than the last backup time of your databases!

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Pokémon Nut. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

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