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!