Move a Database Using Detach and Attach in SQL Server 2012

This article introduces how to Detach and Attach a database in SQL Server 2012 by using SQL Server Management Studio. The data and transaction log files of a database can be detached and then reattached to the same or another instance of SQL Server. Detaching and attaching a database is useful if you want to change the database to a different instance of SQL Server on the same computer or to move the database. To Detach and Attach a database in SQL Server 2012, Let's take a look at a practical example. The example is developed in SQL Server 2012 by using SQL Server Management Studio.

Detaching a Database

Detaching a database means removes the database from the Databases list in the Object Explorer of SQL Server but data and transaction log files will be remain same in the default directory for SQL Server 2012 database is:

C:\Program Files\Microsoft SQL Server\MSSQL11.MYMSSQLSERVER\MSSQL\DATA

Follow these things for detaching database:

Now right-click on the database name in the Object Explorer. Point to Tasks, and then click Detach:

When we click on the Detach the following window will be opened:

Now click on OK to detach a database name from the Object Explorer.

Attaching a Database

Once a database is detached, you may distribute or back it up and once you're done, you may want to attach it again to start using it. When you attach a database, all data files (MDF and transaction log) must be available. If any data file has a different path from when the database was first created or last attached, you must specify the current path of the file. 

Follow these things for attaching a database:

Now open Object Explorer and right-click on Databases. Point to Attach:

Now click the Attach menu item. The following window will be opened:

Now click on the add button to browse a detached file or copied file.

Now click on the OK button. The files have been attached successfully in the Object Explorer.