萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 伺服器教學

SQL Server 2008 資料庫備份與還原圖文完整教學

SQL Server 2008 備份資料庫 (GUI 操作)

完整備份步驟

  1. 開啟 **SQL Server Management Studio (SSMS)**,連線至執行個體
  2. 在「物件總管」展開「資料庫」,右鍵目標資料庫 → **工作** → **備份...**
  3. 「備份資料庫」對話框設定:
    • 備份類型:完整 (Full)
    • 備份元件:資料庫
    • 目的地:磁碟 → **加入** → 選擇路徑與檔名 (建議 D:\Backup\DBName_YYYYMMDD.bak)
  4. 「媒體選項」頁籤:
    • 覆寫現有媒體:備份至現有媒體集 → **覆寫所有現有備份組**
    • 可勾選「檢查媒體集合名稱和備份組過期日期」、「完成時驗證備份」、「執行檢查和驗證」
  5. 「備份選項」頁籤:
    • 壓縮:**壓縮備份** (節省空間、加快 I/O)
    • 加密:可選擇 AES 128/256 加密備份檔
  6. 點擊 **確定** 執行備份,完成後會彈出成功訊息

差異/交易記錄備份 (生產環境必備)

類型內容頻率建議恢復模式需求
完整備份所有資料頁每日/每週完整/大量記錄/簡單
差異備份上次完整備份後變更的區塊每小時/每 4 小時完整/大量記錄
交易記錄備份交易記錄 (可時間點還原)每 15-60 分鐘完整/大量記錄

-- T-SQL 指令範例
-- 完整備份
BACKUP DATABASE [MyDB] TO DISK = N'D:\Backup\MyDB_Full_20240115.bak'
WITH NOFORMAT, NOINIT, NAME = N'MyDB-Full', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10;

-- 差異備份
BACKUP DATABASE [MyDB] TO DISK = N'D:\Backup\MyDB_Diff_20240115_1400.bak'
WITH DIFFERENTIAL, COMPRESSION, STATS = 10;

-- 交易記錄備份
BACKUP LOG [MyDB] TO DISK = N'D:\Backup\MyDB_Log_20240115_1415.trn'
WITH NOFORMAT, NOINIT, NAME = N'MyDB-Log', NOSKIP, NOREWIND, NOUNLOAD, STATS = 10;

SQL Server 2008 還原資料庫 (GUI 操作)

還原完整備份步驟

  1. SSMS 右鍵「資料庫」 → **還原資料庫...** (或右鍵現有 DB → 工作 → 還原 → 資料庫)
  2. 「一般」頁籤:
    • 來源:**從裝置** → 選擇 .bak 檔案
    • 目的地:資料庫名稱 (新建或覆蓋現有)
  3. 「檔案」頁籤:確認還原路徑 (資料/記錄檔路徑),可重新定位至不同磁碟
  4. 「選項」頁籤 (關鍵):
    • **覆寫現有資料庫 (WITH REPLACE)**:若資料庫已存在必須勾選
    • **復原狀態**:
      • RESTORE WITH RECOVERY:完成後資料庫可用 (預設),**無法套用後續記錄備份**
      • RESTORE WITH NORECOVERY:資料庫處於「還原中」狀態,**可套用差異/記錄備份**,完成後再執行 RESTORE DATABASE [DB] WITH RECOVERY
      • RESTORE WITH STANDBY:唯讀待命模式,可查詢並套用後續備份
    • 可勾選「關閉現有連線」、「提示恢復前」等
  5. 點擊 **確定** 執行還原

還原序列範例 (時間點還原)


-- 1. 還原完整備份 (NORECOVERY)
RESTORE DATABASE [MyDB] FROM DISK = N'D:\Backup\MyDB_Full_20240115.bak'
WITH NORECOVERY, REPLACE, STATS = 10, 
MOVE 'MyDB' TO 'D:\Data\MyDB.mdf',
MOVE 'MyDB_log' TO 'L:\Log\MyDB_log.ldf';

-- 2. 還原最新差異備份 (NORECOVERY)
RESTORE DATABASE [MyDB] FROM DISK = N'D:\Backup\MyDB_Diff_20240115_1400.bak'
WITH NORECOVERY, STATS = 10;

-- 3. 依序還原交易記錄備份 (NORECOVERY,最後一筆用 RECOVERY)
RESTORE LOG [MyDB] FROM DISK = N'D:\Backup\MyDB_Log_20240115_1415.trn' WITH NORECOVERY;
RESTORE LOG [MyDB] FROM DISK = N'D:\Backup\MyDB_Log_20240115_1430.trn' WITH NORECOVERY;
RESTORE LOG [MyDB] FROM DISK = N'D:\Backup\MyDB_Log_20240115_1445.trn' WITH RECOVERY;  -- 最後一筆

-- 4. 或指定時間點還原 (STOPAT)
RESTORE LOG [MyDB] FROM DISK = N'D:\Backup\MyDB_Log_20240115_1445.trn'
WITH RECOVERY, STOPAT = '2024-01-15 14:40:00';

常見錯誤與解決

錯誤原因解決
無法覆寫現有資料庫未勾選 WITH REPLACE / 還原資料庫同名存在選項頁籤勾選「覆寫現有資料庫」或先刪除/離線資料庫
存取被拒絕 / 權限不足SQL Server 服務帳號無備份路徑權限授予服務帳號備份目錄讀寫權限,或用 SQL Server 有權限的路徑
備份集持有的資料庫名稱不同嘗試還原不同名稱資料庫的備份確認備份檔來源,或使用 WITH REPLACE 強制
記錄鏈斷裂缺少完整備份、切換恢復模式、手動截斷記錄重新建立完整備份鏈,無法時間點還原
還原後孤立使用者資料庫用戶 SID 與伺服器登入不符EXEC sp_change_users_login 'Auto_Fix', 'user'

自動化備份策略 (維護計畫 / Agent 作業)


-- 維護計畫精靈建立:
-- 1. 子計畫:每日 02:00 完整備份 (保留 7 天)
-- 2. 子計畫:每 4 小時差異備份 (保留 24 小時)
-- 3. 子計畫:每 15 分鐘記錄備份 (保留 24 小時)
-- 4. 子計畫:維護清理任務 (刪除過期 .bak/.trn)
-- 5. 子計畫:資料庫完整性檢查 (DBCC CHECKDB)
-- 6. 子計畫:索引重組/重建 + 更新統計資料

備份檔案管理建議

  • **異地備份**:複製至異地/雲端儲存 (Azure Blob, AWS S3, NAS)
  • **加密備份**:WITH ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = CertName)
  • **壓縮備份**:WITH COMPRESSION (Enterprise/Standard 2008R2+ 支援)
  • **驗證備份**:RESTORE VERIFYONLY FROM DISK = 'path.bak' 定期執行
  • **文件化**:記錄備份時間、檔案大小、驗證結果、還原演練記錄
copyright © 萬盛學電腦網 all rights reserved