SQL Server Magazine

As a DBA, one of the most important jobs you can do for your company is to perform nightly backups. My environment has 30 servers and 600 databases, and I needed a way to monitor all of our backup activity and summarize across multiple SQL Server machines. To verify that our DBAs are monitoring our systems, I'm required to provide a list of all backups taken over the past year for our annual SAS 70 audit.

The SQL Server GUI isn't much help in providing this information. Therefore, I wrote a procedure called cspBackupListing to help automate our backup reporting and perform backups if necessary. This procedure works in one of three ways:

1. You can use cspBackupListing to list the most current backup of all your databases. This solution is useful if you need to quickly determine the state of your current backups when the database goes down. To have cspBackupListing list the most current database backups, use the command

EXECUTE cspBackupListing ‘All'

Table 1 shows the results of running cspBackupListing with the All parameter. (Note that the All parameter is actually optional, because the procedure defaults to all databases even if you don't use this parameter.) When viewing the results, note that the Success or Failure notation in the Status column doesn't tell you if the backup job was successful. Instead, it tells you whether the backup is less than 24 hours old (Success) or not (Failure). Thus, you can quickly scan for problems without needing to decipher a datetime field.

2. You can use cspBackupListing to back up any database with a previous backup older than 24 hours. As you can see in < a href="/Files/09/96264/Table_01.gif">Table 1, one of my database backups is more than 24 hours old. Using the Fix option generates a backup of this database. You must modify the Fix section to match your corporate standards or if you aren't using backup devices with the same format. The current procedure uses a device backup method. All of my devices are named the same as the database, with "_file" attached to the end. In your environment, you might need to change the device name, add a file location, or add a datetime value to make the filenames unique. To have cspBackupListing back up databases older than 24 hours, use the command

EXECUTE cspBackupListing ‘Fix'

Table 2 shows the results.

3. You can use cspBackupListing to list all the backups for one database. An example of when this solution might be useful is if a user deleted all the rows from the payroll table after the last run and needs the history restored to process the next payroll run. As long as you've archived the files to tape for restore, cspBackupListing can tell you what backups were taken and when. This type of report would also come in handy if you needed to provide an SAS 70 auditor with all your backups for the past six months. To have cspBackupListing list all the backups for a database, simply run the procedure and list the database name, as in the command

EXECUTE cspBackupListing ‘Analysis'

Table 3 shows the results of listing backups for the analysis database.

I wrote cspBackupListing for SQL Server 2005. Before you can use this procedure, you need to make a few customizations. The cspBackupListing.sql file, which you can download from SQL Server Magazine's Web site, describes the changes you need to make to get the procedure working in your environment. (Go to http://www.sqlmag.com, enter 96264 in the InstantDoc ID text box, then click the 96264.zip hotlink.) I hope that this solution works as well for you as it has in my shop.
—Eric Peterson, President, Peterson American Consulting

End of Article