xp_delete_file not removing old backups

Question

This command is not deleting backups:

EXEC xp_delete_file 0,N'F:\path\cms',N'*.bak',N'2014-01-30T21:08:04'

Also tried

EXEC xp_delete_file 0,N'F:\path\cms',N'bak',N'2014-01-30T21:08:04'

and

EXEC xp_delete_file 0,N'F:\path\cms',N'.bak',N'2014-01-30T21:08:04'

SQL Server Agent has permissions on the folder.

Answer 1

Solved: Both users for agent and sql server service need read/write/delete permissions on the backup folder.

Answer 2

Make sure that you have "Full Control" on the directory where your backups are. Unfortunately running the xp_delete_file doesn't return an error if you don't have the correct privs nor do you see anything in your SQL Server agent log files.

Answer 3

Did you try:

EXEC xp_delete_file 0,N'F:\path\cms\',N'bak',N'2014-01-30T21:08:04';
--- this slash may be important ---^ 

That said, you should simply not be using this stored procedure to clean up your backup folders. It is undocumented and unsupported. Take a look at this Connect item, which complains about exactly the same problem, seven years ago to the day. Note that it is closed as "won't fix" and of particular interest should be this official statement from Terrence Nevins of Microsoft:

The stored procedure was never intended to be called by an end user and the chances that you could ever be successful in having it do what you intend are almost zero. I took a quick peek at the implementation we are using and it takes some very specialzed arguments for a very specific task.

If you detemine that you do need to access the file system directly from a stored proc, then I would imagine you would need to write that yourself in .net. Or perhaps there is already a vendor that provides this.

We don't document this XP or promote its usage by anyone for very good reasons. Most of all, they can 'go away' from release to release.

Answer 4

The path name must end with a \ and the extension must not contain the dot. Then it will work.