Home / server / Questions / 313698
Accepted
Sam
Asked: 2011-09-21 16:33:16 +0800 CST

Cleanup Backup Files without xp_cmdshell or maintenance plans

  • 772

Background:

We've got a backup script which needs xp_cmdshell to run. I decided that on new servers, we are not going to enable xp_cmdshell. I enabled xp_cmdshell when the job starts and turn it off at the end. The problem is that if a full backup starts and the hourly log backup runs and finishes, it will turn xp_cmdshell off and the hourly backup will fail.

This lead me down the path of trying to detect if a job is running, etc. But is there perhaps a better way to cleanup?

The actual question:

How can I delete old backup files after a successful backup job has been run?

It needs to be scriptable, which rules out maintenance plans if I'm correctly informed.

It would not use xp_cmdshell to avoid this whole job interaction thing and follow good practices.

The best scenario would let it execute from within the backup script, so it would delete backup files after a successful backup is made. If there is an error in a backup for any reason, this lets the backup continue for other databases.

Speculation: Maybe a powershell script would do the trick? That could be scripted into a job step and conditionally executed on success...but then it all happens at the end leaving backups if we fail half way through - the backup drive would then need to be manually cleaned up if we're tight on space.

Maybe I just check if the hourly job is running when I'm going to turn xp_cmdshell off in the hourly job. I'll probably go with that, but what would you do?

sql-server sql-server-2008 database-backup

1 Answers

  1. Best Answer

    I add a job step to my backup job after the backup step and run a version of the code below. This would remove any BAK files that are over 1 day old.

    declare
    @Ext NVARCHAR(4)
    ,@DelDt VARCHAR(19)
    ,@BackupPath VARCHAR(800)

select
    @Ext = 'bak'
    ,@BackupPath = 'D:\SQLBAK'

SELECT
    @DelDt = CONVERT(VARCHAR(19), DATEADD(hh, -24, GETDATE()), 126)

EXEC master.dbo.xp_delete_file 0, @BackupPath, @Ext, @DelDt
    • 0