Database Corruption Part 4 :: Recovering from a failed disk

  • Article

It was a nice day a few days back - nice sunny day, with moderate temperatures. I got up early and after spending some time reading my favorite articles, made my way to office. In office, I realized I hadn't much work lying ahead; so sat down wondering how to account for my day.

Suddenly the phone on my desk rang - tring! tring!

"Hi, this is Suhas. How can I help you?"
"I... I lost my disk...!"
"What!"
"My disk crashed. It had my database..."
"You mean the database files?"
"Yes. I had two disks, one had the mdf file and the other had the ldf."
"And, you lost both?"
"No, just one..."
"Ok, so which one did you loose?"
"The one that had the mdf."
"Oh boy! Do you have a backup of the database."
"I do, but it's over 6 months old... Please help me get my data back. You know, I will get fired if I loose the data..."

That's how the conversation started that day. Needless to say, this is one of the situations you would not like to see yourselves in. However, in PSS, we do come across situations like this, when going back to the last backup is not an option, and we have to recover as much data as possible. However, as I have already mentioned in my first blog on Database Corruption, Microsoft Product Support Services (PSS) does not guarantee that if you call in with a database corruption issue, PSS would recover all your data. All support that PSS provides in corruption cases is on "best efforts basis", meaning that PSS will provide commercially reasonable efforts to recover your database or data off your corrupted database using documented and undocumented commands and procedures. However, 100% data recovery is not guaranteed.

In this case, however, we were able to recover the database back. There were multiple points of failure; however, luck was on our side. Here is what we did:

  1. First thing that we did is:

    RESTORE HEADERONLY FROM DISK = 'Full path to the backup set'

    We were basically looking for 2 options, the Recovery Model and the Backup Type.

    Had the Recovery Model been "Simple" or "Bulk-Logged", the story would have ended there itself. Moreover, at this point, we are still not sure if the Database Recovery Model had been changed; we were trying our luck. Had it been changed, that would have been the end of the story as well. Also, the Backup Type was "Full", so, we were good to go with this backup.

  2. We now stopped the SQL Server instance and renamed the existing log file (the LDF file). We had actually planned to drop this database, and we did not want the LDF file to get deleted.

  3. Now, we started the SQL Server and dropped the database. Drop completed successfully, leaving behind the LDF file.

  4. At this point, we created a new database, and pointed the LDF file of the new database to the location where the old LDF file existed. This was to save us the task of copying the old LDF file over to the new location; the old LDF file was about 300 GB in size.

  5. We now stopped the SQL Server, and replaced the LDF file of the newly created database by the old LDF file.

  6. We started the SQL Server, and, as expected, the database was in Suspect Mode.

  7. We now, issued the following command:

    BACKUP LOG DatabaseName
    TO DISK = 'Full path to TRN Backup file'
    WITH NO_TRUNCATE

  8. This command completed successfully and we had a Tail Backup of the Log File. I would like to mention here, that we had a point-of-failure here. Had the Recovery Model of the database been changed after the Full Backup, this command would have failed.

  9. Now, we were all set to restore the database. So, we proceeded with restoring the Full Backup. We issued the command:

    RESTORE DATABASE NewDatabaseName
    FROM DISK = Full path to the FULL Backup'
    WITH
    MOVE 'Data File Name' TO 'Full path to MDF File location',
    MOVE 'Log File Name' TO 'Full path to LDF File location',
    NORECOVERY

  10. The Full Backup was restored and the database was in Recovering Mode.

  11. Next step was to restore the Log Backup. We issued the command:

    RESTORE LOG DatabaseName
    FROM DISK = 'Full path to LDF File location'
    WITH RECOVERY

    The backup restored successfully, and the database was back up online!!

    Here, again, we had a point of failure. If the Full Backup that we had restored not been the Last Full Backup; meaning, had there been another Full Backup after the backup set we had restored, the restoration of the Tail Log Backup would have failed!

However, we WERE lucky!!!

Disclaimer: All information provided here is my personal opinion and is neither verified nor approved by Microsoft before it is published. All information, and code samples, if any, is provided "AS IS" with no warranties and confers no rights.