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

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.
>

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.googlegroups.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.
>

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 s
cript 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.googlegroups.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.
>