Trending SQL Server Tips

Attach Database in SQL Server


Jun 22, 2012

Introduction

In this article we will see how to Attach Database in SQL Server 2008 R2. You can use database Detach and Attach Method to move database files from one location to another location on the same server. However, the steps mentioned in this article are applicable on SQL Server 2005 and higher versions. To know more about how to Detach Database in SQL Server refer the following article “Detach Database in SQL Server”.

Different ways to Attach Database in SQL Server

  • Attach Database in SQL Server Using SQL Server Management Studio (SSMS)
  • Attach Database in SQL Server Using TSQL Script

Permissions to Attach Database in SQL Server

You need membership in the db_owner fixed database role to Attach Database in SQL Server.

Important Notes:

How to Attach Database in SQL Server Using SQL Server Management Studio

1. Connect to SQL Server Using SQL Server Management Studio

2. Right click Databases -> Attach… option from the drop down list as shown in the snippet below.

Attach Database in SQL Server Using SSMS or Attach Database in SQL Server Using SQL Server Management Studio

3. In Attach Databases dialog box click on Add… button as shown in the snippet below.

Attach Database Dialog Box in SQL Server Management Studio

4. In Locate Database Files dialog box select the .MDF file of the database and click OK to return to Attach Database dialog box.

Locate Database Dialog Box in SQL Server Management Studio

5. In Attach Databases dialog box; under Databases to attach and Database Details you will see all the relevant information with respect to the database which you are attaching in SQL Server. Finally, click OK to attach the database to SQL Server.

Attach Database Dialog Box in SSMS

How to Attach Database in SQL Server Using TSQL Script

Database Administrator can attach database in SQL Server using the below TSQL script.

USE [master]
GO
CREATE DATABASE [MyTechMantra] ON 
( FILENAME = N'D:\MSSQL\Data\MyTechMantra.mdf' ),
( FILENAME = N'D:\MSSQL\Data\MyTechMantra_log.ldf' )
 FOR ATTACH
GO

Conclusion

In this article you have seen the steps to attach a database in SQL Server. You can use Database Detach and Attach option to temporarily remove a database from an instance of SQL Server or to move the database file to a new location. It is always recommended to use Database Backup and Restore option to move a database form one server to another server.

Continue Free Learning...

  • Please leave below your valuable feedback for this article.
  • Feel Free to refer this article to your friends and colleagues using the below “Share this Article” option.
  • Do subscriber to our News Letter to continue your free learning.
  • Don’t forget to Like Us on Facebook and do follow us on Twitter for latest updates.

Share this Article


Geeks who read this article also read…



Follow @MyTechMantra on Twitter
We're on Facebook
Bookmark and Share

"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect your privacy and you can unsubscribe at any time."