Posted by David Gray on 04/12/07 08:47

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@sommarskog.se> wrote:

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

[Back to original message]