Backup troubles, cannot restore transaction logs

Question

Post reply

Backup troubles, cannot restore transaction logs

Jo Pattyn

SSC-Dedicated

Points: 32348
More actions
August 20, 2005 at 11:57 am

#111631

Hello,
I'm trying to set up a standby server by log-shipping.
Currently it is implemented as:
Truncate log
Full backup database, INIT(daily)
Copy database
Restore database, STANDBY,REPLACE
Backup database log (every 15 minutes) NO_TRUNCATE
Copy backup of database log
Restore database log, STANDBY
After a day the restoration of the database log starts to fail with
log starts at LSN ... which is too late to apply
Any suggestions?
Database size varies from 2-10-30gb, the 2 sql 2000 server SP4 are on the same 1 gb switch.

Viewing 11 posts - 1 through 10 (of 10 total)

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

Carsten Kunz Grasshopper Points: 22 More actions · Answer 1

Please try without truncating the log before the full backup

gbn SSCertifiable Points: 6429 More actions · Answer 2

I know this is obvious, but you are either restoring logs in the wrong order or you have a "broken" LSN chain. he LSN sequence can be affected by an action such as log filling up (error 9002) or a log truncate or a db recovery model change

If: You only restore the logs after the initial FULL backup

And. You always use "TRUNCATE_ONLY" before your full daily backup

Then: Your LSN chain is broken

BTW, you shouldn't need NO_TRUNCATE unless you need tpo backup teh "tail" of a log and your MDF is damaged, see "How to set up, maintain, and bring online a standby server (Transact-SQL)" in BOL

Cheers, Shawn

Jo Pattyn SSC-Dedicated Points: 32348 More actions · Answer 3

I removed the truncate log step and the no_truncate clause.

So far so good. Thanks for the advice.

I'll repost in a few days.

Karl Klingler SSCertifiable Points: 5869 More actions · Answer 4

Hi,

neither the truncate log nor the with_notruncate should have an impact on your logshipping.

Most likely the restore of the full backup is not finished when the first log-restore starts. Therefore the first log-restore fails, and the LSNs contained in that backup never get restored.

2 solutions for that:

1. Start the first log-backup and -restore after the db-restore is finished

2. Change the restore script so that it looks for the last LSN in the db and restores from the backup that contains this LSN.

regards

karl

By the way: Don't use with truncate_only, a normal backup with truncate does the same without breaking the LSN-Chain

Best regards
karl

gbn SSCertifiable Points: 6429 More actions · Answer 5

1. Start the first log-backup and -restore after the db-restore is finished

He only restores once... and teh log falls over after a day

And explain to me why truncate log does not affect it

Karl Klingler SSCertifiable Points: 5869 More actions · Answer 6

Hi writes:

Truncate log

Full backup database, INIT(daily)

Copy database

Restore database, STANDBY,REPLACE

I assumed that he restores the db daily also. If so, the truncate before db-backup and -restore won't affect the outcome because of the db-restore afterwards

Best regards
karl

gbn SSCertifiable Points: 6429 More actions · Answer 7

I don't think he is restoring daily, hence the problem with truncate log.

Also, why would your theory not bollix up the first copy and restore of the database, but only a subsequent, assuming all timings are equal (say a log backup 15 mins after full backup)

Karl Klingler SSCertifiable Points: 5869 More actions · Answer 8

I'm also assuming that he first restores the database and only afterwards starts the job doing the log transfers. That way the first conflict would occur the day after..

Perhaps i'm simply prejusticed because my own logshipping procedures are similar to Jo's, and i've had exactly that same problem...

Best regards
karl

Karl Klingler SSCertifiable Points: 5869 More actions · Answer 9

One more point: i'm assuming daily db-restores because for me the standby-server gives availability, but at least as important as that is: it's living proof that my backups and restores are working. if daily db-restore and log-restores work on the standby server i know that i can do a point-in-time recovery out of these backups.

Best regards
karl

Jo Pattyn SSC-Dedicated Points: 32348 More actions · Answer 10

The setup is like Karl Klingler said.

Every weekday I plan a full backup of each database at 5.00 am (normal batch end time), should have finished around 8.00 am.

Saterday: full batch processing.

Sunday: some defragging.

Backup to mobile disk, copy to standby from mobile disk to the standby server across the network,restore from local hd on standy in one job.

From then on, every 15 minutes a log backup/copy/restore from each database.

1. Backups are tested

2. Possible to redirect programs to standby with a small modification without restoring from scratch. Restore from scratch takes too long (24 hours )

Have yet to test if the logs won't go out of sync while I switch the mobile disk (using the detach hardware wizard)