BACKUP EXPIREDATE & RETAINDAYS When is a file replaced?

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.
On Thu, 19 Jul 2007 06:31:46 -0700, Pete <pcnofelt@.gmail.com> wrote:

>Ideally I want to perform
>backups to a single file, replacing the appropriate backup when it
>expires. How can I do this?
You can not. When using a single file you can either overwrite it and
loose all past backups, or append to it adding another backup. It is
NOT possible to remove one backup from a file that has more than one,
it is all or nothing.
Which is why you don't want to backup to a single file. One file for
each backup works if you build the database name, date and time into
the file name. One file for each day works if you just build in the
database name and date.
Roy Harvey
Beacon Falls, CT
|||Which is pretty much what said in another group. Unnecessary work due to cross posting...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:0mru93936e8m0v766lsbsldnbg2an74qgt@.4ax.com...
> On Thu, 19 Jul 2007 06:31:46 -0700, Pete <pcnofelt@.gmail.com> wrote:
>
> You can not. When using a single file you can either overwrite it and
> loose all past backups, or append to it adding another backup. It is
> NOT possible to remove one backup from a file that has more than one,
> it is all or nothing.
> Which is why you don't want to backup to a single file. One file for
> each backup works if you build the database name, date and time into
> the file name. One file for each day works if you just build in the
> database name and date.
> Roy Harvey
> Beacon Falls, CT
|||> Yes, and the excess cross posting because Google
<snip>
Oh yes, I've seen you mentioning this the past day. Somehow I didn't connect it to this particular
post...

> Besides, you said it better. 8-)
I think we said very much the same thing... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:8bcv935kd5s7kfeifmhmt71k2q5etqhq61@.4ax.com...
> On Thu, 19 Jul 2007 20:35:17 +0200, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>
> Yes, and the excess cross posting because Google did not have the
> decency to announce on their interface that it was not working.
> Fortunately they seem to be getting google groups fixed, there are
> some messages in .programming less than half a day old rather than two
> days.
> Besides, you said it better. 8-)
> Roy