Restore one table from SQL Server data file (.mdf)

Question

I stupidly deleted most of the contents of a table in my database. I have a backup of the server which has the .mdf on it.

I assume I can make a new db and attach the backup .mdf. Then I can select the records from the backup database and insert them into my live database.

Is there an easier way to do this? Or is that the only way?

Thanks

Answer 1

Yes, the easiest way is to attach the MDF (if you can). Not all abandoned MDF files are attachable, it depends on how they were detached and what state the server was in when that happened. The proper way to take a backup is BACKUP DATABASE.

There isn't a way that I know of to extract only a single table from a detached MDF file, unless you're really, really comfortable with a hex editor.

So I think you are on the right track - attach your MDF as a different database name, and extract the data from the one table you need. Shark provided the syntax you'll need. However, again, a disk image does not necessarily leave an MDF in a usable state, so without a proper backup, all I can do is cross my fingers for you.

Answer 2

If all you have is an actual file backup of the MDF file, then what you can do is attempt to run:

create database YourNewDatabaseName
on
(
    filename = 'C:\YourMdfDir\YourBackupedFile.mdf'
)
for attach_rebuild_log

Then you can just move the data from the newly restored database. Note, the above method isn't guaranteed under certain scenarios of ongoing transactions at the time the MDF file was backed up.

In the future, create database backups instead of just backing up database files.

Another consideration is that if you plan to make data modifications that you want a little bit of an easier way to revert, consider creating a database snapshot prior to what you're about to do. It's much easier to work with a snapshot then have to wrestle with your ongoing situation.

Answer 3

To repair corrupt MDF files from crashed SQL Server, one can use RecoveryFix for SQL database. It recovers data in two modes i.e., Live SQL mode and Batch mode. The software supports recovery of Database objects from SQL Server 2000, 2005 and 2008 versions.

Thanks