Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
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
    
You can try to use a MDF Recovery tool to recover your database and Kernel for SQL Database can work wonders in cases where your SQL server MDF database has got corrupt or damaged. –  user16430 Mar 18 '13 at 6:46
add comment

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

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

3 Answers

up vote 6 down vote accepted

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.

share|improve this answer
    
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
2  
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
add comment

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.

share|improve this answer
add comment

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

share|improve this answer
    
If you are associated in any way with the product mentioned, you should disclose that. –  Max Vernon Mar 21 '13 at 14:57
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.