First, this is an undocumented proc, and there’s nothing in BOL on this. Second, it’s a holdover from previous versions, so you’d be better served by using Powershell or some other type of scripting mechanism. This procedure is not supposed to be supported in the future, so I’d remove this from your code. In fact, if you want to just remove these, use Remove-DbaBackup from dbatools.

If you use this, or want to know what to refactor, Patrick Keisler has a nice post on the proc as does Andy Leonard, and there’s a parameter list on StackOverflow. I dug in recently as a customer was having issues, and I needed to refresh my memory.

Essentially, there are a few parameters that you use with this procedure, but bear in mind this only deletes SQL Server backup files or report files. You choose this with the first parameter, a 0 for backup files, and a 1 for report files.  The rest of the items are fairly self-explanatory, but keep a few things in mind.

First, the date is a datetime value. Meaning if you just include a date, this is the beginning of the day (midnight). You can see this here. I’ve got some backup files.

I then run this code:

EXEC xp_delete_file 
   0
   , N'D:\SQLServerBackup\MSSQL13.SQL2016\MSSQL\Backup'
   , N'bak'
   , '20170901'

Now, I see this:

The file from Sept 1 still exists because it’s at 9:56am, and the parameter is midnight (2017-09-01 00:00:00). Keep that in mind, and use the appropriate values. If I’d run this:

EXEC xp_delete_file 
   0
   , N'D:\SQLServerBackup\MSSQL13.SQL2016\MSSQL\Backup'
   , N'bak'
   , '20170901 10:00:00am'

The file from Sept 1 is removed.

Next, you need to use the extension to decide which files to remove. That means you might need to have separate calls for .bak and .trn (and .dff), or just remove all old files. The choice depends on what your requirements may be.

Lastly, make sure that if you use separate folders for each database, you set the last parameter.

Again, I wouldn’t use this command, especially not in a modern system, but if you do use this, make sure it’s working.

Filed under: Blog Tagged: administration, backup, syndicated