Problem: You want to remove old SQL Server backup files (older than X days) from the backup folder to free drive space. You want to do this using T-SQL and not by using a Maintenance Plan or a Windows Scheduled Task. The advantage of using T-SQL is that you can keep the administrative tasks inside the database server and keep them as part of a database backup when you backup the database.

Solution: In this article, we will discuss a solution that involves creating a stored procedure called usp_DeleteOldBackupFiles. This procedure takes the backup files folder location (@BackupFolderLocation), the files suffix (@FilesSuffix), and the number of days old (@DaysToDelete) as parameters. It uses the files’ modified date to determine which files to delete.

Here is an example of how the procedure works:

CREATE PROCEDURE usp_DeleteOldBackupFiles (
@BackupFolderLocation VARCHAR(30),
@FilesSuffix VARCHAR(3),
@DaysToDelete SMALLINT
)
AS
BEGIN
 DECLARE @delCommand VARCHAR(400)

 IF UPPER (@FilesSuffix) IN ('BAK','TRN') 
 BEGIN
  SET @delCommand = CONCAT('FORFILES /p ' ,
    @BackupFolderLocation,
    ' /s /m '  ,
    '*.'   , 
    @FilesSuffix ,
    ' /d '  ,
    '-'   , 
    ltrim(Str(@DaysToDelete)),
    ' /c ' ,
    '"'  ,
    'CMD /C del /Q /F @FILE',
    '"')

  PRINT @delCommand
  EXEC sys.xp_cmdshell @delCommand
 END
END
GO

To use the procedure, you need to provide the backup folder location, the files suffix, and the number of days old. For example, if you want to remove all the .BAK files from the folder C:\SQL\Backup and all its sub-folders that are older than 10 days, you would execute the following command:

EXEC usp_DeleteOldBackupFiles @BackupFolderLocation='c:\SQL\Backup', @FilesSuffix='bak', @DaysToDelete=10

The procedure will delete all files with a suffix BAK that are older than 10 days from the current date.

Things to note:

• The procedure was tested with SQL Server 2012 and SQL Server 2014 Developer editions.
• The xp_cmdshell option needs to be enabled. If the option is not enabled, you will get an error message.
• If the backup folder directory does not exist, nothing will be deleted.
• If no BAK or TRN files older than X days exist, nothing will be deleted.
• Suffix allowed values are BAK and TRN. This protects from accidental deletion of other types of files.
• A good practice is to deny execution of this procedure to all database users except the DBA for security reasons.

By using this T-SQL solution, you can easily remove old SQL Server backup files and free up drive space without relying on external tools or tasks.