萬盛學電腦網

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

Access 遷移 SQL Server 完整手把手教程:升遷精靈、腳本生成與資料匯入

Access 適合小型應用,但隨著資料量增長 (超過 100-200 MB)、併發用戶增加 (50+)、安全性需求提升,**遷移至 SQL Server 成為必然**。本文提供從 Access 2003 升級格式、使用升遷精靈、生成腳本建立架構、資料轉移服務 (DTS) 匯入資料的完整步驟。

前置準備

  • 安裝 **Microsoft Office Access 2003** 與 **SQL Server 2000/2005/2008+**。
  • **完整備份** 原 .mdb 檔案。

步驟 1:轉換為 Access 2002-2003 格式

  1. 用 Access 2003 開啟 .mdb (忽略安全性警告)。
  2. 工具 → 資料庫實用程式 → 轉換資料庫 → **轉換為 2002-2003 格式**,指定新檔名 (如 MyApp_2003.mdb)。

步驟 2:升遷精靈建立 SQL Server 資料庫架構

  1. 用 Access 2003 開啟轉換後的 .mdb。
  2. 工具 → 資料庫實用程式 → **升遷精靈** → 下一步。
  3. 選擇 **「新建資料庫」** → 輸入 SQL Server 登入資訊 (伺服器、驗證模式、SA/帳號、密碼) → 指定新資料庫名稱 (如 MyApp_SQL)。
  4. 下一步 → 全選物件 (資料表、查詢、表單、報表、巨集、模組) → 下一步。
  5. 選擇 **「不對應用程式作任何改動」** (僅遷移資料結構與資料) → 完成。
  6. 精靈會在 SQL Server 建立資料庫、資料表、索引、關聯、預設值等架構。

步驟 3:生成 SQL 腳本並修正資料類型相容性

  1. 企業管理員 / SSMS → 右鍵資料庫 MyApp_SQL → 所有工作 → **產生 SQL 腳本**。
  2. 「一般」頁籤 → 全部顯示 → 「格式」頁籤 → 勾選 **「產生所有物件腳本」** → 確定,儲存為 MyApp_Schema.sql。
  3. 用記事本/編輯器開啟腳本檔,**全域取代** 解決 Access→SQL Server 型別不相容:
    • datetime → smalldatetime (Access Date/Time 精度較低,smalldatetime 足夠且省 4 bytes)
    • nvarchar → varchar (若無 Unicode 需求,節省一半空間)
    • text/ntext → varchar(max)/nvarchar(max) (SQL 2005+)
    • memo → nvarchar(max)
    • yes/no → bit
  4. 儲存修改後的腳本。

步驟 4:執行腳本建立乾淨架構

  1. SSMS → 新增查詢 → 開啟 MyApp_Schema.sql → 執行 (F5)。
  2. 確認無錯誤,資料庫架構已就緒 (無資料)。

步驟 5:DTS/SSIS 匯入資料

  1. 企業管理員 / SSMS → 右鍵資料庫 MyApp_SQL → 所有工作 → **匯入資料** (啟動 DTS/SSIS 精靈)。
  2. 資料來源:Microsoft Access → 選取原始 .mdb 檔。
  3. 目的地:Microsoft OLE DB Provider for SQL Server → 目標伺服器/資料庫。
  4. 選擇 **「從來源資料表/檢視表複製」** → 下一步。
  5. 全選資料表 → 可針對個別表對應欄位、轉換 → 下一步。
  6. **立即執行** → 完成。

步驟 6:遷移後驗證清單

檢查項目驗證方式
資料列數一致SELECT COUNT(*) FROM AccessTable vs SELECT COUNT(*) FROM SqlTable
主鍵/身分識別種子DBCC CHECKIDENT ('Table', NORESEED) 確認種子值正確
外鍵關聯啟用SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
索引/統計資料EXEC sp_updatestats 重建統計
應用程式連線字串修改為 SQL Server (SQLOLEDB/SQLNCLI),測試所有功能
權限模型建立資料庫角色、授予最小權限、移除 SA 直連

常見遷移坑點與對策

  • AutoNumber → Identity:Access AutoNumber 遷移後為 Identity,但種子值可能不連續,插入新列時可能衝突 → 遷移後執行 DBCC CHECKIDENT ('Table', RESEED, max_id)。
  • 日期時間精度:Access Date/Time 精度至秒,SQL datetime 至 3.33ms → 使用 smalldatetime (精度至分鐘) 或 datetime2(0) 避免比較問題。
  • 查詢/檢視表語法差異:Access SQL (Jet/ACE) 與 T-SQL 差異大 (如 IIF vs CASE, NZ vs ISNULL/COALESCE, TOP n PERCENT 語法不同) → **必須人工重寫** 所有查詢/檢視表/預存程序。
  • 表單/報表/模組:升遷精靈**不遷移** UI 與 VBA 程式碼,需重寫前端 (建議改 .NET/Web) 或保留 Access 前端連結 SQL 後端 (分離架構)。

現代化替代方案 (SQL Server 2008+ / Azure SQL)

  1. SQL Server Migration Assistant (SSMA) for Access:微軟官方免費工具,自動評估、轉換結構/資料/查詢、產生報告,**強烈推薦取代手動升遷精靈**。
  2. Azure SQL Database:SSMA 直接遷移至雲端,享受 PaaS 優勢。

遷移非一日之功,建議採用 **雙寫/同步驗證** 策略:新舊系統並行運行一段時間,比對資料一致性再全面切換。

copyright © 萬盛學電腦網 all rights reserved