SQL Server Development:  Do you ever need to find a way to manage old backup files that are located in folders specifically named for each day of the week? I recently did, but I didn't want to take the time to create a maintenance task for each day by name. Here's a way to delete old backup and report files with the undocumented stored procedure xp_delete_file.

Xp_delete_file is undocumented. This means that it is not officially supported, and can change without notice. So, use it at your own risk.

 

Here is a listing of what the parameters do to the best of my knowledge:

 

EXECUTE master.dbo.xp_delete_file
@FileTypeToDelete [(0)= FileBackup, (1)= FileReport]
,@FolderPath [N'C:\Backups']
,@FileExtension [N'bak', N'trn', N'log' - will only delete certain types of files]
,@DateTimeOfOldestFile [N'2011-09-12T12:05:43']
,@DeleteFromSubfolderFlag [(0) = Only delete files in the FolderPath, (1) = Delete files that are in subfolders of the FolderPath] 

_________________________________________________________________________ 

 

I have shared my script below:

 

 

DECLARE @dtnm VARCHAR(30)

DECLARE @folderpath VARCHAR(500)

DECLARE @DeleteDate NVARCHAR(50)

DECLARE @DeleteDateTime DATETIME

 

-- Get date 6 days ago

SET @DeleteDateTime = DATEADD(dd, -6, GETDATE())

-- Put together date and time in expected format

SET @DeleteDate =(SELECT REPLACE(CONVERT(NVARCHAR, @DeleteDateTime, 111), '/', '-') + 'T' + CONVERT(NVARCHAR, @DeleteDateTime, 108))

-- Get the name of the day of the week

SET @dtnm = DATENAME(dw, GETDATE())

-- Provide the backup folder and add the date folder to the path

SET @folderpath = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\UserDBs\AdventureWorks\'+@dtnm

 

-- Delete using xp_delete_File

EXECUTE master.dbo.xp_delete_file0,@folderpath,N'bak',@DeleteDate

GO

_______________________________________________________________________________