0

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?

2

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.

  • Yes, database are already being restored - I guess I will invest some time in restoring what is possible. – gotqn Jul 31 at 8:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.