[SQL] 移動使用者資料庫

根據該篇官方文章 - 移動使用者資料庫 的筆記，文章內說明

在 SQL Server 中，您可以在 ALTER DATABASE 陳述式的 FILENAME 子句中指定新的檔案位置，以便將使用者資料庫的資料、記錄和全文檢索目錄檔案移到新位置。 這種方法適用於在相同的 SQL Server執行個體內移動資料庫檔案。 若要將資料庫移到另一個 SQL Server 執行個體或移到另一個伺服器，請使用備份和還原或卸離和附加作業。

搬移使用者資料 4 個步驟

1. 把 DB 設為離線
2. 修改 DB 檔案位置，設定為新位置
3. 把檔案搬移至新位置
4. 讓 DB 上線

上述語法可以確認 DB logical_name 和檔案實體位置

SELECT 
  DB_NAME([database_id]) AS [database_name], 
  [state_desc] AS  OnlineStatus,
  [file_id],
  [type_desc] AS [file_type], 
  [name] AS [logical_name], 
  [physical_name]
FROM sys.[master_files]
WHERE [database_id] IN (DB_ID('DemoDBMove'))
ORDER BY [type], DB_NAME([database_id]);

-- Step1：設定 DB 離線
ALTER DATABASE DemoDBMove SET OFFLINE WITH ROLLBACK IMMEDIATE;

-- Step2：透過上述查詢語法找出 logical_name 並設定 DemoDBMove mdf 和 ldf 檔案位置至 D:\Temp 去
ALTER DATABASE DemoDBMove MODIFY FILE ( NAME = DemoDBMove, FILENAME = 'D:\Temp\DemoDBMove.mdf');
ALTER DATABASE DemoDBMove MODIFY FILE ( NAME = DemoDBMove_log, FILENAME = 'D:\Temp\DemoDBMove_log.ldf');

-- Step3：從原檔案位置搬移 DemoDBMove mdf 和 ldf 檔案至 D:\Temp 去

-- Step4：設定 DB 上線
ALTER DATABASE DemoDBMove SET ONLINE;

• 參考資料
• Move a database using detach and attach
• 查詢資料庫狀態(Database States)，使用 sys.databases 與 sys.master_files 為例