Transaction log backup is not working

Question

Post reply

Transaction log backup is not working

Harish Ajabe

Ten Centuries

Points: 1178
More actions
August 31, 2010 at 3:45 am

#387701

I am trying to take transaction log backup using following query -
BACKUP LOG [Test] TO
DISK = N'X:\Backup\TestLog.trn'
WITH NOFORMAT, NOINIT, NAME = N'TestLog',
SKIP, REWIND, NOUNLOAD, STATS = 10
But it gives me error -
Msg 3049, Level 16, State 1, Line 1
BACKUP detected corruption in the database log. Check the errorlog for more information.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
I am using sqlserver 2008. How can i fix this?
thanks in advance,
Harish

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

David Baffaleuf SSCrazy Points: 2159 More actions · Answer 1

Please post the results of:

^{DBCC CHECKDB([Test]) WITH ALL_ERRORMSGS, NO_INFOMSGS}

GO

Thanks,

David B.

Harish Ajabe Ten Centuries Points: 1178 More actions · Answer 2

as you mentioned executed the stmt-

DBCC CHECKDB([Test]) WITH ALL_ERRORMSGS, NO_INFOMSGS

but no error message found.

Regards,

Harichandra

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Does a full backup work?

p.s. Is there any more info in the SQL error log (as the error indicates)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Harish Ajabe Ten Centuries Points: 1178 More actions · Answer 4

Yes, full backup and differential backup both happening properly

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Not sure why CheckDB's not picking this up, but the following should fix it. Do this in a maintenance window if possible.

Full backup

Switch to simple recovery

Checkpoint

Switch to full recovery

Full backup

Log backup (and see if it works)

There's an article on Paul Randal's blog on, I don't have the link handy.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Harish Ajabe Ten Centuries Points: 1178 More actions · Answer 6

It works! 🙂

log backup truncated successfully and now log backup is happening properly.

Thanks Gila Monster for your help.

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 7

I found Paul's blog: http://www.sqlskills.com/blogs/paul/how-can-a-log-backup-fail-but-a-full-backup-succeed/.

My blog