4

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

migrated from stackoverflow.com Jun 17 '12 at 5:07

This question came from our site for professional and enthusiast programmers.

  • An mdf is not really a backup - how did you make this "backup"? – Aaron Bertrand Jun 15 '12 at 19:01
  • The computer that the .mdf is on backs its entire drive as a .img to a server on our network. I know the .mdf isn't really a backup, but it should be the contents of the database at the time the image was made, correct? – PRNDL Development Studios Jun 15 '12 at 19:02
  • 1
    @PRNDLDevelopmentStudios Yes, that is absolutely correct. Bit for bit. But that doesn't necessarily mean that SQL Server will be able to work with that file. – Thomas Stringer Jun 15 '12 at 19:08
  • A cut paste of a live db is not backup. That .mdf is dynamic file. What backup does is make a point in time copy of that dynamic data. – Blam Jun 15 '12 at 20:18
6

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.

  • What if the .mdf wasn't really 'detached'? think of my backup as like a cut and paste from one live db to another. (See comment on OP for details) – PRNDL Development Studios Jun 15 '12 at 19:04
  • 1
    @PRNDLDevelopmentStudios Then there's a chance you might be out of luck and can't reattach or create a database from that file. That's the risk of not correctly detaching or running database backups. – Thomas Stringer Jun 15 '12 at 19:05
  • 3
    Like I said, if you can. If the MDF file won't attach, and you don't have a backup, this is going to be a painful lesson about why proper backups are important. – Aaron Bertrand Jun 15 '12 at 19:05
4

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.

0

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

  • 2
    If you are associated in any way with the product mentioned, you should disclose that. – Max Vernon Mar 21 '13 at 14:57

Your Answer

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