Dry-run xp_delete_file Before Actually Deleting Files?

xp_delete_file doesn’t really have a built-in dry-run option to preview which files it would remove. But there’s a simple workaround, and that’s exactly what this post will cover.  

We know that we can use the undocumented extneded stored procedure master.dbo.xp_delete_file to delete files, specifically the backup files, even the SQL Server maintenance plans commonly use it to delete old backup files based on their age. Here is a link to a blog post that I think neatly and succinctly explains the xp_delete_file:

https://www.sqlservercentral.com/blogs/using-xp_delete_file

Now as mentioned above, it is not possible to natively list the files that will be deleted by master.dbo.xp_delete_file before running the deletion. It simply deletes backup or report files matching the criteria without returning the list of targeted files.

However, you can work around this limitation by querying the filesystem, by some other ways, to see which files meet the deletion criteria before calling xp_delete_file. One of such approaches involves using the DMF sys.dm_os_enumerate_filesystem (available in SQL Server 2017 and later versions) to enumerate the files:  

• Use DMF sys.dm_os_enumerate_filesystem to list files in a folder filtered by extension.

• Filtering files further based on their last modified date compared to the cutoff date you intend to use with xp_delete_file.

• Reviewing the list to verify which files would be deleted.

The sys.dm_os_enumerate_filesystem DMF takes two required parameters:

• @initial_directory (nvarchar(255)): Your starting directory as an absolute path, like N'O:MSSQLBackup'.

No, it won't dig into subdirectories, stays flat in that initial folder. Need recursion? Try xp_dirtree (with depth > 0) or xp_cmdshell with dir /s.

• @search_pattern (nvarchar(255)): A wildcard pattern like *, *.bak, or Log??.trn to filter files and folders.

For example, to list .bak files in a folder and see their last modified dates:

Copy

SELECT *    

FROM sys.dm_os_enumerate_filesystem('O:MSSQLBackup', '*.bak')
WHERE last_write_time < DATEADD(WEEK, -1, GETDATE());

exec sp_configure 'show advanced options', 1; 
exec sp_configure 'SMO and DMO XPs', 0;  
reconfigure;

Copy    
-- Drop table if needed
-- DROP TABLE dbo.DemoFilesToDelete;

-- Create the log table if it doesn't already exist
IF OBJECT_ID('dbo.DemoFilesToDelete') IS NULL
BEGIN
    CREATE TABLE dbo.DemoFilesToDelete
    (
        full_filesystem_path NVARCHAR(512),
        last_write_time      DATETIME2,
        size_in_bytes        BIGINT,
        is_deleted           BIT DEFAULT 0
    );
END;
GO

-- Define variables
DECLARE @BackupPath NVARCHAR(512) = N'O:MSSQLBackup';  -- Backup folder path
DECLARE @CutoffDate INT = -7;                             -- Negative value for days back
DECLARE @FileExt NVARCHAR(50) = '*BAK';                   -- Filename filter pattern

INSERT INTO dbo.DemoFilesToDelete
SELECT 
    full_filesystem_path,
    last_write_time,
    size_in_bytes,
    0 AS is_deleted
FROM sys.dm_os_enumerate_filesystem(@BackupPath, @FileExt)
WHERE last_write_time < DATEADD(DAY, @CutoffDate, GETDATE())
  AND full_filesystem_path NOT IN (SELECT full_filesystem_path FROM dbo.DemoFilesToDelete)
  AND is_directory = 0
  AND is_system = 0;

-- SELECT * FROM dbo.DemoFilesToDelete;
GO

/*

Here's how this: it grabs file names from the table 
dbo.DemoFilesToDelete we populated in step 1, each with a 
little flag showing if it's been deleted yet 
(0 means nope, still there). It loops through  just those 
undeleted ones, zapping each file off the disk one by one with 
xp_delete_file, then flips the flag to mark it done. That way, 
it skips anything already handled, keeps a full history in case 
the same backup filename gets reused later, and avoids any messy 

repeat attempts.

*/

-- Declare variable to hold the file path to be deleted
DECLARE @file_to_be_deleted NVARCHAR(400);

-- Declare cursor to iterate over files not yet deleted
DECLARE DeleteCursor CURSOR LOCAL FAST_FORWARD FOR
    SELECT full_filesystem_path 
    FROM dbo.DemoFilesToDelete 
    WHERE is_deleted = 0;

OPEN DeleteCursor;

FETCH NEXT FROM DeleteCursor INTO @file_to_be_deleted;

DECLARE @count INT = 0;

-- Loop while fetch is successful
WHILE @@FETCH_STATUS = 0
BEGIN
    
    SET @count = @count + 1;
    RAISERROR('Deleting file: %s', 10, 1, @file_to_be_deleted);


    -- Uncomment this next line to actually delete the file
    -- EXEC master.dbo.xp_delete_file 0, @file_to_be_deleted;

    -- Mark the file as deleted in tracking table and record deletion time
    UPDATE dbo.DemoFilesToDelete
    SET 
        is_deleted = 1,
        deletion_time = GETDATE()
    WHERE 
        full_filesystem_path = @file_to_be_deleted
        AND is_deleted = 0;

    FETCH NEXT FROM DeleteCursor INTO @file_to_be_deleted;
END;

-- Close and deallocate cursor
CLOSE DeleteCursor;
DEALLOCATE DeleteCursor;

IF @count = 0
RAISERROR('** THERE WAS NOTHING TO DELETE **', 10, 1);