Post
Ask Question

How To Delete SQL Server Old Databse Backup Files

In this article, I will guide you on how to delete old backup files on an SQL Server Database.

Though we have moved to the cloud, there are still some projects that have their own server machines, on which we do deployment, SQL Server Database backups, and other jobs. 

There are two ways we can delete old database backup files:

  1. Using C# Code (delete files from location)
  2. Using SQL Script (master.sys.xp_delete_file) 

Delete SQL Server Database backup files Using C# Code

Set a path in App.config file.

<appSettings>		
		<add key="path" value="C:\WorkingProjects\Practice\DataBase\Employee\DBBackup"/>
</appSettings>

In Program.cs file.

In my case, I have set it to delete database backup files that are older than one week.

Note

  • I have used CreationTime, which will return the date and time when the database backup was created. 
  • .bak is an extension of SQL Server backup file. 
var directorypath = ConfigurationManager.AppSettings["path"].ToString();
var files = Directory.GetFiles(directorypath, "*.bak");

foreach (var file in files)
 {
   var _file = new FileInfo(file);
   if (_file.CreationTime < DateTime.Now.AddDays(-7)) // weekly
        _file.Delete(); // Delete File
 }

SQL Server DB backups which we want to delete.

Before Code execution (Before Old DB backup Delete)

 Before Delete

After Code execution (After Old DB backup Delete).

Now, we can verify whether our old database backup is deleted or not.

After

Delete SQL Server Database backup files Using SQL Query (xp_delete_file)

DECLARE @name NVARCHAR(500); -- Database name
DECLARE @path NVARCHAR(500); -- Path for backup files
DECLARE @fileName VARCHAR(500); -- Filename for backup
DECLARE @fileDate VARCHAR(20) = CONVERT(VARCHAR(20),GETDATE(),112); -- Used for file name
DECLARE @FileExtension nvarchar(4) = N'.BAK' -- file extension
DECLARE @DeleteDate DATETIME = DATEADD(wk,-1,GETDATE()); -- last week date
  
-- Path to backups.
SET @path = 'C:\WorkingProjects\Practice\DataBase\Employee\DBBackup' 
 
-- 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','ReportServer','ReportServerTempDB');
  
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 + @FileExtension;
		  --select @fileName

		  -- Loop to the next database.
		  FETCH NEXT FROM db_cursor INTO @name;
	END

--Delete backup files  
EXEC master.sys.xp_delete_file 0,@path,'BAK',@DeleteDate,0;
  
CLOSE db_cursor;
DEALLOCATE db_cursor;
GO

xp_delete_file takes five parameters:

  1. File Type - 0 for backup files or 1 for report files.
  2. Folder Path - The folder to delete files.
  3. File Extension - This could be ‘BAK’.
  4. Date - Cutoff date for what files need to be deleted.
  5. Subfolder - 0 to ignore subfolders, 1 to delete files in subfolders.

Before Query execution (Before Old DB backup Delete)

 Before Delete

After Query execution (After Old DB backup Delete).

Now we can verify whether our old database backup has been deleted or not.

After

Note
We can add our .exe file in the task scheduler and script in the SQL Job, which we can automate to execute as per our choice (weekly/monthly).


Similar Articles