Pruning SQL 2005 backup history

When trying to prune the backup history of a SQL 2005 Server (sp3) I kept getting the error.

Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint “FK__backupfil__backu__793DFFAF”.
The conflict occurred in database “msdb”, table “dbo.backupfilegroup”, column ‘backup_set_id’.

The FIX was to drop the constraints, do the dirty deed, then re-create the constraints.

Here’s the code I used …

--PruneBackupHistory_2005.sql

-- remove foreign keys
use msdb
go
--ALTER TABLE [restorefile] drop CONSTRAINT FK__restorefi__resto__00DF2177;
ALTER TABLE [restorefilegroup] drop CONSTRAINT FK__restorefi__resto__02C769E9;
ALTER TABLE [backupmediafamily] drop CONSTRAINT FK__backupmed__media__72910220;
ALTER TABLE [backupset] drop CONSTRAINT FK__backupset__media__76619304;
ALTER TABLE [backupfilegroup] drop CONSTRAINT FK__backupfil__backu__793DFFAF;
ALTER TABLE [backupfile] drop CONSTRAINT FK__backupfil__backu__7C1A6C5A;
ALTER TABLE [restorehistory] drop CONSTRAINT FK__restorehi__backu__7EF6D905;
go

-- empty tables
truncate table backupfile
truncate table backupfilegroup
truncate table backupmediafamily
truncate table backupmediaset
truncate table backupset
truncate table restorefile
truncate table restorefilegroup
truncate table restorehistory
go

-- replace foreign keys
--ALTER TABLE [restorefile] ADD CONSTRAINT FK__restorefi__resto__00DF2177 FOREIGN KEY (restore_history_id) REFERENCES restorehistory (restore_history_id);
ALTER TABLE [restorefilegroup] ADD CONSTRAINT FK__restorefi__resto__02C769E9 FOREIGN KEY (restore_history_id) REFERENCES restorehistory (restore_history_id);
ALTER TABLE [backupmediafamily] ADD CONSTRAINT FK__backupmed__media__72910220 FOREIGN KEY (media_set_id) REFERENCES backupmediaset (media_set_id);
ALTER TABLE [backupset] ADD CONSTRAINT FK__backupset__media__76619304 FOREIGN KEY (media_set_id) REFERENCES backupmediaset (media_set_id);
ALTER TABLE [backupfilegroup] ADD CONSTRAINT FK__backupfil__backu__793DFFAF FOREIGN KEY (backup_set_id) REFERENCES backupset (backup_set_id);
ALTER TABLE [backupfile] ADD CONSTRAINT FK__backupfil__backu__7C1A6C5A FOREIGN KEY (backup_set_id) REFERENCES backupset (backup_set_id);
ALTER TABLE [restorehistory] ADD CONSTRAINT FK__restorehi__backu__7EF6D905 FOREIGN KEY (backup_set_id) REFERENCES backupset (backup_set_id);
go
dbcc shrinkdatabase ('msdb')
go

Its a bit of a sledge-hammer, but that’s just the sort of day I had.