將使用者資料庫從 C 槟移至 D 槟(不同實體磁碟)以分離 I/O、便於管理。提供可重複使用的參數化 T-SQL 腳本,輸入「資料庫名」與「目標資料夾」即可完成全流程。
KILL spid)。OFFLINE(鎖定檔案)。xp_cmdshell 呼叫 OS MOVE 指令移動 .mdf/.ldf。ALTER DATABASE ... MODIFY FILE 更新 sys.master_files 路徑。ONLINE 並驗證新路徑。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; 執行完畢建議關閉。@DestDataPath、@DestLogPath 兩個參數。BACKUP DATABASE ... TO DISK → RESTORE ... WITH MOVE, REPLACE:無需 OS 指令,支援跨實例/跨機器,但需額外備份儲存空間與時間。DETACH/ATTACH:舊版常用,但需處理檔案權限、全文索引、複寫等相依物件,風險較高。此腳本實測於 SQL Server 2008R2 ~ 2022,能大幅縮減手動遷移人為錯誤與時間成本。