I recently noticed that my SQL backup files were not getting deleted, and after some research, found the commands to delete old backup files.

 

Xp_delete_file take five parameters:

  1. File Type = 0 for backup files or 1 for report files.
  2. Folder Path = The folder to delete files. The path must end with a backslash "\".
  3. File Extension = This could be 'BAK' or 'TRN' or whatever you normally use.
  4. Date = The cutoff date for what files need to be deleted.
  5. Subfolder = 0 to ignore subfolders, 1 to delete files in subfolders.

EXECUTE master.dbo.xp_delete_file 0, N'FullDirectoryName, not including backslash', N'bak', N'cutoff date and time'

where the cutoff date and time is YYYY-MM-DD HH:MM:SS.

An example of this looks like,

EXECUTE master.dbo.xp_delete_file 0, N'C:\BACKUP\BlogDirectory', N'bak', N'2019-12-01 00:00:00'

This command needs to be executed in SQL Management Studio, after right clicking on the correct database, and clicking New Query.