Today I needed to transfer one of our development databases to another development environment. I had two choices: I could make a full backup/restore and drop the database since we did not want the same database stay on both servers or I could use the detach/attach database method. In order to eliminate some steps I decided to use detach/attach database method.
You can use Detach/Attach Database Method to move database files onto different servers. You can also use this methods to move database files from one location to another location on the same server.
But there are some of important notes you need to remember before you decided to use this methods:
- The database must first be detached.
- You need membership in the db_owner fixed database role or CREATE DATABASE, CREATE ANY DATABASE, or ALTER ANY DATABASE permissions to Attach Database in SQL Server.
- You will not be able to detach/attach database from a higher version of SQL Server to a lower version. Downgrade cannot be archived by using Backup/Restore Database Method and also by using Detach/Attach Database Method either.
- You also need to have permissions on the database folders to be able to move the database files.
You can use SQL Server Management Studio (SSMS) to attach database:
1. Connect to SQL Server Using SQL Server Management Studio
2. Right click Databases -> Attach… option from the drop down list
3. In Attach Databases dialog box click on Add… button
4. In Locate Database Files dialog box select the .MDF file of the database and click OK.
5. When you return to Attach Databases dialog box, you will see all the information of the database that you are attaching, under the Databases to attach and Database Details windows.
6. Click OK to attach the database to SQL Server.
Or you can use below T-SQL Script:
CREATE DATABASE [Database_Name] ON
( FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\DatabaseNameData.mdf’ ),
( FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\DatabaseNamelog.ldf’ )
FOR ATTACH;
GO
