I have a database and want to move the
.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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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
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.
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
FileNameof MDF and LDF files. This step is important in case you don't want to end up searching for missing files...
Tasks -> Detach
Databasesnode of your server
You should be OK now. Info about the
ATTACH process can be found here.
In the link about
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
RESTORE. In the restore process you may define the new location of the database files.
To Move system database files follow these steps:
Log in as sa user in SSMS
Take a backup of the user created database for safety.
Kill all sessions connected to the Server from SSMS.
Execute the following command to check the current file location of system databases:
SELECT * FROM sys.master_files;
Identify the path and note the current path of the files.
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' )
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
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
Stop the instance of SQL Server.
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.)
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.
Log in as
sa user in SSMS and verify the location of the database files by executing the following query:
SELECT * FROM sys.master_files;
You do step by step:
close all connection
ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
set database with status offline
ALTER DATABASE MyDB SET OFFLINE
To new path
ALTER DATABASE MyDB MODIFY FILE ( Name = MyDB, Filename = 'N:\DATA\MyDB.MDF' )
set database with status online
ALTER DATABASE MyDB SET ONLINE
ALTER DATABASE MyDB SET MULTI_USER
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.
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';
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).
Follow these simple 4 steps:
SELECT name, physical_name AS CurrentLocation FROM sys.master_files WHERE database_id = DB_ID('DATABASE_NAME');
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');
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.
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);
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;
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.