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.

Click to Download Scripts

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

About these ads

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

  1. 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” ‘

    Like

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

  3. 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.

    Like

  4. 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.

    Like

  5. 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?

    Like

  6. 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?

    Like

  7. 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

    Like

  8. 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

    Like

  9. 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.

    Like

  10. I am getting Insufficient memory error in SQL Server 2012 while executing a 363 MB SQL file . Please help me to resolve this issue.

    cannot execute script.
    Additional Information
    Insufficient memory to continue the execution of the program. (mscorlib).

    Nick

    Like

  11. Pingback: SQL SERVER – Attach or Detach Database – SQL in Sixty Seconds #068 | Journey to SQL Authority with Pinal Dave

  12. Hi, how to attach the ldf file file from some other location, on thw same time the ldf will on other location. Ex:- mdf will on C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA and ldf will on d-drive .(folder with any name)

    Like

  13. Hi there,
    I have tried this script in SQL 2012. The database was set to Read-only after query was run successfully. And, I have encountered an error when trying to set the DB to Read-Write. Any idea how to bring the DB online?
    Thank you for help in advance!

    Yvonne

    Like

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