TRUNCATING TABLES IN MSDB DATABASE

[icemel]

Hi,

Our backupset table has grown out of control and efforts to delete the data caused log space problems.

So, we've decided to just go ahead and truncate all the backup history tables:

backupset
restorehistory
restorefile
restorefilegroup
backupfile
backupmediaset
backupmediafamily

What effect will this have in the event of restore operation? (until new, current history is create)

Thanks

 

[SQLBill]

I've never done this, so I can't help....but I'm curious why you started a new thread when you have THREAD962-1143530 on the same subject?

-SQLBill

Posting advice: FAQ481-4875

 

[icemel]

Well, the old threads dealt with deleting data from msdb, now I'm trying to find out the effects of truncating data... also I didn't bother to see what threads I'd already started - call me lazy

 

[yelworcm]

As you wish:

Lazy!

;-)

 

[yelworcm]

OK, OK.

The effects of outright truncating the tables are >probably< minimal. Enterprise Mangler may not be able to suggest where to restore a database from, but that is about all I can think of. A database can be restored onto a server that has no knowledge of that database's backup/restore history.

As always, try it on test first, yadda, yadda, yadda.

 

[mrdenny]

The hisotry tables are bascialy used for EM to populate the GUI so that you can click and it knows what the trail is that it needs to restore.

If you restore by hand then it's not much of a problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[icemel]

Great, my truncates went without a hitch (well, for the most part!)

Here's what I did in case anyone wants to know:

-- stop all backup jobs

-- dropping FK's
alter table backupset drop constraint FK__backupset__media__00200768
go
alter table restorehistory drop constraint FK__restorehi__backu__05D8E0BE
go
alter table backupmediafamily drop constraint FK__backupmed__media__7D439ABD
go
alter table backupfile drop constraint FK__backupfil__backu__02FC7413
go

-- truncate or delete - whatever works here
delete from restorefilegroup
delete from restorehistory
delete from restorefile
truncate table backupfile
delete from backupmediafamily
truncate table backupmediaset
truncate table backupset

-- check to make sure empty
select * from restorefilegroup
select * from restorehistory
select * from restorefile
select * from backupfile
select * from backupmediafamily
select * from backupmediaset
select * from backupset
select * from logmarkhistory

-- add FK's
alter table backupset add constraint FK__backupset__media__00200768 FOREIGN KEY (media_set_id) REFERENCES backupmediaset(media_set_id)
go
alter table restorehistory add constraint FK__restorehi__backu__05D8E0BE FOREIGN KEY (backup_set_id) REFERENCES msdb.dbo.backupset (backup_set_id)
go
alter table backupmediafamily add constraint FK__backupmed__media__7D439ABD FOREIGN KEY (media_set_id) REFERENCES msdb.dbo.backupmediaset (media_set_id)
go
alter table backupfile add constraint FK__backupfil__backu__02FC7413 FOREIGN KEY (backup_set_id) REFERENCES msdb.dbo.backupset (backup_set_id)
go

 

[icemel]

oh, yeah the reason you have to deal with the FK's is b/c you can't truncate backupset otherwise, which is the whole objective

 

[icemel]

Just shrunk msdb database - went from 9 GB to 15 MB in about 15 seconds - yeah!