?
Solved

Log shipping restore fail

Posted on 2006-11-26
29
Medium Priority
?
476 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 

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
 

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
 

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 1500 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

752 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