Restore log backup problem....

Question

Restore log backup problem....

sathyareddy

Ten Centuries

Points: 1158
More actions
August 30, 2007 at 9:09 am

#197536

Hi All,
I have a database which takes the .TRN files from a log shipping pair and updates itself every 2 hours.
when i restore the log files manually they restore fine but when i run it as ascheduled job every 2 hrs it fails and gives me this error:
Executed as user: NWHSC\sql2000. ALTER DATABASE [PRODbihourly] SET SINGLE_USER WITH ROLLBACK IMMEDIATE [SQLSTATE 01000] (Message 0) The log in this backup set terminates at LSN 1170059000000089400001, which is too early to apply to the database. A more recent log backup that includes LSN 1171065000000861600001 can be restored. [SQLSTATE 42000] (Error 4326) RESTORE LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013) RESTORE LOG [PRODbihourly] FROM DISK = 'G:\SQL\LogShipping\PROD_tlog_200708291100.TRN' WITH FILE=1 , NORECOVERY [SQLSTATE 01000] (Error 0). The step failed.

i tried restoring a full backup then restored all log file upto current time.
but still its giving me the same error.
can someone help me out of this.
Thanks!!
Sathya.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Deepak-405225 SSCertifiable Points: 6120 More actions · Answer 1

It seems there is a mismatch in the LSN as a result the t-logs are unable to be restored........you need to restore all the t-logs from source to destination server sequentially........now you can perform as below,

1.Disable the t-log backup job in source server and copy and restore jobs in destination servers

2.Take a full backup fo your db in source and restore it using with standby option in destination server

3.Enable all the jobs which were disabled in step 1 and check how it works....it shud work fine

[font="Verdana"]- Deepak[/font]

Cath Trimble SSCarpal Tunnel Points: 4201 More actions · Answer 2

Hi,

Your log restore sequence is being broken somehow. Double check all the times and durations for your scheduled backups, copies and restores, make sure they all have time to complete before the next job runs. Also check that there isn't another scheduled job - maintenance plan or backup - that's upsetting the log sequence.

Cath

Vik Nat Hall of Fame Points: 3930 More actions · Answer 3

I would start by taking a full backup - restore it on the standby server and then follow that up with the transaction log from there.

Narayan Iyer-473234 Valued Member Points: 52 More actions · Answer 4

NO - I wouldn't agree w/ starting from FULL backup again - Since the manual restore of TLOGs works, there is absolutely no problem with the log backups or no obvious LSN issues. Even if you do a FULL backup restore, you're most certainly going to hit this same issue again.

When you say, you're trying to restore the files using scheduled SQL jobs every two hours, how frequent are you running TLOG backup job on your primary SQL Server? I would guess that your SQL job that performs the scheduled restore on the secondary lacks the mechanism of identifying the file that's next to the last restored file from a bunch of backup files.

I propose that you write a script to read the file modified time of the backup files (.TRN) and determine the file that needs to be restored. Things would be OK. Let me know if you need any help.

Thanks,

Narayan Iyer

Naveen Voleti SSC Enthusiast Points: 174 More actions · Answer 5

Hi,

From the above thread, I found that this issue is due to the latency of tlog files that were copied from the primary server to secondary server. To overcome this, disable the tlog job in the secondary server and try to copy the tlog files in order till the latest, and enable the tlog job of secondary server ...Hope this works.. ) Let me know if you face any issues.

Regards,

Naveen Voleti