2

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?

Sam
  • 2,020
  • 1
  • 16
  • 22

1 Answers1

0

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
Jason Cumberland
  • 1,579
  • 10
  • 13
  • I was looking more closely at the script and the xp_cmdshell was just looking to see if there were more that 1 backups in the folder - I'm not sure that is necessary though, since if the backup was not successful, this step would never have been executed. – Sam Sep 22 '11 at 15:34
  • We will also be using xp_create_subdir to create subdirs for new dbs. – Sam Sep 22 '11 at 15:49
  • That's a pretty typical use of xp_cmdshell for a backup script; we needed to stop using it for security reasons so those extended stored procs came in handy. Thanks for the answer. – Jason Cumberland Sep 22 '11 at 20:36