A script to help delete backup history on SQL Server 2000

Every now and again I'll come across a SQL Server 2000 install which hasn't been touched in forever and has no maintenance scheduled, and I have to clear up the backup history tables manually.

Normally this just requires a call to msdb.dbo.sp_delete_backuphistory but in some cases when you have a few hundred thousand (or a million!) records this can literally hang up for days. So I put a little something together to help me do it quickly.

Obviously this would be prettier if we could use SQL 2005 syntax but it also doesn't have this kind of deletion problem either.

Declare	@ToDate Datetime = '20151001'

-- Part 1

Delete From msdb.dbo.restorefile
Delete From msdb.dbo.restorefilegroup
Delete From msdb.dbo.restorehistory

-- Part 2

Set Nocount On
Set Rowcount 1000
Declare @Rows Int

Set 	@Rows = 1
While 	@Rows <> 0
Begin
	Delete	bf
	From	msdb.dbo.backupset bs
	Join	msdb.dbo.backupfile bf
	On	bs.backup_set_id = bf.backup_set_id
	Where	bs.backup_start_date < @ToDate

	Set	@Rows = @@Rowcount
	Print	'.'
End

-- Part 3

Set 	@Rows = 1
While 	@Rows <> 0
Begin
	Delete	bs
	From	msdb.dbo.backupset bs
	Where	bs.backup_start_date < @ToDate
    
	Set	@Rows = @@Rowcount
	Print 	'.'
End

-- Part 4

Exec	msdb.dbo.sp_delete_backuphistory @ToDate

-- Clean up

Set Nocount Off
Set Rowcount 0

If this doesn't work for your situation I've also seen people describe dropping the foreign keys so you can truncate the tables involved and then recreating them. That seemed a little drastic to me.