Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Recover Data File - MDF Expand / Collapse
Author
Message
Posted Monday, January 4, 2010 5:44 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 4:14 AM
Points: 709, Visits: 1,430
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"
Post #841804
Posted Monday, January 4, 2010 5:57 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 3:24 PM
Points: 17,812, Visits: 15,737
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
Post #841811
Posted Tuesday, January 5, 2010 6:49 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:15 PM
Points: 2,832, Visits: 8,508
Are your other databases backed up ?

This might help show holes in your backup strategy:

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



Post #842037
Posted Tuesday, January 5, 2010 7:35 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 23, 2011 10:43 AM
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
Post #842093
Posted Tuesday, January 5, 2010 8:02 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 4:08 PM
Points: 40,177, Visits: 36,580
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 2008, MVP
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

Post #842119
Posted Tuesday, January 5, 2010 8:26 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, May 30, 2014 6:27 PM
Points: 2,808, Visits: 7,175
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.
Post #842133
Posted Wednesday, January 6, 2010 11:25 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 10, 2010 11:36 AM
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

Post #843056
Posted Thursday, January 7, 2010 8:03 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, December 2, 2013 5:25 AM
Points: 58, Visits: 349
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.
Post #843577
Posted Friday, January 8, 2010 3:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 8, 2010 3:33 PM
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 ...
Post #844729
Posted Friday, January 8, 2010 4:36 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, October 2, 2014 12:09 PM
Points: 4,358, Visits: 9,538
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 opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #844758
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse