Since we introduced sp_CheckBackup last year we’ve gotten some great feedback on ways to improve this tool that helps you quickly and easily review the backup history for your SQL Server databases. We’ve even noticed a few things ourselves that we wanted to change as we used it more internally here at Straight Path Solutions.
Today we’re announcing a new version that includes some additions, corrections, and a few other adjustments that should be helpful. Here’s what new!
Added @Mode = 5 to check restore history. Backups are important, but sometimes databases are restored on reporting servers (yeah, some people use that old school method of replicating data) or non-Production servers, and sometimes those databases need a little troubleshooting as well.
Added @RPO value to check for Recovery Point Objective in minutes. Meeting the RPO is a critical component of recovery, so now you can quickly check to see if your backups are within your RPO. Use this to impress those C-level folks who love to speak in acronyms.
Added @Override to complete checks if instance has more than 50 databases. In the previous version we noted there could be some performance issues if there were a lot of databases. Although 50 is a bit arbitrary, we wanted to have a threshold to warn you that executing sp_CheckBackup might be resource intensive
Improved performance of missing backup checks. This goes with the previous addition, so that if you do want to check the status of backup for a lot of databases, we are now using a few different temporary tables with less data that has greatly improved the performance – even with 100+ databases.
Changed the default to show copy only backups instead of exclude them. We made this change because lots of folks are using applications that make backups that are recorded as copy only. The @ShowCopyOnly parameter still exists, it’s just showing those backups by default now.
Changed default from 30 days to 7 days of history. Another change to improve performance. If you want 30 days or any other value you can still use the @StartDate parameter as desired.
Change default log backup check from 1 hour to 1 day. Yeah, 24 hour RPO isn’t for everyone, but on many servers (mostly non-Production, we’ve found) that isn’t completely unheard of. This is only related to the message that indicates log backups aren’t happening for databases not in Simple recovery model. You can use @Mode = 1 or @Mode = 2 and even the new @RPO parameter noted above to see if log backups are happening more frequently.
Added date of Failed backups to message. Previously you would only be alerted if there were failed backups in the error log, which admittedly makes us look a bit lazy. Optics matter. Anyhow, we added the date of any failed backup messages to help you troubleshoot those.
Made most issue checks database specific if @DatabaseName is populated. Now, if a value is supplied for @DatabaseName, the issues output from @Mode = 0 will show only results that database. Well, mostly. Only a few configuration issues may also show up.
Fixed an error resulting in duplicate messages of Failed backups. Because nobody wants the noise of duplicate messages.
Other minor fixes like typos and formatting. Hey, nobody’s perfect. We’re trying our best.
We hope you are finding sp_CheckBackup (and sp_CheckSecurity and sp_CheckTempdb) useful. You can download the latest version at the sp_CheckBackup GitHub Repository.
And please let us know if there are any issues or enhancements you want us to know about by using the Issues page at the GitHub repository.
