Attaching or Restoring via .mdf file on SQL Server

Ok, theres no doubt that restoring via proper backups is the best way to go but sometimes that is not the option available to you as much as we wish and pray not to be in that situation. For some scenarios it might the obvious way. One of my internal clients have .mdf files arriving as BAU.


I will use the SQL Server management studio for this. The task is very simple but few small points need to be taken care of otherwise we get error and alerts and the database does not get restored / attached.

Step 1: Copy the database in the standard location were you keep or intend to keep your database’s main .mdf file(s) on local drive(s).

Step 2: Start management studio.

Step 3: Right click Databases folder in Object Explorer window and Select “Attach…”

ScreenHunter_40 Nov. 18 11.13

Step 4: The following window will appear which I will explain with labels on sections of the screen.

ScreenHunter_40 Nov. 18 11.19



Step 5: Now.. The button labelled of course is the place where it all begins. Locate the .mdf file which you have as per Step 1 put in the right location where it will reside for the rest of its life.

Step 6: This screen tries to assume that you have the log file in the same location and lists it the area labelled B in the diagram. If you have the log file in some other place remove and add the right one or if you do not have it at all remove the file altogether.

Step 7: Press OK.

If you have removed the Log file that was assumed by this SQL Mgmt Studio screen. Then after pressing OK SQL will create a log file at the default location set in SQL Server decided during installation if not changed afterwards. You can find that easily in Mgmt Studio or otherwise use TSQL or registry. More about that later.



IT professional with wider interest in technology

Posted in Uncategorized

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: