Solved

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

Posted on 2006-11-30
11
1,314 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
[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
  • 6
  • 3
  • 2
11 Comments
 
LVL 35

Accepted Solution

by:
David Todd earned 400 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 69

Expert Comment

by:Scott Pletcher
ID: 18049683
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 100 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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

Industry Leaders: 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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

691 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