Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Transaction log backup is not working Expand / Collapse
Author
Message
Posted Tuesday, August 31, 2010 3:45 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 6, 2014 12:32 AM
Points: 186, Visits: 172
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
Post #977792
Posted Tuesday, August 31, 2010 4:45 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, August 11, 2014 2:43 AM
Points: 61, Visits: 700
Please post the results of:

DBCC CHECKDB([Test]) WITH ALL_ERRORMSGS, NO_INFOMSGS
GO


Thanks,


David B.
Post #977820
Posted Tuesday, August 31, 2010 7:33 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 6, 2014 12:32 AM
Points: 186, Visits: 172
as you mentioned executed the stmt-

DBCC CHECKDB([Test]) WITH ALL_ERRORMSGS, NO_INFOMSGS

but no error message found.

Regards,
Harichandra
Post #977969
Posted Tuesday, August 31, 2010 7:39 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:23 AM
Points: 40,620, Visits: 37,088
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 2008, MVP
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

Post #977984
Posted Tuesday, August 31, 2010 7:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 6, 2014 12:32 AM
Points: 186, Visits: 172
Yes, full backup and differential backup both happening properly
Post #978002
Posted Tuesday, August 31, 2010 8:00 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:23 AM
Points: 40,620, Visits: 37,088
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 2008, MVP
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

Post #978012
Posted Thursday, September 2, 2010 3:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 6, 2014 12:32 AM
Points: 186, Visits: 172
It works!
log backup truncated successfully and now log backup is happening properly.

Thanks Gila Monster for your help.
Post #979367
Posted Monday, December 30, 2013 1:56 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, November 5, 2014 3:47 PM
Points: 3,087, Visits: 1,437
I found Paul's blog: http://www.sqlskills.com/blogs/paul/how-can-a-log-backup-fail-but-a-full-backup-succeed/.




My blog
Post #1526419
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse