6.5 - deleting backup information

[Ken Proctor]

Does anyone know a way of deleting old backup information? We'll shortly be in a position where SQL thinks we have got a certain number of backups whereas in fact the older ones will have been copied to tape & deleted. Apart from being misleading this must also be leaving redundant data in the system/control tables & I'd prefer not to have to look at increasing their size just because of this. Anyone got any tips? Thanks very much to the guys who have responded to my previous 2 questions, being new to SQL I certainly appreciate the help.

[Gregory]

u can delete backup device that you no longer use, using Enterprise manager , under 'backup devices folder'. it will not delete physical file.
-or-
u can use sp_dropdevice with optional parameter to drop (delete) device as well as physical file.


------------
Ken Proctor at 3/12/99 8:01:41 AM

Does anyone know a way of deleting old backup information? We'll shortly be in a position where SQL thinks we have got a certain number of backups whereas in fact the older ones will have been copied to tape & deleted. Apart from being misleading this must also be leaving redundant data in the system/control tables & I'd prefer not to have to look at increasing their size just because of this. Anyone got any tips? Thanks very much to the guys who have responded to my previous 2 questions, being new to SQL I certainly appreciate the help.

[Craig]

I think you are looking for a stored procedure to cleanup your msdb..sysbackup and sysrestore logs. Here is the one I use on a monthly basis:
/*
** CRECLEAN.SQL
**
** This script creates a stored proc in MSDB to run sp_cleanbackupResotre_log
**
** This sp will delete all rows from MSDB related to backups and restores
** that are over 60 days old.
*/

USE MSDB
GO

/*
** Drop it if it exists.
*/
IF EXISTS ( SELECT name FROM sysobjects
WHERE type = 'P' AND name = 'sp_cleanbackupRestore_log' )
DROP PROCEDURE sp_cleanbackupRestore_log
GO

CREATE PROC sp_cleanbackupRestore_log
as
BEGIN
Declare @DeleteBeforeDate datetime
Select @DeleteBeforeDate = DATEADD (day,-60,getdate())
Delete from msdb.dbo.sysbackupdetail where backup_id
in (Select backup_id from sysbackuphistory where backup_start <=
@DeleteBeforeDate)
Delete from msdb.dbo.sysbackuphistory where backup_start <= @DeleteBeforeDate
Delete from msdb.dbo.sysrestoredetail where restore_id
in (Select restore_id from sysrestorehistory where backup_start <=
@DeleteBeforeDate)
Delete from msdb.dbo.sysRestorehistory where backup_start <= @DeleteBeforeDate
PRINT &#34;MSDB cleanup sp complete&#34;
END
GO



------------
Gregory at 3/12/99 9:38:01 AM

u can delete backup device that you no longer use, using Enterprise manager , under &#39;backup devices folder&#39;. it will not delete physical file.
-or-
u can use sp_dropdevice with optional parameter to drop (delete) device as well as physical file.


------------
Ken Proctor at 3/12/99 8:01:41 AM

Does anyone know a way of deleting old backup information? We&#39;ll shortly be in a position where SQL thinks we have got a certain number of backups whereas in fact the older ones will have been copied to tape & deleted. Apart from being misleading this must also be leaving redundant data in the system/control tables & I&#39;d prefer not to have to look at increasing their size just because of this. Anyone got any tips? Thanks very much to the guys who have responded to my previous 2 questions, being new to SQL I certainly appreciate the help.