Continuing on my recent trend of using undocumented stored procedures, this week I thought we can cover using xp_delete_file to purge old backup files from a folder. This is stored procedure used by the maintenance plans to clean up old backup files, but it makes for a handy purge tool when creating your own backup scripts.
Let’s suppose we have a simple script (taken from a recent post on stackoverflow.com) to backup all databases on a server.
DECLARE @name VARCHAR(50); -- Database name DECLARE @path VARCHAR(256); -- Path for backup files DECLARE @fileName VARCHAR(256); -- Filename for backup DECLARE @fileDate VARCHAR(20); -- Used for file name -- Path to backups. SET @path = ‘D:Backup’; -- Get date to include in file name. SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112); -- Dynamically get each database on the server. DECLARE db_cursor CURSOR FOR SELECT name FROM master.sys.databases WHERE name NOT IN ('master','model','msdb','tempdb'); OPEN db_cursor; FETCH NEXT FROM db_cursor INTO @name; -- Loop through the list to backup each database. WHILE @@FETCH_STATUS = 0 BEGIN -- Build the path and file name. SET @fileName = @path + @name + '_' + @fileDate + '.BAK'; -- Backup the database. BACKUP DATABASE @name TO DISK = @fileName WITH INIT; -- Loop to the next database. FETCH NEXT FROM db_cursor INTO @name; END -- Clean up. CLOSE db_cursor; DEALLOCATE db_cursor; GO
The script will create a new database backup file of every database on the server each time it’s executed. However, the path may not have unlimited disk space. This is where we need to use xp_delete_file
Xp_delete_file takes 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.
To incorporate this into our backup script, we just need to add another parameter to define the cutoff date.
DECLARE @DeleteDate DATETIME = DATEADD(wk,-1,GETDATE());
In this example, we want to purge any backup files that are more than one week old and have a file extension of ‘BAK’.
Next we just need to add the xp_delete_file after the backup loop completes.
EXEC master.sys.xp_delete_file 0,@path,'BAK',@DeleteDate,0;
We only need to execute xp_delete_file once with subfolder=0 parameter since all backup files are being stored in the same folder. If there was a separate subfolder for each database, then we’d change the subfolder parameter to 1 to delete files from each of those database subfolders.
The updated script would look like this.
DECLARE @name VARCHAR(50); -- Database name DECLARE @path VARCHAR(256); -- Path for backup files DECLARE @fileName VARCHAR(256); -- Filename for backup DECLARE @fileDate VARCHAR(20); -- Used for file name DECLARE @DeleteDate DATETIME =DATEADD(wk,-1,GETDATE()); -- Cutoff date -- Path to backups. SET @path = 'D:Backup'; -- Get date to include in file name. SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112); -- Dynamically get each database on the server. DECLARE db_cursor CURSOR FOR SELECT name FROM master.sys.databases WHERE name NOT IN ('master','model','msdb','tempdb'); OPEN db_cursor; FETCH NEXT FROM db_cursor INTO @name; -- Loop through the list to backup each database. WHILE @@FETCH_STATUS = 0 BEGIN -- Build the path and file name. SET @fileName = @path + @name + '_' + @fileDate + '.BAK'; -- Backup the database. BACKUP DATABASE @name TO DISK = @fileName WITH INIT; -- Loop to the next database. FETCH NEXTFROM db_cursor INTO @name; END -- Purge old backup files from disk. EXEC master.sys.xp_delete_file 0,@path,'BAK',@DeleteDate,0; -- Clean up. CLOSE db_cursor; DEALLOCATE db_cursor; GO
Don’t let the stored procedure file name, xp_delete_file, fool you. Microsoft has some internal code that only allows for the deletion of database and transaction log backup files. The stored procedure cannot be used to delete any other type of file.
Yes, it really is that simple.
HI
You know how SQL can backup to remote storage ( like lower tier storage ) using UNC ( ‘\\RemoteServer\SQLBackups\) for example…
Can SQL also maintain these aforementioned UNC paths?
Assuming… how did you put it, Ah yes : “However, the path may not have unlimited disk space.”.
Or does this xp_delete_file ONLY work with local storage?
Thank you for your expose on hidden gems like this one.
xp_delete_file can manage UNC paths as long as the SQL Server service account has access to it.
Hi there, Patrick. If you haven’t seen it, there’s a new extended stored procedure in SQL Server 2019 (also undocumented):
sys.xp_delete_files
And it appears to have been backported to SQL Server 2017 (possibly in CU18).
I wrote a post on it, detailing what it can do (which is a bit more than
xp_delete_file
):sys.xp_delete_files and ‘allow filesystem enumeration’: two new undocumented items in SQL Server 2019
Take care, Solomon..
Solomon,
No I had not seen that new proc yet. That looks to be way more flexible than the old version. Thanks for sharing your post!
“The path must end with a backslash.”
This statement is FALSE. It works just fine without it.
Thanks! Just what I needed. Had to grant permissions on folder to Sql Agent account too.
When using parameter Subfolder = 1 xp_delete_file will only work only one sublevel deep (first level subfolders).