Logshipping

Question

Logshipping

shanila_minnu

Hall of Fame

Points: 3465
More actions
May 19, 2009 at 6:47 am

#134429

In my environment logshipping process is there.For this we created backup job from primary server (LON549)and restore job in secondary server(BCPDBS01). every 15 minutes differential backup will happen and restore it in secondary server....
today the job is getting failed...error details is as follows
[Executed as user: CMCK\SQLBCPDBS01. Cannot apply the backup on device '\\Bcpdbs01\g$\Backups\LON549\MH\Diff\MH_DiffBackup_20090519_084500.BAK' to database 'MH'. [SQLSTATE 42000] (Error 3136) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.]
Please help me on this its urgent

Viewing 14 posts - 1 through 13 (of 13 total)

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

george sibbald SSC Guru Points: 104210 More actions · Answer 1

you don't want to be taking and restoring differential backups, you should be doing log backups.

with differential backups as soon as you take a full backup the differential resets and starts again, at a guess this is what has happened to you.

use the logshipping wizard to set up log shipping based on transaction log backups.

---------------------------------------------------------------------

shanila_minnu Hall of Fame Points: 3465 More actions · Answer 2

For one year we are following this way...we cant do any other methods to solve this....

george sibbald SSC Guru Points: 104210 More actions · Answer 3

shanila_minnu (5/19/2009)
For one year we are following this way...we cant do any other methods to solve this....

why can't you?

has a full backup been taken?

---------------------------------------------------------------------

shanila_minnu Hall of Fame Points: 3465 More actions · Answer 4

shanila_minnu

Hall of Fame

Points: 3465

May 19, 2009 at 7:34 am

#996948

ya..every day morning full backup will happen

george sibbald SSC Guru Points: 104210 More actions · Answer 5

then is that backup normally taken with the copy only option, or is it normally restored on the standby server?, and one of these two things was not done.

I would suggest switching to log backups.

---------------------------------------------------------------------

shanila_minnu Hall of Fame Points: 3465 More actions · Answer 6

i restored the full backup again and then differential

now the problem is solved

george sibbald SSC Guru Points: 104210 More actions · Answer 7

good, thanks for posting resolution.

I would still recommend switching to logshipping longer term

---------------------------------------------------------------------

starterm Mr or Mrs. 500 Points: 518 More actions · Answer 8

I am confused with lsn in backup. can u please explain how lsn has importance in taking database backup and log backups. And how the log chain will break and effects the logshipping

Thanks in advance..

starterm Mr or Mrs. 500 Points: 518 More actions · Answer 9

How do you make failure in primary server instance and make secondary server instance databases online

please provide your suggestions..

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 10

Failover - http://msdn.microsoft.com/en-us/library/ms191233.aspx

The LSNs track the ordering of log records. You cannot restore if your LSNs do not go in order.

sql_lock SSCrazy Eights Points: 9373 More actions · Answer 11

Steve, sorry to hijack this posts... But if you have 15 minute logs backups and P2P replication could it lead to lost transations when the backup takes place and the log reader processes?

thanks

Paul White SSC Guru Points: 150467 More actions · Answer 12

sql_lock (3/26/2010)
Steve, sorry to hijack this posts... But if you have 15 minute logs backups and P2P replication could it lead to lost transations when the backup takes place and the log reader processes?

No. Log records cannot be removed until replication has finished with them. The log_reuse_wait_desc column on sys.databases will show 'REPLICATION' in such a case.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

sql_lock SSCrazy Eights Points: 9373 More actions · Answer 13

sql_lock

SSCrazy Eights

Points: 9373

March 26, 2010 at 2:48 am

#1140203

Thanks Paul... Much appreciated!

Jonlee