Backup Question - Problems with Retain Days

[bsingu]

Folks,
I am running a full backup everynight onto a backup device (separate backup devices for each database). This is the syntax I use for backing up the database.

BACKUP DATABASE MSDB TO BACKUP_MSDB WITH RetainDays = 3

I have specified the retain days as 3. When the database gets backed up in the device. it sets the expiration date after 3 days. however, the backup still exists on the device even after the expiration date. This causes the .bak file to grow and eventually fill up the disk drive.

Can anyone point out my mistake or different way ie., the backups automatically gets deleted after three days. I am using Microsoft SQL Server 2000.

Any help is appreciated.

thanks

Bhupesh

 

[SQLBill]

Are you backing up to separate backup files? If not, then this is what is happening.

Initial backup on 1 Sep
Backup and create .bak file - expire in three days (4 Sep)
Next backup on 2 Sep
Backup and append to .bak file - expire in three days (5 Sep)
Next backup on 3 Sep
Backup and append to .bak file - expire in three days (6 Sep).

Expire is all or nothing. It can't expire a part of a file. So every time you add (append) to the backup file it resets the expiration date.

-SQLBill