您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 資料庫檔案移動自動化腳本：參數化、殺連線、離線移動、上線驗證

編輯：關於SqlServer

將資料庫檔案（.mdf/.ldf）從系統碟 (C:) 移至資料碟 (D:) 是常見維護作業。本文提供參數化、交易安全、含錯誤處理的完整 T-SQL 腳本，輸入 @DBName 與 @DestPath 即可自動完成殺連線、離線、實體移動、路徑更新、上線與驗證。

完整執行腳本

USE master;
GO

DECLARE 
    @DBName sysname = 'TargetDatabaseName',   -- 目標資料庫名稱
    @DestPath varchar(256) = 'D:\SqlData';    -- 目標目錄（需存在，結尾無反斜線）

DECLARE @DB TABLE (
    name sysname,
    physical_name sysname
);

BEGIN TRY
    /* ---------- 1. 殺掉所有連線 ---------- */
    DECLARE @SPID varchar(20);
    DECLARE curProcess CURSOR FOR
        SELECT spid FROM sys.sysprocesses WHERE DB_NAME(dbid) = @DBName;
    OPEN curProcess;
    FETCH NEXT FROM curProcess INTO @SPID;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC('KILL ' + @SPID);
        FETCH NEXT FROM curProcess INTO @SPID;
    END
    CLOSE curProcess; DEALLOCATE curProcess;

    /* ---------- 2. 取得目前實體檔案路徑 ---------- */
    INSERT @DB (name, physical_name)
    SELECT A.name, A.physical_name
    FROM sys.master_files A
    INNER JOIN sys.databases B ON A.database_id = B.database_id
    WHERE B.name = @DBName AND A.type <= 1;  -- 0=資料列, 1=記錄

    /* ---------- 3. 設定 OFFLINE ---------- */
    EXEC('ALTER DATABASE ' + @DBName + ' SET OFFLINE');

    /* ---------- 4. 逐檔移動並更新路徑 ---------- */
    DECLARE @logical_name sysname, @physical_name sysname, @file_name varchar(256);
    DECLARE curMove CURSOR FOR SELECT name, physical_name FROM @DB;
    OPEN curMove;
    FETCH NEXT FROM curMove INTO @logical_name, @physical_name;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 取得純檔名
        SET @file_name = RIGHT(@physical_name, CHARINDEX('\', REVERSE(@physical_name)) - 1);
        
        -- 實體移動（建議生產環境改用 COPY + 驗證 + DEL）
        EXEC('EXEC master..xp_cmdshell ''MOVE "' + @physical_name + '" "' + @DestPath + '"''');
        
        -- 更新系統目錄路徑
        EXEC('ALTER DATABASE ' + @DBName + ' MODIFY FILE (NAME = ' + @logical_name 
            + ', FILENAME = ''' + @DestPath + '\' + @file_name + ''')');
        
        FETCH NEXT FROM curMove INTO @logical_name, @physical_name;
    END
    CLOSE curMove; DEALLOCATE curMove;

    /* ---------- 5. 設定 ONLINE ---------- */
    EXEC('ALTER DATABASE ' + @DBName + ' SET ONLINE');

    /* ---------- 6. 驗證新路徑 ---------- */
    SELECT A.name, A.physical_name
    FROM sys.master_files A
    INNER JOIN sys.databases B ON A.database_id = B.database_id
    WHERE B.name = @DBName;

END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE() AS ErrorMessage;
    -- 發生錯誤時嘗試設定 ONLINE 避免資料庫卡在 OFFLINE
    EXEC('ALTER DATABASE ' + @DBName + ' SET ONLINE');
END CATCH
GO

關鍵設計說明

參數化：僅需修改腳本頂端兩個變數，適合納入部署流程或排程作業。
游標處理多檔案：支援多資料檔、多記錄檔資料庫。
xp_cmdshell 移動：需啟用 xp_cmdshell；生產環境建議改用 PowerShell / C# / 檔案系統 API 移動，避免啟用 xp_cmdshell 風險。
TRY...CATCH 保護：錯誤時自動嘗試設定 ONLINE，防止資料庫長期離線。
檔名擷取邏輯：RIGHT(..., CHARINDEX('\', REVERSE(...))-1) 相容含子目錄的完整路徑。

前置條件與注意事項

目標資料夾 @DestPath 必須存在，且 SQL Server 服務帳戶具備讀寫權限。
xp_cmdshell 需啟用：EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;（用畢建議關閉）。
資料庫設為 OFFLINE 會強制中斷所有連線，請在維護視窗執行。
AlwaysOn 可用性群組資料庫需在主要複本執行，並同步更新次要複本路徑（或使用共享磁碟）。
大型資料庫移動耗時較長，建議先 COPY 驗證檔案大小/雜湊值，再刪除來源。

替代方案：使用 ALTER DATABASE MODIFY FILE + 手動移動

若不想啟用 xp_cmdshell，可分兩階段：

ALTER DATABASE ... MODIFY FILE (NAME=..., FILENAME='新路徑') → 離線 → 手動移動檔案 → 上線。
或使用 BACKUP DATABASE ... TO DISK ... WITH FORMAT → RESTORE ... WITH MOVE, REPLACE 重建至新路徑。

此腳本已在 SQL Server 2008 R2 / 2012 / 2016 / 2019 實測可用，可作為標準化資料庫檔案遷移作業程序（SOP）範本。