Backup log isn't overwritten in SQL Server 2005

I have planned a job to backup the transaction log of a my db and then to
shrink it.
I have specified this script:
BACKUP LOG SampleDB TO DISK = 'D:\BACKUPLOG\LogSampleDB.TRN' WITH RETAINAYS
= 1.
The backup is executed days after days successfully, but it isn't overwritten.
The dimension of file ldf is maintained low.
I don't see any errors in the history of the job.
How can I solve this issue?> The backup is executed days after days successfully, but it isn't overwritten.
You didn't tell it to overwrite. NOINIT is the default which means append. If you want to overwrite
you specify INIT (or FORMAT). RETAINDAYS does not have anything with automatically keeping that many
backups in the backup set, it only generates an error if you try to INIT before that number of days.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
news:994509B4-16C1-4C3D-A6E9-82A48266789F@.microsoft.com...
>I have planned a job to backup the transaction log of a my db and then to
> shrink it.
> I have specified this script:
> BACKUP LOG SampleDB TO DISK = 'D:\BACKUPLOG\LogSampleDB.TRN' WITH RETAINAYS
> = 1.
> The backup is executed days after days successfully, but it isn't overwritten.
> The dimension of file ldf is maintained low.
> I don't see any errors in the history of the job.
> How can I solve this issue?|||So, if I want to overwrite the backup of the log that expires after one day I
must specify this sintax:
BACKUP LOG SampleDB TO DISK = 'D:\BACKUPLOG\LogSampleDB.TRN' WITH RETAINAYS
= 1, INIT.
Is it correct?
Thanks
"Tibor Karaszi" wrote:
> > The backup is executed days after days successfully, but it isn't overwritten.
> You didn't tell it to overwrite. NOINIT is the default which means append. If you want to overwrite
> you specify INIT (or FORMAT). RETAINDAYS does not have anything with automatically keeping that many
> backups in the backup set, it only generates an error if you try to INIT before that number of days.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
> news:994509B4-16C1-4C3D-A6E9-82A48266789F@.microsoft.com...
> >I have planned a job to backup the transaction log of a my db and then to
> > shrink it.
> > I have specified this script:
> > BACKUP LOG SampleDB TO DISK = 'D:\BACKUPLOG\LogSampleDB.TRN' WITH RETAINAYS
> > = 1.
> > The backup is executed days after days successfully, but it isn't overwritten.
> > The dimension of file ldf is maintained low.
> > I don't see any errors in the history of the job.
> >
> > How can I solve this issue?
>|||Hello,
Looks good, this will overwrite the log file next day. Please correct the
spelling mistake in RETAINDAYS.
BACKUP LOG Sampledb TO DISK = 'C:\LogSampleDB.TRN' WITH RETAINDAYS = 1, INIT
Thanks
Hari
"Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
news:DD95C0D4-4A2C-4E0A-90B1-70DA07A4D894@.microsoft.com...
> So, if I want to overwrite the backup of the log that expires after one
> day I
> must specify this sintax:
> BACKUP LOG SampleDB TO DISK = 'D:\BACKUPLOG\LogSampleDB.TRN' WITH
> RETAINAYS
> = 1, INIT.
> Is it correct?
> Thanks
>
> "Tibor Karaszi" wrote:
>> > The backup is executed days after days successfully, but it isn't
>> > overwritten.
>> You didn't tell it to overwrite. NOINIT is the default which means
>> append. If you want to overwrite
>> you specify INIT (or FORMAT). RETAINDAYS does not have anything with
>> automatically keeping that many
>> backups in the backup set, it only generates an error if you try to INIT
>> before that number of days.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
>> news:994509B4-16C1-4C3D-A6E9-82A48266789F@.microsoft.com...
>> >I have planned a job to backup the transaction log of a my db and then
>> >to
>> > shrink it.
>> > I have specified this script:
>> > BACKUP LOG SampleDB TO DISK = 'D:\BACKUPLOG\LogSampleDB.TRN' WITH
>> > RETAINAYS
>> > = 1.
>> > The backup is executed days after days successfully, but it isn't
>> > overwritten.
>> > The dimension of file ldf is maintained low.
>> > I don't see any errors in the history of the job.
>> >
>> > How can I solve this issue?
>>|||That command will error if you try to overwrite and the number of days hasn't happened yet, and no
backup will be produced! If you want to have several generations of a backup, you need to work with
several backup devices... See below script to demonstrate the issue:
alter database pubs set recovery full
backup database pubs to disk = 'nul'
backup log pubs to disk = 'C:\Xpubslog.bak' with retaindays = 1, INIT
WAITFOR DELAY '00:00:10'
backup log pubs to disk = 'C:\Xpubslog.bak' with retaindays = 1, INIT
GO
RESTORE HEADERONLY FROM disk = 'C:\Xpubslog.bak'
Note that above backup device has only one backup on it, not two!
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
news:DD95C0D4-4A2C-4E0A-90B1-70DA07A4D894@.microsoft.com...
> So, if I want to overwrite the backup of the log that expires after one day I
> must specify this sintax:
> BACKUP LOG SampleDB TO DISK = 'D:\BACKUPLOG\LogSampleDB.TRN' WITH RETAINAYS
> = 1, INIT.
> Is it correct?
> Thanks
>