Hey Doc, can we attach only the data file in SQL Server?

“Hey Doc, I have salvaged the MDF file of an important database. Can I attach it to SQL Server in order to use it?” was the question from an old colleague.

Yes, you can…

“And oh! I don’t have the log file. And I renamed the MDF file by mistake too.”

No problem…

This is how you do it…

When you bring up the Attach Databases dialog and select the salvaged and renamed file, you would see the following on it.

Attach Database Dialog

The second grid lists the original database files followed by the Not Found message. This is obviously since you do not have them in the original location. At this stage you cannot move ahead by clicking on OK. You will have to remove both the entries using the Remove button below that grid.

Then you have to add the new data file; click on the Add Catalog button, then browse and select the new MDF file (the one which you had salvaged and renamed). You can then optionally change the name of the new (to be attached) database from the Attach As textbox.

Attach Database Properties

Click on OK and Voila!, you’ve got your old database back.