SQL Server 2017 – Is It Possible to Restore a Database from Corrupted .mdf File

corruptionsql serversql-server-2017t-sql

I know that the answer of the question may vary depending on what exactly is broken in each .mdf file – only looking for advice if there is another way to approach this.

So, I have received some bad news from the database administrator. Something like this happened last night (that's the only info I have from him):

  1. a drive failed
  2. the drive was replace but during the rebuild it failed again .. and again
  3. it was new drive, so it was moved to a different drive slot on the chassis
  4. doing the above, I mistakenly pulled the cachcade drive which in turn locked up any volume with cachecade protection
  5. after re-inserting the cachecade drive, drive activity resumed (drives went to a “locked” state)

It seems the above steps lead to .mdf files corruption and not working properly. So, we started to force attached them to new SQL instance, following the steps below:

  1. Create new database
  2. delete its files

  3. add a corrupted .mdf and ldf files and restore them with the script below:

    USE master GO
ALTER DATABASE [] MODIFY FILE(NAME='', FILENAME= '') 
ALTER DATABASE [] MODIFY FILE(NAME='', FILENAME= '') 
GO

ALTER DATABASE [] SET ONLINE 
GO
DBCC TRACEON(3604) 
GO
ALTER DATABASE [] SET EMERGENCY 
GO

ALTER DATABASE [] SET SINGLE_USER 
GO
DBCC CHECKDB('[]', REPAIR_ALLOW_DATA_LOSS) 
WITH ALL_ERRORMSGS 
GO

ALTER DATABASE [] SET MULTI_USER 
GO

    He also told me that Repair errors are different per database and some database cannot be repaired at all.

So, have I any other options here or I need to check how the above goes for each database and move some of data (if possible) to database restored from older copy by hand?

Best Answer

If it were me?

Nuke it from orbit and restore from backup.

Stop any processes that rely on this database and restore from your most recent backups.

After you're stable, you can go through the academic exercise of trying to repair as much of the corrupt media as you've got the energy & budget to put up with. Assuming you have a strong understanding of the upstream & downstream dependencies of this media, you may be able to logically repair restore some of the data you lost in the time between the end of your last stable backup and the outage.

Related Question