Search This Blog

EXECUTE master.dbo.xp_delete_file in Sql Server 2008

This is a quick post to show some details on this undocumented stored procedure used to delete older backup files. Parameters for this stored procedure are not documented in BoL or not readily available, may be for a good reason. But I wanted to add some details to shed some light.
1
2
3
4
5
6
7
8
EXECUTE master.dbo.xp_delete_file
   0
 , N'S:\SqlMaintFolder\'
 , N'bak'
 , N'2012-10-10T01:05:34'
 , 1
 
 
Some points to note:
  1. First parameter is ‘FileTypeSelected’, it says what kind of files to delete; 1 means Report Files, 0 means Backup files.
  2. Second parameter indicates, the path where the files are located. Make sure there is a trailing ‘\’
  3. Third parameter indicates the file extension. Make sure there isno dot in the file extension  [ '.bak' ]
  4. Fourth parameter: delete all files before this date and time.
  5. Fifth parameter indicates if you want to delete files from sub-folders or not.