Coupon code copied
Server Intellect
(855) 850-HOST

Toll Free 24/7/365        (4678)

Get help from a Microsoft Certified Engineer.

Intellect Connect

Technical problem with your Windows Hosting?

Let our Microsoft Certified experts handle the problem for you.
Chat Now

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.

kb-attach-db-mgmt-studio-1

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‘.

kb-attach-db-mgmt-studio-2

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‘.

kb-attach-db-mgmt-studio-3

Step 4

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

kb-attach-db-mgmt-studio-4

Having Problems with Windows Server?

Try our troubleshooting service

  • Microsoft Certified Engineers
  • Remote Log-in to Your Servers
  • End-to-End Troubleshooting

Our Certified Technicians have been supporting Microsoft® products for well over a decade.

Tell us where you are getting stuck

Get Help Now!

Having Trouble Solving This Problem?

Let our Microsoft® Certified Engineers fix it for you.
Just $50.00 one time fee.

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’.

kb-attach-db-mgmt-studio-5

Step 6

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

kb-attach-db-mgmt-studio-6

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.

kb-attach-db-mgmt-studio-7

Step 8

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

kb-attach-db-mgmt-studio-8

Step 9

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

kb-attach-db-mgmt-studio-9

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.

kb-attach-db-mgmt-studio-10

Step 11

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

kb-attach-db-mgmt-studio-11

Step 12

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

kb-attach-db-mgmt-studio-12

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‘.

kb-attach-db-mgmt-studio-13

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.

kb-attach-db-mgmt-studio-14

Step 15

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

kb-attach-db-mgmt-studio-15

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.

kb-attach-db-mgmt-studio-16

Step 17

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

kb-attach-db-mgmt-studio-17

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.

Interested in letting our experts solve your IT problems for you?
Get a free, no-obligations consultation with one of our experts today!

Call us at (855) 850-HOST

Chat Now

Start Your Order

X

This form does not accept free e-mail accounts. Please enter a business e-mail to submit it.

Submit Form Cancel

Please wait...

Submitting your info. This may take less than a minute.