Retain/expiration inside a backup device.

[David Gray]

Hi,

SQL Server 2005 Standard (X64) edition.

Have a database which has its log backed up every hour during the
normal business day and each evening a full backup is performed.

After two days we would like the the log backups to be automatically
overwritten in the backup device [WebAdmin], is this possible?

This is the command being used.

BACKUP LOG [WebAdmin] TO [WebAdmin]
WITH RETAINDAYS = 2,
NOFORMAT,
NOINIT,
NAME = N'WebAdmin',
SKIP,
NOREWIND,
NOUNLOAD



Currently the backup device has log backups going back to 18/02/2007
which is position number 1 in the device. Basically nothing is being
overwritten.

Thanks in advance.
Dave

[Erland Sommarskog]

Re: Retain/expiration inside a backup device.

David Gray (grayd@turpin-distribution.co m) writes:

SQL Server 2005 Standard (X64) edition.
>
Have a database which has its log backed up every hour during the
normal business day and each evening a full backup is performed.
>
After two days we would like the the log backups to be automatically
overwritten in the backup device [WebAdmin], is this possible?
>
This is the command being used.
>
BACKUP LOG [WebAdmin] TO [WebAdmin]
WITH RETAINDAYS = 2,
NOFORMAT,
NOINIT,
NAME = N'WebAdmin',
SKIP,
NOREWIND,
NOUNLOAD
>
Currently the backup device has log backups going back to 18/02/2007
which is position number 1 in the device. Basically nothing is being
overwritten.

I did some reading in Books Online, I found a couple of things. First,
the SKIP option seems to prevents the expiration check, but changing
it to NOSKIP would not help, as long as you backup every day, since
there would always be a non-expired backup in the file. And it's not
that you can replace single backups in the file, you can only overwrite
it all.

I would keep it simple. Set up three backup jobs that writes to three
different devinces, all WITH INIT. One job runs Monday, Wednesday and
Friday, one runs Tuesday, Thursday and Saturday. And the third on
Sunday.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[David Gray]

Re: Retain/expiration inside a backup device.

Hi,
Thanks for the answer. Was hoping that the backup devices would
be self maintaining if used in conjunction with RETAINDAYS.

We may well move to using maintenance plans as these give you the
option to retain a specified number of days worth of database and log
backups.

Thanks
Dave



On Tue, 10 Apr 2007 21:40:11 +0000 (UTC), Erland Sommarskog
<esquel@sommars kog.sewrote:

>David Gray (grayd@turpin-distribution.co m) writes:

>SQL Server 2005 Standard (X64) edition.
>>
>Have a database which has its log backed up every hour during the
>normal business day and each evening a full backup is performed.
>>
>After two days we would like the the log backups to be automatically
>overwritten in the backup device [WebAdmin], is this possible?
>>
>This is the command being used.
>>
>BACKUP LOG [WebAdmin] TO [WebAdmin]
> WITH RETAINDAYS = 2,
> NOFORMAT,
> NOINIT,
> NAME = N'WebAdmin',
> SKIP,
> NOREWIND,
> NOUNLOAD
>>
>Currently the backup device has log backups going back to 18/02/2007
>which is position number 1 in the device. Basically nothing is being
>overwritten.

>
>I did some reading in Books Online, I found a couple of things. First,
>the SKIP option seems to prevents the expiration check, but changing
>it to NOSKIP would not help, as long as you backup every day, since
>there would always be a non-expired backup in the file. And it's not
>that you can replace single backups in the file, you can only overwrite
>it all.
>
>I would keep it simple. Set up three backup jobs that writes to three
>different devinces, all WITH INIT. One job runs Monday, Wednesday and
>Friday, one runs Tuesday, Thursday and Saturday. And the third on
>Sunday.