Solved

Log shipping restore fail

Posted on 2006-11-26
29
469 Views
Last Modified: 2008-01-09
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
Comment
Question by:motioneye
  • 14
  • 13
  • 2
29 Comments
 
LVL 28

Expert Comment

by:imran_fast
ID: 18014296
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 18014300
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
 
LVL 29

Expert Comment

by:Nightman
ID: 18014304
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
 

Author Comment

by:motioneye
ID: 18014674
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
 
LVL 29

Expert Comment

by:Nightman
ID: 18014767
Do you have the log files prior to the one you are trying to restore?
0
 

Author Comment

by:motioneye
ID: 18015012
Yes I do have, I mean the log file that were backup in my backup folder, sp how do I restore it?
0
 
LVL 29

Expert Comment

by:Nightman
ID: 18015020
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
 

Author Comment

by:motioneye
ID: 18015157
but how do I make sure the one that I have inside the backup folder has similar lsn that being reported on error?
0
 
LVL 29

Expert Comment

by:Nightman
ID: 18015169
Do you name then sequentially by date?
0
 

Author Comment

by:motioneye
ID: 18015194
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
 
LVL 29

Expert Comment

by:Nightman
ID: 18015230
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
 
LVL 29

Expert Comment

by:Nightman
ID: 18015233
0
 

Author Comment

by:motioneye
ID: 18015265
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
 
LVL 29

Expert Comment

by:Nightman
ID: 18015278
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

Author Comment

by:motioneye
ID: 18015309
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
 
LVL 29

Accepted Solution

by:
Nightman earned 500 total points
ID: 18015320
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
 

Author Comment

by:motioneye
ID: 18015337
Its only me exists in the process,n I killed other process already.. any other possibility?
0
 
LVL 29

Expert Comment

by:Nightman
ID: 18015349
Any client applications running against this database?
You could run sp_who2 from Query analyser and see what else is connecting.
0
 

Author Comment

by:motioneye
ID: 18015366
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
 
LVL 29

Expert Comment

by:Nightman
ID: 18015375
Yes. Close down all your connections (including Enterprise Manager) and then re-open query analyser and try again
0
 

Author Comment

by:motioneye
ID: 18015397
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
 
LVL 29

Expert Comment

by:Nightman
ID: 18015410
If you run sp_who2 on the master database, what is the SPID (process identifier) of the connection to that database?
0
 

Author Comment

by:motioneye
ID: 18015425
sp_who2 on master database results me with no any process connected to that particular restore db.
0
 

Author Comment

by:motioneye
ID: 18015437
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
 
LVL 29

Expert Comment

by:Nightman
ID: 18015440
OK, try set the database to single user mode
0
 

Author Comment

by:motioneye
ID: 18015448
hmm,,,
I'm trying but sql wont allow me to do that bcoz other users is using the db.. that the msg given......
0
 
LVL 29

Expert Comment

by:Nightman
ID: 18015456
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
 

Author Comment

by:motioneye
ID: 18015500
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
 
LVL 29

Expert Comment

by:Nightman
ID: 18018199
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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now