SQL Server Backup Maintenance: Configuring the Cleanup Task Correctly

It often happens that the Backup Maintenance Cleanup Task is not configured correctly. As a result, the hard drive fills up over time because old backups of the database or transaction log are not deleted. The following explains the correct configuration:

• For the file extension, DO NOT specify *.BAK or *.TRN, but only BAK or TRN.
• Furthermore, we recommend specifying the exact path without using the “Include subfolders at the top level” option, as this does not always work reliably.
• We also recommend appending a backslash (/) at the end of the folder path. This is illustrated in the example below (see image): EXECUTE master.dbo.xp_delete_file 0,N’C:BackupCIM_RIMIS’,N’BAK’. Without this, anything named “CIM_RIMIS” or similar and ending in “BAK” would be deleted. However, this may not be desired, for example, if there are multiple subfolders.

A general rule of thumb is that backups should remain on the server at least twice as long as they are backed up to an external medium—there is always a risk that the external backup might fail.

Do you have any questions? Feel free to contact our experts via our contact page or by phone at +49 7331 3076–0.