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 12»»

How to recover .mdf file database? Expand / Collapse
Author
Message
Posted Thursday, May 8, 2014 10:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 8, 2014 10:32 AM
Points: 1, Visits: 0
I am having one of my database in SUSPECT mode.

1) I tried to Detach the database and than attach it, but it did not allow me to detach from the begging.

2) I tried to run the following commands:
ALTER DATABASE Database_Name SET EMERGENCY (Successfully).
ALTER DATABASE Database_Name SET SINGLE_USER (Successfully).
DBCC CHECKDB('Database_Name', REPAIR_ALLOW_DATA_LOSS) (Failed).


All these actions can't provide proper result. I'm using SQL Server 2014. I supposed it can't be possible in such newest version of SQL.
Post #1569012
Posted Thursday, May 8, 2014 11:17 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: Today @ 8:47 AM
Points: 42,479, Visits: 35,547
Restore from backup.

Never detach a suspect database. It won't fix anything, it will only make things far worse.



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 #1569035
Posted Friday, May 9, 2014 8:32 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:37 AM
Points: 1,604, Visits: 4,594
Restore from backup is best, if that's an option.

How successful you are with attaching a suspect database and getting it back in normal multiuser mode depends on why and how the database was corrupted in the first place. Maybe the first step should be to stop the SQL Server service and check the mssql errors logs, windows event logs, and storage for errors. You don't want to re-attach the database on a faulty subsystem.

Paul Randal's blogs are perhaps the single best place to look when you find yourself in this type of jam. Read the article below and related posts.

Creating, detaching, re-attaching, and fixing a SUSPECT database
http://www.sqlskills.com/blogs/paul/creating-detaching-re-attaching-and-fixing-a-suspect-database/
Post #1569308
Posted Friday, May 9, 2014 8:39 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: Today @ 8:47 AM
Points: 42,479, Visits: 35,547
Eric M Russell (5/9/2014)
Restore from backup is best, if that's an option.


In this case it's about the only option. The repair failed, so the damage is irreparable, so it's restore backup or recreate database.

Detaching a suspect database won't fix anything, Prior to SQL 2012 (I think), SQL would happily let you detach a suspect DB but it wouldn't re-attach. The complex hack-db-back-in would be needed to get back to the point of having a suspect DB. 2014 at least prevents detaching a suspect DB, 2012 I think also did that



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 #1569313
Posted Tuesday, May 13, 2014 7:38 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 7:03 AM
Points: 294, Visits: 582
Restore is your only option. I had the same thing happen to me, I cannot stress that there is nothing else that you can do but restore. Good Luck.

MCSA SQL Server 2012
Post #1570306
Posted Tuesday, May 13, 2014 8:06 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:37 AM
Points: 1,604, Visits: 4,594
If you're the DBA, then you want to explore every possible fix before going back to management and explaining that data has been lost and there is no hope of recovery. Of course restoring from backup is the best (and perhaps only) option. The DBA needs to be swatted across his or her behind if there is no backup available, but an outside consultant sometimes steps into a less than ideal situation and our job to is help the client get themselves out of a jam.

There are more than a handful of 3rd party tools that claim the ability to repair a corrupted SQL Server .mdf file. I've never used any of them, so I can't vouch for whether any specific tool actually works.

Bing or Google the following:
"sql server" mdf file repair

If anyone here has actually had success in the field with one of these tools, then let us know.
Post #1570339
Posted Wednesday, May 14, 2014 1:07 AM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 5:10 AM
Points: 112, Visits: 1,207
I have tried & successfully recover data from corrupt mdf file using Stellar Phoenix SQL Database Repair software from Stellar Phoenix. I have tested this tool on following corruption scenarios:

1. Metadata Corruption
2. Consistency corruption
3. Clustered Index Leaf Level corruption
4. GAM/SGAM/PFS page corruption
5. Boot Page corruption and some more

But it doesn't support SQL server 2014.


SQL Database Recovery Expert
Post #1570659
Posted Monday, June 2, 2014 5:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 4:51 AM
Points: 5, Visits: 11
Inbuilt dbcc checkdb and dbcc dbrepair statements can be executed to repair corrupt MDF file of SQL Server. You may read the following blog article and note the statements. After noting it, try repairing your MDF files by executing these statements as a new query.

nalavadebela.wordpress.com/2014/04/05/how-to-repair-corrupt-mdf-file-of-microsoft-sql-server

This solution is the only manual solution and doesn't involve any cost.
Post #1576521
Posted Monday, June 2, 2014 1:12 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:03 AM
Points: 6,194, Visits: 13,348
jordanfranz (5/8/2014)
I am having one of my database in SUSPECT mode.

1) I tried to Detach the database and than attach it, but it did not allow me to detach from the begging.

2) I tried to run the following commands:
ALTER DATABASE Database_Name SET EMERGENCY (Successfully).
ALTER DATABASE Database_Name SET SINGLE_USER (Successfully).
DBCC CHECKDB('Database_Name', REPAIR_ALLOW_DATA_LOSS) (Failed).


All these actions can't provide proper result. I'm using SQL Server 2014. I supposed it can't be possible in such newest version of SQL.

For future reference, immediately upon seeing the suspect database you should

  • ALTER DATABASE Database_Name SET EMERGENCY

  • ALTER DATABASE Database_Name SET SINGLE_USER

  • DBCC CHECKDB('Database_Name') WITH ALL_ERRORMSGS, NO_INFOMSGS



Find out what the issues are and make sure you have backups on standby for an immediate restore.


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1576739
Posted Monday, June 2, 2014 3:38 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:06 PM
Points: 36,786, Visits: 31,243
betty_bakke (6/2/2014)
Inbuilt dbcc checkdb and dbcc dbrepair statements can be executed to repair corrupt MDF file of SQL Server. You may read the following blog article and note the statements. After noting it, try repairing your MDF files by executing these statements as a new query.

nalavadebela.wordpress.com/2014/04/05/how-to-repair-corrupt-mdf-file-of-microsoft-sql-server

This solution is the only manual solution and doesn't involve any cost.


No cost, eh? Ok, take a look at one of those statements and tell me what the actual cost is...

DBCC DATABASE (database_name, REPAIR_ALLOW_DATA_LOSS)



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1576787
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse