Solved

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

Posted on 2006-11-30
11
1,306 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

726 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