萬盛學電腦網

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

SQL Server 資料庫檔案移動自動化腳本:離線、移動實體檔、更新系統目錄、上線一氣呵成

將使用者資料庫從 C 槟移至 D 槟(不同實體磁碟)以分離 I/O、便於管理。提供可重複使用的參數化 T-SQL 腳本,輸入「資料庫名」與「目標資料夾」即可完成全流程。

核心流程

  1. 強制中斷現有連線(KILL spid)。
  2. 設為 OFFLINE(鎖定檔案)。
  3. 透過 xp_cmdshell 呼叫 OS MOVE 指令移動 .mdf/.ldf。
  4. ALTER DATABASE ... MODIFY FILE 更新 sys.master_files 路徑。
  5. 設為 ONLINE 並驗證新路徑。

完整腳本(可直接部署為 Agent 作業步驟)

USE master;
GO

DECLARE
    @DBName    SYSNAME = 'TargetDatabaseName',   -- 參數 1:目標資料庫
    @DestPath  NVARCHAR(260) = 'D:\SqlData';     -- 參數 2:目標資料夾(資料與記錄同目錄,或自行拆分)

BEGIN TRY
    /* 1. 終止連線 */
    DECLARE @SPID VARCHAR(20), @KillCmd NVARCHAR(100);
    DECLARE cur CURSOR FOR
        SELECT spid FROM sys.sysprocesses WHERE DB_NAME(dbid) = @DBName;
    OPEN cur;
    FETCH NEXT FROM cur INTO @SPID;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @KillCmd = N'KILL ' + @SPID;
        EXEC (@KillCmd);
        FETCH NEXT FROM cur INTO @SPID;
    END
    CLOSE cur; DEALLOCATE cur;

    /* 2. 取得邏輯名與實體路徑 */
    DECLARE @Files TABLE (LogicalName SYSNAME, PhysicalName NVARCHAR(260));
    INSERT @Files
    SELECT name, physical_name
    FROM sys.master_files
    WHERE database_id = DB_ID(@DBName) AND type IN (0,1); -- 0=資料, 1=記錄

    /* 3. 離線 */
    EXEC (N'ALTER DATABASE [' + @DBName + N'] SET OFFLINE WITH ROLLBACK IMMEDIATE;');

    /* 4. 逐檔移動並更新路徑 */
    DECLARE @Logical SYSNAME, @Physical NVARCHAR(260), @FileName NVARCHAR(260),
            @NewPhysical NVARCHAR(260), @Cmd NVARCHAR(500);
    DECLARE curMove CURSOR FOR SELECT LogicalName, PhysicalName FROM @Files;
    OPEN curMove;
    FETCH NEXT FROM curMove INTO @Logical, @Physical;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 取純檔名
        SET @FileName = RIGHT(@Physical, CHARINDEX('\', REVERSE(@Physical)) - 1);
        SET @NewPhysical = @DestPath + '\' + @FileName;

        -- OS 移動
        SET @Cmd = N'MOVE "' + @Physical + N'" "' + @NewPhysical + N'"';
        EXEC xp_cmdshell @Cmd, NO_OUTPUT;

        -- 更新系統目錄
        EXEC (N'ALTER DATABASE [' + @DBName + N'] MODIFY FILE (NAME = ' + @Logical + N', FILENAME = ''' + @NewPhysical + N''');');

        FETCH NEXT FROM curMove INTO @Logical, @Physical;
    END
    CLOSE curMove; DEALLOCATE curMove;

    /* 5. 上線 */
    EXEC (N'ALTER DATABASE [' + @DBName + N'] SET ONLINE;');

    /* 6. 驗證 */
    SELECT name, physical_name FROM sys.master_files
    WHERE database_id = DB_ID(@DBName) AND type IN (0,1);
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE() AS ErrorMessage;
    -- 嘗試恢復上線
    EXEC (N'ALTER DATABASE [' + @DBName + N'] SET ONLINE;');
END CATCH
GO

前置條件與注意事項

  • xp_cmdshell 預設關閉:EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE; 執行完畢建議關閉。
  • SQL Server 服務帳號需具備目標資料夾 修改/寫入 NTFS 權限。
  • 若資料/記錄檔需分離放置,擴充腳本加入 @DestDataPath、@DestLogPath 兩個參數。
  • 生產環境建議在維護視窗執行,並先驗證備份可用。

替代方案(無 xp_cmdshell)

  • BACKUP DATABASE ... TO DISK → RESTORE ... WITH MOVE, REPLACE:無需 OS 指令,支援跨實例/跨機器,但需額外備份儲存空間與時間。
  • DETACH/ATTACH:舊版常用,但需處理檔案權限、全文索引、複寫等相依物件,風險較高。

此腳本實測於 SQL Server 2008R2 ~ 2022,能大幅縮減手動遷移人為錯誤與時間成本。

copyright © 萬盛學電腦網 all rights reserved