| Author |
Topic |
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
 Posted - 2011-03-25 : 22:16:28
|
| Hello,I am 15 minutes away from receiving an MDF and LDF from a server that crashed. This database is extremely important, and right now its looking like the backups have failed :|I've been researching how to restore from MDFs and LDFs on the internet and have come across some stuff which I will be attempting to do.I know there are some guru's here so any extra info that anyone could share would be extremely appreciated.I'll also post updates to specific errors etc I receive during the process. I'm hoping this works.What I know now is that the RAID 0+1 failed, we pulled the file off the failed drives, altho it did freeze, the file ended up the same size on the new drive. Again I have no idea the condition of this file and I am praying it works.Today is my worst nightmare in 11 years of IT and I made the ultimate bonehead move not checking my backups =Again, thanks for any support !!Thanks!Mike123 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-03-25 : 23:33:59
|
| Could you explain a little bit about the backups failing? What's the error?You'll have to attempt to attach the database, first with the ldf and if that fails, then without the ldf. If it doesn't work, well there's not a whole lot you can do except maybe try hiring a third party data recovery company. Let us know what happens on the attach. I don't have much hope that this will work as backups are what you need.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-03-25 : 23:34:37
|
Trying sp_attach_db?Likely that the database will be marked as suspect or the attach will fail....but if it works, run DBCC CHECKDB()If it doesn't let us know. |
|
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2011-03-25 : 23:48:28
|
| Yikes, not what I wanted to hear =Sorry I wasn't clear the backups have failed because the hard drive did not have enough space to do a backup, so we did not have a backup for quite sometime.So essentially we do not have backups.Is this why you said you didn't have much hope? (hope so)I'm hoping one of these LDF / MDF situations will work .. =\ What are the chances ? too hard to say I would imagine.Again any help is greatly appreciated..would be interested in a 3rd party company if thats my only option =Thanks,Mike |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-03-25 : 23:49:58
|
| tell us what you're planning to try.attempting an attach won't make things worse, but it may not work. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-03-25 : 23:52:00
|
| Can't really say what your chances are. We've certainly heard of stories where this has worked, but we've also heard stories where it hasn't. I don't actually have any 3rd party company to recommend, but I've seen numerous people posting here from those companies that they'd be happy to help with some various recovery item for a fee. I have no idea how expensive it is, probably depends on how complex it would be to recover anything/everything.We don't have much hope of the attach working because the service was very likely in a started state at the time of the crash which means that the files were open. Attach works great if the service was stopped or the databases were detached which closes those files. But in your scenario, the service was almost certainly in a started state and the database files were almost certainly in an open state. Not an ideal situation.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2011-03-26 : 00:08:23
|
| Hey Tara,Your correct it was in a started state. Russell - I have tried to attach and it does not work. I have the MDF and the LDF, to be honest I am bit confused the best method to try. I tried and got a message about the log file being from a system that wasn't shut down properly.. to be honest i dont have it anymore..just finished applying sp4 for sql2005about ready to try again here.. any suggestion HUGELY appreciated :)thanks!,mike |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2011-03-26 : 00:15:58
|
| trying that now russell......... thx! |
|
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2011-03-26 : 00:30:34
|
| no luck =[This file path is in fact incorrect, not sure where I should set it since we are doing from tsql and this doesn't appear to be an option ?File activation failure. The physical file name "D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\dbname_1.LDF" may be incorrect.The log cannot be rebuilt because the database was not cleanly shut down.Msg 1813, Level 16, State 2, Line 1Could not open new database 'dbname'. CREATE DATABASE is aborted. |
|
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-03-26 : 01:09:02
|
| Mike, you're in a tough spot.I wouldn;t suggest any 3rd party tools, but maybe a data recovery specialist.If you're a realllllly good programmer, you may be able to parse the mdf file yourself, and recover whatever is still good in it, but probably that's a longshot.If my memory serves, Paul Randal recently shared a letter in his blog where a guy did just that.How old are your last good backups? Any way you go, you're probably going to lose some data.Forgive me for stating the obvious, but the #1 responsibility for most of us here is to make sure that we have good backups. Hard lesson learned. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2011-03-26 : 01:11:30
|
| eek =[is it worth a try? im just waiting for the MDF to copy in the directoyI don't know where to go from here :(any suggestions greatly appreciated |
|
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2011-03-26 : 01:13:29
|
quote:
Originally posted by russell
Mike, you're in a tough spot.I wouldn;t suggest any 3rd party tools, but maybe a data recovery specialist.If you're a realllllly good programmer, you may be able to parse the mdf file yourself, and recover whatever is still good in it, but probably that's a longshot.If my memory serves, Paul Randal recently shared a letter in his blog where a guy did just that.How old are your last good backups? Any way you go, you're probably going to lose some data.Forgive me for stating the obvious, but the #1 responsibility for most of us here is to make sure that we have good backups. Hard lesson learned.
my backups where on the drive that crashed =[ any reason not to try a 3rd party tool ? have you tried before?how can I reach a datarecovery specialist ? where do I start ? =[thanks again, mike |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-03-26 : 07:01:03
|
| Before you go off spending money...., there's a post 'Deleting the transaction log' on my blog (http://sqlinthewild.co.za). I can't get the exact link for the post right now, but it's one of the 5 most popular and is listed in the side bar right at the top.That covers a step-by-step for recovering a database where the ldf has been lost (and it looks, from a brief read that you are in that situation)I won't promise full recovery, but if the ldf is the only problem then the technique in there will work. If you get errors, post back here, I'll be offline most of the day unfortunately (adsl line is down), but will check in from time to time.--Gail ShawSQL Server MVP |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-03-26 : 12:11:06
|
| Thanks. Was posting from internet cafe this morning and for some reason my blog was blocked.--Gail ShawSQL Server MVP |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-03-26 : 13:15:34
|
I guess it's their way of saying you're a troublemaker.  |
|
|
|
Next Page
|
restoring database from MDF / LDF