萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server MDF/LDF 檔案移動完整操作:離線、修改路徑、實體移動、上線

場景

資料庫預設路徑 (MSSQL\DATA) 空間不足,需將 .mdf/.ldf 移至其他磁碟 (如 F:\MSSQL\data)。

標準四步驟流程

1. 設定資料庫離線 (獨佔存取)

ALTER DATABASE [test_db] SET OFFLINE WITH ROLLBACK IMMEDIATE;

WITH ROLLBACK IMMEDIATE 強制中斷現有連線,確保獨佔。

2. 邏輯修改檔案路徑 (中繼資料更新)

USE master;
GO
ALTER DATABASE test_db MODIFY FILE (
    NAME = test_db,           -- 邏輯檔案名 (非實體檔名)
    FILENAME = N'F:\MSSQL\data\test_db.mdf'
);
GO
ALTER DATABASE test_db MODIFY FILE (
    NAME = test_db_log,
    FILENAME = N'F:\MSSQL\data\test_db_log.ldf'
);
GO

關鍵:NAME 是 sys.database_files 中的 name 欄位,非實體檔名。可用 SELECT name, physical_name FROM sys.database_files 確認。

3. 實體移動檔案

  • 開啟檔案總管,將原位置 (如 C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\) 的 test_db.mdf、test_db_log.ldf 剪下貼上 至新路徑
  • 檔案名稱必須與步驟 2 指定的 FILENAME 完全一致

4. 設定資料庫上線

ALTER DATABASE [test_db] SET ONLINE;

驗證

SELECT name, physical_name, state_desc 
FROM sys.master_files 
WHERE database_id = DB_ID('test_db');

確認 physical_name 指向新路徑,state_desc = ONLINE。

常見錯誤與排查

錯誤原因解決
無法離線有作用中連線SET OFFLINE WITH ROLLBACK IMMEDIATE 或關閉應用/殺掉 SPID
修改路徑後上線失敗邏輯名稱錯誤 / 實體檔案未移動 / 路徑權限不足核對 sys.database_files.name、確認檔案存在、SQL 服務帳號有 NTFS 讀寫權限
掛起狀態檔案損毀/版本不符檢查 ERRORLOG、嘗試 DBCC CHECKDB、從備份還原

替代方案:備份/還原指定路徑 (無需離線)

BACKUP DATABASE test_db TO DISK = 'F:\Backup\test_db.bak' WITH COMPRESSION;
RESTORE DATABASE test_db 
FROM DISK = 'F:\Backup\test_db.bak'
WITH 
    MOVE 'test_db' TO 'F:\MSSQL\data\test_db.mdf',
    MOVE 'test_db_log' TO 'F:\MSSQL\data\test_db_log.ldf',
    REPLACE, STATS = 10;

優點:線上備份、自動建立新檔案、可同時重命名資料庫。缺點:大庫耗時久、需雙倍空間。

總結

移動資料庫檔案核心是「離線 → 修改中繼資料路徑 → 實體移動檔案 → 上線」。掌握邏輯名 vs 實體名區別、服務帳號權限、VLF 狀態,可確保零風險遷移。大型資料庫優先考慮備份/還原指定路徑法。

copyright © 萬盛學電腦網 all rights reserved