Solved

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

Posted on 2006-11-30
11
1,294 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 400 total points
Comment Utility
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:ScottPletcher
Comment Utility
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 100 total points
Comment Utility
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
 

Author Comment

by:it2gostl
Comment Utility

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
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 35

Expert Comment

by:David Todd
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

743 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

8 Experts available now in Live!

Get 1:1 Help Now