2008删除MSDB外键进行数据库备份记录删除

use msdb

ALTER TABLE [backupmediafamily] DROP CONSTRAINT FK__backupmed__media__0A688BB1;
ALTER TABLE [backupset] DROP CONSTRAINT FK__backupset__media__10216507;
ALTER TABLE [backupfilegroup] DROP CONSTRAINT FK__backupfil__backu__14E61A24;
ALTER TABLE [backupfile] DROP CONSTRAINT FK__backupfil__backu__19AACF41;
ALTER TABLE [restorehistory] DROP CONSTRAINT FK__restorehi__backu__1E6F845E;
ALTER TABLE [restorefile] DROP CONSTRAINT FK__restorefi__resto__2057CCD0;
ALTER TABLE [restorefilegroup] DROP CONSTRAINT FK__restorefi__resto__22401542;

go

use msdb

truncate table backupfile

truncate table backupfilegroup

truncate table backupmediafamily

truncate table backupmediaset

truncate table backupset

truncate table restorefile

truncate table restorefilegroup

truncate table restorehistory

truncate table log_shipping_monitor_history_detail

go

use msdb

ALTER TABLE [backupmediafamily] ADD CONSTRAINT FK__backupmed__media__0A688BB1 FOREIGN KEY (media_set_id) REFERENCES backupmediaset(media_set_id);
ALTER TABLE [backupset] ADD CONSTRAINT FK__backupset__media__10216507 FOREIGN KEY (media_set_id) REFERENCES backupmediaset(media_set_id);
ALTER TABLE [backupfilegroup] ADD CONSTRAINT FK__backupfil__backu__14E61A24 FOREIGN KEY (backup_set_id) REFERENCES backupset(backup_set_id);
ALTER TABLE [backupfile] ADD CONSTRAINT FK__backupfil__backu__19AACF41 FOREIGN KEY (backup_set_id) REFERENCES backupset(backup_set_id);
ALTER TABLE [restorehistory] ADD CONSTRAINT FK__restorehi__backu__1E6F845E FOREIGN KEY (backup_set_id) REFERENCES backupset(backup_set_id);
ALTER TABLE [restorefile] ADD CONSTRAINT FK__restorefi__resto__2057CCD0 FOREIGN KEY (restore_history_id) REFERENCES restorehistory(restore_history_id);
ALTER TABLE [restorefilegroup] ADD CONSTRAINT FK__restorefi__resto__22401542 FOREIGN KEY (restore_history_id) REFERENCES restorehistory(restore_history_id);

go
dbcc shrinkdatabase('msdb')
go

转载于:https://www.cnblogs.com/anivie/archive/2011/04/21/2023692.html