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
23 Comments
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.
Very good points.
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…
care to share you scheduled script?
Hi Andrew,
Can you please share the “scheduled script that checks and alerts” with me?
Thanks
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.
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.
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!
That would be awesome. Send me an email at Tim @ timradney dot com.
Is that dashboard available to anyone? Thanks!
Hi Tim
I need help with the below question.
How to write a query that will display the last full backup and the last log backup for each database on each of the production servers using the system table “backupset” and related backup tables.
Regards
Janice
The query in my blog does exactly that.
Love the simplicity of the code.
What would I have to add to get the backup “name” from msdb.dbo.backupset.
Sent you a direct email for more input.
Hi Tim
Thanks heaps for your script !! Simple and gives the main info that I need
Thanks again
Thanks for sharing the script, but I had to change it to function on my server.
I have a case sensitive SQL Server (Czech_BIN2 sever collation).
You need to change type = ‘d’ to ‘D’, and so on (type = ‘I’ and on two places type = ‘L’).
You can find correct type column values of backupset table at documentation.
Hi Tim,
I love this script and point people to it all the time. Thank you for putting it together.
Today, I recently noticed a small recommendation. I would recommend capitalizing the character used for backupset.type. I reciently ran this on a system that didn’t use the default collation and I saw that switching these to capital D, I, L resolved the error. I would hate to see others run into this without double checking and realizing that case sensitive does matter pending the collation that is used.
Regards,
John
thank you for sharing nice scripts
Nice work and Thank you for sharing , used it all the time.
Hi Tim
I have AlwaysOn AG groups and I perform copy only backups.
I had to change AND is_copy_only = ‘1’
Otherwise it was returning No Full.
It’s Outstanding job!
Thanks