As someone who has helped design systems, I know auditing can take a back seat to more ‘pressing’ requirements; however, as anyone responsible for maintaining a system will know, a good audit trail can be immensely helpful in tracking down a problem. When it comes to backup information, SQL Server actually has a lot of good detail which can be very useful. For example, SQL Server can tell you the very first time you took a backup of your database. This script can give you this information by database.
Select MIN(backupset.backup_finish_date), backupset.database_name, backupset.server_name
INNER JOIN msdb.dbo.backupmediafamily ON backupset.media_set_id = backupmediafamily.media_set_id
GROUP BY backupset.database_name, backupset.server_name
Order by backupset.database_name, backupset.server_name
The very first backup?
While it may be nostalgic to look at the very first backup you took, it is not practical to keep all that information around? Why is this? Because, as Books On Line puts it–“When a restore [or Backup] is performed, backup history tables and restore history tables are modified.” Just like any audit table, this information grows and as each operation is performed, more data is added to the mix. Over time these operations can actually be slowed because of the amount of data.
Just like any good audit table, we need to have a plan to trim the fat on a regular basis. What to trim? Well, my thoughts are they should line up with your requirements for data longevity. If the policy says we keep backups around (even on tape) for 6 months, then I would make it six months and run the cleanup process every week. Your time may vary by application and in that case, you can set it for the longest requirement in the environment. Regular cleanup is the key–you never know how long those servers are going to be around. Isn’t that right all you SQL 2000 boxes out there? 🙂
Script It Out
Removing old records is straightforward with stored procedure sp_delete_backuphistory. You only have to provide a date and SQL Server will remove ALL backup history up to that date. I normally have a SQL Agent job, scheduled to run once a week, looking something like this.
DECLARE @BackupDate DATETIME;
SET @BackupDate = GETDATE() – 365;
Exec sp_delete_backuphistory @BackupDate;