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.

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

  • 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

    Reply
  • it is possible to move tempdb in shard network location?

    Reply
  • it’s better to copy files than to move them because if there are trouble with big files, you had to restore the database.

    Reply
  • 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:OriginalLocationTestDB_log.ldf” “C:NewLocationTestDB_log.ldf”‘
    GO
    EXEC xp_cmdshell ‘COPY “C:OriginalLocationTestDB.mdf” “C:NewLocationTestDB.mdf”‘
    GO
    — 3.- ALTER DATABASE MODIFY FILE NAME
    — 3.- LOG
    ALTER DATABASE [TestDB] MODIFY FILE ( NAME = ‘TestDB_log’, FILENAME = ‘C:NewLocationTestDB_log.ldf’ )
    GO
    — 3.- DATA
    ALTER DATABASE [TestDB] MODIFY FILE ( NAME = ‘TestDB’, FILENAME = ‘C:NewLocationTestDB.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:OriginalLocationTestDB_log.ldf”‘
    GO
    EXEC xp_cmdshell ‘DEL /Q “C:OriginalLocationTestDB.mdf” ‘

    Reply
  • What if i want to create a backup and then want to move it

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

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

    Reply
  • is it work when database in replication mode

    Reply
  • 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?

    Reply
  • Marcos Lago
    June 27, 2013 4:19 am

    thank’s brother, helpfully

    Reply
  • 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?

    Reply
  • Hi Pinal,

    How we can move the .ldf & .mdf on standby configured with log shipping.
    How to move .mdf, .ldf, .tuf fle.

    Thanks

    Reply
  • 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

    Reply
  • 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

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

    Reply
  • Thanks so much

    Reply
  • 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

    Reply
  • 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)

    Reply
    • this is very good question, as most of the only need is; to move LDF file from location A to Location B

      Reply
  • 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

    Reply
    • I have fixed the problem myself. The problem was caused by the permission issue. The MSSQLSERVER account needs to be added to the destination folder with allowing full control permission.

      Reply
      • Thanks for sharing it Yvonne.

      • how to add MSSql server account to destination folder?

      • I find other solution: Use can login in Windows Authentication by: osql -S (servername) -E.
        And after you do the same as the article, it will be OK for writing.

  • pravinwadichar11Pravoin
    March 9, 2015 7:47 pm

    Hi ,
    It shows access denied . Follow is error occurred
    Unable to open the physical file “E:\FlexiPayroll.mdf”. Operating system error 5: “5(Access is denied.)”.

    Reply
    • You need to go to file, properties and security tab. Make sure SQL Service account has permissions.

      Reply

Leave a Reply Cancel reply

Menu
Exit mobile version