How do I move SQL Server database files?

Question

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?

Answer 1

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
ALTER DATABASE foo MODIFY FILE (name='DB_Data1_log',filename='X:\NewDBFile\DB_Data1_log.ldf');

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.

Answer 2

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.

Answer 3

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.

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

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

7. Stop the instance of SQL Server.

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

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

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

Answer 4

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

Answer 5

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: https://www.itprotoday.com/sql-server/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

Answer 6

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

SELECT 'ALTER DATABASE ' + QUOTENAME(d.name) + 
       ' MODIFY FILE (name=' + QUOTENAME(f.name, '''') +
       ', 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 d.name <> 'master';

Note:

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

Answer 7

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

2. 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');

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

   

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

Answer 8

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.

Answer 9

Shrink and empty the secondary .mdf or .ldf before removing them and it will allow you to remove:

USE [DataFileRemoval];
go
DBCC SHRINKFILE ('DataFileRemoval_2', EMPTYFILE);

https://www.mssqltips.com/sqlservertip/4349/how-to-remove-a-sql-server-data-file-for-a-multidata-file-database/#:~:text=Removing%20a%20SQL%20Server%20Data%20File%20Fails&text=Right%20click%20on%20the%20database,the%20bottom%20of%20the%20window.

Answer 10

I just did it now following the above but had to make some changes for my requirment. Below is the steps:

Assuming DB name is AdventureWorks and you are having 'sa' credentials

-- First : Take the DB offline
ALTER DATABASE AdventureWorks SET OFFLINE WITH ROLLBACK IMMEDIATE;

-- Second : Find out the 'name' of file. Output is Data File Name and Log File Name
SELECT name, physical_name AS NewLocation, state_desc AS OnlineStatus
FROM sys.master_files  
WHERE database_id = DB_ID(N'AdventureWorks')  
GO

-- Third - update the 'Data' file path to the right value
USE master; --do this all from the master
ALTER DATABASE AdventureWorks
MODIFY FILE (name='AdventureWorks'
             ,filename='D:\DB\SQLData\AdventureWorks.mdf'); --Filename is new location

-- Fourth - update the 'Log' file path to the right value
USE master; --do this all from the master
ALTER DATABASE AdventureWorks
MODIFY FILE (name='AdventureWorks_log'
             ,filename='D:\DB\SQLLog\AdventureWorks.ldf'); --Filename is new location

-- Fifth : Set the DB Online
ALTER DATABASE AdventureWorks SET ONLINE;

Answer 11

I like Mike Fal's answer above, but sometimes I can't use this method. The Robocopy thinks the files still open. I don't know if there is something missing.

So I have to use the detach and reattach method. It seems a little riskier (or unsettling at the least), but it has always worked for me.

https://learn.microsoft.com/en-us/sql/relational-databases/databases/move-a-database-using-detach-and-attach-transact-sql?view=sql-server-ver15

Answer 12

In case your database has some unusual characters like a dash character:

ALTER DATABASE [SBO-COMMON] 
MODIFY FILE (NAME = 'SBO-COMMON', FILENAME = 'D:\Database\SBO-COMMON.mdf'); 

ALTER DATABASE [SBO-COMMON] 
MODIFY FILE (NAME = 'SBO-COMMON_log', FILENAME = 'E:\DatabaseLogs\SBO-COMMON_log.ldf');