[sourcecode language=”sql”]
Select MIN(backupset.backup_finish_date), backupset.database_name, backupset.server_name
FROM msdb.dbo.backupset
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
[/sourcecode]

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 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.

History Cleanup

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.

[sourcecode language=”sql”]
USE msdb
GO
DECLARE @BackupDate DATETIME;
SET @BackupDate = GETDATE() – 365;

Exec sp_delete_backuphistory @BackupDate;
[/sourcecode]