[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Disaster recovery: Attaching a database, error 3456, cannot redo the log.

Posted on 2006-11-30
11
Medium Priority
?
1,338 Views
Last Modified: 2012-08-13
Hello All,

I've inherited the following problem and really need some of your great advice: Server crashed hard (two raid-5 drives in 12 hours), only backup available of sql database is the actual file backup of the MDF and LDF files which were made using file locks with BackupExec. This pair of files, when put in place and attempting to attach, give error 3456: cannot redo the log. I have tried putting the database in emergency mode and swapping around the MDF files, which allowed me some access to the data, but when I DBCC checkdb it's full of page errors, allocation errors, trashed indexes, and lots of error messages, etc. I spent hours dbcc'ing and it is now running cleanly.

I just wanted to make sure that the method I followed was the best route to follow as I am sure there is critical data missing. Looking for some guidance and confirmation, or an alternate method to try. This problem is unique from others I've researched in that I actually have both database files, but since they were backed up one after the other with the system live I guess they are not in sync and cannot be directly attached?

I consulted this article and tried everything listed: http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21088296.html?qid=21088296

Would appreciate any input. This database is fairly large and would be a total disaster to lose. I already gave them the "I told you so" and "backup often" speeches ;)

NOTE: I will open up additional questions relating to this so I can award 500 points to every person with good solutions.

Thank you very much.
Mike
0
Comment
Question by:it2gostl
  • 6
  • 3
  • 2
11 Comments
 
LVL 35

Accepted Solution

by:
David Todd earned 1600 total points
ID: 18049665
Hi Mike,

In the first instance, and only if you ahve the space and time, I'd try making a copy of the recovered mdf file, and attaching jsut that.

Most of the time, SQL will then create an empty log file.

Lumigent has a 14 day trial on log explorer. It may give you something you can use on the log file and look at recovering transactions. Not sure if it will work on an unconnected log file or what. Just my two cents worth ... (Whoops, 10 cents worth, they've withdrawn the smaller coins from circulation!)

Regards
  David
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 18049683
0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 400 total points
ID: 18049687
Sounds like it may just be the index files that are damaged.  If so, that's no big thing.  Just script out existing indexes, drop them and re-create them.
0
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!

 

Author Comment

by:it2gostl
ID: 18049850

David, thanks for the input. When I try to attach the singular MDF file I get this error:

Error 1813: Cound not open new database 'dbname'. CREATE DATABASE is aborted. Device activation error. The physical file name 'C:\....\mssql$KHI\data\dbname_log.ldf' may be invalid.
0
 

Author Comment

by:it2gostl
ID: 18049866
Scott, I am running SQL2KSP4, so that KB from MS does not apply. This db is from a custom application which I do not have source or index maps. There are literally hundreds, so probably not practical, unless there is a tool I could use to "Remember" the indexes, then dump and recreate them properly?
0
 
LVL 35

Expert Comment

by:David Todd
ID: 18049892
Hi,

1.
Is that path to the log file valid??

2.
What Scott is suggesting, is that when you do get the database attached, even with the errors, is from Enterprise Manager, to right click on the database, and select create script or similar. I think that Query Analyser from the Object Browser might be able to do this as well. There are several places you can do this from.

Once you have the script you can edit by hand to drop and recreate the indexes.

Regards
  David
0
 

Author Comment

by:it2gostl
ID: 18049976
The path to the log file would be valid if this was the original server. On my machine it is not valid. The old machine had a D: drive and I only have a C: so I cannot try to fake it out unless I repartition.

I follow you on the script thing, I could try that might be worth a shot, thank you.
0
 

Author Comment

by:it2gostl
ID: 18049983
What about the page allocation errors? Will dropping indexes fix that? Just asking now before starting a lot of scripting work.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 18049992
Hi,

But back to the log file path, the one you quote above in the error message was on C:\ Where is the server trying to create the log file?

The allocation errors - I'll let Scott respond to that one.

BTW Have you called Micrsoft Support? There may be a charge, but how valuable is the data you are missing?

Regards
  David

0
 

Author Comment

by:it2gostl
ID: 18050015
Sorry to confuse the issue, I quoted C: accidentally above, it was really D: where the error msg was pointing.
0
 

Author Comment

by:it2gostl
ID: 18050018
I am opening a separate question for how to index the scripts since it's not on this direct topic, and to spread some points around.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Ready to get certified? Check out some courses that help you prepare for third-party exams.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

834 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