Thursday, February 16, 2012

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 overwritte
n.
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 overwrit
ten.
You didn't tell it to overwrite. NOINIT is the default which means append. I
f you want to overwrite
you specify INIT (or FORMAT). RETAINDAYS does not have anything with automat
ically keeping that many
backups in the backup set, it only generates an error if you try to INIT bef
ore 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 RETAINAY
S
> = 1.
> The backup is executed days after days successfully, but it isn't overwrit
ten.
> 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:

> 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 autom
atically keeping that many
> backups in the backup set, it only generates an error if you try to INIT b
efore 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...
>|||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...[vbcol=seagreen]
> 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:
>|||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 da
y I
> must specify this sintax:
> BACKUP LOG SampleDB TO DISK = 'D:\BACKUPLOG\LogSampleDB.TRN' WITH RETAINAY
S
> = 1, INIT.
> Is it correct?
> Thanks
>

No comments:

Post a Comment