Sql Server Xp_Delete_File Parameters
Who Can Explain List of Xp_Delete_File Parameters in Sql Server. I Don't Find Msdn Document for This Stored Procedure. I Got Script of Xp_Delete_File from...
Who can explain list of xp_Delete_file parameters in sql server. I don't find msdn document for this stored procedure.
I got script of xp_Delete_file from maintenance plan but didn't understand first parameter.
4 Answers
xp_delete_file take five parameters:
- File Type = 0 for backup files or 1 for report files.
- Folder Path = The folder to delete files. The path must end with a backslash "".
- File Extension = This could be 'BAK' or 'TRN' or whatever you normally use.
- Date = The cutoff date for what files need to be deleted.
- Subfolder = 0 to ignore subfolders, 1 to delete files in subfolders.
Source How to Use xp_delete_file to Purge Old Backup Files by Patrick Keisler
Following on from the comment above, I have been testing this on SQL Server 2012 SP4 and I can confirm the syntax EXEC master.dbo.xp_delete_file 0, 'C:\Some Path\Backup file.bak' works and deletes the specific named file.
I found this to be more obvious, since I like showing parameters:
DECLARE @DeleteDate DATETIME = DATEADD(wk,-2,GETDATE());
DECLARE @ReturnVal int
EXEC @ReturnVal = master.dbo.xp_delete_file
@FileType = 0,
@FolderPath = N'U:\SQLBackups',
@FileExtension = N'bak',
@Date = @DeleteDate,
@Subfolder = 1
print @ReturnVal
Note the following extra information (tested on SQL Server 2019 in a Microsoft Windows environment... I can't speak for other OSs):
- the
@Dateparameter can include a time component; - it is accurate to at least 1 second (I haven't tested milliseconds), suggesting the parameter is of type
datetime(at least); - the SP uses the Date Modified OS property (not the Date Created).
Elena Rostova holds a Master's degree in Public Health Journalism. She covers groundbreaking medical research, holistic wellness trends, mental health awareness, and nutritional science.
