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:
- Using C# Code (delete files from location)
- Using SQL Script (master.sys.xp_delete_file)
Delete SQL Server Database backup files Using C# Code
Set a path in App.config file.
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.
SQL Server DB backups which we want to delete.
Before Code execution (Before Old DB backup Delete)
After Code execution (After Old DB backup Delete).
Now, we can verify whether our old database backup is deleted or not.
Delete SQL Server Database backup files Using SQL Query (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.
- File Extension - This could be ‘BAK’.
- Date - Cutoff date for what files need to be deleted.
- Subfolder - 0 to ignore subfolders, 1 to delete files in subfolders.
Before Query execution (Before Old DB backup Delete)
After Query execution (After Old DB backup Delete).
Now we can verify whether our old database backup has been deleted or not.
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).