How to move SQL server database from one location to another?

Do you want to change the location of your SQL server database? If yes then it is not a tough task! SQL server database files (data or transaction log) can be detached and reattached to the different instances of SQL server at the same machine or move to the another machine. Detaching & Reattaching is the best way to move the SQL server database from one location to another.

Steps to change the location: You will have to perform 3 tasks to change the location of your SQL server database from source computer to designation computer and the task are...

1. Detach the SQL server database from source computer.
2. Copy the database on the designation computer.
3. Attach the SQL server database on the designation computer.

Detach a SQL server database: Detaching is a process to remove database from the SQL server instance without altering the data within data and transaction log file. These files are used to reattach the database to another SQL server instance at the same machine or instance at the different machine.

How to Detach a Database: SQL server database administrator can detach a database by two methods:

1. Detach a database using stored procedure.
2. Detach a database using SQL server management studio (SSMS).

Detach a database using Stored Procedure: Run the below syntax to detach a database using stored procedure (sp_detach_db).

sp_detach_db [ @dbname= ] 'database_name'

[ , [ @skipchecks= ] 'skipchecks' ]

[ , [ @keepfulltextindexfile = ] 'KeepFulltextIndexFile' ]

Output: If output is '0' then you have successfully detach the database from instance, if '1' then you will get an error message on your screen. In case of failure, try to run the syntax again.

Detach a database using SQL server management studio: Follow below steps to detach a database using SSMS:

• Connect to the instance and then expand it.
• Now expand the databases, and select your desired user database.
• If database is in use currently then restrict it to single access.
• Right click on the database->Tasks->Detach.
• Detach dialog box will be open on your screen.
• Select the database from the database name column to detach.
• Verify that you are detaching the right database.
• Click on the 'OK' button to start the detach process.

Note: Always perform detach operation on the database when database is not in use.

Limitations: A database administrator can't detach a database when any of the following point is true:

1. Database is being mirrored in mirroring session.
2. Detach database is a system database.
3. Database is in suspect mode.
4. Database is replicated.

How to attach a database: Database administrator can attach a previously detached database from the same machine or database files are location at another machine. There are 3 methods available to attach a SQL server database files.

1. Attach a database using stored procedure
2. Attach a database using 'Create Database' statement
3. Attach a database using SQL server management studio

Attach a database using stored procedure: It is the easiest method to attach a database to the SQL server instance. Microsoft suggest that don't use this method to attach a production database using it because it will be remove in the future version of SQL server. To attach a database, run below stored procedure:

sp_attach_db [ @dbname= ] 'dbname'

, [ @filename1= ] 'filename_n' [ ,...16 ]

Where [ @dbname= ] 'dbname' is the name of database & [ @filename1= ] 'filename_n' is the physical file name.

Output: If output is '0' then you have successfully attach the database to an instance, if '1' then you will get an error message on your screen.

Attach a database using 'Create Database' statement: It is the recommended method for attaching a new or detached database to the SQL server instance. To attach a database using this method, run below T-SQL syntax:

To attach a database

CREATE DATABASE database_name

ON <filespec> [ ,...n ]

FOR { ATTACH [ WITH <service_broker_option> ]

| ATTACH_REBUILD_LOG }

[;]

Attach a database using SSMS: Follow below steps to attach a database using SSMS.

• Connect to the instance and then expand it.
• Now right click on the databases, and select attach.

• A attach dialog box will be open on your screen.
• Click on the 'Add' button to specify the database.

• Select the database from the disk drive for attach.
• Verify that you are attaching the right database.
• Click on the 'OK' button to start the attach process.

Note: To attach a database, all the data files (.mdf & .ndf) are necessary. If any of the data file is unavailable then you can't attach the database to SQL server instance.

Need to attach a database without Transaction log file: As I have mentioned above, for attaching a database all SQL server data file (.mdf & .ndf) are necessary but you can attach a database without transaction log file (.ldf). Follow below steps to test this.

• Download a sample database (AdventureWork) from the codeplex website.
• After downloading the sample database, you will have only sample data file not transaction log files.
• Now attach this sample database using SSMS.
• Connect to the instance and then expand it.
• Now right click on the databases, and select attach.
• A attach dialog box will be open on your screen.
• Click on the 'Add' button to specify the database.
• Select the database from the disk drive for attach.
• Verify that you are attaching the right database.
• To attach database without transaction log file (.ldf); select & remove transaction log file. Now click on 'OK' to attach.
• SQL server automatically creates a transaction log file (.ldf) for you while attaching SQL server database.

Summary: Now you are able to detach & reattach the SQL server database. Additionally you can attach database without the transaction log file.