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 in SQL Server Instance (this default instance, can be changed and we will see that in future blog posts). Now, once the database files 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 in SAN, Separate Disk Array or on SSDs. This is done usually for performance reason and manageability perspective. Now the challenges come 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.

SQL SERVER - Move Database Files MDF and LDF to Another Location MoveDB

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.

SQL SERVER - Move Database Files MDF and LDF to Another Location MoveDB1

-- 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 (https://blog.sqlauthority.com)

, , , ,
Previous Post
SQL SERVER – Storing Variable Values in Temporary Array or Temporary List
Next Post
SQLAuthority News – Windows Efficiency Tricks and Tips – Personal Technology Tip

Related Posts

49 Comments. Leave new

  • I am sorry to say Pinal .. The process you explained this is not correct.

    Reply
  • Question : If admin wants to move the database file from one location to other . Their is possibility that other users detached the Database and give it to admin. So now for admin he needs user permission to copy file from the Detach Database ..
    So how to get rid of the permission issue…

    Reply
  • i faced error:
    A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 – No process is on the other end of the pipe.)

    Reply
  • Hello Pinal,

    I ran into this error:

    “Msg 5120, Level 16, State 101, Line 1
    Unable to open the physical file “G:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\PTest_Log.ldf”. Operating system error 5: “5(Access is denied.)”.
    File activation failure. The physical file name “G:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\PTest_Log.ldf” may be incorrect.
    Msg 5181, Level 16, State 5, Line 1
    Could not restart database “PTest”. Reverting to the previous status.
    Msg 5069, Level 16, State 1, Line 1
    ALTER DATABASE statement failed.”

    This perhaps mean the SQL service account does not have permission to the new location? I’ve read somewhere that one would have to preserve the permission when copying the file to the new location. Not sure how to do that. Can you help please?

    Reply
  • Awesome Pinal, clean and simple.

    Reply
  • Hi Pinal,

    I have a similar issue to what Phu Huynh explained in his post on May 26, 2016. In December I detached my database, and moved my .mdf and .ldf from the F drive to the D drive on the same virtual machine.. I then re-attached. All is working fine. We did a reboot of the server just last week and the database would not come up. I looked in the Event Viewer and found that SQL Server was still trying to locate the files on the F drive which no longer exists. I’m really baffled on how/why this would happen. Even when restoring from backup, it would error telling me the F drive does not exists. The only way I could recover was to restore using SQL script and actually specify the D drive paths. It seems to me that detach/re-attaching a database isn’t changing a default for the database in the system databases. Any thought on how/why this would be? I use SQL Server 2014 SP2 standard edition.

    Thanks. John

    Reply
    • That can only happen when SQL configuration was restored to old state (typical master database restore). Any possibility that VM was restored to older state.

      Reply
  • ChenChi13☯™ (@carbonbasedkid)
    February 13, 2017 3:03 pm

    TITLE: Microsoft SQL Server Management Studio
    ——————————

    Cannot show requested dialog.

    ——————————
    ADDITIONAL INFORMATION:

    Cannot show requested dialog. (SqlMgmt)

    ——————————

    Object reference not set to an instance of an object. (SqlManagerUI)

    ——————————
    BUTTONS:

    OK
    ——————————

    Reply
  • ChenChi13☯™ (@carbonbasedkid)
    February 13, 2017 3:04 pm

    Msg 5011, Level 14, State 5, Line 3
    User does not have permission to alter database ‘KasaMP’, the database does not exist, or the database is not in a state that allows access checks.
    Msg 5069, Level 16, State 1, Line 3
    ALTER DATABASE statement failed.
    Msg 15010, Level 16, State 1, Procedure sp_detach_db, Line 40
    The database ‘KasaMP’ does not exist. Supply a valid database name. To see available databases, use sys.databases.
    Msg 5120, Level 16, State 101, Line 4
    Unable to open the physical file “C:UsersSlavenDocumentsvisual studio 2013ProjectsPCKasaKasaMPDatabaseKasaMP.mdf”. Operating system error 2: “2(The system cannot find the file specified.)”.

    Reply
    • ChenChi13☯™ (@carbonbasedkid)
      February 13, 2017 3:10 pm

      OK I ran this piece of code to my original file path and it’s ok now LOL! But I guess I didnt’ have permissions to alter DB o_O??

      — Move MDF File from Loc1 to Loc 2
      — Re-Attached DB
      CREATE DATABASE [KasaMP] ON
      ( FILENAME = N’C:UsersSlavenKasaMP.mdf’ ),
      ( FILENAME = N’C:UsersSlavenKasaMP_log.ldf’ )
      FOR ATTACH
      GO

      Reply
  • ChenChi13☯™ (@carbonbasedkid)
    February 13, 2017 3:18 pm

    Msg 5011, Level 14, State 5, Line 3
    User does not have permission to alter database ‘KasaMP’, the database does not exist, or the database is not in a state that allows access checks.
    Msg 5069, Level 16, State 1, Line 3
    ALTER DATABASE statement failed.
    Msg 15010, Level 16, State 1, Procedure sp_detach_db, Line 40
    The database ‘KasaMP’ does not exist. Supply a valid database name. To see available databases, use sys.databases.

    It seemed like an awesome solution if the code worked :C

    Reply
  • ChenChi13☯™ (@carbonbasedkid)
    February 13, 2017 6:49 pm

    UPDATE: Even though I got some errors while runing the code, I right clicked on my DB -> Properties -> Files and I see the new path that I set… now I’m just confused but I guess that’s it – sorry for spaming your post ^ ^

    Reply
  • Pinal, thanks for the post but you are missing a vital step. For each file moved, you need to run the following ALTER statement:

    ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = ‘new_pathos_file_name’ );

    Essential! See here:
    https://docs.microsoft.com/en-us/sql/relational-databases/databases/move-user-databases?view=sql-server-2017

    Reply
  • Hi Pinal, Need to understand if we can move the SSISDB database (MSSQL server) the same way, as this contains the package(s) deployed, will this cause any disturbance with jobs which are scheduled with the packages.

    Please suggest

    Reply
  • Tried a few other ways, but found this one to be the easiest. Thank you.

    Reply
  • arvind barwtal
    March 5, 2018 1:25 pm

    HI Pinal,

    Can you let me know for the below if I can move the user DB data and log file by following below step

    DB; MS sequel Server 2014
    Old File Loc :C:/
    New File Loc : D:/
    Db name : customer

    Steps:-
    Right click on the name of the database (customer)
    Select Properties
    Go to the Files tab
    Make a note of the Path and FileName of MDF and LDF files.
    Right click on the database (customer)
    Select Tasks -> Detach
    Move the files from C: to D:
    Right click on the Databases node of your server
    Select Attach
    Click on the Add button
    Point to the new location as D:
    Click OK

    Please confirm me on the above if I can do this way for my user DB

    Reply

Leave a Reply

Menu