在 SQL Server 2008 中,微軟宣佈棄用 `sp_attach_db` 和 `sp_attach_single_file_db` 預存程序,推薦使用 `CREATE DATABASE ... FOR ATTACH` 和 `FOR ATTACH_REBUILD_LOG` 語法。新語法支援更多檔案、更靈活的路徑指定、缺失日誌檔自動重建等優勢。本文實戰演示附加資料庫的多種場景。
-- 假設檔案路徑
-- MDF: D:\SQLData\MyDB1.mdf
-- LDF: D:\SQLLog\MyDB1_log.ldf
CREATE DATABASE MyDB1
ON (FILENAME = 'D:\SQLData\MyDB1.mdf'),
(FILENAME = 'D:\SQLLog\MyDB1_log.ldf')
FOR ATTACH;
GO
-- 僅有 MDF 檔案
CREATE DATABASE MyDB1
ON (FILENAME = 'D:\SQLData\MyDB1.mdf')
FOR ATTACH_REBUILD_LOG;
GO
CREATE DATABASE MyDB_Complex
ON
PRIMARY (FILENAME = 'D:\Data\MyDB_Pri.mdf'),
FILEGROUP FG_Data (FILENAME = 'D:\Data\MyDB_Data1.ndf', FILENAME = 'D:\Data\MyDB_Data2.ndf'),
FILEGROUP FG_Index (FILENAME = 'D:\Index\MyDB_Idx.ndf'),
FILEGROUP FG_FS CONTAINS FILESTREAM (FILENAME = 'D:\FS\MyDB_FS') -- Filestream 容器
LOG ON
(FILENAME = 'D:\Log\MyDB_Log1.ldf'),
(FILENAME = 'D:\Log\MyDB_Log2.ldf') -- 支援多日誌檔!舊 sp_attach_db 不支援
FOR ATTACH;
GO
只要在 `ON (FILENAME = '新路徑\檔名')` 中指定新的實際路徑即可,SQL Server 會自動更新系統表中的路徑。無需先移動檔案再附加,直接附加到新位置。
-- 在來源伺服器執行,生成目標伺服器可用的附加腳本
SELECT 'CREATE DATABASE ' + name +
' ON ' + STUFF((
SELECT ', (FILENAME = ''' + physical_name + ''')'
FROM sys.master_files
WHERE database_id = DB_ID() AND type_desc = 'ROWS'
FOR XML PATH('')), 1, 2, '') +
' LOG ON ' + STUFF((
SELECT ', (FILENAME = ''' + physical_name + ''')'
FROM sys.master_files
WHERE database_id = DB_ID() AND type_desc = 'LOG'
FOR XML PATH('')), 1, 2, '') +
' FOR ATTACH;'
FROM sys.databases
WHERE name = 'YourDBName';
| 錯誤碼/訊息 | 原因 | 解決 |
|---|---|---|
| 5120 / 5173 / 1813: 無法開啟實體檔案 / 標頭錯誤 / 資料庫不可用 | 路徑錯誤、檔案被佔用/唯讀、權限不足、檔案損壞、非乾淨關閉(場景二) | 核對路徑、關閉佔用進程、給 SQL 服務帳號資料夾權限、檢查檔案完整性、確保乾淨關閉 |
| 948: 資料庫版本過高無法降級附加 | 嘗試將高版本 (如 2012/2014/2016/2019) 資料庫附加到低版本 (2008) | 不可逆。需在高版本導出腳本/數據 (BCP/SSIS/生成腳本) 重建 |
| 1813: 新資料庫名稱已存在 | 目標實例已有同名資料庫 | 刪除/重命名現有資料庫,或附加時用新名稱 |
| 5105: 檔案啟動失敗 | Filestream 容器路徑不存在/權限/非 NTFS 磁碟 | 建立目錄、給權限、確保 NTFS |
結語:`CREATE DATABASE ... FOR ATTACH[_REBUILD_LOG]` 是 SQL Server 2008+ 附加資料庫的標準、強大、靈活方式。掌握其語法細節、多檔案支援、日誌重建條件、跨路徑附加與權限管理,讓資料庫遷移、災難恢復、測試環境構建得心應手。