January 29, 2010 at 10:44 am
Do any of you delete historical info related to backups from the MSDB database? (dbo.BackupSet, dbo.BackupMediaFamily, etc.)
Is it safe to do this? What are the ramifications?
January 29, 2010 at 10:51 am
Yes, the ramification is that you can't query for what is contained in a backup or have SSMS automatically do a restore. The history cleanup task in the maintenance plans will let you do this. I have done it for older data, keeping a few weeks worth of info
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
January 29, 2010 at 11:03 am
Thanks, Steve.
I'm not using SSMS for backups or restores, so I don't think that will be an issue.
My personal preference is to use tsql to do the the cleanup. Do you know of any system stored procs that could be used instead of the history cleanup task in the maintenance plans?
Also, you mentioned keeping a few weeks worth of info. Has that ever come back to haunt you? (I was thinking of keeping a few months of info.)
January 29, 2010 at 11:16 am
Hasn't come back to haunt me. I almost never use that stuff. I backup to disk files, name them by date, use that for restores.
deletes
Most DR solutions include log shipping strategies. Log shipping (LS) is an extremely inexpensive solution for DR and also one I recommend. There is little learning curve to individuals just coming into the administration career for setting LS up and maintaining the flow.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
January 29, 2010 at 11:39 am
I use the backup history information to see growth trends, so I like keeping it. I made a copy of the system stored procedure that deletes the information, and modified my version to delete transaction log & diff backup information while retaining full backup info for historical purposes.
January 29, 2010 at 12:15 pm
I stumbled upon this stored proc (which should meet my needs quite nicely): msdb.dbo.sp_delete_backuphistory
Is this the sp you were referring to, Homebrew?
January 29, 2010 at 2:36 pm
Yes. Apparently you will improve performace by keeping MSDB small and deleting old backup history. I don't need the t-log history which takes up the bulk of the space, so I made my own version that retains the full backup history.
February 2, 2010 at 3:17 am
Hey guys,
I am also looking for the sp to retain the full backups, and modified it by excluding the full backups in the conditions from the original sp.
Can you please check and let me know if this is correct?
CREATE PROCEDURE sp_delete_backuphistory
@oldest_date datetime
AS
--declare @oldest_date datetime
--set @oldest_date = '01/22/2009'
BEGIN
SET NOCOUNT ON
DECLARE @backup_set_id TABLE (backup_set_id INT)
DECLARE @media_set_id TABLE (media_set_id INT)
DECLARE @restore_history_id TABLE (restore_history_id INT)
INSERT INTO @backup_set_id (backup_set_id)
SELECT DISTINCT backup_set_id
FROM msdb.dbo.backupset
WHERE backup_finish_date < @oldest_date
AND type <> 'D'
INSERT INTO @media_set_id (media_set_id)
SELECT DISTINCT media_set_id
FROM msdb.dbo.backupset
WHERE backup_finish_date < @oldest_date
AND type <> 'D'
INSERT INTO @restore_history_id (restore_history_id)
SELECT DISTINCT restore_history_id
FROM msdb.dbo.restorehistory
WHERE backup_set_id IN (SELECT backup_set_id
FROM @backup_set_id)
AND restore_type <> 'D'
BEGIN TRANSACTION
DELETE FROM msdb.dbo.backupfile
WHERE backup_set_id IN (SELECT backup_set_id
FROM @backup_set_id)
IF (@@error > 0)
GOTO Quit
DELETE FROM msdb.dbo.backupfilegroup
WHERE backup_set_id IN (SELECT backup_set_id
FROM @backup_set_id)
IF (@@error > 0)
GOTO Quit
DELETE FROM msdb.dbo.restorefile
WHERE restore_history_id IN (SELECT restore_history_id
FROM @restore_history_id)
IF (@@error > 0)
GOTO Quit
DELETE FROM msdb.dbo.restorefilegroup
WHERE restore_history_id IN (SELECT restore_history_id
FROM @restore_history_id)
IF (@@error > 0)
GOTO Quit
DELETE FROM msdb.dbo.restorehistory
WHERE restore_history_id IN (SELECT restore_history_id
FROM @restore_history_id)
IF (@@error > 0)
GOTO Quit
DELETE FROM msdb.dbo.backupset
WHERE backup_set_id IN (SELECT backup_set_id
FROM @backup_set_id)
IF (@@error > 0)
GOTO Quit
DELETE msdb.dbo.backupmediafamily
FROM msdb.dbo.backupmediafamily bmf
WHERE bmf.media_set_id IN (SELECT media_set_id
FROM @media_set_id)
AND ((SELECT COUNT(*)
FROM msdb.dbo.backupset
WHERE media_set_id = bmf.media_set_id) = 0)
IF (@@error > 0)
GOTO Quit
DELETE msdb.dbo.backupmediaset
FROM msdb.dbo.backupmediaset bms
WHERE bms.media_set_id IN (SELECT media_set_id
FROM @media_set_id)
AND ((SELECT COUNT(*)
FROM msdb.dbo.backupset
WHERE media_set_id = bms.media_set_id) = 0)
IF (@@error > 0)
GOTO Quit
COMMIT TRANSACTION
RETURN
Quit:
ROLLBACK TRANSACTION
END
Regards
Akhil
February 2, 2010 at 10:17 am
I did a similar thing as I mentioned above. However, I did not change the system SP. I made a copy with a slightly different name and created it in my own DBA database. You should test it on test data before running it live.
February 2, 2010 at 11:15 pm
Yep. Thanks. I got that, I plan to make a copy and schedule it to run as a job on a monthly basis or so.
I hope it to be successful.
Regards
Akhil
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply