DBAtlas · Database operations atlas

Move a data/log file to another location

SQL ServerStorage

Command

-- 1) Tell SQL Server the new path (nothing is moved yet):
ALTER DATABASE [Customer]
MODIFY FILE (NAME = N'Customer_data',
             FILENAME = N'G:\Data\Customer_data.mdf');

-- 2) Take the database offline:
ALTER DATABASE [Customer] SET OFFLINE WITH ROLLBACK IMMEDIATE;

-- 3) Physically copy/move the file to the new path AT THE OPERATING SYSTEM level (G:\Data\...).

-- 4) Bring it online:
ALTER DATABASE [Customer] SET ONLINE;

What it does

Changes the physical location of a database file: the new path is defined in metadata, the file is moved physically while the database is offline, then the database is brought online.

When to use it

When replacing a disk or moving to another drive to gain space.

Caution

OFFLINE + ROLLBACK IMMEDIATE drops all active connections; plan for downtime. Do not bring the database ONLINE before actually copying the file to the new path in the OS, or the database becomes SUSPECT. tempdb requires a different method (MODIFY FILE + SQL Server service restart).

Open in DBAtlas →

Related