• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 482
  • Last Modified:

Log shipping restore fail

I have this error msg with the log shipping, I checked in the backup folder and confirmed that the backup log file still exists, but I'm confuse which lsn mean here in the error msg? so is there any chance for me to retsore the log backup, this is very huge db 300Gb and I think full db restore will take a lot of time.


[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4305: [Microsoft][ODBC SQL Server Driver][SQL Server]The log in this backup set begins at LSN 124192000015036000001, which is too late to apply to the database. An earlier log backup that includes LSN 124191000020139000001 can be restored.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating abnormally.
0
motioneye
Asked:
motioneye
  • 14
  • 13
  • 2
1 Solution
 
imran_fastCommented:
http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part4/c1361.mspx?mfr=true
A When the full database backup was restored, one that was too old was restored. The transaction logs must be applied sequentially, so if the full backup is out of date, log shipping will fail.
0
 
NightmanCTOCommented:
The LSN is the logical sequence number. Logs need to be restored in sequence - the moment your sequence is broken you can only restore up until the point of the last log in the sequence - e.g. you have LSN's A, B, C and D, but you log files only containt A,B and D. You can only restore up until B, because transaction logs must be restored in the same order in which they were backed up.

The possibilities of a broken chain include someone backing up a log with TRUNCATE_ONLY or NO_LOG when the previous log backup has not been taken, overwriting of a log backup (resulting in a missing backup file and breaking the sequence).
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
motioneyeAuthor Commented:
Okey,
where can i find the lsn in sql server? I checked in backup folder the trans. log file is exists? for my case here what can i do rather then doing a full db restore? what can i do over here?
0
 
NightmanCTOCommented:
Do you have the log files prior to the one you are trying to restore?
0
 
motioneyeAuthor Commented:
Yes I do have, I mean the log file that were backup in my backup folder, sp how do I restore it?
0
 
NightmanCTOCommented:
try restoring from a previous log file - if  you have all of them you should be able to find the one that has the correct lsn and then move forward from there.
0
 
motioneyeAuthor Commented:
but how do I make sure the one that I have inside the backup folder has similar lsn that being reported on error?
0
 
NightmanCTOCommented:
Do you name then sequentially by date?
0
 
motioneyeAuthor Commented:
yep I name it sequentially by date, I see the restore failed with one of the file, so if I restore this file do I need to restore the subsequent file and what is the statement to retore the transaction log for log shipping
0
 
NightmanCTOCommented:
RESTORE LOG myDatabase
   FROM MyLogFile1
   WITH NORECOVERY

If using the wizard to do the database backup, make sure either the Leave database non-operational but able to restore additional transaction logs or Leave database read-only and able to restore additional transaction logs option is selected on the Options screen for restoring a database. If doing it through Transact-SQL, use the WITH STANDBY or WITH NORECOVERY options, depending on your requirements.
0
 
motioneyeAuthor Commented:
Hi,
I do not want to do a database backup, this db is 300GB n I manage it remotely... so will restore able to recover the db back in sync?
0
 
NightmanCTOCommented:
restoring from the log files will be able to do this as long as you have all the log files in the chain.

If the chain has been broken and you can't recover the transaction log backup file that you need, you will have to start from a fresh backup, and then start building your log chain again.
0
 
motioneyeAuthor Commented:
I'm trying,

restore log cartsa from disk n='d:\backup\****.trn' with no recovery and I got below error

exclusive access could not be obtain bcoz the database is in use
0
 
NightmanCTOCommented:
That is because you have another process with an open connection to the database. this could be Enterprise Manager (or Management Studio in SQL 2005), Query Analyser (how many windows do you have open) or 1 of each. This can only be done if you have 1 connection, and that is the one attempting to restore the log.
0
 
motioneyeAuthor Commented:
Its only me exists in the process,n I killed other process already.. any other possibility?
0
 
NightmanCTOCommented:
Any client applications running against this database?
You could run sp_who2 from Query analyser and see what else is connecting.
0
 
motioneyeAuthor Commented:
yes I run sp_who2 n run sp_who2 active I see only my login is exists or do I need to disable the log ship retore job?
0
 
NightmanCTOCommented:
Yes. Close down all your connections (including Enterprise Manager) and then re-open query analyser and try again
0
 
motioneyeAuthor Commented:
still the same even after disbale all the jobs for log shipping, for ur info this db had been set with "read only' mode but I dont think this had causing a problem, what else I can do here???
0
 
NightmanCTOCommented:
If you run sp_who2 on the master database, what is the SPID (process identifier) of the connection to that database?
0
 
motioneyeAuthor Commented:
sp_who2 on master database results me with no any process connected to that particular restore db.
0
 
motioneyeAuthor Commented:
I checked in db properties, I see numbers of user =9 but I dont see any process attach to it when I run sp_who2.. what shall I do to terminate all these 9 users? will this causing the restore fail just now?
0
 
NightmanCTOCommented:
OK, try set the database to single user mode
0
 
motioneyeAuthor Commented:
hmm,,,
I'm trying but sql wont allow me to do that bcoz other users is using the db.. that the msg given......
0
 
NightmanCTOCommented:
You have to find those users and terminate their access.
Have you closed down and restarted all instances of Query Analyser and Enterprise manager?
0
 
motioneyeAuthor Commented:
how do I find those users?? I run the sp_who2 i did not find any except me, yes I had restarted the QA and EM but I still have the same error
0
 
NightmanCTOCommented:
run sp_who2, and post the results of connections to that database here. I need the following columns:

SPID
status
login
HostName
DBName
Command
ProgramName
LastBatch
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

  • 14
  • 13
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now