BACKUP EXPIREDATE & RETAINDAYS

I'm hoping that someone can help me clarify some questions I have
regarding BACKUP and how files are replaced. Ideally I want to perform
backups to a single file, replacing the appropriate backup when it
expires. How can I do this?
Consider the following backup statement:
BACKUP DATABASE
[DB1]
TO DISK = N'C:\back.bak'
WITH
retaindays = 5,
NAME = N'Full Database Backup'
GO
The above statement (exact) is then run 2 days in a row. The file
grows as expected.
Then 5 days pass and the same script is run. Rather than replacing the
first backup within the file, the file itself continues to grow. Is
there a reason for this?
I would like to know how to setup the backup statement so that a
backup is replaced once it expires.
This is not what EXPIREDATE or RETAINDAYS do. All the do is not allow you to do INIT before the
date. If you do, you get an error message. You cannot have a backup file and overwrite only a few of
the backups. It is overwrite all (INIT) or nothing (NOINIT, the default).
If you want to keep x number of days worth, work with several backup files. Most name the files
according to the database name and include date and time, and then let the script delete files older
than x days. This is exactly how the maintenance wizard does it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Pete" <pcnofelt@.gmail.com> wrote in message
news:1184851878.081775.267520@.x40g2000prg.googlegr oups.com...
> I'm hoping that someone can help me clarify some questions I have
> regarding BACKUP and how files are replaced. Ideally I want to perform
> backups to a single file, replacing the appropriate backup when it
> expires. How can I do this?
> Consider the following backup statement:
> BACKUP DATABASE
> [DB1]
> TO DISK = N'C:\back.bak'
> WITH
> retaindays = 5,
> NAME = N'Full Database Backup'
> GO
> The above statement (exact) is then run 2 days in a row. The file
> grows as expected.
> Then 5 days pass and the same script is run. Rather than replacing the
> first backup within the file, the file itself continues to grow. Is
> there a reason for this?
> I would like to know how to setup the backup statement so that a
> backup is replaced once it expires.
>