SQL Server 備份：完整指南 [2026]

方面	完整備份	差異備份
尺寸	完整的資料庫	僅自上次完整備份以來的更改
備份時間	最長	比滿載速度更快
還原過程	單一文件恢復	需要全連接+差分連接
需要儲存	大部分空間	最初空間較小，隨著時間的推移而增大

2.3 交易日誌備份

交易日誌備份會擷取自上次日誌備份以來的所有事務，從而實現時間點復原。

2.3.1 了解交易日誌

交易日誌記錄了資料庫的每次修改。日誌備份會截斷日誌中不活躍的部分，防止其無限增長並填滿磁碟空間。

2.3.2 時間點恢復

交易日誌備份可讓您將資料庫還原到日誌備份中的任意特定時刻。這對於從意外的資料修改或刪除中恢復至關重要。

要執行時間點恢復，您需要：

上次完整備份
最新差異備份（可選）
從完整備份/差異備份到目標時間的所有交易日誌備份

2.4 尾部日誌備份

尾日誌備份會擷取尚未備份的日誌記錄，從而防止資料遺失並維護完整的日誌鏈。在恢復之前 SQL Server 若要將資料庫還原到其最新時間點，必須備份其交易日誌的尾部。尾部日誌備份是資料庫復原計畫中最後一個需要關注的備份。

請注意： 並非所有復原場景都需要尾日誌備份。如果復原點包含在較早的日誌備份中，則無需尾日誌備份。如果您要移動或取代（覆蓋）資料庫，並且不需要將其還原到最近一次備份之後的某個時間點，則也不需要尾日誌備份。

2.4.1 何時需要尾部日誌備份

以下場景描述了何時應該進行結尾日誌備份：

線上資料庫復原： 如果資料庫處於線上狀態，且您計劃對資料庫執行還原作業，請先備份日誌尾部。為避免線上資料庫發生錯誤，備份時必須使用 BACKUP Transact-SQL 語句的 WITH NORECOVERY 選項 SQL Server 數據庫。

離線資料庫復原： 如果資料庫離線且無法啟動，而您需要還原資料庫，請先備份日誌尾部。由於此時無法進行任何事務，因此使用 WITH NORECOVERY 選項是可選的。在這種情況下，NORECOVERY 選項實際上等同於僅複製交易日誌的備份。

資料庫備份損壞： 如果資料庫損壞，請嘗試使用 BACKUP 語句的 WITH CONTINUE_AFTER_ERROR 選項執行尾日誌備份。對於損壞的資料庫，只有當日誌檔案未損壞、資料庫處於支援尾日誌備份的狀態且資料庫不包含任何批次日誌變更時，尾日誌備份才能成功。如果無法建立尾日誌備份，則在最新 MS 之後提交的任何事務 SQL Server 備份資料庫遺失。

2.4.2 尾部日誌備份的關鍵選項

無法恢復： 如果您要備份計劃隨後復原的線上資料庫的日誌尾部，請使用 WITH NORECOVERY。 NORECOVERY 會使資料庫離線。您也可以備份 SQL Server 離線資料庫的尾日誌。如果要保持資料庫離線，請使用 WITH NORECOVERY。請注意，除非指定 COPY_ONLY 或 NO_TRUNCATE 選項，否則日誌將會被截斷。

錯誤發生後繼續： 僅當備份損壞資料庫的尾部時才使用 CONTINUE_AFTER_ERROR。備份損壞資料庫上的日誌尾部時，日誌備份中通常會捕獲的某些元資料可能無法使用。

2.5 僅複製備份

僅複製備份會建立獨立備份，而不會影響正常的備份順序。它們不會破壞差異備份鍊或交易日誌的連續性。

使用僅複製備份進行以下操作：

建立測試或開發資料庫副本
臨時備份，不影響計畫備份
在進行重大更改或測試之前進行備份

2.6 文件和文件組備份

文件和文件組備份的目標是特定的資料庫文件或文件組，而不是整個資料庫。這種方法非常適合備份所有資料耗時過長的超大型資料庫。

優勢包括：

大型資料庫的備份作業更快
多個文件組的平行備份
粒度恢復選項
針對唯讀檔案群組的最佳化備份計劃

2.7 部分備份

部分備份包括主文件組和任何讀寫文件組（不包括只讀文件組）中的所有資料。這可以減少將靜態歷史資料儲存在唯讀檔案群組中的資料庫的備份大小和備份時間。

3. SQL Server 恢復模型

SQL Server 復原模型決定了哪些備份類型可用以及如何管理交易日誌。

3.1 簡單恢復模型

3.1.1 特點和用例

簡單還原會在每個檢查點後自動截斷交易日誌，因此無需日誌備份即可回收空間。

最適合：

開發和測試資料庫
可以接受備份之間資料遺失的資料庫
具有可重新運行的 ETL 流程的資料倉儲
只讀或報告資料庫

3.1.2 可用的備份選項

簡單恢復支援：

完整備份
差異備份
文件和文件組備份
僅複製備份

交易日誌備份是 不可用 在簡單恢復模型中。

3.2 全面恢復模型

3.2.1 特點和優點

完整復原會記錄所有事務，並保留日誌記錄直至您備份它們。這使得資料能夠完整還原到交易日誌備份中的任意時間點。

主要優點：

資料遺失的可能性極小
時間點恢復功能
支援日誌傳送和資料庫鏡像
最大程度的恢復彈性

3.2.2 交易日誌管理

在完全復原下，您必須執行定期交易日誌備份以：

防止交易日誌填滿磁碟空間
維護連續的備份鏈
啟用時間點恢復
控制日誌檔案的成長

典型的備份計畫：每週完整備份、每天差異備份、每 15-30 分鐘日誌備份。

3.3 批次日誌復原模型

3.3.1 何時使用批次日誌

批次日誌復原以最少的方式記錄批次操作，例如 BULK INSERT、SELECT INTO 和索引重建，同時維護常規交易的完整日誌記錄。

在以下情況下使用大容量日誌復原：

執行大量導入操作
在大型表上重建索引
執行受益於最少日誌記錄的操作
需要在特定操作期間減少交易日誌大小

3.3.2 限制和注意事項

重要限制：

批次操作期間無法進行時間點還原
發生批量操作時日誌備份會更大
必須根據需要在完整日誌和批量日誌之間切換

3.4 選擇正確的恢復模型

根據業務需求選擇恢復模型：

恢復模型	資料遺失風險	時間點恢復	最適合
一般看板	自上次備份以來的更改	沒有	開發/測試，可接受的資料遺失
全	最少（通常幾分鐘）	可以	生產資料庫、關鍵數據
大量記錄	自上次日誌備份以來的更改	批量操作時受到限制	大宗作業期間的臨時使用

4。備用 SQL Server 使用 SSMS 的資料庫

4.1 先決條件與準備

在備份您的 SQL Server 資料庫，確保：

您具有適當的權限（db_owner 或 BACKUP DATABASE 權限）
有足夠的磁碟空間用於備份文件
SQL Server 已安裝 Management Studio (SSMS)
如果備份到網路位置，則可存取網路路徑

4.2 分步：使用 SSMS 進行完整備份

請按照以下步驟建立您的 SQL Server 使用 SSMS 的資料庫。

4.2.1 開幕 SQL Server 管理工作室

發佈會 SQL Server 管理工作室
在 服務器名稱 領域
選擇您的身份驗證方法
點擊連結

4.2.2 選擇資料庫和備份選項

In 對象資源管理器，展開 數據庫 節點
右鍵單擊要備份的資料庫
選擇任務 -> 備份
在 備份資料庫 視窗中，驗證資料庫名稱
選擇全作為 備份類型

4.2.3 配置備份目標

下 目的地點擊此處成為Trail Hunter 移除清除預設路徑（如果需要）
點擊新增指定新的備份位置
輸入檔案路徑和名稱 .bak的 延期
點擊 OK 確認目的地

4.2.4 Advanced Backup 設定

點擊 媒體選項 在左側面板中
選擇備份選項：
- 覆蓋所有現有備份集 – 取代現有備份
- 附加到現有備份集 – 新增至現有備份文件
點擊 備份選項 在左側面板中
配置可選設定：
- 壓縮備份 – 減少備份檔案大小
- 加密備份 – 保護敏感資料
- 完成後驗證備份 – 檢查備份完整性

4.2.5 執行備份

檢查所有設定 備份資料庫 窗口
點擊 OK 開始備份過程
等待備份完成
備份完成後會出現成功訊息
點擊 OK 關閉確認對話框

4.3 使用 SSMS 建立差異備份

若要建立差異備份，請按照與完整備份相同的步驟操作，但選擇 高頻差動式測試棒 作為步驟 4.2.2 中的備份類型。請記住，差異備份需要先前的完整備份作為基準。

4.4 使用 SSMS 建立交易日誌備份

交易日誌備份僅適用於使用完整或批次日誌復原模型的資料庫。

右鍵單擊資料庫 對象資源管理器
選擇任務 -> 備份
選擇 交易日誌 作為備份類型
根據需要配置目標和選項
點擊 OK 建立日誌備份

4.5 使用 SSMS 建立僅複製備份

僅複製備份不會幹擾您的常規備份順序。

依照步驟建立完整備份
在 備份選項 頁面
Check the 僅複製備份 選擇
正常完成備份過程

5。備用 SQL Server 使用 T-SQL 的資料庫

5.1 基本備份資料庫語法

T-SQL BACKUP DATABASE 指令提供對 SQL Server 備份。

BACKUP DATABASE database_name
TO DISK = 'backup_file_path'
WITH options;

5.2 完整備份 T-SQL 指令

5.2.1 簡單完整備份腳本

使用最少的選項建立基本的完整備份：

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks.bak'
GO

5.2.2 帶選項的完整備份

新增描述資訊和格式選項：

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks.bak'
WITH FORMAT,
     INIT,
     NAME = 'AdventureWorks-Full Database Backup',
     DESCRIPTION = 'Full backup of AdventureWorks database',
     STATS = 10
GO

選項解釋：

FORMAT – 建立新的備份集
INIT – 覆蓋現有的備份文件
名稱 – 指派備份集名稱
商品描述 – 新增描述性文字
STATS – 每 10% 顯示一次進度

5.3 差異備份 T-SQL 指令

差異備份使用 DIFFERENTIAL 選項：

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks_Diff.bak'
WITH DIFFERENTIAL,
     INIT,
     NAME = 'AdventureWorks-Differential Backup',
     STATS = 10
GO

5.4 交易日誌備份 T-SQL 指令

使用 BACKUP LOG 進行交易日誌備份：

BACKUP LOG AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks_Log.trn'
WITH INIT,
     NAME = 'AdventureWorks-Transaction Log Backup',
     STATS = 10
GO

5.5 進階 T-SQL 備份選項

5.5.1 備份到多個文件

將備份分佈到多個檔案以獲得更快的效能：

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks_1.bak',
   DISK = 'D:\Backups\AdventureWorks_2.bak',
   DISK = 'E:\Backups\AdventureWorks_3.bak'
WITH FORMAT, INIT
GO

5.5.2 壓縮備份

減少備份檔案大小和網路頻寬：

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks_Compressed.bak'
WITH COMPRESSION,
     INIT,
     STATS = 10
GO

5.5.3 加密備份

使用加密保護敏感資料：

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks_Encrypted.bak'
WITH COMPRESSION,
     ENCRYPTION (
         ALGORITHM = AES_256,
         SERVER CERTIFICATE = BackupCertificate
     ),
     STATS = 10
GO

5.5.4 密碼保護備份

新增密碼保護（已棄用，改用加密）：

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks.bak'
WITH PASSWORD = 'StrongPassword123!',
     INIT
GO

5.5.5 鏡像備份

建立到不同位置的同步副本：

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks.bak'
MIRROR TO DISK = 'D:\Backups\AdventureWorks_Mirror.bak'
WITH FORMAT, INIT
GO

5.6 T-SQL 備份範例和腳本

帶有錯誤處理的完整備份腳本：

DECLARE @BackupPath NVARCHAR(500);
DECLARE @DatabaseName NVARCHAR(128) = 'AdventureWorks';
DECLARE @BackupDate NVARCHAR(20);

SET @BackupDate = CONVERT(NVARCHAR(20), GETDATE(), 112);
SET @BackupPath = 'C:\Backups\' + @DatabaseName + '_' + @BackupDate + '.bak';

BEGIN TRY
    BACKUP DATABASE @DatabaseName
    TO DISK = @BackupPath
    WITH COMPRESSION,
         INIT,
         NAME = @DatabaseName + '-Full Backup',
         STATS = 10;
    
    PRINT 'Backup completed successfully: ' + @BackupPath;
END TRY
BEGIN CATCH
    PRINT 'Backup failed: ' + ERROR_MESSAGE();
END CATCH
GO

6。備用 SQL Server 使用 PowerShell 的資料庫

6.1 PowerShell 備份指令

SQL Server PowerShell 模組提供了備份自動化的 cmdlet：

備份-SqlDatabase – 建立資料庫備份
恢復-SqlDatabase – 還原資料庫備份
取得 SqlDatabase – 檢索資料庫資訊

導入 SQL Server 模塊：

Import-Module SqlServer

6.2 使用 PowerShell 建立備份腳本

基本 PowerShell 備份指令：

Backup-SqlDatabase -ServerInstance "localhost" `
                    -Database "AdventureWorks" `
                    -BackupFile "C:\Backups\AdventureWorks.bak" `
                    -BackupAction Database `
                    -CompressionOption On

差異備份範例：

Backup-SqlDatabase -ServerInstance "localhost" `
                    -Database "AdventureWorks" `
                    -BackupFile "C:\Backups\AdventureWorks_Diff.bak" `
                    -BackupAction Database `
                    -Incremental

交易日誌備份：

Backup-SqlDatabase -ServerInstance "localhost" `
                    -Database "AdventureWorks" `
                    -BackupFile "C:\Backups\AdventureWorks_Log.trn" `
                    -BackupAction Log

6.3 使用 PowerShell 自動備份

為多個資料庫建立自動備份腳本：

# Configuration
$ServerInstance = "localhost"
$BackupPath = "C:\Backups"
$Databases = @("AdventureWorks", "TestDB", "ProductionDB")
$Timestamp = Get-Date -Format "yyyyMMdd_HHmmss"

# Create backup directory if not exists
if (-not (Test-Path $BackupPath)) {
    New-Item -ItemType Directory -Path $BackupPath
}

# Backup each database
foreach ($Database in $Databases) {
    $BackupFile = Join-Path $BackupPath "$Database`_$Timestamp.bak"
    
    try {
        Backup-SqlDatabase -ServerInstance $ServerInstance `
                          -Database $Database `
                          -BackupFile $BackupFile `
                          -BackupAction Database `
                          -CompressionOption On
        
        Write-Host "Successfully backed up $Database to $BackupFile" -ForegroundColor Green
    }
    catch {
        Write-Host "Failed to backup $Database : $_" -ForegroundColor Red
    }
}

7。備用 SQL Server 使用命令列的資料庫

SQL Server 提供命令列實用程序，允許您備份 SQL Server 無需使用 SSMS 或圖形介面即可管理資料庫。這些工具對於自動化、腳本編寫和遠端管理場景至關重要。

7.1 使用SQLCMD備份資料庫

SQLCMD 是用於 SQL Server 取代了 OSQL。它提供了增強的功能，並且是從命令提示字元執行 T-SQL 命令的建議工具。

7.1.1 基本 SQLCMD 語法

sqlcmd -S ServerName -d DatabaseName -Q "BACKUP DATABASE statement"

-S： 指定 SQL Server 實例名稱
-d： 指定資料庫名稱
-問： 執行查詢並退出
-和： 使用 Windows 驗證
-U： 指定 SQL Server 登入使用者名稱
-P： 指定密碼 SQL Server 登錄

7.1.2 使用 SQLCMD 建立備份

備份 SQL Server 使用 SQLCMD，請依照下列步驟操作：

未結案工單 命令提示符 or PowerShell的
導航到 SQL Server 工具目錄（通常在安裝期間添加到 PATH）
使用適當的參數執行 SQLCMD 備份資料庫指令
驗證備份檔案是否已成功創建

使用 Windows 驗證的完整備份命令範例：

sqlcmd -S localhost -E -Q "BACKUP DATABASE AdventureWorks TO DISK='C:\Backups\AdventureWorks.bak' WITH COMPRESSION, INIT"

使用範例 SQL Server 驗證：

sqlcmd -S localhost -U sa -P YourPassword -Q "BACKUP DATABASE AdventureWorks TO DISK='C:\Backups\AdventureWorks.bak' WITH COMPRESSION, INIT"

使用 SQLCMD 建立差異備份

sqlcmd -S localhost -E -Q "BACKUP DATABASE AdventureWorks TO DISK='C:\Backups\AdventureWorks_Diff.bak' WITH DIFFERENTIAL, COMPRESSION, INIT"

使用 SQLCMD 建立交易日誌備份

sqlcmd -S localhost -E -Q "BACKUP LOG AdventureWorks TO DISK='C:\Backups\AdventureWorks_Log.trn' WITH COMPRESSION, INIT"

7.1.3 備份發布者資料庫 SQL Server 複製

在備份發布者資料庫時 SQL Server 複製時，使用 WITH REPLICATION 選項來保留複製元資料並確保交易一致性。

-- Backup publisher database with replication support
BACKUP DATABASE PublisherDB 
TO DISK = 'C:\Backup\PublisherDB_Full.bak'
WITH REPLICATION, 
     COMPRESSION,
     CHECKSUM,
     INIT,
     STATS = 10;
GO

有關更多詳細信息 SQL Server 複製，請參閱我們的綜合指南.

7.2 使用OSQL備份資料庫

OSQL 是一個傳統的命令列實用程序，用於 SQL Server。雖然 Microsoft 建議使用 SQLCMD，但 OSQL 仍然可用，以便與舊腳本和系統保持向後相容。

7.2.1 基本 OSQL 語法

OSQL 語法類似 SQLCMD：

osql -S ServerName -d DatabaseName -Q "BACKUP DATABASE statement"

-S： SQL Server 實例名稱
-d： 數據庫名稱
-問： 執行查詢並退出
-和： 使用可信任連線（Windows 驗證）
-U： 登錄用戶名
-P： 登錄密碼

7.2.2 使用 OSQL 建立備份

若要執行 OSQL 備份資料庫操作：

未結案工單 命令提示符
驗證 OSQL 是否可用 SQL Server 安裝
執行OSQL備份指令

完整備份範例：

osql -S localhost -E -Q "BACKUP DATABASE AdventureWorks TO DISK='C:\Backups\AdventureWorks.bak' WITH INIT"

差異備份範例：

osql -S localhost -E -Q "BACKUP DATABASE AdventureWorks TO DISK='C:\Backups\AdventureWorks_Diff.bak' WITH DIFFERENTIAL, INIT"

8.第三方 SQL Server 備份工具

而 SQL Server 包括原生備份功能，第三方工具則為需求複雜的組織提供增強功能、自動化和企業級管理。這些解決方案提供進階壓縮、集中管理和簡化的備份工作流程。 SQL Server 跨多個環境的資料庫。

8.1 Veeam 備份 SQL Server

Veeam 提供全面的資料保護解決方案，專門用於備份 SQL Server 對生產系統影響最小的資料庫。

主要功能：

應用感知處理 SQL Server 備份一致性
交易日誌備份和管理
具有精細還原選項的時間點恢復
與 Veeam Backup & Replication 整合以實現統一資料保護
自動備份驗證和確認
支援 Always On 可用性組
VM級別和應用程式級別 SQL Server 備份選項

8.2 Barracuda 備份 SQL Server

Barracuda 為 MS 提供簡化管理的雲端整合備份解決方案 SQL Server 備份資料庫操作。

主要功能：

自動 SQL Server 備份調度
內建雲複製到 Barracuda Cloud Storage
全域重複資料刪除和壓縮
即時本地復原功能
基於 Web 的管理控制台
支援完整、差異和交易日誌備份
使用不可變備份進行勒索軟體保護

8.3 Veritas NetBackup SQL Server

Veritas NetBackup 是一款企業級備份平台，可為 SQL Server 跨複雜 IT 環境的資料庫。

主要功能：

為數千個 SQL Server 實例
進階重複資料刪除和壓縮演算法
靈活的備份策略和調度
支持所有人 SQL Server 恢復模型
與磁帶庫和雲端儲存集成
資料庫、表格和物件的粒度恢復
多平台支援（Windows、Linux SQL Server)
自動備份生命週期管理

8.4 Commvault 完整備份和恢復 SQL Server

Commvault 透過全面的備份提供智慧資料管理 SQL Server 功能和先進的自動化特性。

主要功能：

人工智慧驅動的備份優化和異常檢測
統一的備份、復原與歸檔平台
進階功能 SQL Server 備份壓縮（最多可減少 90%）
自動化災難復原編排
Live Sync 實現接近零的 RPO 保護
支持 SQL Server 本地、雲端和混合部署
IntelliSnap 用於基於快照的備份
全面的合規性和電子發現功能

8.5 Cohesity DataProtect SQL Server

Cohesity 為現代企業提供具有超融合基礎架構的新一代資料管理 SQL Server 備份操作。

主要功能：

用於簡化管理的 Web 規模架構
即時批次恢復功能 SQL Server 數據庫
應用程式一致的快照
所有備份的全域重複資料刪除
原生雲端整合（AWS、Azure、Google Cloud）
內建分析和監控儀表板
克隆和測試資料庫功能
使用不可變快照進行勒索軟體保護

8.6 Red Gate SQL備份專業版

Red Gate SQL Backup Pro 是一款專注於最佳化的專業工具 SQL Server 具有卓越壓縮和效能的備份和復原作業。

主要功能：

業界領先的壓縮率（高達 95%）
備份網路彈性 SQL Server 跨不可靠的連接
使用 256 位元 AES 進行備份加密
備份副本驗證和完整性檢查
詳細的備份歷史記錄和報告
整合 SQL Server 管理工作室
支援備份到網路位置和雲端存儲
並行備份和恢復，加快操作速度

9.如何恢復 SQL Server 數據庫

9.1 了解還原過程

恢復一個 SQL Server 資料庫從備份檔案重新建立資料庫。還原過程讀取備份檔案並將資料庫重建到其備份狀態。

重要注意事項：

復原將覆蓋現有資料庫
恢復期間用戶斷開連接
復原必須遵循備份順序（完整備份、差異備份、日誌備份）
復原作業期間資料庫不可用

9.2 使用 SSMS 還原完整備份

請依照以下步驟還原完整的資料庫備份。

9.2.1 逐步恢復過程

未結案工單 SQL Server 管理工作室 並連接到您的伺服器
In 對象資源管理器，右鍵點擊 數據庫
選擇 恢復數據庫
在來源部分，選擇設備
在操作欄點擊 ... 瀏覽備份檔案的按鈕
點擊新增並導航到你的 .bak 文件
選擇備份檔案並點擊 OK
在 目的地 部分，輸入資料庫名稱
查看要還原的備份集
點擊 OK 開始恢復

9.2.2 恢復選項和設置

點擊選項在左側面板中進行配置：

覆蓋現有資料庫（WITH REPLACE） – 允許復原現有資料庫
保留複製狀態（WITH KEEP_REPLICATION） – 保留 SQL Server 複製為
限制對已復原資料庫的存取（WITH RESTRICTED_USER） 限制恢復後的存取權限
恢復狀態 – 選擇“恢復還原”或“不恢復”

9.3 恢復差異備份

差異還原需要完整備份和差異備份：

首先，使用以下方法還原完整備份 無法恢復 選擇
然後使用以下方法還原差異備份 RECOVERY 選擇

T-SQL範例：

-- Restore full backup (NORECOVERY to allow differential)
RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\Backups\AdventureWorks_Full.bak'
WITH NORECOVERY, REPLACE;

-- Restore differential backup (RECOVERY to complete)
RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\Backups\AdventureWorks_Diff.bak'
WITH RECOVERY;
GO

9.4 使用交易日誌備份恢復

對於時間點恢復，依序恢復：

使用 NORECOVERY 還原完整備份
使用 NORECOVERY 恢復差異備份（如果可用）
使用 NORECOVERY 依序還原交易日誌備份
使用 RECOVERY 還原最終日誌備份

-- Restore full backup
RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\Backups\AdventureWorks_Full.bak'
WITH NORECOVERY, REPLACE;

-- Restore first log backup
RESTORE LOG AdventureWorks
FROM DISK = 'C:\Backups\AdventureWorks_Log1.trn'
WITH NORECOVERY;

-- Restore second log backup with recovery
RESTORE LOG AdventureWorks
FROM DISK = 'C:\Backups\AdventureWorks_Log2.trn'
WITH RECOVERY;
GO

9.5 時間點還原

使用 STOPAT 選項將資料庫還原到特定時間點：

-- Restore to specific time: January 15, 2025 at 2:30 PM
RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\Backups\AdventureWorks_Full.bak'
WITH NORECOVERY, REPLACE;

RESTORE LOG AdventureWorks
FROM DISK = 'C:\Backups\AdventureWorks_Log.trn'
WITH RECOVERY, STOPAT = '2025-01-15 14:30:00';
GO

9.6 表恢復

SQL Server 不支援直接從備份檔案進行表級還原。不過，仍有一些解決方案。

9.6.1 方法 1：資料庫快照（最適合預防）

如果資料庫快照是在問題發生之前創建的，那麼它是恢復表資料的最快方法。快照是資料庫在特定時間點的唯讀靜態視圖。

建立資料庫快照：

-- Create snapshot before making changes
CREATE DATABASE ProductionDB_Snapshot_20250107
ON
( NAME = ProductionDB_Data, 
  FILENAME = 'C:\Snapshots\ProductionDB_Snapshot.ss' )
AS SNAPSHOT OF ProductionDB;
GO

從快照恢復表格資料：

USE ProductionDB;
GO

-- Replace entire table content
BEGIN TRANSACTION;

-- Disable constraints temporarily
ALTER TABLE dbo.Orders NOCHECK CONSTRAINT ALL;

-- Clear current data
TRUNCATE TABLE dbo.Orders;

-- Restore from snapshot
INSERT INTO dbo.Orders
SELECT * FROM ProductionDB_Snapshot_20250107.dbo.Orders;

-- Re-enable constraints
ALTER TABLE dbo.Orders CHECK CONSTRAINT ALL;

COMMIT TRANSACTION;
GO

版本要求： 資料庫快照可在以下位置取得： SQL Server 企業版（所有版本）和標準版（從…開始） SQL Server 2016 SP1。

9.6.2 方法二：恢復到臨時資料庫（最常用）

當您需要在出現問題後恢復表格資料且不存在快照時，此方法有效：

將備份還原到臨時資料庫
將臨時資料庫中的表格資料複製到目前資料庫

9.7 頁面恢復

頁面恢復功能無需恢復整個資料庫即可恢復單一損壞的頁面，從而僅針對損壞的頁面進行恢復，最大限度地減少停機時間。此功能僅在完整復原模式或批次日誌復原模式下可用，並且需要從頁面備份到目前日誌檔案的完整交易日誌備份鏈。

要執行頁面恢復，首先要識別損壞的頁面，進行尾部日誌備份，恢復特定頁面，然後套用所有交易日誌：

-- Identify damaged pages
SELECT * FROM msdb.dbo.suspect_pages
WHERE database_id = DB_ID('AdventureWorks');

-- Take tail-log backup
BACKUP LOG AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks_TailLog.trn'
WITH NORECOVERY;

-- Restore damaged pages
RESTORE DATABASE AdventureWorks
PAGE = '1:123, 1:456'
FROM DISK = 'C:\Backups\AdventureWorks_Full.bak'
WITH NORECOVERY;

-- Apply transaction logs
RESTORE LOG AdventureWorks
FROM DISK = 'C:\Backups\AdventureWorks_Log1.trn'
WITH NORECOVERY;

RESTORE LOG AdventureWorks
FROM DISK = 'C:\Backups\AdventureWorks_TailLog.trn'
WITH RECOVERY;
GO

請注意： 簡單恢復模式下不支援頁面恢復。您無法從系統表或主文件組元資料還原頁面。

9.8 逐步恢復

分段復原（部分復原）以檔案群組為單位分階段復原資料庫，首先復原主檔案群組。這樣可以立即恢復關鍵數據，而不太重要的數據則在後台恢復。在簡單恢復模式下，所有讀寫文件組必須與主文件組一起恢復；只有唯讀文件組可以單獨恢復。在完整復原模式或大容量日誌復原模式下，套用交易日誌後，每個檔案群組都可以獨立復原。

恢復模型	逐步恢復行為
一般看板	主文件組和所有讀寫文件組一起恢復。只讀文件組單獨恢復。
完整/批量日誌	每個文件組均在文件組層級獨立恢復。

完整復原模式範例－先還原主文件群組以使資料庫聯機，然後在資料庫保持運作狀態的情況下復原輔助檔案群組：

-- Stage 1: Restore primary filegroup (database comes online)
RESTORE DATABASE AdventureWorks
FILEGROUP = 'PRIMARY'
FROM DISK = 'C:\Backups\AdventureWorks_Full.bak'
WITH PARTIAL, NORECOVERY;

RESTORE LOG AdventureWorks
FROM DISK = 'C:\Backups\AdventureWorks_Log1.trn'
WITH RECOVERY;
GO

-- Stage 2: Restore secondary filegroup (database stays online)
RESTORE DATABASE AdventureWorks
FILEGROUP = 'HistoricalData'
FROM DISK = 'C:\Backups\AdventureWorks_Full.bak'
WITH NORECOVERY;

RESTORE LOG AdventureWorks
FROM DISK = 'C:\Backups\AdventureWorks_Log1.trn'
WITH RECOVERY;
GO

簡單恢復模型範例：

-- Restore primary with all read-write filegroups
RESTORE DATABASE AdventureWorks
FILEGROUP = 'PRIMARY'
FROM DISK = 'C:\Backups\AdventureWorks_Full.bak'
WITH PARTIAL, RECOVERY;

-- Restore read-only filegroup separately
RESTORE DATABASE AdventureWorks
FILEGROUP = 'ReadOnlyArchive'
FROM DISK = 'C:\Backups\AdventureWorks_ReadOnly.bak'
WITH RECOVERY;
GO

9.9 使用 T-SQL 指令恢復

帶有檔案重定位的完整恢復腳本：

RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\Backups\AdventureWorks.bak'
WITH MOVE 'AdventureWorks_Data' TO 'D:\Data\AdventureWorks.mdf',
     MOVE 'AdventureWorks_Log' TO 'E:\Logs\AdventureWorks.ldf',
     REPLACE,
     STATS = 10;
GO

9.10 恢復前驗證備份完整性

檢查備份有效性但不恢復：

RESTORE VERIFYONLY
FROM DISK = 'C:\Backups\AdventureWorks.bak';
GO

此命令會驗證備份集是否完整且可讀，而無需實際還原資料庫。

10 SQL Server 備份最佳實踐

10.1 制定備份策略

10.1.1 評估業務需求

在實施備份之前，請先評估：

數據關鍵性： 這些數據對於營運有多重要？
變更頻率： 數據多久更改一次？
資料庫大小： 資料庫有多大？
可用資源： 有哪些可用的儲存空間和頻寬？
合規需求： 你必須遵守哪些規定？

10.1.2 定義 RTO 和 RPO

恢復時間目標 (RTO)： 可接受的最長停機時間。確定您需要多快恢復營運。

復原點目標 (RPO)： 可接受的最大資料遺失。確定備份頻率。

RTO/RPO要求	推薦的備份策略
RPO：小時，RTO：小時	每日完整+每1-2小時交易日誌
RPO：分鐘，RTO：小時	每日完整備份 + 每 15-30 分鐘進行一次日誌備份
RPO：接近零，RTO：幾分鐘	始終在線可用性組 + 頻繁的日誌備份
RPO：天，RTO：天	每週全額+每日差額

10.2 建立備份計劃

10.2.1 頻率建議

生產資料庫的典型備份計畫：

完整備份： 每週（活動較少時為週日晚上）
差異備份： 每日（每晚）
交易日誌備份： 工作時間內每 15-30 分鐘
僅複製備份： 根據測試或開發需要

10.2.2 平衡性能和保護

安排時間時請考慮以下因素：

非高峰時間： 在低活動期間執行完整備份
資源影響： 壓縮減少了 I/O，但增加了 CPU 使用率
網絡帶寬： 在流量較低時安排網路備份
備份視窗： 確保備份在工作時間之前完成

10.3 備份儲存最佳實踐

10.3.1 現場存儲與異地存儲

現場備份：

更快的備份和復原時間
降低高頻接取成本
易受局部災害影響
最適合快速恢復場景

異地備份：

預防特定地點的災害
符合地理冗餘要求
恢復時間較慢
災難復原必不可少

10.3.2 雲端備份選項

雲端儲存優勢：

Azure Blob 儲存體： 本地人 SQL Server 整合化，經濟高效，適用於不頻繁訪問
亞馬遜 S3： 高度耐用、靈活的儲存層
谷歌云存儲： 價格有競爭力，全球供應

10.3.3 備份保留策略

樣本保留政策：

保留 7 天的每日備份
每週備份保留 4 週
每月備份保留 12 個月
保留 7 年的年度備份（合規）

10.4 備份壓縮和加密

壓縮的好處：

將備份檔案大小減少 50-70%
減少備份時間
降低儲存成本
減少遠端備份的網路頻寬

加密最佳實踐：

始終加密包含敏感資料的備份
使用 AES 256 位元加密
安全性憑證或金鑰管理
記錄加密金鑰並單獨存儲

10.5 測試和驗證備份

10.5.1 定期恢復測試

每季或每月測試恢復程序：

將備份還原到測試環境
驗證資料的完整性和完整性
檢查應用程式功能
記錄恢復時間（驗證 RTO）
識別並解決任何問題

10.5.2 使用 RESTORE VERIFYONLY

自動備份驗證：

-- Verify backup integrity
RESTORE VERIFYONLY
FROM DISK = 'C:\Backups\AdventureWorks.bak'
WITH CHECKSUM;
GO

備份完成後立即執行驗證或作為計畫維護的一部分執行驗證。

10.6 備份自動化與監控

10.6.1 SQL Server 代理職位

建立自動備份作業：

拓展 SQL Server 經紀人 在 SSMS 中
右鍵單擊工作並選擇 新工作
命名作業（例如「每日完整備份」）
添加步驟使用 T-SQL 備份指令
創建一個 活動行程 執行時間
配置通知成功/失敗

10.6.2 維護計劃

SQL Server 維護計劃為備份自動化提供了可視化介面：

前往管理 -> 維護計劃
右鍵單擊並選擇 維護計劃嚮導
選擇要自動執行的備份任務
配置備份計畫和選項
設定報告和日誌記錄

10.6.3 備份警報和通知

配置電子郵件通知：

在中設定資料庫郵件 SQL Server
建立備份失敗警報
監視備份作業歷史記錄
向管理員發送摘要報告

10.7 文件和災難復原規劃

維護全面的文件：

備份計畫： 何時備份以及備份哪些內容
保留政策： 備份保留多長時間
儲存位置： 備份儲存在哪裡
恢復步驟： 逐步恢復說明
聯繫信息： 關鍵人員和供應商
回收率試驗結果： 記錄測試結果

11。高級 SQL Server 備份場景

11.1 備份超大型資料庫（VLDB）

11.1.1 文件和文件組策略

對於超過幾百 GB 的資料庫：

將唯讀資料和讀寫資料分成不同的檔案組
不頻繁地備份只讀檔案組
專注於活動文件組上的頻繁備份
使用檔案級備份進行精細控制

檔案備份範例：

-- Back up specific file
BACKUP DATABASE LargeDB 
FILE = 'LargeDB_Data1'
TO DISK = 'C:\Backups\LargeDB_File1.bak'
WITH COMPRESSION;
GO

11.1.2 備份效能優化

提高 VLDB 備份效能：

條帶備份： 同時寫入多個文件
壓縮： 減少 I/O 和儲存需求
多個備份設備： 並行備份作業
快速儲存： 使用 SSD 進行備份暫存
緩衝區數量： 增加 BUFFERCOUNT 選項
最大傳輸大小： 優化 MAXTRANSFERSIZE 設定

-- Optimized VLDB backup
BACKUP DATABASE LargeDB
TO DISK = 'C:\Backups\LargeDB_1.bak',
   DISK = 'D:\Backups\LargeDB_2.bak',
   DISK = 'E:\Backups\LargeDB_3.bak'
WITH COMPRESSION,
     BUFFERCOUNT = 100,
     MAXTRANSFERSIZE = 4194304;
GO

11.2 Always On 可用性群組中的備份

Always On 可用性群組在副本之間指派備份負載：

配置備份首選項（主、輔助或任何副本）
將備份卸載到輔助副本以減少主副本工作負載
在輔助副本上使用 COPY_ONLY 備份
監視備份優先權設定

-- Check backup preferences
SELECT 
    ag.name AS AvailabilityGroup,
    ar.replica_server_name,
    ar.backup_priority
FROM sys.availability_replicas ar
INNER JOIN sys.availability_groups ag ON ar.group_id = ag.group_id;
GO

11.3 資料庫鏡像備份

在資料庫鏡像場景中：

定期備份主體資料庫
交易日誌備份對於鏡像至關重要
鏡像資料庫處於 RESTORING 狀態（無法直接備份）
考慮在故障轉移後備份鏡像

11.4 備份到 Azure Blob 存儲

SQL Server 可以直接備份到 Azure Blob Storage：

創建 Azure 存儲帳戶
創建 SQL Server Azure 驗證的憑證
使用 URL 語法作為備份目標

-- Create credential for Azure
CREATE CREDENTIAL [https://mystorageaccount.blob.core.windows.net/backups]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'your_SAS_token';
GO

-- Backup to Azure
BACKUP DATABASE AdventureWorks
TO URL = 'https://mystorageaccount.blob.core.windows.net/backups/AdventureWorks.bak'
WITH COMPRESSION,
     STATS = 10;
GO

11.5 備份到 URL

備份到 URL 的好處：

無限雲端儲存容量
自動處理地理冗餘
即用即付定價模式
無需本機磁碟空間
每個備份最多支援 64 個 URL（條帶化）

11.6 條帶備份的效能

條帶備份將資料拆分到多個檔案中，以實現更快的 I/O：

-- Striped backup to 4 files
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backups\AW_Stripe1.bak',
   DISK = 'D:\Backups\AW_Stripe2.bak',
   DISK = 'E:\Backups\AW_Stripe3.bak',
   DISK = 'F:\Backups\AW_Stripe4.bak'
WITH COMPRESSION, FORMAT;
GO

注意：還原需要所有條帶檔案。缺少任何檔案都會導致備份無法使用。

12。故障排除 SQL Server 備份問題

12.1 常見備份錯誤及解決方案

錯誤：“作業系統錯誤 5：存取被拒絕”

原因： SQL Server 服務帳戶缺少權限
解決方案： 授予寫入權限 SQL Server 備份資料夾上的服務帳戶

錯誤：“無法開啟備份設備...設備錯誤或設備離線”

原因： 路徑無效或網路共用不可用
解決方案： 驗證路徑是否存在，檢查網路連接，確保有足夠的磁碟空間

錯誤：“磁碟空間不足”

原因： 磁碟空間不足以進行備份
解決方案： 釋放磁碟空間，使用壓縮，備份到不同位置

錯誤：“資料庫正在使用中。其他使用者正在使用該資料庫”

原因： 恢復期間的活動連接
解決方案： 使用 WITH REPLACE 選項或先中斷使用者連接

12.2 備份效能問題

診斷慢速備份：

使用以下方式檢查磁碟 I/O 效能性能監視器
使用 STATS 選項監控備份進度
評論 SQL Server 瓶頸錯誤日誌
考慮壓縮以減少 I/O
使用跨多個磁碟的條帶備份

查詢監控備份進度：

SELECT 
    session_id,
    command,
    percent_complete,
    CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
    + CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
    + CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
    CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
    + CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
    + CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
    dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time
FROM sys.dm_exec_requests 
WHERE command LIKE 'BACKUP%';
GO

12.3 空間和儲存問題

預防儲存問題：

實施保留政策： 自動刪除舊備份
使用壓縮： 將備份檔案大小減少 50-70%
歸檔到更便宜的存儲： 將舊備份移至歸檔存儲
監控磁碟空間： 設定磁碟空間不足警報
估計備份大小： 備份前計算預期大小

估計備份大小：

-- Estimate full backup size
EXEC sp_spaceused;
GO

12.4 權限和存取問題

備份所需的權限：

備份資料庫 允許
db_backupoperator 角色成員資格
系統管理員 伺服器角色（用於所有備份操作）

授予備份權限：

-- Grant backup permission to user
GRANT BACKUP DATABASE TO [BackupUser];
GRANT BACKUP LOG TO [BackupUser];
GO

-- Add user to backup operator role
ALTER ROLE db_backupoperator ADD MEMBER [BackupUser];
GO

12.5 備份檔損壞

檢測並處理損壞的備份：

驗證備份完整性：

RESTORE VERIFYONLY 
FROM DISK = 'C:\Backups\AdventureWorks.bak'
WITH CHECKSUM;
GO

為將來的備份啟用 CHECKSUM：

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks.bak'
WITH CHECKSUM, INIT;
GO

預防策略：

備份期間始終使用 CHECKSUM 選項
建立後立即驗證備份
定期測試恢復
將備份儲存在可靠的儲存空間上
保留多個備份副本

12.6 從損壞的備份檔案中恢復數據

如果您的備份檔案已損壞，而您仍想從中恢復數據，則可以使用第三方工具，例如 DataNumen SQL Recovery，如下：

開始 DataNumen SQL Recovery.
將過濾器變更為「所有檔案（*.*）」來選擇損壞的備份檔案作為來源檔案：
如有必要，設定輸出 .MDF 檔案。
點擊“開始恢復”，然後按照指示恢復資料庫。
復原過程結束後，新的復原資料庫將出現在 SQL Server 其中包含所有恢復的資料。

13 SQL Server 備份安全

13.1 保護備份文件

保護備份檔案免於未經授權的存取：

檔案系統權限： 僅限授權管理員訪問
網絡安全： 使用安全協定進行網路備份
物理安全： 將備份媒體儲存在安全的位置
訪問日誌記錄： 審核備份文件訪問

13.2 加密選項

SQL Server 支援透明備份加密：

建立加密證書：

-- Create master key
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongP@ssw0rd!';
GO

-- Create certificate
CREATE CERTIFICATE BackupCertificate
WITH SUBJECT = 'Database Backup Certificate',
EXPIRY_DATE = '2026-12-31';
GO

加密備份：

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks_Encrypted.bak'
WITH COMPRESSION,
     ENCRYPTION (
         ALGORITHM = AES_256,
         SERVER CERTIFICATE = BackupCertificate
     );
GO

重要提示：請分別備份憑證和私鑰。如果沒有它們，加密備份將無法復原。

-- Backup certificate
BACKUP CERTIFICATE BackupCertificate
TO FILE = 'C:\Certificates\BackupCertificate.cer'
WITH PRIVATE KEY (
    FILE = 'C:\Certificates\BackupCertificate.key',
    ENCRYPTION BY PASSWORD = 'C3rt!f!c@t3P@ss'
);
GO

13.3 存取控制和權限

實施最小特權原則：

僅向必要的帳戶授予備份權限
使用單獨的帳戶進行備份和還原操作
避免使用 sa 帳號進行備份
定期審核備份權限
不再需要時刪除權限

13.4 合規性考慮因素

滿足監理要求：

通用數據保護條例： 加密包含個人資料的備份，實施保留政策
健康保險流通與責任法案： 在備份中加密 PHI、控制存取、維護審計跟踪
PCI DSS： 加密持卡人資料備份，安全備份存儲
薩斯喀徹溫省法案： 維護備份完整性、文件保留政策

14. 監控和維護備份操作

14.1 追蹤備份歷史記錄

SQL Server 將備份歷史記錄儲存在 msdb 資料庫中：

-- View recent backup history
SELECT 
    bks.database_name,
    bks.backup_start_date,
    bks.backup_finish_date,
    CASE bks.type
        WHEN 'D' THEN 'Full'
        WHEN 'I' THEN 'Differential'
        WHEN 'L' THEN 'Log'
        ELSE 'Other'
    END AS backup_type,
    bks.backup_size / 1024 / 1024 AS backup_size_mb,
    bkmf.physical_device_name
FROM msdb.dbo.backupset bks
INNER JOIN msdb.dbo.backupmediafamily bkmf ON bks.media_set_id = bkmf.media_set_id
WHERE bks.backup_start_date >= DATEADD(DAY, -7, GETDATE())
ORDER BY bks.backup_start_date DESC;
GO

尋找沒有最近備份的資料庫：

SELECT 
    d.name AS database_name,
    MAX(bs.backup_finish_date) AS last_backup_date,
    DATEDIFF(DAY, MAX(bs.backup_finish_date), GETDATE()) AS days_since_last_backup
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset bs ON d.name = bs.database_name
WHERE d.database_id > 4  -- Exclude system databases
GROUP BY d.name
HAVING MAX(bs.backup_finish_date) < DATEADD(DAY, -7, GETDATE())
    OR MAX(bs.backup_finish_date) IS NULL
ORDER BY last_backup_date;
GO

14.2 使用 SQL Server 分析報告

SQL Server Management Studio 包含內建備份報表：

在物件資源管理器中右鍵點選資料庫
選擇 分析報告 -> 標準報表
從可用報告中選擇：
- 備份和復原事件
- 所有備份
- 交易日誌傳送狀態

14.3 第三方監控工具

商業監控解決方案：

SQL哨兵： 全面監控和警報
Redgate SQL 監視器： 即時監控和診斷
SolarWinds 資料庫效能分析器： 效能和備份監控
Idera SQL 診斷管理器： 備份驗證和警報

14.4 備份健康檢查

建立健康檢查程序：

-- Backup health check procedure
CREATE PROCEDURE sp_BackupHealthCheck
AS
BEGIN
    -- Check for databases without recent full backup
    SELECT 
        'Missing Recent Full Backup' AS issue,
        d.name AS database_name,
        ISNULL(CAST(MAX(bs.backup_finish_date) AS VARCHAR), 'Never') AS last_backup
    FROM sys.databases d
    LEFT JOIN msdb.dbo.backupset bs 
        ON d.name = bs.database_name AND bs.type = 'D'
    WHERE d.database_id > 4
    GROUP BY d.name
    HAVING MAX(bs.backup_finish_date) < DATEADD(DAY, -7, GETDATE()) OR MAX(bs.backup_finish_date) IS NULL; -- Check for failed backup jobs SELECT 'Failed Backup Job' AS issue, j.name AS job_name, jh.run_date, jh.run_time, jh.message FROM msdb.dbo.sysjobs j INNER JOIN msdb.dbo.sysjobhistory jh ON j.job_id = jh.job_id WHERE jh.run_status = 0 -- Failed AND jh.step_id = 0 AND jh.run_date >= CONVERT(INT, CONVERT(VARCHAR, GETDATE()-7, 112))
        AND j.name LIKE '%backup%';
END
GO

15 SQL Server 備份常見問題解答

15.1 我應該多久備份一次 SQL Server?

備份頻率取決於您的復原點目標（RPO）：

關鍵生產資料庫： 每週完整記錄，每日差異記錄，每 15-30 分鐘記錄一次
標準生產資料庫： 每週完整記錄，每日差異記錄，每 1-2 小時記錄一次
開發資料庫： 每日或每週
只讀資料庫： 每次數據更改後都充滿

15.2 完整備份和差異備份有什麼不同？

完整備份會複製整個資料庫，而差異備份僅捕獲自上次完整備份以來的變更。差異備份更小、速度更快，但需要基礎完整備份才能還原。

15.3 我可以備份嗎 SQL Server 當它運行時？

是的， SQL Server 支援線上備份。使用者可以在備份操作期間繼續工作。 SQL Server 使用其交易日誌來保持一致性，確保即使並發修改，備份仍然有效。

15.4 多長時間 SQL Server 備份需要嗎？

備份持續時間取決於：

資料庫大小： 資料庫越大，需要的時間越長
備份類型： 完整備份耗時最長
壓縮： 可以增加 CPU 時間但減少整體持續時間
儲存速度： SSD 的速度明顯快於 HDD
伺服器負載： 較高的活動會減慢備援速度

典型範圍：在現代硬體上，10GB 資料庫可能需要 5-15 分鐘進行完整備份和壓縮。

15.5 我應該在哪裡存儲 SQL Server 備份？

最佳實務：遵循 3-2-1 法則：

3 您的資料副本
2 不同的儲存類型（例如磁碟和磁帶/雲端）
1 異地複製

推薦地點：

本機磁碟，快速復原
網路存儲，集中管理
用於災難復原的雲端儲存（Azure、AWS）

15.6 什麼是 .bak 檔案副檔名？

.bak 副檔名是 SQL Server 備份檔案。這只是慣例，並非強制要求 – SQL Server 備份可以使用任何檔案副檔名。但是，使用 .bak 副檔名可以使備份檔案更易於識別，這也是業界標準做法。

15.7 如何備份 SQL Server 到網路驅動器？

要備份到網路磁碟機：

請確保 SQL Server 服務帳戶對網路共用具有寫入權限
在備份指令中使用 UNC 路徑： \\ServerName\ShareName\BackupFile.bak
在安排自動備份之前測試連接性

BACKUP DATABASE AdventureWorks
TO DISK = '\\BackupServer\SQLBackups\AdventureWorks.bak'
WITH COMPRESSION, INIT;
GO

15.8 我可以壓縮嗎 SQL Server 備份？

是的， SQL Server 支援原生備份壓縮（企業版或標準版起） SQL Server 2016 SP1）。壓縮通常會將備份大小減少 50-70%，並且通常會透過減少 I/O 來縮短備份時間，但這會增加 CPU 使用率。

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks.bak'
WITH COMPRESSION;
GO

16. 結論

16.1個關鍵要點

有效 SQL Server 備份策略可以保護您的資料並確保業務連續性。請記住以下要點：

了解備份類型： 根據復原要求選擇適當的備份類型（完整、差異、交易日誌）
選擇適當的恢復模型： 關鍵資料的完整恢復，開發資料庫簡單
實施備份計畫： 定期完整備份與差異備份和日誌備份相結合，最大限度地減少資料遺失
測試恢復程序： 備份只有能夠成功復原才有價值
自動化和監控： 使用 SQL Server 代理、維護計劃和監控工具
安全備份： 加密敏感資料並控制對備份文件的訪問
儲存異地副本： 使用雲端或遠端儲存防止站點範圍內的災難
記錄一切： 維護備份和恢復程序的清晰文檔

16.2 後續步驟和資源

為了提高你的 SQL Server 備份實作：

根據最佳實踐評估您目前的備份策略
計算您的 RTO 和 RPO 要求
在非生產系統上測試復原程序
定期檢討和更新備份計劃
實施自動監控和警報
為團隊成員進行恢復程序培訓

其他資源：

Microsoft微軟 SQL Server 文件：官方備份和復原指南
SQL Server 備份社群論壇：分享經驗和解決方案
專業認證：Microsoft 認證：Azure 資料庫管理員助理

16.3 推薦的工具和解決方案

根據不同的場景：

小型企業：

本地人 SQL Server 規劃備份 SQL Server 代理職位
SQLBackupAndFTP 用於雲端集成
Azure 備份 SQL Server

中型企業：

SQL Server 維護計劃
第三方工具，例如 Redgate SQL Backup Pro
Veeam Backup SQL Server

大型企業：

Quest LiteSpeed 實現最大壓縮
Commvault 或 Veritas NetBackup 用於企業備份管理
Always On 可用性組高可用性

SQL Server 備份是資料庫管理的基礎。透過合理的規劃、實施和測試，您可以確保資料始終受到保護，並在需要時恢復。立即開始實施這些最佳實踐，以保護您的資料安全。 SQL Server 數據庫。

關於作者

元盛是一位資深資料庫管理員 (DBA)，擁有超過 10 年的 SQL Server 環境和企業資料庫管理。他成功解決了金融服務、醫療保健和製造業等行業的數百個資料庫恢復場景。

袁專長於 SQL Server 資料庫復原、高可用性解決方案和效能優化。他擁有豐富的實務經驗，包括管理多TB資料庫、實施Always On可用性群組以及為關鍵業務系統開發自動備份和復原策略。

透過他的技術專長和實踐方法，袁致力於創建全面的指南，幫助資料庫管理員和 IT 專業人員解決複雜的 SQL Server 高效應對挑戰。他始終掌握最新 SQL Server 版本和微軟不斷發展的資料庫技術，定期測試恢復場景以確保他的建議反映現實世界的最佳實踐。

有關於的問題 SQL Server 恢復或需要額外的資料庫故障排除指導？袁歡迎回饋和建議用於改進這些技術資源。

SQL Server 備份：完整指南 [2026]

1. 簡介 SQL Server 備份

1.1 什麼是 SQL Server 備份？

1.2為什麼 SQL Server 備份至關重要

1.3 常見資料遺失狀況

2.理解 SQL Server 備份類型

2.1 完整備份

2.1.1 何時使用完整備份

2.1.2 完整備份的優點和局限性

2.2 差異備份

2.2.1 差異備份的工作原理

2.2.2 完整備份與差異備份

2.3 交易日誌備份

2.3.1 了解交易日誌

2.3.2 時間點恢復

2.4 尾部日誌備份

2.4.1 何時需要尾部日誌備份

2.4.2 尾部日誌備份的關鍵選項

2.5 僅複製備份

2.6 文件和文件組備份

2.7 部分備份

3. SQL Server 恢復模型

3.1 簡單恢復模型

3.1.1 特點和用例

3.1.2 可用的備份選項

3.2 全面恢復模型

3.2.1 特點和優點

3.2.2 交易日誌管理

3.3 批次日誌復原模型

3.3.1 何時使用批次日誌

3.3.2 限制和注意事項

3.4 選擇正確的恢復模型

4。 備用 SQL Server 使用 SSMS 的資料庫

4.1 先決條件與準備

4.2 分步：使用 SSMS 進行完整備份

4.2.1 開幕 SQL Server 管理工作室

4.2.2 選擇資料庫和備份選項

4.2.3 配置備份目標

4.2.4 Advanced Backup 設定

4.2.5 執行備份

4.3 使用 SSMS 建立差異備份

4.4 使用 SSMS 建立交易日誌備份

4.5 使用 SSMS 建立僅複製備份

5。 備用 SQL Server 使用 T-SQL 的資料庫

5.1 基本備份資料庫語法

5.2 完整備份 T-SQL 指令

5.2.1 簡單完整備份腳本

5.2.2 帶選項的完整備份

5.3 差異備份 T-SQL 指令

5.4 交易日誌備份 T-SQL 指令

5.5 進階 T-SQL 備份選項

5.5.1 備份到多個文件

5.5.2 壓縮備份

5.5.3 加密備份

5.5.4 密碼保護備份

5.5.5 鏡像備份

5.6 T-SQL 備份範例和腳本

6。 備用 SQL Server 使用 PowerShell 的資料庫

6.1 PowerShell 備份指令

6.2 使用 PowerShell 建立備份腳本

6.3 使用 PowerShell 自動備份

7。 備用 SQL Server 使用命令列的資料庫

7.1 使用SQLCMD備份資料庫

7.1.1 基本 SQLCMD 語法

7.1.2 使用 SQLCMD 建立備份

7.1.3 備份發布者資料庫 SQL Server 複製

7.2.1 基本 OSQL 語法

7.2.2 使用 OSQL 建立備份

8.第三方 SQL Server 備份工具

8.1 Veeam 備份 SQL Server

8.2 Barracuda 備份 SQL Server

8.3 Veritas NetBackup SQL Server

8.4 Commvault 完整備份和恢復 SQL Server

8.5 Cohesity DataProtect SQL Server

8.6 Red Gate SQL備份專業版

9.如何恢復 SQL Server 數據庫

9.1 了解還原過程

9.2 使用 SSMS 還原完整備份

9.2.1 逐步恢復過程

9.2.2 恢復選項和設置

4。備用 SQL Server 使用 SSMS 的資料庫

5。備用 SQL Server 使用 T-SQL 的資料庫

6。備用 SQL Server 使用 PowerShell 的資料庫

7。備用 SQL Server 使用命令列的資料庫

11。高級 SQL Server 備份場景

12。故障排除 SQL Server 備份問題