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]

David Gray (gr***@turpin-distribution.com) 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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[David Gray]

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
<es****@sommarskog.sewrote:

>David Gray (gr***@turpin-distribution.com) 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.