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.
End of Article
