stored procedure to clean up old backup files

[divinyl]

Hi all,

I manage a couple of Biztalk servers in my organisation - Biztalk has a number of databases associated with it and when you install it, it automatically creates a Biztalk db backup job which you are supposed to use (and not modify) for all Biztalk backups. Unfortunately this job does not clean up the backup files from the file system - and Microsoft does not provide anything to help with this. So i found a stored procedure on the net that should do this for me, but for some reason it is not deleting the old backup files. It executes without errors, but the files remain. I'd appreciate if someone could have a look through the sproc & let me know what i am doing wrong. I have a feeling it has something to do with the number of files in the backup set but i've tried various things and no luck....


<code>

CREATE PROCEDURE [dbo].[sp_DeleteBackupHistoryAndFiles] @DaysToKeep smallint = null

AS

BEGIN

set nocount on

IF @DaysToKeep IS NULL OR @DaysToKeep <= 0

RETURN

/*

Only delete full sets

If a set spans a day such that some items fall into the deleted group and the other don't don't delete the set

*/


DECLARE DeleteBackupFiles CURSOR

FOR SELECT 'del ' + [BackupFileLocation] + '\' + [BackupFileName] FROM [adm_BackupHistory]

WHERE datediff( dd, [BackupDateTime], getdate() ) >= @DaysToKeep

AND [BackupSetId] NOT IN ( SELECT [BackupSetId] FROM [dbo].[adm_BackupHistory] [h2] WHERE [h2].[BackupSetId] = [BackupSetId] AND datediff( dd, [h2].[BackupDateTime], getdate() ) < @DaysToKeep )

DECLARE @cmd varchar(400)

OPEN DeleteBackupFiles

FETCH NEXT FROM DeleteBackupFiles INTO @cmd

WHILE (@@fetch_status <> -1)

BEGIN

IF (@@fetch_status <> -2)

BEGIN

EXEC master.dbo.xp_cmdshell @cmd, NO_OUTPUT

delete from [adm_BackupHistory] WHERE CURRENT OF DeleteBackupFiles

print @cmd

END

FETCH NEXT FROM DeleteBackupFiles INTO @cmd

END


CLOSE DeleteBackupFiles

DEALLOCATE DeleteBackupFiles


END

</code>

Thanks all!
Div

 

[JohnBates]

divinyl,

Are you executing the proc as a Job, or thru Query Analyzer?

xp_cmdshell requires high-level permissions (sa I think).
I would think the job would fail if your problem was related to permissions, but ....

John

... unrelated to your post: Divinyl was a cool pop group from the UK in the late '80s. Did you change careers ?

 

[mrdenny]

An important piece of info you forgot to provide.

SQL 2000 or SQL 2005?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

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

http://www.mrdenny.com