SQL SERVER – Move Database Files MDF and LDF to Another Location

When a novice DBA or Developer create a database they use SQL Server Management Studio to create new database. Additionally, the T-SQL script to create a database is very easy as well. You can just write CREATE DATABASE DatabaseName and it will create new database for you. The point to remember here is that it will create the database at the default location specified for SQL Server Instance (this default instance can be changed and we will see that in future blog posts). Now, once the database goes in production it will start to grow.

It is not common to keep the Database on the same location where OS is installed. Usually Database files are on SAN, Separate Disk Array or on SSDs. This is done usually for performance reason and manageability perspective. Now the challenges comes up when database which was installed at not preferred default location and needs to move to a different location. Here is the quick tutorial how you can do it.

Let us assume we have two folders loc1 and loc2. We want to move database files from loc1 to loc2.

USE MASTER;
GO
-- Take database in single user mode -- if you are facing errors
-- This may terminate your active transactions for database
ALTER DATABASE TestDB
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
-- Detach DB
EXEC MASTER.dbo.sp_detach_db @dbname = N'TestDB'
GO

Now move the files from loc1 to loc2. You can now reattach the files with new locations.

-- Move MDF File from Loc1 to Loc 2
-- Re-Attached DB
CREATE DATABASE [TestDB] ON
( FILENAME = N'F:\loc2\TestDB.mdf' ),
(
FILENAME = N'F:\loc2\TestDB_log.ldf' )
FOR ATTACH
GO

Well, we are done. There is little warning here for you: If you do ROLLBACK IMMEDIATE you may terminate your active transactions so do not use it randomly. Do it if you are confident that they are not needed or due to any reason there is a connection to the database which you are not able to kill manually after review.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

18 thoughts on “SQL SERVER – Move Database Files MDF and LDF to Another Location

  1. Hi,

    Is the procedure the same if I use database mirroring or replication? If I’m not wrong is not possible to detach db

    Regards

  2. Hi Pinal,

    Excellent Blog Congrats.!!!

    According to the topic, I prefer to use ALTER DATABASE to indicate new location of the database files. What do you think?

    – 0.- MAKE DATABASE BACKUP (IF POSSIBLE)

    – 1.-SET DATABASE OFFLINE
    ALTER DATABASE [TestDB] SET OFFLINE WITH ROLLBACK IMMEDIATE
    GO
    – 2.- MOVE FILES TO NEW LOCATION
    EXEC xp_cmdshell ‘COPY “C:\OriginalLocation\TestDB_log.ldf” “C:\NewLocation\TestDB_log.ldf”‘
    GO
    EXEC xp_cmdshell ‘COPY “C:\OriginalLocation\TestDB.mdf” “C:\NewLocation\TestDB.mdf”‘
    GO
    – 3.- ALTER DATABASE MODIFY FILE NAME
    – 3.- LOG
    ALTER DATABASE [TestDB] MODIFY FILE ( NAME = ‘TestDB_log’, FILENAME = ‘C:\NewLocation\TestDB_log.ldf’ )
    GO
    – 3.- DATA
    ALTER DATABASE [TestDB] MODIFY FILE ( NAME = ‘TestDB’, FILENAME = ‘C:\NewLocation\TestDB.mdf’ )
    GO
    – 4.- SET DATABASE ONLINE
    ALTER DATABASE [TestDB] SET ONLINE
    GO
    – OPTIONAL
    – 5.- CHECK INTEGRITY
    DBCC CHECKDB(‘TestDB’)
    GO
    – 6.- DELETE THE OLD DATABASE FILES
    EXEC xp_cmdshell ‘DEL /Q “C:\OriginalLocation\TestDB_log.ldf”‘
    GO
    EXEC xp_cmdshell ‘DEL /Q “C:\OriginalLocation\TestDB.mdf” ‘

  3. Pingback: SQL SERVER – Changing Default Installation Path for SQL Server « SQL Server Journey with SQL Authority

  4. Moving a standard datafile is as easy as it is written here but a better article would have included how to move the master database to a new location. Although it isn’t done often it is well worth knowing simply because there are occasions where this might save you a lot of time, effort and downtime.

  5. Hi guys,

    This is my situation. I have 68 servers, all local. Now i want these servers to be hosted a single or 2 servers. how do I go about the procedure of transferring the data without making disruptions on the operation on the server.

  6. Hie Dave,
    Requesting your help..thanx in advance. One of our pc was crashed and windows was not booting up. I have connected the hdd to another pc and copied the database (.mdf & .ldf). Now how will make the .bak files from the downloaded files?

  7. In SQL server, Detach/attach is on database level, can we do it on filegroup or file level? is there anyway that you can detach a filegroup or a physical file, copy it to different database server and install it to different db server?

  8. Hello Sir,
    How i can access database file in c# without using sql server i means i want work on mdf file in c# on window08 without installing sql server 2000,because it does not installed on window08

  9. hi pinal, i have a mdf and ldf files saved in sql server 2012 version and want to use them in sql server 2008 R2. how can i do with import/export utility or restore .pl help

  10. i am getting below error
    File activation failure. The physical file name “I:\MSSQL11.MSSQLSERVER\MSSQL\DATA\learner_log.ldf” may be incorrect.
    Msg 5170, Level 16, State 1, Line 1
    Cannot create file ‘I:\MSSQL11.MSSQLSERVER\MSSQL\DATA\Learner_log.ldf’ because it already exists. Change the file path or the file name, and retry the operation.
    Msg 1813, Level 16, State 2, Line 1
    Could not open new database ‘Learner’. CREATE DATABASE is aborted.

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