How to move a user database to a different location

Posted on Updated on

Here are a couple of ways to move the database files to a different location other than the default.

Using SSMS:

1.  Using the SQL Server Management Console, right-click on the database, go to Task and Detach.  You may also have to restrict access to the database to SINGLE_USER before you can perform the Detach if there is a process currently using it.  To set it to SINGLE_USER mode, right-click on the database, properties, Options and scroll down to Restrict Access.

2.  Once the database is detached, copy/move the data file (.MDF) and log file (.LDF) to the new location, and then Attach the database back.  Right-click on Databases, select Attach.  Hit Add on the Attach Databases window and then browse to the new data file (.MDF) location and select the database.  If the log file (.LDF) is located on a separate disk, then you will also have to specify the path for the log file under the Database details window.  Then hit OK.

Using T-SQL scripts with Detach:

1.  You can run the following scripts to Detach, copy/move the data files and then perform the Attach command.  I will use the TEST database with an example path.

USE MASTER;

GO

ALTER DATABASE TEST

SET SINGLE_USER

WITH ROLLBACK IMMEDIATE;

GO

EXEC MASTER.DBO.SP_DETACH_DB @dbname = N’TEST

CREATE DATABASE [TEST] ON

( FILENAME = N’Y:\DATABASE\TEST.mdf‘ ),

( FILENAME = N’Z:\LOG\TEST_log.ldf‘ )

FOR ATTACH

GO

Using T-SQL Scripts with Alter Command:

ALTER DATABASE [TEST] SET OFFLINE

WITH ROLLBACK IMMEDIATE

GO

— Please note that the EXEC XP_CMDSHELL command might not work if it is not enabled in your SQL server.  You will need to enable it first or you can substitute this command with manually copying/moving the data files.

EXEC XP_CMDSHELL ‘COPY “C:\OLD_LOCATION\TEST.mdf” “Y:\DATABASE\TEST.mdf“‘

GO

EXEC XP_CMDSHELL ‘COPY “C:\OLD_LOCATION\TEST_LOG.ldf” “Z:\LOG\TEST_log.ldf“‘

GO

ALTER DATABASE [TEST] MODIFY FILE (NAME = ‘TEST‘, FILENAME = ‘Y:\DATABASE\TEST.mdf“)

GO

ALTER DATABASE [TEST] MODIFY FILE (NAME = ‘TEST_log‘, FILENAME = ‘Z:\LOG\TEST_log.ldf‘)

GO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s