Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Recover Data File - MDF


Recover Data File - MDF

Author
Message
Joie Andrew
Joie Andrew
SSC Eights!
SSC Eights! (968 reputation)SSC Eights! (968 reputation)SSC Eights! (968 reputation)SSC Eights! (968 reputation)SSC Eights! (968 reputation)SSC Eights! (968 reputation)SSC Eights! (968 reputation)SSC Eights! (968 reputation)

Group: General Forum Members
Points: 968 Visits: 1899
Those steps 'should not' corrupt the database.

Can you post the output from running CHECKDB ?


It is hard to say since none of us know the entire situation involved, but first guess I would have to agree with this assessment. What errors does DBCC CHECKDB come back with?

Joie Andrew
"Since 1982"
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21073 Visits: 18259
Grant Fritchey (1/4/2010)
sudhakara (1/4/2010)
Jack Corbett (1/4/2010)
Sorry to say that you are basically screwed since you have no backups. Your only option is to run DBCC CHECKDB or CHECKTABLE with one of the repair options, probably REPAIR_ALLOW_DATA_LOSS which does exactly what you it says, it fixes the corruption, but you will most likely lose data. Check out Paul Randal's blog and look at the CHECKDB and Corruption tags.


After attaching the user databases in the server, i will apply the some data patches and fixes application related, after completing this process only i will take the backup of the database.But unfortunately after completing this data patches and fixes database got corrupted.



Take a backup before, take a backup after, heck, depending on how extensive the number of changes you're performing, take a backup in the middle. If you're mucking with production data, it's difficult to be overly paranoid.


In agreement with Grant - It is better to be overly paranoid, than ill-prepared.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

homebrew01
homebrew01
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2980 Visits: 9071
Are your other databases backed up ?

This might help show holes in your backup strategy:

http://www.mssqltips.com/tip.asp?tip=1601



pankaj.baluni
pankaj.baluni
SSC-Enthusiastic
SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)

Group: General Forum Members
Points: 126 Visits: 124
Do you have old .mdf and .ldf files(not of the day when DB got corrupted)? you might have taken file backup on some storage.... so get it and save it some where on this server.

If yes, just delete the current .mdf and .ldf as the DB is any which ways useless now.

attach the old .mdf and .ldf files and see if that works.... (as the chances are very less).... if not then I'm sorry to say that the DB is gone now.....

Rgds,
Pankaj
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47189 Visits: 44356
pankaj.baluni (1/5/2010)
if not then I'm sorry to say that the DB is gone now.....


That is not necessarily true. There are ways to repair a SQL database with varying amounts of data loss. Until they have been tried you cannot say that the DB is gone. Recommending to delete the mdf without being 1000% sure that it is completely irreparable is very poor advice.

I'm still waiting for the output of CheckDB to see exactly what's wrong. Sudhakara, can you run the CheckDB statement I gave you above and post the results?


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


steveb.
steveb.
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2848 Visits: 7195
pankaj.baluni (1/5/2010)
Do you have old .mdf and .ldf files(not of the day when DB got corrupted)? you might have taken file backup on some storage.... so get it and save it some where on this server.

If yes, just delete the current .mdf and .ldf as the DB is any which ways useless now.

attach the old .mdf and .ldf files and see if that works.... (as the chances are very less).... if not then I'm sorry to say that the DB is gone now.....


I wouldn't be so quick to delete the .mdf file, there are different types of corruption and it can be possbile to recover the data and the amount of data lost depends on the corruption.

for example a corrupt NC index would not be as big issues as a corrupt Clustered index.
jmartinez-847815
jmartinez-847815
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 38
I am sorry to hear of your data loss. It is standard operating procedure at my company to always take a backup of any database, live or test, before making any changes; and to store the backup to a different server on the network. This is in addition to any regularly scheduled nightly backups. Then, every other weekend, we select a handful of databases in our backup sets to restore to a test server and verify our backups are good. This is all because we lost data several years back. Nothing teaches you paranoia more than data loss!

Jack
sparky-407434
sparky-407434
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 494
There is a solution outside of SQLServer if the normal database backup method fails. Is there a backup performed of the actual files on the server - e.g C:\ drive contents etc using e.g. Veritas or similar? Or check if there are any Shadow copies of the C:\ drive (right click on C:\ drive, properties, Shadow Copies. If so you can recover to the time of the last back up of the files. If Shadow Copy is not enabled - then enable it for the future - it is a life saver.
bhoward 86472
bhoward 86472
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 0
Getting your nuts busted a bit cause many of us have been there...not me of course ;-)

Doesn't Red-Gate have some tools for recovery via the log file? I used them once testing out the suite and was able to recover transactional data. I don't know much, as a programmer, about log files except they fill up the hard drive and make my head hurt, but if it is complete would that work?

www.red-gate.com

They have trials...no pun intended ...
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4461 Visits: 9829
bhoward 86472 (1/8/2010)
Getting your nuts busted a bit cause many of us have been there...not me of course ;-)

Doesn't Red-Gate have some tools for recovery via the log file? I used them once testing out the suite and was able to recover transactional data. I don't know much, as a programmer, about log files except they fill up the hard drive and make my head hurt, but if it is complete would that work?

www.red-gate.com

They have trials...no pun intended ...


These tools only work *if you have a backup* of the database in the first place. Without a backup, you cannot scan the transaction log. If your database is in simple recovery - there might not be anything in the transaction log for you to recover from.

Note: if you have never backed up your database, the transaction log is in a psuedo-simple recovery model and not saved anyways.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search