Question: How do you move SQL Server MDF and LDF files to another location?
Answer: For a move within the same SQL Server instance, use ALTER DATABASE ... MODIFY FILE, take the database offline, move its physical files, and bring it online. Detach and attach is not required.
This question came up during a Comprehensive Database Performance Health Check. The customer wanted the data and log files on a different drive. Changing the recorded path and moving the file are two separate steps; SQL Server will not do the Windows file move for you.
The original D: to E: example
Start with a verified backup, an agreed outage, and a complete file inventory. A database can have more than one data or log file. Create the destination folder and confirm the Database Engine service can access it. Run the database commands from master, after applications have disconnected.
USE master;
GO
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'SQLAuthority');
GO
-- Replace these logical names and destination paths with your inventory.
ALTER DATABASE SQLAuthority MODIFY FILE
(NAME = SQLAuthority_Data, FILENAME = 'E:\data\SQLAuthority.mdf');
ALTER DATABASE SQLAuthority MODIFY FILE
(NAME = SQLAuthority_Logs, FILENAME = 'E:\data\SQLAuthority_Logs.ldf');
GO
ALTER DATABASE SQLAuthority SET OFFLINE;
GO
-- Now physically move every affected file to its specified destination.
-- Run the next statement only after the move and permissions check.
ALTER DATABASE SQLAuthority SET ONLINE;
GO
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'SQLAuthority');The first two ALTER statements record the new locations. SET OFFLINE needs exclusive access and can wait for other connections. Do not add WITH ROLLBACK IMMEDIATE casually, because it disconnects sessions and rolls back their work. While the database is offline, move the files to the destinations specified in the script.
If the database will not come online, check the recorded paths, the actual files and service permissions. If you must abandon the move, keep the database offline, restore the recorded paths and physical files to their original locations, then bring it online. Keep every original file until the move is verified. A cross-instance migration or moving system databases needs its own procedure.
Related: Rename a logical database file and rename a physical database file.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
4 Comments. Leave new
Note to Team: For your review.
This was a process/preventive strike on a database and server almost out of space on a server this schedule to be migrated.
In this automated world we work in with DBA Tools, It is sometimes nice to review an issue resolution that works well and can be shared. In the past moving database file with DETACH and ATTACH has been fret with errors. While backing up a database and restoring the MDF and NDF files to another location also works. The below is a lessoned that should be a good review for anyone on the team. Needless to say it does not rise to the occasion of all the good work done on OLB, systems with mounting points or AO-AG, but the below technique should be at the very least a good basic read/review.
Such is the case with below Ctask CTASK0261896 for Co# CHG0132034
The issue: SPPATLSQL06 L-drive was 94% utilized and during the Freeze the server would have run out of operational space thus bringing down all database on that heavily used BI-Server.
SystemName Description VolumeName DeviceID MediaType Size(GB) Free Space(GB) Free (%) Used (%)
———- ———– ———- ——– ——— ——– ————– ——– ——–
before SPPATLSQL06 Local Fixed Disk Data3 H: 12 1331 316 24 % 76 %
after SPPATLSQL06 Local Fixed Disk Data3 H: 12 1331 528 40 % 60 %
before SPPATLSQL06 Local Fixed Disk DATA L: 12 1450 92 6 % 94 %
after SPPATLSQL06 Local Fixed Disk DATA L: 12 1450 304 21 % 79 %
name CurrentLocation state_desc
Access_BA_Data L:\MSSQL\DATA\Access_BA.MDF ONLINE
Access_BA_Log M:\MSSQL\LOGS\Access_BA_2.LDF ONLINE
Access_BA_Index L:\MSSQL\DATA\Access_BA_1.NDF ONLINE
— Inventory report of script from Tempdb
Name FileSize(mb) SpaceUsed(mb) FreeSpace(mb) PCT_Used PCT_Free Disk Location and Filename
Access_BA_Index 2,052 2,031 21 99 1 L:\MSSQL\DATA\Access_BA_1.NDF
Access_BA_Data 215,489 192,664 22,825 89 11 L:\MSSQL\DATA\Access_BA.MDF
Access_BA_Log 1,301 101 1,201 8 92 M:\MSSQL\LOGS\Access_BA_2.LDF
The solution: Move an appropriately sized database to another data drive where it would fit on another drive thus freeing up NNN GB of space and in this case the [Access_BA] database MDF and NDF files. 217 GB.
The technique:
1. Put database [Access_BA] in Single user mode.
ALTER DATABASE Access_BA SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
2. Backup database [Access_BA] as a safety precaution 30 mins elapsed time
3. ALTER DATABASE Access_BA SET OFFLINE;
4. Move Access_BA.MDF and Access_BA_1.NDF from L-drive to H-drive (229 GB file move) Elapse time 25 mins.
5. ALTER DATABASE Access_BA
MODIFY FILE ( NAME = Access_BA_Data, FILENAME = ‘H:\data\Access_BA.MDF’ );
ALTER DATABASE Access_BA
MODIFY FILE ( NAME = Access_BA_Index, FILENAME = ‘H:\data\Access_BA_1.NDF’ );
–Message: The file “Access_BA_Data” has been modified in the system catalog. The new path will be used the next time the database is started.
6. ALTER DATABASE Access_BA SET ONLINE;
7. ALTER DATABASE [Access_BA] SET MULTI_USER WITH NO_WAIT
8. Validate access to database.
end.
Hank Freeman | Senior SQL Server DBA
Elavon – U.S.Bank
Thanks for this . You contribution is really helpful
After running the last step
ALTER DATABASE [Test_3.6_log] SET ONLINE;
This error comes
Msg 5011, Level 14, State 5, Line 18
User does not have permission to alter database ‘Test_3.6_log’, the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 18
ALTER DATABASE statement failed.
Thanks for this . Very helpful info.