How to Manually Attach Databases in Management Studio

In addition to the ability to integrate into the HELM Control Panel for the creation of new databases, Microsoft SQL Server provides users the capability to directly attach existing databases to an SQL instance with the use of SQL Server Management Studio.

Step 1

First, we will need to locate the databases files to attach by using Windows Explorer to navigate to them. In this case, we are attaching a database named, ‘example_db‘, located in the folder, ‘C:\Database‘, seen below.

Step 2

As you can see, there are two files which drive an SQL database: the ‘.MDF‘ file which contains the primary data, and the ‘.LDF‘ file which logs all transactions related to the database. Both files are necessary for the database to function correctly.

Now, we need to place them in the appropriate folder containing the other SQL databases on the server. Typically, this folder will be, ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data‘, however, depending on the data directory specified during installation, the database files may be in a different location.

It is not a requirement that all database files be kept in the same location, as they technically can be attached from any location on the server, however for the sake of organization, we will place the files in the specified data folder. Start by highlighting both files, right-clicking on them and choosing ‘Copy‘.

Step 3

Next, using Windows Explorer, navigate to the data directory specified above, and paste the files into them by right-clicking anywhere in the empty space within the folder, and choosing ‘Paste‘.

Step 4

You will now see the database files have been pasted into the folder, located beneath the existing databases files.

Having Trouble Implementing This Solution?

Why not let our Microsoft^® Certified Engineers do it for you.
Just $50.00 one time fee.

Request Immediate Expert Help 

Step 5

Now, we are ready to attach them. Open SQL Server Management Studio from the Start Menu. If the icon is not in the list of recently used programs, you can navigate to ‘All Programs –> Microsoft SQL Server 2005 –> SQL Server Management Studio’.

Step 6

Log in to Management Studio using your server’s name and Windows Authentication, and pressing ‘Connect‘.

Step 7

Once logged in, click the plus sign next to the ‘Databases‘ heading to see a list of existing databases within the SQL instance.

Step 8

Right-click on ‘Databases‘, and choose ‘Attach‘ from the list of available options.

Step 9

This will bring up the Attach Databases window. Click the button which says ‘Add‘.

Step 10

You should now be able to see a list of existing databases on the server, as Management Studio will default to the data directory mentioned above. Click on the database you wish to attach, ‘example_db.mdf‘, in this case, and click OK.

Step 11

Back at the Attach Databases window, it will confirm the locations of the primary data and transaction log files. ClickOK.

Step 12

As you can see, the database is now attached to the SQL instance.

Step 13

Now, we need to create a user for the database to allow for external connectivity. Click the plus sign next to ‘Security‘, right-click the ‘Logins‘ folder and click ‘New Login‘.

Step 14

There are many options on the New Login windows which need to be set for the database user to be created successfully:

• Enter your desired name for the database user in the Login Name field.
• Set the user to use SQL Authentication, to allow for external connectivity.
• Enter in and confirm your desired password.
• Uncheck the option for ‘Enforce password expiration‘, which will in turn disable the option for ‘User must change password at next login‘.
• Next to the ‘Default Database‘ drop-down menu, select the manually attached database.

Step 15

Once your desired options are set, click the option in the left-hand menu which says ‘User Mapping‘.

Step 16

In the User Mapping windows, there are two options which need to be set. The first is to place a check-mark next to the manually attached database in the ‘Map‘ column. Then, in the list of Database Membership Roles, place a check mark next to the ‘db_owner‘ role. Click OK once the options are in place.

Step 17

The newly created user can now be seen in the list of all SQL users within the instance.

You have now manually attached an SQL database using Management Studio. If you have any questions or concerns regarding this procedure, feel free to contact our Technical Support Department for assistance.