97

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?

migrated from stackoverflow.com Oct 22 '13 at 22:28

This question came from our site for professional and enthusiast programmers.

144

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
ALTER DATABASE foo
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)

ALTER DATABASE foo SET OFFLINE WITH ROLLBACK IMMEDIATE;

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

ALTER DATABASE foo SET ONLINE;

You can see this described in more detail here.

  • 13
    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 – Dewi Rees Jun 26 '15 at 8:01
  • 1
    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. – DShook Sep 24 '15 at 20:46
  • 21
    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. – Demonslay335 Feb 24 '17 at 18:53
  • What is "name" supposed to be? In the documentation it has "logical_name." What do they mean? – johnny May 2 '18 at 14:38
  • 2
    @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. – Lionet Chen Jun 4 '18 at 0:21
46

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.

  • 2
    I recommend opening SQL Management Studio as administrator to avoid file access issues when reattaching – Simon_Weaver Feb 5 '17 at 6:55
6

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' )

Eg:

ALTER DATABASE tempdb
MODIFY FILE ( NAME = tempdev
, FILENAME = "DestinationPath\tempdb.mdf");

ALTER DATABASE tempdb
MODIFY FILE ( NAME = templog
, FILENAME = "DestinationPath\templog.ldf");

ALTER DATABASE model
MODIFY FILE ( NAME = modeldev
, FILENAME = "DestinationPath\model.mdf");

ALTER DATABASE model
MODIFY FILE ( NAME = modellog
, FILENAME = "DestinationPath\modellog.ldf");

ALTER DATABASE msdb
    MODIFY FILE ( NAME = MSDBData
, FILENAME = "DestinationPath\msdbdata.mdf");

ALTER DATABASE msdb
    MODIFY FILE ( NAME = MSDBLog
, 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:

-dE:\projects\DataBaseFiles\MSSQL\DATA\master.mdf
-lE:\projects\DataBaseFiles\MSSQL\DATA\mastlog.ldf

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.

3

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: http://sqlmag.com/t-sql/move-database-files-without-taking-database-offline

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.

Thorfinn

3

You do step by step:

  1. close all connection
    ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

  2. set database with status offline
    ALTER DATABASE MyDB SET OFFLINE

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

  4. set database with status online
    ALTER DATABASE MyDB SET ONLINE

  5. set multi-user
    ALTER DATABASE MyDB SET MULTI_USER

1

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.

  • 1
    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. – vonPryz Jul 1 '16 at 11:47
  • @Ali - Backup and Restore. May take longer, but is generally a safer path. See Aaron Bertrand's analysis at: blogs.sqlsentry.com/aaronbertrand/bad-habits-file-backups Also: sqlmag.com/blog/should-i-be-using-san-snapshots-backup-solution – RLF Jul 1 '16 at 17:31
  • 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. – Brian May 16 '18 at 14:41
0

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.

ALTER DATABASE DATABASE_NAME MODIFY FILE ( NAME = DATABASE_FILE_NAME , FILENAME = 'NEW_PATH\DATABASE_NAME.mdf');

ALTER DATABASE DATABASE_NAME MODIFY FILE ( NAME = DATABASE_FILE_NAME_log , FILENAME = 'NEW_PATH\DATABASE_FILE_NAME_log.ldf');

  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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy