Altirium logo

Recovering dropped table data from an MSSQL MDF file

Many off-the-shelf  Microsoft SQL Recovery tools state that they can recover from corrupt files, deleted data and some claim to recover from dropped tables, so the recent arrival of an MSSQL Recovery into the lab (all of the tables within the database had been dropped) gave us the ideal opportunity to undertake some tests. From looking at the MDF file of the database, the data was still present, yet out of the 4 or so packages we tried “NONE” of them could retrieve any of the dropped tables, yet we were still able recover the required data for our client.

The problem was that the database had been migrated to a new server. Since the migration, the database had not been backed up although the transaction files were still complete from the time of the migration. In error, all the tables in the database had been dropped. As soon as the mistake was noticed the server was taken off-line and copies of the database MDF and LDF files were made so the data was still in the files, just could not be accessed.

Now, I am not a MSSQL expert I am a data recovery engineer so my knowledge of MSSQL is from a “Technical User” standpoint. Whilst examining the files with our trusted hex editor, Winhex, we thought we try downloading some “off the shelf” recovery tools that looked as if they would do what was required.

The software we downloaded and tested were:

  • ApexSQL, Recover
  • officerecovery.com, Recovery for SQL Server
  • Kernel, SQL Database Recovery
  • Stellar Phoenix SQL Recovery

Of the above however, only ApexSQL Recover claimed that it could recover from dropped tables so we weren’t very optimistic.

All the software we tested went through the motions of appearing to scan the entire data file but when it came to results, they were as suspected and no data from the dropped tables was reported to be recoverable.

Fortunately the software testing didn’t distract us too much from our usual approach of “looking at the data”.

Now it used to be that internal data structures of proprietary files, such as MDF files, had to be reverse engineered from scratch. Fortunately though for the MDF file structure, much information can be found on the internet, even if not completely, but it’s a good starting point.

From visually interrogating the file we could see that the required data was still present within the MDF file. The 1st step in actually recovering the data was to break out the pages for each of the tables, both internal and user tables. From this we could then find the data that related to what our client required recovering. The schemas for the tables were not available in the active data because the tables had been dropped and not just truncated. So once we’d located the correct data files from the data generated from our 1st process we then set about coding software to turn the data in those files into a CSV type format which could later be used to repopulate a database.

The outcome was that we were able to recover the data that the client wanted, approximately 3 million records, and they were able to use this to help rebuild their system, proving that sometimes there is nothing better for recovery than the skills of an experienced data recovery team. It also begs the question, how many times have people run recovery software that has failed to recover data and believed that this meant there was nothing that could be done, when with professional help a recovery of data could have been achieved.

References

http://improve.dk/archive/2011/05/19/reverse-engineering-sql-server-page-headers.aspx

http://www.sqlskills.com/blogs/paul/post/Inside-the-Storage-Engine-Anatomy-of-a-page.aspx

VN:F [1.9.22_1171]
Rating: 10.0/10 (1 vote cast)
Recovering dropped table data from an MSSQL MDF file, 10.0 out of 10 based on 1 rating

Leave a Reply