How do I move SQL Server database files?

  • I have a database and want to move the .mdf and .ldf files to another location. But I do not want to stop the MSSQLSERVER service, and I do not want to export to another server.

    How can I do this?

  • You don't have to stop the SQL Server service to move database files, but you do have to take the specific database offline. This is because you can't move files while they're being accessed and taking the database offline stops the files from being used by the SQL Server application.

    The process to move them is fairly simple. Detach/Attach was already described, but it is not nearly this complex.

    Change the file locations with an ALTER DATABASE command:

    USE master; --do this all from the master
    MODIFY FILE (name='DB_Data1'
                 ,filename='X:\NewDBFile\DB_Data1.mdf'); --Filename is new location

    Note, you do not need to declare the old location in this command. Changing this path does not take effect immediately, but will be used the next time the database starts up.

    Set the database offline

    (I use WITH ROLLBACK IMMEDIATE to kick everyone out and rollback all currently open transactions)


    Move/Copy the files to the new location

    Just copy the files over using your favorite method (Click 'n Drag, XCopy, Copy-Item, Robocopy)

    Bring the database online


    You can see this described in more detail here.

    This worked for me. In my case I also had to move the LDF file too using the first command: e.g. `USE master; --do this all from the master ALTER DATABASE foo MODIFY FILE (name='DB_Data1_log' ,filename='X:\NewDBFile\DB_Data1_log.ldf'); --Filename is new location`

    Also can be used to move the full text index files just by updating the file name to match what you see in the files dialog.

    Make sure after moving the database file(s), that the "NT SERVICE\MSSQLSERVER" user has permissions to it, or you'll get errors when trying to bring the database back online.

    What is "name" supposed to be? In the documentation it has "logical_name." What do they mean?

    It wasn't crystal clear to me: order is important and not the same as listed in the answer: you need to 1) take db offline, 2) move files, 3) run `MODIFY FILE` command(s), then 4) put db online.

    @mlhDev Actually in my case `MODIFY FILE` order is changable. If you run MODIFY FILE first, it'd tell you the command runs successfully and the location will be changed after an offline-online operation (wording is different but you get the idea). The Offline->Move files->Online order does matter, though, for obvious reason. Also mark Demonslay335's note. File permission is important.

    I got the "name" from the Files dialog found within the Database Properties in MS SQL Server Mgmt Studio (v17.7). Right click on the database name, select Properties then Files. The "name" is in the Logical Name column.

    Adding the @Demonslay335 's important permissions comment, "NT SERVICE\MSSQL$SQLEXPRESS" worked for SQL Express for me.

    Using XCOPY with the switch to preserver permissions is a good way to not have to worry about permission errors.

  • MDF and LDF files are protected and cannot be moved while the database is online.

    If you don't mind stop the database from working, then you can DETACH it, move the files and then ATTACH it.

    • Right click on the name of the database
    • Select Properties
    • Go to the Files tab
    • Make a note of the Path and FileName of MDF and LDF files. This step is important in case you don't want to end up searching for missing files...
    • Right click on the database name
    • Select Tasks -> Detach
    • Move the files where you want
    • Right click on the Databases node of your server
    • Select Attach
    • Click on the Add button
    • Point to the new location
    • Click OK

    You should be OK now. Info about the DETACH - ATTACH process can be found here.

    In the link about DETACH - ATTACH there is a recomendation of using ALTER DATABASE statement if keeping the database on the same instance of SQL Server. More reference in Move User Databases.

    If you want to keep it running while moving, then do a BACKUP - RESTORE. In the restore process you may define the new location of the database files.

    I recommend opening SQL Management Studio as administrator to avoid file access issues when reattaching

  • To Move system database files follow these steps:

    1. Log in as sa user in SSMS

    2. Take a backup of the user created database for safety.

    3. Kill all sessions connected to the Server from SSMS.

    4. Execute the following command to check the current file location of system databases:

      USE master;
      SELECT * FROM sys.master_files;

    Identify the path and note the current path of the files.

    1. Use TSQL to change the file path for all database except master:

      ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' )


    MODIFY FILE ( NAME = tempdev
    , FILENAME = "DestinationPath\tempdb.mdf");
    MODIFY FILE ( NAME = templog
    , FILENAME = "DestinationPath\templog.ldf");
    MODIFY FILE ( NAME = modeldev
    , FILENAME = "DestinationPath\model.mdf");
    MODIFY FILE ( NAME = modellog
    , FILENAME = "DestinationPath\modellog.ldf");
    , FILENAME = "DestinationPath\msdbdata.mdf");
    , FILENAME = "DestinationPath\msdblog.ldf");

    Now the file location has been changed.

    Make sure to move both ldf and mdf files

    1. In SSMS right click the Server and select properties . Inside properties go to Database Settings . Change the database default locations for Data and Log to the destination path. Log off from the server.

      Eg : change C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\ to E:\projects\DataBaseFiles\MSSQL\DATA\

    2. Stop the instance of SQL Server.

    3. Copy the file or files to the new location. Use Robocopy to move the files in order to copy the acccess permissions to the destination folder. Open cmd and run as administrator and use the following command:

      robocopy /sec sourceFolder destinationFolder

    It's better to go to the source location to run the command. Delete other files other than system database files which are copied. Eg:

     robocopy /sec .\DATA E:\projects\DataBaseFiles\MSSQL\DATA\

    (Here we are moving all system database files to new location.)

    1. From the Start menu, point to All Programs, point to Microsoft SQL Server, point to Configuration Tools, and then click SQL Server Configuration Manager.

    Perform the follwing steps in SQL Server Configuration Manager:

    In the SQL Server Services node, right-click the instance of SQL Server (for example, SQL Server (MSSQLSERVER)) and choose Properties.. In the SQL Server (instance_name) Properties dialog box, click the Startup Parameters tab. In the Existing parameters box, select the –d parameter to move the master data file. Click Update to save the change. In the Specify a startup parameter box, change the parameter to the new path of the master database. In the Existing parameters box, select the –l parameter to move the master log file. Click Update to save the change. In the Specify a startup parameter box, change the parameter to the new path of the master database.

    The parameter value for the data file must follow the -d parameter and the value for the log file must follow the -l parameter. The following example shows the parameter values for the default location of the master data file.

    -dC:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\master.mdf
    -lC:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\mastlog.ldf

    If the planned relocation for the master data file is E:\SQLData, the parameter values would be changed as follows:


    Stop the instance of SQL Server by right-clicking the instance name and choosing Stop. Restart the instance of SQL Server.

    1. Log in as sa user in SSMS and verify the location of the database files by executing the following query:

      USE master;
      SELECT * FROM sys.master_files;

    All done.

    You could use the SQL function in to automate task for all databases:

  • There is a way to move database data files (not sure yet if there is a way to do this for logfiles) without taking the database offline.

    Dejan Nakarada-Kordic has an explanation + scripts for this method here:

    The short version is that you add another database file at the new location, and then use DBCC Shrinkfile, with option EMPTYFILE to move the data from the old file to the new file. When this is done you can remove the old data file.

    Not my solution, I was looking for this solution myself and found it very useful for our production environment.


  • You do step by step:

    1. close all connection

    2. set database with status offline

    3. To new path
      ALTER DATABASE MyDB MODIFY FILE ( Name = MyDB, Filename = 'N:\DATA\MyDB.MDF' )

    4. set database with status online

    5. set multi-user

  • Follow these simple 4 steps:

    1. Open SSMS and select New Query option from top of the window. Then Copy and execute the Following Query for find path of Database which we want to move to new path and note that path which you show in CurrentLocation column.

    SELECT name, physical_name AS CurrentLocation FROM sys.master_files WHERE database_id = DB_ID('DATABASE_NAME');

    1. Now go to that path and note the name of Database_Name.mdf & Database_Name_log.ldf files. Then execute following two Queries for move Database to Specific Location.



    1. Now, Stop the Server By Right Click on Server_Name which you can see in Object Explorer(Left Side).

      enter image description here

    2. Then Move both files from Old Path to New Path and again start the server by Right-Click on Server_Name. Confirm New Path of Database by again execute query of 1st step.

  • To supplement the existing answers: Here's a script to create the ALTER DATABASE ... MOVE ... statements for all databases:

           ' MODIFY FILE (name=' + QUOTENAME(, '''') +
           ', filename=' + QUOTENAME(REPLACE(f.physical_name, 'C:\', 'D:\'), '''') +
      FROM sys.master_files AS f 
           INNER JOIN sys.databases AS d ON f.database_id = d.database_id
     WHERE <> 'master';


    • Replace REPLACE(f.physical_name, 'C:\', 'D:\') by whatever transformation you want to make to the file paths.

    • master is exempt, since its path is determined by SQL Server's startup options (see, for example, this answer for details).

  • I am not sure that this is the best way (I would welcome any comments to tell me how it is not), but it's very simple (and quick if you have a small database):

    First, backup the database to a .bak file. Then, restore the database from the same .bak file, choosing the new .mdf and .ldf file locations under file options for the restore task.

    I wouldn't do it in a production environment ouside of a maintenance window, as you cannot access the database during the restore. Other methods I've seen above would have similar drawbacks though. After the restore task is done, you don't have to delete the old file. It's automatically done.

    This scenario has some drawbacks. When restoring, the original DB must be overwritten or renamed. For large databases, the method introduces serious IO overhead. Moving files as described in *detach-attach* or *alter db* methods is *way* faster. If the files are moved within the same NTFS partition, it's a metadata operation only.

    @Ali - Backup and Restore. May take longer, but is generally a safer path. See Aaron Bertrand's analysis at: Also:

    For very large databases, this approach (and the detach/attach approach) introduce substantial downtime. To avoid this, place the source database in full recovery mode, then perform the initial backup restore with destination DB left non-operational. Then backup/restore one or more transaction logs. The database will need to be down only during the final backup/restore of the transaction log, which can contain an arbitrarily small time and size. Obviously you'll also need to restore to a different name and then perform a name swap. This approach is roughly equivalent to log-shipping.

License under CC-BY-SA with attribution

Content dated before 6/26/2020 9:53 AM