Ramblings of a DBA

Tara Kizer
posts - 165, comments - 832, trackbacks - 75

My Links

Advertisement

News

Subscribe
Search this Blog

Archives

Post Categories

Work

Delete Backup History stored procedure

Kristen recently asked why his msdb database had grown so large (750MB).  He found out that it was due to the sizes of the backup system tables.  These tables store information used for backup and restore operations.  If you've ever used the restore database wizard in Enterprise Manager (and if you have, you might want to start using Query Analyzer instead to get more familiar with backup and restore operations), you might notice that it displays a listing of previous backups to choose from for the restore.  It gathers this information from these backup system tables.  If you backup your transaction logs as often as we do (every 15 minutes), these tables can get quite large.  Microsoft provides sp_delete_backuphistory to delete this data.  As Kristen found out, this stored procedure is very slow as it uses a cursor to loop through the data.  Using sp_delete_backuphistory took over 2 hours to delete all but 10 days worth of data.  I provided to him my version of this stored procedure that uses JOINs instead of a cursor.  With slight modifications, he was able to get it working for him.  This version was able to delete all but 10 days worth of data in 32 seconds.  Here's the stored procedure:

CREATE PROC isp_DeleteBackupHistory
(@DaysToRetain int)
AS

SET NOCOUNT ON

DECLARE @Err int
DECLARE @rc int

BEGIN TRAN

 DELETE FROM msdb..restorefile
 FROM msdb..restorefile rf
 INNER JOIN msdb..restorehistory rh
 ON rf.restore_history_id = rh.restore_history_id
 INNER JOIN msdb..backupset bs
 ON rh.backup_set_id = bs.backup_set_id
 WHERE bs.backup_finish_date < (GETDATE() - @DaysToRetain)

 SET @Err = @@ERROR

 IF @Err <> 0
  GOTO Error_Exit
 
 DELETE FROM msdb..restorefilegroup
 FROM msdb..restorefilegroup rfg
 INNER JOIN msdb..restorehistory rh
 ON rfg.restore_history_id = rh.restore_history_id
 INNER JOIN msdb..backupset bs
 ON rh.backup_set_id = bs.backup_set_id
 WHERE bs.backup_finish_date < (GETDATE() - @DaysToRetain)

 SET @Err = @@ERROR

 IF @Err <> 0
  GOTO Error_Exit
 
 DELETE FROM msdb..restorehistory
 FROM msdb..restorehistory rh
 INNER JOIN msdb..backupset bs
 ON rh.backup_set_id = bs.backup_set_id
 WHERE bs.backup_finish_date < (GETDATE() - @DaysToRetain)

 SET @Err = @@ERROR

 IF @Err <> 0
  GOTO Error_Exit
 
 SELECT media_set_id, backup_finish_date
 INTO #Temp
 FROM msdb..backupset
 WHERE backup_finish_date < (GETDATE() - @DaysToRetain)

 SET @Err = @@ERROR

 IF @Err <> 0
  GOTO Error_Exit
 
 DELETE FROM msdb..backupfile
 FROM msdb..backupfile bf
 INNER JOIN msdb..backupset bs
 ON bf.backup_set_id = bs.backup_set_id
 INNER JOIN #Temp t
 ON bs.media_set_id = t.media_set_id
 WHERE bs.backup_finish_date < (GETDATE() - @DaysToRetain)

 SET @Err = @@ERROR

 IF @Err <> 0
  GOTO Error_Exit
 
 DELETE FROM msdb..backupset
 FROM msdb..backupset bs
 INNER JOIN #Temp t
 ON bs.media_set_id = t.media_set_id

 SET @Err = @@ERROR

 IF @Err <> 0
  GOTO Error_Exit
 
 DELETE FROM msdb..backupmediafamily
 FROM msdb..backupmediafamily bmf
 INNER JOIN msdb..backupmediaset bms
 ON bmf.media_set_id = bms.media_set_id
 INNER JOIN #Temp t
 ON bms.media_set_id = t.media_set_id

 SET @Err = @@ERROR

 IF @Err <> 0
  GOTO Error_Exit
 
 DELETE FROM msdb..backupmediaset
 FROM msdb..backupmediaset bms
 INNER JOIN #Temp t
 ON bms.media_set_id = t.media_set_id

 SET @Err = @@ERROR

 IF @Err <> 0
  GOTO Error_Exit

COMMIT TRAN

SET @rc = 0

GOTO isp_DeleteBackupHistory_Exit

Error_Exit:

ROLLBACK TRAN

SET @rc = -1

isp_DeleteBackupHistory_Exit:

DROP TABLE #Temp

SET NOCOUNT OFF

RETURN @rc

 

 

GO

Print | posted on Friday, July 02, 2004 9:59 AM | Filed Under [ SQL Server - Database Administration ]

Feedback

Gravatar

# Database maintenance routines

7/2/2004 1:52 PM | Ramblings of a DBA
Gravatar

# re: Delete Backup History stored procedure

Hello,
First off, this is THE best blog / SQL site I have encountered, thank you for your time - honestly.

Is there a way to configure the SP to excluded or only include certain DB's?

I have certain DB's that only need one day recovery and others that need a week.

TIA,
J
7/7/2004 7:40 AM | jeff
Gravatar

# re: Delete Backup History stored procedure

Is the backup history important though for recovery? IMO, no. You can still restore a database without this information in the backup system tables. It's just there to let you know what backups have occurred. So if you've deleted all but 2 days in these tables, you can still restore everything backup (as long as you have them) prior to those 2 days.

But yes you can modify it for a specific database. You would need an input parameter that gets the database name or database id. Then you'd need to add that input parameter to each of the queries.
7/7/2004 8:53 AM | Tara
Gravatar

# re: Delete Backup History stored procedure

Ok, thanks.
7/7/2004 11:56 AM | Jeff
Gravatar

# re: Delete Backup History stored procedure

Love the blog also. Just wanted to comment as much as i love a good stored procedure challenge and i frequently write my own don't count microsoft out just yet. they have a procedure in the msdb database that will do this for you . sp_delete_database_backuphistory will clear out your backup history just run it and pass the dbname.

pat
7/8/2004 6:36 AM | Pat Wright
Gravatar

# re: Delete Backup History stored procedure

Pat,

Did you read my blog intro? It mentions how slow that stored procedure is. It uses a cursor. Mine uses joins instead. Mine is at least 10 times faster.
7/8/2004 9:17 AM | Tara
Gravatar

# re: Delete Backup History stored procedure

Msg 547, Level 16, State 0
Server 'xxxxx', Procedure 'isp_DeleteBackupHistory', Line 72
DELETE statement conflicted with COLUMN REFERENCE constraint
'FK__backupfil__backu__02FC7413'. The conflict occurred in database 'msdb',
table 'backupfile', column 'backup_set_id'.
The statement has been terminated.
Msg 3701, Level 11, State 5
Server 'HERSQLNDB01', Procedure 'isp_DeleteBackupHistory', Line 118
Cannot drop the table '#Temp', because it does not exist in the system catalog.
(return status = 0)
7/8/2004 1:06 PM | Kevin Zou
Gravatar

# re: Delete Backup History stored procedure

Interesting. Works fine for me. Kristen had originally received foreign key errors, but he rearranged some of the code and got it to work. I posted the revised version. So it works on his system and mine. Don't know why it doesn't work for you.
7/8/2004 1:12 PM | Tara
Gravatar

# re: Delete Backup History stored procedure

Am I dense....Kristen seems to me to be a girl's name (I should know, it's my daughter's name), but you keep referring to Kristen as "He."

Anyhow, love the procs.
7/9/2004 10:26 AM | crazyjoe
Gravatar

# re: Delete Backup History stored procedure

Check out his profile on SQLTeam by clicking on his name in my blog. It says Gender: Male. I thought Kristen was a female too until I saw the profile.

7/9/2004 10:30 AM | Tara
Gravatar

# re: Delete Backup History stored procedure

Essa procedure esta dando erro.
Server: Msg 547, Level 16, State 1, Procedure sp_DeleteBackupHistory, Line 38
DELETE statement conflicted with COLUMN REFERENCE constraint 'FK__restorefi__resto__07C12930'.
the conflict occurred in database 'msdb', table 'restorefile', column 'restore_history_id'.
Server: Msg 3701, Level 11, State 1, Procedure sp_DeleteBackupHistory, Line 118
Cannot drop the table '#Temp', because it does not exist in the system catalog.
The statement has been terminated.

O que fazer...???
9/1/2004 1:34 PM | Carlos
Gravatar

# re: Delete Backup History stored procedure

I'm getting the same error as the others when running this procedure. I'm running Windows 2000 Server with SQL Server 2000 (all patched).

Any ideas on how to get this to work? I have a LOT of junk in my msdb file that will litterally take me 1 week of running to clear out. I have to find a better way!

Thanks,

Josh
6/29/2005 6:25 AM | Josh D
Gravatar

# re: Delete Backup History stored procedure

SQLTeam.com has a few ways of how to fix it. I've never gone back to fix mine though. I just added a retry step of 2 in the job step and it completes successfully always on the first or second try.

Other people have just added an index to one of the tables in the msdb database and ran MS' version of the stored procedure. I don't add indexes to any system objects, so I've never tried the performance out doing it that way.
6/29/2005 9:36 AM | Tara
Gravatar

# re: Delete Backup History stored procedure

I tried the index to speed up Microsofts stored procedure and it didn't work, I was able to get the code above to run with slight modifications to some of the delete statements, to avoid deleting media labels that were reused at a later date.
6/30/2005 2:05 PM | Shay
Gravatar

# re: Delete Backup History stored procedure

I believe....the solution to the FK issue is that there needs to be a "where statement" on the JOINS to the #temp file...re the date check.
ie "WHERE T.backup_finish_date < (GETDATE() - @DaysToRetain)"

AS IT IS - the code probably doesn't encounter any problems on 'clean systems'.
9/2/2005 8:28 AM | AndrewMurphy
Gravatar

# re: Delete Backup History stored procedure

I experienced the foreign key conflicts too, even after adding the WHERE statements in the proc. Disabled the related foreign keys, then it ran OK. Re-enabled the FKs afterwards.
10/22/2005 8:37 AM | Tim
Gravatar

# re: Delete Backup History stored procedure

yah
11/2/2005 2:38 AM | kuttappan
Gravatar

# re: Delete Backup History stored procedure

It works fine on clean systems because the restoreXXXX databases are empty. It dies when there are entries in the restorefile/history/filegroups databases.

Don't have time right now to do a workaround on it ... so my simplest solution was to do truncate table on restorefile, restorehistory and restorefilegroups.

11/19/2005 5:45 PM | Bud
Gravatar

# re: Delete Backup History stored procedure

Does anyone have any "finalized" proc that will work??? Unfortunately, I have many SQL Server installations that have tons of rows that need deleted. Running Microsofts proc will never work cause of the extradordinary amount of time it will take to clean this all up. My intnetions are to "catchup" and then run a "clean" proc monthly. Any assistance would be appreciated (The posted proc abends because of the rows in restoreXXXX tables) I can fudege this by truncating those rows as someone posted...but I am looking for a longterm solution . Thaks and sorry for rambling on....
12/21/2005 5:37 AM | Phil
Gravatar

# re: Delete Backup History stored procedure

I was trying to move forward and tried Bud's solution of truncating tables restoreXXX so I have zero rows in them. Then I tried to run the provided proc...and it still abends..... any/all help appreciated.....
12/21/2005 7:02 AM | phil
Gravatar

# re: Delete Backup History stored procedure

What I have done to workaround the problem is set the job to retry 3 times prior to completing. This fixes the problem for me, so that's why I haven't gone back and fixed the code. I believe Kristen (that's his userid and name) has a fixed proc in the forums at SQLTeam.com. He took mine and modified it as necessary. I never got around to testing his though.
12/21/2005 9:37 AM | Tara
Gravatar

# re: Delete Backup History stored procedure

Hi Tara. I cant find Kristens proc at that site...but I did some experimenting this morning and I can get 'your' proc to execute successfully with just one pass. Only thing I had to do was disable the one Foreign Key on the "backupfile" table in MSDB. I then ran your proc(successfully). I then re-enable that FK after I run your proc. All seems fine when I follow that methodology. I tried that on 2 boxes and both run fine using this....
12/21/2005 11:20 AM | phil
Gravatar

# re: Delete Backup History stored procedure

The error people spoke of has to do with the date qualifier on the first query after building the temp table. Also, I run a modified version that sets the qualifier to a consistent value at midnight.

CREATE PROC isp_DeleteBackupHistory
(@DaysToRetain int)
AS

SET NOCOUNT ON

DECLARE @Err int
DECLARE @rc int
DECLARE @DeleteDate datetime

BEGIN TRAN

set @DeleteDate = convert(datetime,convert(varchar,getdate()-@DaysToRetain,101),101)

DELETE FROM msdb..restorefile
FROM msdb..restorefile rf
INNER JOIN msdb..restorehistory rh
ON rf.restore_history_id = rh.restore_history_id
INNER JOIN msdb..backupset bs
ON rh.backup_set_id = bs.backup_set_id
WHERE bs.backup_finish_date < @DeleteDate

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

DELETE FROM msdb..restorefilegroup
FROM msdb..restorefilegroup rfg
INNER JOIN msdb..restorehistory rh
ON rfg.restore_history_id = rh.restore_history_id
INNER JOIN msdb..backupset bs
ON rh.backup_set_id = bs.backup_set_id
WHERE bs.backup_finish_date < @DeleteDate

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

DELETE FROM msdb..restorehistory
FROM msdb..restorehistory rh
INNER JOIN msdb..backupset bs
ON rh.backup_set_id = bs.backup_set_id
WHERE bs.backup_finish_date < @DeleteDate

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

SELECT media_set_id, backup_finish_date
INTO #Temp
FROM msdb..backupset
WHERE backup_finish_date < @DeleteDate

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

DELETE FROM msdb..backupfile
FROM msdb..backupfile bf
INNER JOIN msdb..backupset bs
ON bf.backup_set_id = bs.backup_set_id
INNER JOIN #Temp t
ON bs.media_set_id = t.media_set_id
--WHERE bs.backup_finish_date < @DeleteDate

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

DELETE FROM msdb..backupset
FROM msdb..backupset bs
INNER JOIN #Temp t
ON bs.media_set_id = t.media_set_id

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

DELETE FROM msdb..backupmediafamily
FROM msdb..backupmediafamily bmf
INNER JOIN msdb..backupmediaset bms
ON bmf.media_set_id = bms.media_set_id
INNER JOIN #Temp t
ON bms.media_set_id = t.media_set_id

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

DELETE FROM msdb..backupmediaset
FROM msdb..backupmediaset bms
INNER JOIN #Temp t
ON bms.media_set_id = t.media_set_id

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

COMMIT TRAN

SET @rc = 0

GOTO isp_DeleteBackupHistory_Exit

Error_Exit:

ROLLBACK TRAN

SET @rc = -1

isp_DeleteBackupHistory_Exit:

DROP TABLE #Temp

SET NOCOUNT OFF

RETURN @rc





GO
1/24/2006 10:18 AM | PeterRahalski
Gravatar

# re: Delete Backup History stored procedure

Peter, I'm no SQL programmer...my questions will prove that....so with that discalimer...in your proc, you have the code

set @DeleteDate = convert(datetime,convert(varchar,getdate()-@DaysToRetain,101),101)


What exactly is that doing/mean??? What significance is the value 101 ?? Thank you.
2/22/2006 5:40 AM | Phil
Gravatar

# re: Delete Backup History stored procedure

101 is the style used for the datetime conversion. Check out CONVERT in SQL Server Books Online for details.
2/22/2006 9:43 AM | Tara
Gravatar

# re: Delete Backup History stored procedure

Thanks for this SP. It wiped out 3 years worth of useless backup / restore information from my SQL Server in just over 6 minutes.
2/28/2006 7:16 AM | Todd Nelson
Gravatar

# re: Delete Backup History stored procedure

Question....I am using the most recent updated proc that Peter had posted at this site. I am using it on servers that have log shipping enabled. On some servers, it is executing fine. On others I get an error. The only solution I have found is to disable the constraint in question and then re-run the proc....and then it runs fine. Can anyone explain why I must disable this constraint??? I would like to have this run without disabling constraints on msdb tables. If I can achieve that, then i feel comfortable scheduling this proc to run. Thanks in advance for any insight anyone can provide.....

The cut/paste of the error when trying to run the proc is:
Server: Msg 547, Level 16, State 1, Procedure Zsp_DeleteBackupHistory, Line 75
DELETE statement conflicted with COLUMN REFERENCE constraint 'FK__restorehi__backu__05D8E0BE'. The conflict occurred in database 'msdb', table 'restorehistory', column 'backup_set_id'.
Server: Msg 3701, Level 11, State 1, Procedure Zsp_DeleteBackupHistory, Line 121
Cannot drop the table '#Temp', because it does not exist in the system catalog.
The statement has been terminated.
3/10/2006 11:47 AM | Phil
Gravatar

# re: Delete Backup History stored procedure

Don't bother disabling the constraint. Just modify the job so that the job step retries 4 times. It always succeed.
3/10/2006 11:49 AM | Tara
Gravatar

# re: Delete Backup History stored procedure

Tara, I cant get it to work for me. I created a job to run the proc and set it to 4 retrys on the Advanced tab..it still abends. I then as a test ran the proc 6 times consecutively in a Sql Query Analyzer session and it abends 6 times...all with the same abend. Could it be this is something new( these servers have log shipping)...or am I not following your directions/suggestions correctly????? If I disable the constraint, then the proc executes fine. The error I get is:

Server: Msg 547, Level 16, State 1, Procedure Zsp_DeleteBackupHistory, Line 75
DELETE statement conflicted with COLUMN REFERENCE constraint 'FK__restorehi__backu__05D8E0BE'. The conflict occurred in database 'msdb', table 'restorehistory', column 'backup_set_id'.
Server: Msg 3701, Level 11, State 1, Procedure Zsp_DeleteBackupHistory, Line 121
Cannot drop the table '#Temp', because it does not exist in the system catalog.
The statement has been terminated.
3/13/2006 12:25 PM | Phil
Gravatar

# re: Delete Backup History stored procedure

Any solution to the above.....rerunning the job 6 times doesn't solve this. Any other ideas????????
3/28/2006 4:49 AM | phil
Gravatar

# re: Delete Backup History stored procedure

Did you try the code that was posted in the comments here as well? If you did and it didn't work, try searching the SQLTeam.com forums for this stored procedure. In it, I posted my solution to Kristen's post. He came across the same errors and reworked my code. He posted his final solution.
3/28/2006 9:18 AM | Tara
Gravatar

# re: Delete Backup History stored procedure

Tara, could you post what 'topic' you have this procedure saved under in the sqlteam.com site. I'm having difficulty finding yours. Thank you
3/28/2006 12:22 PM | phil
Gravatar

# re: Delete Backup History stored procedure

consider me as a lazy DBA, i have more than 500 databases on my sql server with 1 full backup each day and transaction log backup after every 10 minutes for every user database.

The size of msdb was 11 GB a month ago, i tried to run this customized stored procedure, it run for a couple of days but then i restarted my server because of patch management.

Today, the size of msdb WAS 13.5 GB with 15 million rows in each of backup system tables... what I did was I removed all the foreign key constraints from the backup system tables and run the "truncate table" command for each table. after that i shrink the database (13.5 GB to 40 MB) and recovered all the space. Then I re-created all the foreign key constraints and it hardly took 10 to 15 minutes.

please comment on it,
4/4/2006 4:10 AM | Shariq
Gravatar

# re: Delete Backup History stored procedure

Tara, thank you for sticking with me thru this. I believe I have installed a proc that does not abend because of RI issues. I have this running weekly on 20 Sql Servers and no longer get abends. I have used your proc and added the code that "SHAY" had posted in his forum response. The proc runs fine with servers with and with out Log Shipping...and all execute with success with just one pass. Your posting pointing me to the Forum allowed me to find SHAYS posting. Again, Thanks Tara.......
4/11/2006 6:23 AM | Phil
Gravatar

# re: Delete Backup History stored procedure

Phil,

Could you post the code that you are using. I am having problems with the FKey constraints.
4/20/2006 12:31 PM | Chris
Gravatar

# re: Delete Backup History stored procedure

Chris, Let me know if this solved your Fkey issues:
You'll have to make sure this cut/paste doesnt word wrap for you.



CREATE PROC Zsp_DeleteBackupHistory
(@DaysToRetain int)
AS

SET NOCOUNT ON

DECLARE @Err int
DECLARE @rc int

BEGIN TRAN

DELETE FROM msdb..restorefile
FROM msdb..restorefile rf
INNER JOIN msdb..restorehistory rh
ON rf.restore_history_id = rh.restore_history_id
INNER JOIN msdb..backupset bs
ON rh.backup_set_id = bs.backup_set_id
WHERE bs.backup_finish_date < (GETDATE() - @DaysToRetain)

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

DELETE FROM msdb..restorefilegroup
FROM msdb..restorefilegroup rfg
INNER JOIN msdb..restorehistory rh
ON rfg.restore_history_id = rh.restore_history_id
INNER JOIN msdb..backupset bs
ON rh.backup_set_id = bs.backup_set_id
WHERE bs.backup_finish_date < (GETDATE() - @DaysToRetain)

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

DELETE FROM msdb..restorehistory
FROM msdb..restorehistory rh
INNER JOIN msdb..backupset bs
ON rh.backup_set_id = bs.backup_set_id
WHERE bs.backup_finish_date < (GETDATE() - @DaysToRetain)

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

SELECT media_set_id, backup_finish_date
INTO #Temp
FROM msdb..backupset
WHERE backup_finish_date < (GETDATE() - @DaysToRetain)

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

DELETE FROM msdb..backupfile
FROM msdb..backupfile bf
INNER JOIN msdb..backupset bs
ON bf.backup_set_id = bs.backup_set_id
INNER JOIN #Temp t
ON bs.media_set_id = t.media_set_id
WHERE bs.backup_finish_date < (GETDATE() - @DaysToRetain)

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

DELETE FROM msdb..backupset
FROM msdb..backupset bs
INNER JOIN #Temp t
ON bs.media_set_id = t.media_set_id
where bs.backup_finish_date < getdate() -@DaysToRetain

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

DELETE FROM msdb..backupmediafamily
FROM msdb..backupmediafamily bmf
INNER JOIN msdb..backupmediaset bms
ON bmf.media_set_id = bms.media_set_id
INNER JOIN #Temp t
ON bms.media_set_id = t.media_set_id
where not exists
(select media_set_id from msdb..backupset bs
where bs.media_set_id=t.media_set_id and bs.backup_finish_date> (getdate()-@DaysToRetain))

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

DELETE FROM msdb..backupmediaset
FROM msdb..backupmediaset bms
INNER JOIN #Temp t
ON bms.media_set_id = t.media_set_id
where not exists
(select media_set_id from msdb..backupset bs
where bs.media_set_id=t.media_set_id and bs.backup_finish_date> (getdate()-@DaysToRetain))


SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

COMMIT TRAN

SET @rc = 0

GOTO isp_DeleteBackupHistory_Exit

Error_Exit:

ROLLBACK TRAN

SET @rc = -1

isp_DeleteBackupHistory_Exit:

DROP TABLE #Temp

SET NOCOUNT OFF

RETURN @rc
GO
4/21/2006 8:27 AM | Phil
Gravatar

# re: Delete Backup History stored procedure

Phil,

Thanks. I will try it out.
5/4/2006 7:18 AM | Chris
Gravatar

# Database maintenance routines

8/14/2006 6:28 PM | Ramblings of a DBA
Gravatar

# re: Delete Backup History stored procedure

BTW, for SQL Server 2005, you will need to include another table, backupfilegroup, before the delete for backupfile.

Below is the code for the stored procedure I am using:


CREATE PROCEDURE [dbo].[Delete_Backup_History]
(@days_to_retain int = 186)
AS
BEGIN
SET NOCOUNT ON

DECLARE @Err int
DECLARE @rc int
DECLARE @target_date datetime

SET @target_date = GETDATE() - @days_to_retain

BEGIN TRAN

DELETE FROM msdb..restorefile
FROM msdb..restorefile rf
INNER JOIN msdb..restorehistory rh ON rf.restore_history_id = rh.restore_history_id
INNER JOIN msdb..backupset bs ON rh.backup_set_id = bs.backup_set_id
WHERE bs.backup_finish_date < @target_date

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

DELETE FROM msdb..restorefilegroup
FROM msdb..restorefilegroup rfg
INNER JOIN msdb..restorehistory rh ON rfg.restore_history_id = rh.restore_history_id
INNER JOIN msdb..backupset bs ON rh.backup_set_id = bs.backup_set_id
WHERE bs.backup_finish_date < @target_date

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

DELETE FROM msdb..restorehistory
FROM msdb..restorehistory rh INNER JOIN msdb..backupset bs
ON rh.backup_set_id = bs.backup_set_id
WHERE bs.backup_finish_date < @target_date

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

SELECT media_set_id, backup_finish_date
INTO #Temp
FROM msdb..backupset
WHERE backup_finish_date < @target_date

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

DELETE FROM msdb..backupfilegroup
FROM msdb..backupfilegroup bfg
INNER JOIN msdb..backupset bs ON bfg.backup_set_id = bs.backup_set_id
INNER JOIN #Temp t ON bs.media_set_id = t.media_set_id
WHERE bs.backup_finish_date < @target_date

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

DELETE FROM msdb..backupfile
FROM msdb..backupfile bf
INNER JOIN msdb..backupset bs ON bf.backup_set_id = bs.backup_set_id
INNER JOIN #Temp t ON bs.media_set_id = t.media_set_id
WHERE bs.backup_finish_date < @target_date

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

DELETE FROM msdb..backupset
FROM msdb..backupset bs
INNER JOIN #Temp t ON bs.media_set_id = t.media_set_id
WHERE bs.backup_finish_date < @target_date

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

DELETE FROM msdb..backupmediafamily
FROM msdb..backupmediafamily bmf
INNER JOIN msdb..backupmediaset bms ON bmf.media_set_id = bms.media_set_id
INNER JOIN #Temp t ON bms.media_set_id = t.media_set_id
WHERE NOT EXISTS (SELECT media_set_id FROM msdb..backupset bs
WHERE bs.media_set_id = t.media_set_id
AND bs.backup_finish_date >= @target_date)

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

DELETE FROM msdb..backupmediaset
FROM msdb..backupmediaset bms
INNER JOIN #Temp t ON bms.media_set_id = t.media_set_id
WHERE NOT EXISTS (SELECT media_set_id FROM msdb..backupset bs
WHERE bs.media_set_id = t.media_set_id
AND bs.backup_finish_date >= @target_date)


SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

COMMIT TRAN

SET @rc = 0

GOTO isp_DeleteBackupHistory_Exit

Error_Exit:

ROLLBACK TRAN

SET @rc = -1

isp_DeleteBackupHistory_Exit:

DROP TABLE #Temp

SET NOCOUNT OFF

RETURN @rc
END



11/17/2006 1:11 PM | Randy
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET