SQL SERVER – RETAINDAYS Does Not Delete Backup After x Days

RETAINDAYS sets backup expiration for overwrite checks, without deleting the file. I keep SQL expiration separate from Windows file cleanup.

A customer expected RETAINDAYS to delete older backup files. The files remained after the configured interval. That recurring question prompted the distinction between expiration and cleanup.

-- Backup template: SQL Server service account needs access to this destination.
-- BACKUP DATABASE [YourDatabase]
-- TO DISK = N'D:\SQLBackups\YourDatabase_full.bak'
-- WITH RETAINDAYS = 7;

RETAINDAYS sets an expiration interval used by SQL Server’s overwrite checks. Afterward, an appropriate backup operation can overwrite the backup set. The option doesn’t schedule deletion.

INIT attempts to replace existing backup sets while retaining the media header, subject to the normal checks. SKIP bypasses expiration and name checks, while FORMAT creates a new media set and can overwrite existing media. The default NOINIT appends rather than replacing existing backup sets.

A Windows file operation can still delete a .bak file regardless of this SQL Server expiration value. RETAINDAYS is therefore not immutable retention protection. Design a separate cleanup policy that respects full, differential and log restore dependencies, then preview the exact files before deletion.

Reference: Backup expiration and overwrite options.

Backup expiration is not scheduled deletion, it is an input to SQL Server overwrite checks.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.