Delete old backup files

Question

Hi All,

I am trying to automate one backup cleanup job which should delete bak files on certain conditions.

Conditions for old files cleanup as below

1] Basebackups we need for 3 days atleast
2] Dont delete anything with file groups ( Starting with "FG ")
3] RPTSTGLive backups lets keep 1 month

I am familiar with the xp_delete_file but I am not able to figure out how can I do this even with separate step in a job.

Any help must be appreciated.

@Erland Sommarskog Please help

Answer 1

Good day

Off topic 1: Do you know that you can have a better nickname without the addition of the number? When you create a new user in the QnA system, then the Engine do not even check if the username which you selected is in used. The app automatically adds a random number to your username to make it unique. You can edit your username after it was created and select a better one and even the original one which you anted without the addition of the number, if none else already use it.

Back to the question...

Important! xp_delete_file is an undocumented Extended Stored Procedure. You need to remember that it might not exists in future version or even after an update of the version, and since it is undocumented, no one will inform you that it was removed. You must use it with a lot of carful and on your own responsivity!

There’s a new Extended Stored Procedure (ESP) in SQL Server 2019, which is more flexible than the old xp_delete_file (both undocumented): sys.xp_delete_files

This sys.xp_delete_files supports standard DOS wildcard characters, which allow you to do exactly what you want - not delete files starting with FG for example. It also supports multiple fully-qualified path specifications at once.

IMPORTANT!!! sys.xp_delete_files is much more flexible but also more dangerous! It allows for example to delete all types of files and not only backups.

Always! Before deleting file, check that the path and file names are correct using the sys.dm_os_enumerate_filesystem

This function takes the following 2 parameters: @initial_directory nvarchar(255) and @search_pattern nvarchar(255) and it returns the information about all the files and folders which fit the wildcard condition. It uses the same wildcard condition as the new sys.xp_delete_files which makes it highly useful to verify what you are going to delete before you actually execute the delete

For example:

SELECT * FROM sys.dm_os_enumerate_filesystem(N'C:\_RonenExtendedEventResult\','*.dat')
GO

How to use sys.xp_delete_files` :

-- Simple scenario: delete single specific file
DECLARE @ReturnedValue INT;
EXEC @ReturnedValue = sys.xp_delete_files N'C:\_RonenExtendedEventResult\Book1.dat';
SELECT @ReturnedValue ;
GO
-- Verify the file was deleted as expected
SELECT * FROM sys.dm_os_enumerate_filesystem(N'C:\_RonenExtendedEventResult\','*.dat')
GO

-- Delete all files that fit the wildcard condition
DECLARE @ReturnedValue INT;
EXEC @ReturnedValue = sys.xp_delete_files N'C:\_RonenExtendedEventResult\*.fmt';
SELECT @ReturnedValue ;
GO

-- Delete all files that fit multiple wildcard conditions
DECLARE @ReturnedValue INT;
EXEC @ReturnedValue = sys.xp_delete_files N'C:\_RonenExtendedEventResult\*.fmt', N'C:\_RonenExtendedEventResult\*.dat';
SELECT @ReturnedValue ;
GO

Answer 2

Hi @Ronen Ariely

Name change done, thanks :)

I am getting little worried about undocumented approach.

Is there any promising way to achieve this rather than using xp_delete_file though?

Thanks