Posted by Erland Sommarskog on 04/10/07 21:40

David Gray (grayd@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, esquel@sommarskog.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

[Back to original message]