Take the Drudgery Out of Making Sure Your Databases Are Being Backed Up Properly

As Eric Peterson noted in his Reader to Reader article, “Keep Track of Your Backups” (September 2007, InstantDoc ID 96264), one of the most important tasks of a DBA is to perform backups. However, picking through event logs to ensure the databases are being backed up properly can be quite time-consuming, especially for DBAs who manage multiple servers and databases. Like Eric, I wrote some code, sp_ShowBackups, to take the drudgery out of examining event logs and backup jobs. This store procedure generates an easy-to-read report that details all the various database backups that have occurred on a server within the specified number of days.

The sp_ShowBackups stored procedure relies on the vBackupHistory view, which needs to be created in the msdb database. This view joins several backup-related tables together and can be quite useful on its own. You can download sp_ ShowBackups, vBackupHistory, and a sample report (sp_ShowBackups_SampleOutput.txt) by clicking the 98570.zip hotlink at the top of this page.

By default, sp_ShowBackups is set up to obtain the backup information for the past day on all the databases on the server on which you run the stored procedure. To specify a different number of days, you can change the @days parameter’s value from 1 to the desired number of days. (See Listing 1.) If you want to obtain the backup information for only one database rather than all the databases on the server, you can change the @dbname parameter’s value from % to the database’s name.

The report produced by sp_ShowBackups has five sections. The first section is “BACKUP SUMMARY,” which consists of two components, as Figure 1 shows. One component lists the type and number of backups that have occurred on each database since that database was created (assuming the database’s backup history hasn’t been deleted from the system tables). The other component uses YES and NO indicators to reveal whether or not a particular type of backup has occurred on each database within the specified time period. Vigilant DBAs can look at either component to determine whether all the backups from the previous day completed successfully. However, I prefer the second component because I’ve found that the visual presentation of the YES and NO indicators lets me quickly detect problems with just a cursory glance.

The next three sections—“FULL DATABASE BACKUPS,” “INCREMENTAL BACKUPS,” and “TRANSACTION LOG BACKUPS (100 max)”— provide detailed information for full backups, incremental backups, and transaction log backups. The details include the size of the backup, the duration (HH:MM:SS), start time, finish time, and the user who initiated the backup. The meticulous DBA can use this information to quickly and easily find out the size and duration of the backups. The security-conscious DBA can also use this portion of the report to look for any unauthorized backups.

The final section is “BACKUP THROUGHPUT (MB/s),” which specifies how many megabytes per second the backup processes are achieving. This data is broken down by backup type and month. This section is mainly for DBAs who want to brag about how fast their I/O subsystem is.

I originally wrote sp_Show- Backups for SQL Server 2000. However, it works well on SQL Server 2005, which is a nice surprise.