萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 網路安全 >  >> 網路基礎知識

SQL Server 2008 附加資料庫完整指南:FOR ATTACH 與 FOR ATTACH_REBUILD_LOG 實戰

在 SQL Server 2008 中,微軟宣佈棄用 `sp_attach_db` 和 `sp_attach_single_file_db` 預存程序,推薦使用 `CREATE DATABASE ... FOR ATTACH` 和 `FOR ATTACH_REBUILD_LOG` 語法。新語法支援更多檔案、更靈活的路徑指定、缺失日誌檔自動重建等優勢。本文實戰演示附加資料庫的多種場景。

場景一:標準附加(擁有 .mdf + .ldf 完整檔案)

-- 假設檔案路徑
-- 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/.ndf) 和日誌檔 (.ldf) 的完整路徑。
  • 檔案順序不重要,但路徑必須準確。
  • 資料庫名稱 (MyDB1) 建議與原名一致,否則元資料中名稱會改變。

場景二:僅有 .mdf 檔案,日誌檔遺失/損壞 (FOR ATTACH_REBUILD_LOG)

-- 僅有 MDF 檔案
CREATE DATABASE MyDB1
ON (FILENAME = 'D:\SQLData\MyDB1.mdf')
FOR ATTACH_REBUILD_LOG;
GO
  • SQL Server 會自動在 MDF 同目錄下重建新的日誌檔,命名規則:`<原資料庫名>_log.ldf` (如 `MyDB1_log.ldf`)。
  • 新日誌檔初始大小 1MB,自動增長 10%,無最大限制。
  • 適用於:日誌檔損壞、誤刪、單檔遷移、只讀資料庫附加。
  • 前提: 資料庫必須乾淨關閉 (Clean Shutdown),即無未提交事務、無檢查點未寫入。否則附加失敗報錯 5173/1813。

場景三:多檔案群組、多日誌檔、Filestream、FileTable 附加

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
  • 突破舊限制: 支援 >16 個檔案、多日誌檔、Filestream/FileTable、Memory-Optimized Filegroup。
  • 檔案群組結構必須與原資料庫完全一致。

場景四:異機/異路徑附加(路徑不同時)

只要在 `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

最佳實踐清單

  1. 來源資料庫乾淨關閉: `SHUTDOWN WITH NOWAIT` 或正常停止服務/離線資料庫後再複製檔案。
  2. 權限預配: 目標資料/日誌目錄授予 SQL Server 服務帳號 (NT Service\MSSQLSERVER / 域帳號) **完全控制** 權限。
  3. 路徑規劃: 數據、日誌、Filestream 分盤 (Data/Low-Latency Log/Capacity),避免單點瓶頸。
  4. 版本相容: 僅支援同版本或低版本附加到高版本 (向上相容),自動升級內部版本號 (如 655 → 661/706/852/869/904)。
  5. 附加後驗證: `DBCC CHECKDB('MyDB1')`、檢查相容性等級 `ALTER DATABASE MyDB1 SET COMPATIBILITY_LEVEL = 100` (SQL 2008)、更新統計 `sp_updatestats`、配置備份計畫。

結語:`CREATE DATABASE ... FOR ATTACH[_REBUILD_LOG]` 是 SQL Server 2008+ 附加資料庫的標準、強大、靈活方式。掌握其語法細節、多檔案支援、日誌重建條件、跨路徑附加與權限管理,讓資料庫遷移、災難恢復、測試環境構建得心應手。

copyright © 萬盛學電腦網 all rights reserved