This is a free SQL Server backup check tool from Straight Path Solutions for SQL Server Database Administrators (or people who play DBA at their organization) to check their SQL Server recoverability. It is used to detect recoverability vulnerabilities and discrepancies in the backups of SQL Server instances. You can download the most recent version of sp_CheckBackup from the Straight Path Solutions GitHub repository.
Check the Backups on your SQL Server Instances!
Here at Straight Path Solutions, we’re big fans of community tools like 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 an overview of potential recoverability issues. We didn’t find one, so we made one.
What does sp_CheckBackup do?
This tool will allow you to review your SQL Server backup history quickly and easily, and to also identify potential issues with your backups like missing backups, failed backups, and split backup chains.
This tool has several modes that present a different set of data, depending on what you want to examine.
- @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.
Using each of these Modes, you should be able to quickly identify recoverability issues with your backups and focus on facts about them to help you resolve any issue.
How do I use it?
Execute the script to create sp_CheckBackup in the database of your choice, although we would recommend the master so you can call it from the context of any database.
Executing it without using parameters will return two results sets:
• The results of Mode 1, ordered by database name
• The results of Mode 0, ordered by Importance
Although you can simply execute it as is, there are currently three parameters.
@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.
@Mode – see the previous few paragraphs to decide which Mode you want to use.
@ShowCopyOnly – the default is 0, which excludes showing copy only backups from the results. If you only want to include copy only backups to show all backups performed, set this to 1.
@DatabaseName – the default results include information for all databases, but use this parameter if you have a specific database that you are reviewing. Using this parameter can greatly reduce the results.
@BackupType– the default results include information for all three kinds of backups, but use this parameter if you have a specific type of backup that you are reviewing. Use “F” for full backups, “D” for differential backups, and “T” for transaction log backups. Using this parameter can also greatly reduce the results.
@StartDate – the default is to return results from backups completed in the last 30 days, but use this to filter results from a different time period.
@EndDate – the default is to return results from the last 30 days, but use this as well to filter results from a different time period.
What do the Importance levels in Mode 0 mean?
1 – High. This is stuff that prevents recoverability, including databases without backups or that have not had recent backups, or encryption certificates that have not been backed up.
2 – Medium. This is the stuff that can complicate recovery, like split backup chains or backups without checksum checks.
3 – Low. This is stuff that could be affecting backup or restore performance, like having backup compression disabled.
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.
2. 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.