Whether you are backing up your SQL Server databases with Ola Hallengren’s scripts, Maintenance Plans, or some third-party software, your backups are your lifeline for recovering your data in case of a disaster.
So…are your databases recoverable? Do you know WHERE your most recent backups were written? Do you know WHEN they were written? Did you verify the backups for recoverability? Have you backed up any encryption certificates associated with your backups?
And if you think know the answers to these questions…are you sure? And can you confirm your Recovery Point Objective (RPO)?
Here at Straight Path Solutions, we’re big fans of community tools like Adam Machanic’s sp_WhoIsActive, Brent Ozar’s First Responder’s Kit, and Erik Darling’s suite of helpful stored procedures. As database administrators who are constantly looking at new clients and new servers, we wished there was a tool to quickly give a SQL Server recoverability check – an overview of any potential issues that could affect the ability to successfully restore databases. We didn’t find one, so we made one: sp_CheckBackup.
One caveat before we start: the aforementioned First Responder’s Kit includes the tool named sp_BlitzBackups, which includes some similar information as sp_CheckBackup. The sp_BiltzBackups tool is definitely useful, especially if you want to analyze potential performance of restores. We found we needed a tool that allows us not only to find backup and restore issues instantly, but to also allow us to focus quickly into the history for specific databases or backup types – which is what sp_CheckBackup does.
With that out of the way, we want to emphasize that sp_CheckBackup is completely free, it’s easy to use, and you don’t even need to give us your email address. You can download it today from the Straight Path GitHub repository.
What does sp_CheckBackup do?
As noted, not only does sp_CheckBackup help identify issues that could affect the recoverability of your SQL Server data, but by using one or more parameters you can easily review backup history to better understand and hopefully resolve these issues. You can read more details about this on the sp_CheckBackup page.
If you don’t feel like clicking over to that page, that’s fine. We’ll just note that the key to this flexibility is the use of several different “Modes”, which each return different result sets.
- @Mode = 0: the potential issues we could find related to recoverability from backups.
- @Mode = 1: the database-level overview, with general information about the most recent backups. This is useful if you need to answer basic questions about RPO for your databases.
- @Mode = 2: the details, showing information about each backup file that was written.
- @Mode = 3: the backup file locations, to help with identifying and resolving split backup chains.
- @Mode = 4: the big picture, returning results for each of Modes 1-3.
You can get lots of valuable information from sp_CheckBackup just by executing with a particular mode selected. In fact, because we’ve found it most useful to look at the general information as well as any potential problems, if you execute sp_CheckBackup without any @Mode selected, you will get the results of both Modes 1 (the overview) and 0 (the potential issues). We typically look at the results of Modes 0 and 1 to find issues, and then troubleshoot those issues with Mode 2 (or if a split backup chain is found, Mode 3).
But there’s way more you can do with this tool.
How do I use the tool to check SQL Server recoverability?
As noted, there is flexibility in how sp_CheckBackup can be used. As with many stored procedures, we’ve added useful parameters to help you focus on only the data you need.
@Help – the default is 0, but setting this to 1 will return some helpful information about sp_CheckBackup and its usage in case you aren’t able to read this web page.
@ShowCopyOnly – the default is 0, which does not include any backups made with the COPY_ONLY option, which does not affect the backup chain. If you want to see copy-only backups, and you may need to if those are the only backups being written, then set this to 1.
@DatabaseName – the default results include all databases, but you can use this parameter to only return information about a specific database.
@BackupType – the default results include all backup types, but you can use this parameter to only return information about a specific backup type. If you can’t recall what those are in SQL Server, don’t worry – we’re using intuitive single character values: ‘F’ for full backups, ‘D’ for differential backups, and ‘L’ for transaction log backups.
@StartDate – the default results include the last 30 days of data, but if you want to change that you can use this parameter with a valid date value.
@EndDate – the default results include backups written up to NOW, but if you want to change that you can use this parameter with a valid date value.
Although you are not required to use any of these parameters to execute sp_CheckBackup, we definitely recommend using one or more of them with @Mode = 2 so you don’t see information about every backup file ever written.
What are the requirements to use sp_CheckBackup?
There are two requirements.
- You need to have VIEW SERVER STATE permissions. This tool uses several system tables and DMVs to collect information about your SQL Server backups, but VIEW SERVER STATE permissions will allow you to read all necessary information.
- Your SQL Server instance should be using SQL Server 2014 or higher. If you are using an earlier version, execution of the stored procedure will skip some checks because some of the DMVs used don’t exist in earlier versions.
What if I have any questions or feature requests?
Please report any issues on our GitHub sp_CheckBackup Issues page.
Final thoughts
We hope this tool is useful in helping you quickly identify potential recoverability issues with your backups. We’ve been using it for a while now, and it’s been a great time saver. Checking for backups is just one of the parts of our comprehensive health checks that we offer, which also include checks for issues related to security, availability, reliability, integrity, and performance. Regardless of whether or not you are a Straight Path client, we hope sp_CheckBackup can help you and SQL Server Database Administrators like you to identify and address any obvious or potential recoverability vulnerabilities before it’s too late.
