排程並自動化 SQL Server Express 資料庫的備份

原始產品版本：SQL Server
原始 KB 編號： 2019698

摘要

SQL Server Express 沒有包含 SQL Server Agent，所以你無法像其他 SQL Server 版本那樣排程備份工作或維護計畫。 要在 SQL Server Express 上自動化備份，請結合三個元件：sp_BackupDatabases儲存程序、sqlcmd命令列工具與 Windows 任務排程器。 本文將說明設定 SQL Server Express 資料庫的排程完整備份、差異備份及交易記錄備份的步驟。 它也說明了如何確認排程備份是否已執行。

本文僅適用於 SQL Server Express 版本。 它不適用於 SQL Server Express LocalDB。

為什麼 SQL Server Express 無法用 SQL Server Agent 排程備份

SQL Server Agent 是執行排程工作和維護計畫的元件，但它不包含在 SQL Server Express 中。 欲完整比較各版本內容，請參閱 SQL Server 的版本與支援功能。

即使沒有 SQL Server Agent，你仍可使用以下任一工具隨時備份 SQL Server Express 資料庫：

若要以排程方式執行這些備份，而非按需執行，請使用 Windows 任務排程器作為排程引擎，步驟如下所述。 關於備份類型與策略的背景，請參見 SQL Server 資料庫的備份與還原。

Prerequisites

在開始之前，請確保你具備：

  • 一個執行中的 SQL Server Express 實例。 本文中的範例使用預設命名實例 .\SQLEXPRESS。
  • 系統 管理員 成員身份固定了該實例的伺服器角色，這是建立資料庫儲存程序 master 所必需的。
  • 一個本機資料夾或硬碟，有足夠空間存放備份檔案。 範例會使用 D:\SQLBackups。
  • 在執行 SQL Server Express 的電腦上建立排程任務的權限。

步驟 1：建立sp_BackupDatabases儲存程序

sp_BackupDatabases是 Microsoft 提供的儲存程序，能對一個資料庫或實例上的每個線上資料庫執行相應BACKUP指令。 你只需在 master 資料庫中建立一次即可。

從 SQL_Express_Backups.sql 下載腳本並存到本地，例如儲存為 C：\Temp\SQL_Express_Backups.sql。 接著用 SSMS 或 sqlcmd建立儲存程序。

使用 SSMS 建立儲存程序

  1. 打開 SSMS 並連接到你的 SQL Server Express 實例。 在 伺服器名稱中輸入 .\SQLEXPRESS，然後選擇 連接。
  2. 選擇 檔案>開啟>檔案，選擇你儲存的 SQL_Express_Backups.sql 檔案，然後選擇 開啟。
  3. 確認工具列上的資料庫清單是否顯示 master。 腳本以 開頭 USE [master]，因此會自動鎖定正確的資料庫。
  4. 選擇 執行 （或按 F5）。 訊息窗格會顯示Commands completed successfully。

使用 sqlcmd 建立儲存程序

從命令提示字元執行下列命令：

sqlcmd -S .\SQLEXPRESS -E -i "C:\Temp\SQL_Express_Backups.sql"

確認儲存程序已被建立

對該實例執行以下查詢。 若存在儲存程序，則回傳一列：

SELECT name, create_date
FROM master.sys.procedures
WHERE name = 'sp_BackupDatabases';

sp_BackupDatabases參數

儲存程序接受以下參數：

參數 Required Description
@backupLocation 是的 接收備份檔案的資料夾，例如 D:\SQLBackups\。 資料夾必須已經存在。
@backupType 是的 備份類型： F 用於完整備份、 D 差分備份或 L 交易日誌備份。
@databaseName No 要備份的資料庫。 如果你省略這個參數，儲存程序會備份實例上除了腳本排除的資料庫外的所有線上資料庫。

儲存程序從不備份 tempdb。 它也會針對所有備份類型跳過範例資料庫 Northwind、pubs 和 AdventureWorks；此外，對於差異和記錄備份，也會跳過 master，因為 master 資料庫不支援這些備份類型。

Important

被排除的資料庫名稱會被硬編碼在腳本中。 如果你有一個資料庫使用這些名稱，儲存程序會跳過它而不會報錯。 要備份這類資料庫，請在建立儲存程序前先在腳本中編輯 DELETE @DBs WHERE DBNAME IN (...) 清單。

步驟 2：安裝 sqlcmd 工具

此 sqlcmd 工具允許您從命令列執行 Transact-SQL 語句、系統儲存程序及腳本檔案。 它是用 SSMS 安裝的，也提供給沒有 SSMS 的電腦獨立下載。 要安裝它，請參考 下載並安裝 sqlcmd 工具。

要確認該程式 sqlcmd 已安裝且可用，請從命令提示字元執行 sqlcmd -? 。

安裝 SQL Server 或獨立工具時，通常會加入包含sqlcmd該執行檔的資料夾到Path環境變數中。 如果 sqlcmd -? 顯示無法識別該命令，請將該資料夾加入 Path 變數，或在批次檔中指定該工具的完整路徑。

步驟 3：建立 Sqlbackup.bat 批次檔案

在文字編輯器中，建立一個名為 Sqlbackup.bat的批次檔案。 根據你的情況，將以下範例中的文字複製到該檔案中。

在選擇範例前，請考慮以下幾點：

  • 每個例子都使用 D:\SQLBackups 作為佔位符。 更改你想在環境中使用的磁碟和備份資料夾的路徑，並確保該資料夾存在。
  • 如果你使用 SQL Server 認證，密碼會以明文形式儲存在批次檔案中。 限制只授權使用者存取存放批次檔案的資料夾。

範例 1：使用 Windows 認證對所有資料庫進行完整備份

REM Sqlbackup.bat
sqlcmd -S .\SQLEXPRESS -E -b -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @backupType='F'"

範例 2：利用 SQL Server 認證對所有資料庫進行差分備份

REM Sqlbackup.bat
sqlcmd -U <YourSQLLogin> -P <StrongPassword> -S .\SQLEXPRESS -b -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @backupType='D'"

注意

若要執行備份，登入帳戶必須是在每個要備份的資料庫中屬於 db_backupoperator 或 db_owner 固定資料庫角色的成員，或屬於 sysadmin 固定伺服器角色的成員。 欲了解更多資訊，請參閱 資料庫層級角色。

範例 3：使用 Windows 驗證備份所有資料庫的交易記錄

REM Sqlbackup.bat
sqlcmd -S .\SQLEXPRESS -E -b -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @backupType='L'"

交易日誌備份需要完整或批量記錄的復原模型，且至少需要一次完整備份。 如需詳細資訊，請參閱 恢復模式 （SQL Server）。

範例 4：使用 Windows 認證完整備份單一資料庫

REM Sqlbackup.bat
sqlcmd -S .\SQLEXPRESS -E -b -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @databaseName='USERDB', @backupType='F'"

若要對 USERDB 進行差異備份，請將 @backupType 參數變更為 D。 若要備份交易日誌，請將其變更為 L。

當 SQL Server 錯誤嚴重度超過 10 時，該-b選項會讓 sqlcmd exit 並DOS ERRORLEVEL回傳 值1。 若未使用 -b，即使備份失敗，sqlcmd 仍會傳回 0，而且工作排程器會將該工作回報為成功。

在繼續之前，請手動從命令提示字元執行 Sqlbackup.bat ，並確認備份檔案是否會出現在備份資料夾中。 現在修正錯誤比事後透過任務排程器診斷要容易得多。

步驟 4：在 Windows 任務排程器中排程批次檔

請依照以下步驟，依排程執行 Sqlbackup.bat：

  1. 在執行 SQL Server Express 的電腦上，選取 [開始 ]，然後在文字框中輸入 工作排程器 。

    Windows 搜尋結果截圖，顯示任務排程器桌面應用程式在最佳匹配中列出。

  2. 在 [最佳比對] 底下，選取 [工作排程器] 來啟動它。

  3. 在 任務排程器中，右鍵點擊 任務排程器（本地）， 並選擇 建立基本任務。

  4. 輸入新任務名稱（例如 SQLBackup），然後選擇 「下一步」。

  5. 選擇 每日 作為任務觸發，然後選擇 下一步。

  6. 將重複次數設為一天，然後選擇 下一天。

  7. 選擇 啟動程式 作為動作，然後選擇 「下一步」。

  8. 選擇瀏覽，選擇你在步驟 3 建立 Sqlbackup.bat 批次檔案時建立的 Sqlbackup.bat 檔案，然後選擇開啟。

  9. 選取 當我按一下 [完成] 時開啟此工作的 [內容] 對話方塊 核取方塊，然後選取 [完成]。

  10. 在 一般 標籤中，檢視 安全性選項 並確認下列帳戶的以下事項。 執行任務時，請使用以下使用者帳戶：

    • 帳號至少有讀取與執行權限來執行該 sqlcmd 工具。
    • 如果批次檔案使用 Windows 認證，該帳號有權備份 SQL Server 資料庫。
    • 如果批次檔案使用 SQL Server 認證，批次檔案中的 SQL Server 登入時有權備份資料庫。

  11. 調整剩餘設定以符合你的需求，然後選擇 確定。

提示

作為測試用途，請從以擁有該工作的同一個使用者帳戶啟動的命令提示字元中執行 Sqlbackup.bat。 此步驟確認帳號在排程執行前已取得所需權限。

欲了解更多排程選項資訊，請參閱 任務排程器。

確認排程備份是否已執行

在第一次預定運行後，確認備份成功：

  • 檢查備份資料夾是否有新的 .bak 檔案（完整和差異備份）或 .trn 檔案（交易日誌備份）。 儲存程序會在每個檔案名稱中加入日期和時間戳記。

  • 在任務排程器中，選擇任務 排程器函式庫，選擇你的任務，並檢視 最後執行結果 欄位。 此欄位顯示批次檔案回傳的出口碼。 由於範例中使用了 -b 這個選項，值 表示 0x1 備份指令失敗。 其他數值請參見 任務排程器錯誤與成功常數。

  • 查詢該實例的備份歷史：

    SELECT database_name, type, backup_start_date, backup_finish_date, physical_device_name
FROM msdb.dbo.backupset AS bs
INNER JOIN msdb.dbo.backupmediafamily AS bmf
    ON bs.media_set_id = bmf.media_set_id
ORDER BY backup_start_date DESC;

    這些資料表是 SQL Server 在資料庫中維護msdb的備份歷史的一部分。 欲了解更多資訊，請參閱備份歷史與標頭資訊（SQL Server）。

Important

務必確認備份檔案存在，且備份歷史是否包含最近的資料列。 任務排程器回報為成功的任務，並不代表備份成功。

此備份方法的需求與限制

使用本文程序時，請注意以下要求與限制：

  • 任務排程器服務必須在任務排程開始時正在執行。 我們建議您將此服務的啟動類型設為 自動 ，這樣即使重新啟動後服務也能繼續運作。
  • 接收備份的硬碟必須有足夠的空間。 定期清理備份資料夾裡的舊檔案，避免硬碟空間不足。 sp_BackupDatabases儲存程序不會刪除舊的備份檔案。
  • 儲存程序只備份線上的資料庫。 它會靜默跳過離線、還原中或無法使用的資料庫。
  • 此方法無法取代 SQL Server Agent。 它沒有內建的工作歷史、重試邏輯或失敗警報。 定期檢閱備份歷程，或者，如果您需要這些功能，請升級到包含 SQL Server Agent 的版本。

相關內容

  • 上次更新時間：