How to check for last SQL Server backup

As a database professional, I get asked to review the health of database environments very often. When I perform these reviews, one of the many checks I perform is reviewing backup history and making sure that the backup plans in place meet the requirements and service level agreements for the business. I have found a number of backup strategies implemented using full, differential and transaction log backups in some fashion.

In more cases then I would like to share, I have found business critical databases that are not being backed up properly. This could be in the worst case having no backups or a backup strategy that does not meet the recoverability requirement of the business.

When doing an initial check I gather many details about the environment. Regarding backups, I capture things such as recovery model, last full backup, last differential, and the last two transaction log backups. Having this information will allow me to determine what the backup strategy is and point out any recover-ability gaps.

Some examples I have found are 1) no backup’s period, 2) full backup from months ago and daily differentials. In this case the full had been purged from the system, 3) Full backup of user database in Full recovery mode with no transaction log backups, 4) Proper use of weekly full, daily differential, and schedule transaction log backups – however the schedule was set to hourly and the customer expected they would have no more than 15 minutes of data loss.  I am happy to report that I do find proper backup routines that meet the customers’ service level agreement too.

The code I like to use for this check is below.

SELECT
  DISTINCT
        a.Name AS DatabaseName ,
        CONVERT(SYSNAME, DATABASEPROPERTYEX(a.name, 'Recovery')) RecoveryModel ,
        COALESCE(( SELECT   CONVERT(VARCHAR(12), MAX(backup_finish_date), 101)
                   FROM     msdb.dbo.backupset
                   WHERE    database_name = a.name
                            AND type = 'd'
                            AND is_copy_only = '0'
                 ), 'No Full') AS 'Full' ,
        COALESCE(( SELECT   CONVERT(VARCHAR(12), MAX(backup_finish_date), 101)
                   FROM     msdb.dbo.backupset
                   WHERE    database_name = a.name
                            AND type = 'i'
                            AND is_copy_only = '0'
                 ), 'No Diff') AS 'Diff' ,
        COALESCE(( SELECT   CONVERT(VARCHAR(20), MAX(backup_finish_date), 120)
                   FROM     msdb.dbo.backupset
                   WHERE    database_name = a.name
                            AND type = 'l'
                 ), 'No Log') AS 'LastLog' ,
        COALESCE(( SELECT   CONVERT(VARCHAR(20), backup_finish_date, 120)
                   FROM     ( SELECT    ROW_NUMBER() OVER ( ORDER BY backup_finish_date DESC ) AS 'rownum' ,
                                        backup_finish_date
                              FROM      msdb.dbo.backupset
                              WHERE     database_name = a.name
                                        AND type = 'l'
                            ) withrownum
                   WHERE    rownum = 2
                 ), 'No Log') AS 'LastLog2'
FROM    sys.databases a
        LEFT OUTER JOIN msdb.dbo.backupset b ON b.database_name = a.name
WHERE   a.name <> 'tempdb'
        AND a.state_desc = 'online'
GROUP BY a.Name ,
        a.compatibility_level
ORDER BY a.name

Ensuring that you have backups is crucial to any check of a SQL Server instance. In addition to ensuring that backups are being created, validation of those backups is just as important. Backups are only valid if you can restore them.

When I have the opportunity to share my experiences of backup and recovery with people I always like to share about how to backup the tail end of a transaction log and how to attach a transaction log from one database to another in order to backup the tail end of the log. I have created a couple of videos on how to accomplish this that you can view using this like http://www.timradney.com/taillogrestore

Share
This entry was posted in Backups/Recovery, Query Tidbits, SQLServerPedia Syndication. Bookmark the permalink.

8 Responses to How to check for last SQL Server backup

  1. Andy Warren says:

    Tim, I always like to add a disclaimer that just because the history is there doesn’t mean the file is – I’ve seen times when they got cleaned up too soon! Another edge case is having deadlocks that prevent the history record being added, making it look like the backup didn’t happen. I like the premise of checking/confirming backups match what they expect.

  2. Andrew Alger says:

    Tim,
    Great script, thanks for sharing!
    To take this one step further, I created a scheduled script that checks and alerts me if my backups have not been run within a set time. Server updates and restarts always seem to take place during my backup windows.

    Also, enough cannot be said for validating backups. My sys admin was running nightly backups that messed up my backup chain and I had no idea until I began validating these. No need to say what would have happened had I needed to perform an actual restore…

  3. Taz says:

    Thanks for sharing your thoughts and your script.

    I am curiuos as to what questions you would ask to determine what the SLA should be (or more specifically what point people want to be able to recover to)

    In my experience I quite often find full recovery models with full, differential and transaction log backups in place for systems that I feel simply do not need that level of backup.

    For instance I found a system backup scheduled to be restored daily onto a seperate database on a reporting server. This then had full backups with log file backups running on the reporting server.

    In other instances, quite often sytems are able to dynamically recreate the data in an instant, but simply use the database as a convenience. In such cases, I would set the recovery model to simple and have no backups run. I then use a powershell script to shutdown the (Vsphere) server at night and simply backup the entire server in a shutdown state. Since it is in a shutdown state, the data file is backed up without a problem and since the SLA is content with falling back one day (which many production systems I have are) this seems to be the quickest model of recovery without any fuss or hunting for a script and backup files.

    • Tim Radney says:

      Great question. For me I typically ask two very simple questions. 1) How much data can you afford to lose 2) How long can your system be down. The typical response is none and none and then start the explanations and negotiations. I have systems like you mentioned where a full from the previous night is sufficient. Your scenario of using a file backup such as shutting down the service and backing up all the files meets your SLA. For a Reporting server like you mentioned that is just a restored copy from production on a daily basis then why backup at all? For organizations I support, we document the SLA (RPO and RTO) of each database and work to meet that.

      Many times working with different lines of business requires explaining to the business how backups work and what are industry standards and what is realistic. When they don’t want to hear that a 15 minute RPO is best then present them with the price tag to lower the RPO. It really boils down to numbers and dollars in some cases.

  4. Fred Stemp says:

    Tim,

    Awesome query! I’ve been looking for something like this for awhile. If you are interested, I modified it a bit for my use and turned it into to a sProc that uses Dynamic SQL to check all my Servers and Instances. I’m making myself a dashboard with this. I’d like to share it with you.

    I still have one more piece I’d like to add involving ‘xp_fileexist’ to complete my dashboard project. I hope to have solved that soon.

    Thanks again for taking the time to share this with all of us! Outstanding job!

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>