DB-유

Moving SQL Server Database Files to a New Drive

· mssql, storage, maintenance

The data drive fills up, or storage gets a new tier and the logs should go on the fast disks. Moving the .mdf, .ndf and .ldf files is straightforward, but I don't use detach and attach for it. Detaching loses full-text catalogs, breaks Availability Group membership and mirroring, and resets some metadata. I use ALTER DATABASE ... MODIFY FILE, take the database offline, move the files, and bring it back.

Below is that method for user databases, then the special cases: tempdb, which needs no copying, and master, which needs a startup parameter change.

Before you start: ALTER ANY DATABASE or sysadmin, Full Control for the service account on the new folders, a maintenance window (the database is down during the copy), and a recent backup. A copy error halfway is not the moment to find out the backup job has been failing.

Step 1: Record the current file layout

SELECT DB_NAME(database_id) AS database_name, name AS logical_name, type_desc,
       physical_name, size * 8 / 1024 AS size_mb
FROM sys.master_files
WHERE database_id = DB_ID('Sales');

Note every logical name. You will need them for the MODIFY FILE statements, and the total size tells you how long the copy will take.

Step 2: Prepare the destination folders

New-Item -ItemType Directory -Force -Path 'E:\SQLData', 'F:\SQLLog'
$acct = (Get-WmiObject Win32_Service -Filter "Name='MSSQLSERVER'").StartName
foreach ($p in 'E:\SQLData', 'F:\SQLLog') {
    icacls $p /grant "${acct}:(OI)(CI)F"
}

If the service runs as NT Service\MSSQLSERVER, $acct already has that form and icacls accepts it.

Step 3: Point the database at the new locations

This only changes the catalog. Nothing moves yet, and the change takes effect the next time the database starts.

ALTER DATABASE Sales MODIFY FILE (NAME = N'Sales',     FILENAME = N'E:\SQLData\Sales.mdf');
ALTER DATABASE Sales MODIFY FILE (NAME = N'Sales_log', FILENAME = N'F:\SQLLog\Sales_log.ldf');

SQL Server confirms with The file "Sales" has been modified in the system catalog. The new path will be used the next time the database is started.

Step 4: Take the database offline and move the files

ALTER DATABASE Sales SET OFFLINE WITH ROLLBACK IMMEDIATE;

ROLLBACK IMMEDIATE kicks out active connections. Then move the files; I copy rather than move, so the originals stay intact until I've confirmed the database comes back:

Copy-Item 'D:\SQLData\Sales.mdf'    'E:\SQLData\Sales.mdf'
Copy-Item 'D:\SQLData\Sales_log.ldf' 'F:\SQLLog\Sales_log.ldf'

For very large files robocopy /J (unbuffered I/O) is noticeably faster than Copy-Item.

Step 5: Bring the database online

ALTER DATABASE Sales SET ONLINE;

If it comes online, delete the old files. If it does not, the old files are still in place; set the paths back with MODIFY FILE and bring it online from the original location while you work out what went wrong.

Step 6: Moving tempdb

tempdb is recreated at every startup, so there is nothing to copy. Change the paths and restart:

ALTER DATABASE tempdb MODIFY FILE (NAME = N'tempdev', FILENAME = N'T:\TempDB\tempdb.mdf');
ALTER DATABASE tempdb MODIFY FILE (NAME = N'temp2',   FILENAME = N'T:\TempDB\tempdb_mssql_2.ndf');
ALTER DATABASE tempdb MODIFY FILE (NAME = N'templog', FILENAME = N'T:\TempDB\templog.ldf');

Run the first query from Step 1 against tempdb to get all the logical names (SQL Server 2016 setup creates one data file per core up to eight). Restart the service, and delete the old files afterwards.

Step 7: Moving master

master is the exception because its location is a startup parameter, not a catalog entry. In SQL Server Configuration Manager, open the SQL Server service properties, Startup Parameters tab, and edit the -d (master data file) and -l (master log file) entries. Stop the service, move master.mdf and mastlog.ldf, and start it again. model and msdb are moved like user databases, but the service must be stopped to copy them because they cannot be set offline.

Did it work?

SELECT DB_NAME(database_id), name, physical_name, state_desc
FROM sys.master_files m
JOIN sys.databases d ON d.database_id = m.database_id
WHERE d.name IN ('Sales', 'tempdb');

DBCC CHECKDB (Sales) WITH NO_INFOMSGS;

The physical names should show the new paths, state_desc should be ONLINE, and CHECKDB should return without errors. Run a backup afterwards so the backup history reflects the new layout.

Things that bit me

  • Msg 5120: Unable to open the physical file "E:\SQLData\Sales.mdf". Operating system error 5: "5(Access is denied.)". The service account lacks permission on the new folder or the copied file. Copying with your own account can give the file an ACL the service cannot read; re-run icacls on the file itself.
  • Msg 5133: Directory lookup for the file "E:\SQLData\Sales.mdf" failed with the operating system error 3(The system cannot find the path specified.). Typo in the path or the folder was not created. Fix the path with another MODIFY FILE before trying SET ONLINE again.
  • The service does not start after moving tempdb. The new folder does not exist or is not writable. Start SQL Server with -f -T3608 from the command line (sqlservr.exe -f -T3608), fix the tempdb paths with MODIFY FILE, and restart normally.
  • Msg 5011 / the database is in Recovery Pending after coming online. The log file was copied from the wrong location or is a stale copy. Set offline, copy the correct .ldf, set online again.

Keep the original files until CHECKDB and a fresh backup have both succeeded. I've never regretted waiting a day to delete them.