建立完整資料庫備份

適用於：SQL Server

本文說明如何使用 SQL Server Management Studio、Transact-SQL 或 PowerShell 在 SQL Server 中建立完整資料庫備份。

如需詳細資訊，請參閱使用 Azure Blob 儲存體備份和還原 SQL Server 和 SQL Server 備份至 Azure Blob 儲存體的 URL。

局限性

在明確或隱含交易中不允許使用 BACKUP 陳述式。
較新版本的 SQL Server 所建立的備份無法在舊版 SQL Server 中還原。

如需備份概念和工作的概觀和深入探討，請參閱備份概觀（SQL Server），再繼續。

建議

資料庫的大小增加時，完整資料庫備份就需要更多的時間才能完成，同時也需要更多的儲存空間。若為大型資料庫，請考慮透過一系列的差異資料庫備份來補充完整資料庫備份。
使用 sp_spaceused 系統預存程序來估計完整資料庫備份的大小。
根據預設，每項成功的備份作業都會在 SQL Server 錯誤紀錄檔與系統事件紀錄檔加入一筆輸入。如果經常備份，成功訊息會迅速累積，導致巨大的錯誤日誌，這使得找到其他訊息變得困難。在這類情況下，如果沒有任何指令碼相依於這些備份記錄項目，您就可以使用追蹤旗標 3226 來隱藏這些項目。如需詳細資訊，請參閱使用 DBCC TRACEON 設定追蹤旗標。

安全性

TRUSTWORTHY在資料庫備份上設定為。OFF 如需如何設定 TRUSTWORTHY 為 ON的相關資訊，請參閱 ALTER DATABASE SET 選項。

從 SQL Server 2012 （11.x）開始，和 PASSWORD 選項MEDIAPASSWORD無法用於建立備份。您仍然可以還原以密碼建立的備份。

權限

BACKUP DATABASE 和 BACKUP LOG 權限預設為 系統管理員固定伺服器角色以及 db_owner 和 db_backupoperator 固定資料庫角色的成員。

備份裝置實體檔案的擁有權和權限問題可能會干擾備份作業。 SQL Server 服務應該從裝置讀取和寫入裝置。執行 SQL Server 服務的帳戶必須具備備份裝置的寫入權限。不過，在系統資料表中加入備份裝置項目的 sp_addumpdevice 並不會檢查檔案存取權限。備份裝置實體檔案中的問題可能不會在使用備份或嘗試還原之前出現。

使用 SQL Server Management Studio

注意

當您使用 SQL Server Management Studio 指定備份工作時，您可以選取 [指令碼] 按鈕，然後選取指令碼目的地，以產生對應的 Transact-SQL BACKUP 指令碼。

連線到 SQL Server 資料庫引擎的適當實例之後，在 [物件總管] 中，展開伺服器樹狀結構。
展開 [資料庫] ，並選取使用者資料庫或展開 [系統資料庫] ，然後選取系統資料庫。
以滑鼠右鍵按一下您要備份的資料庫，指向 [工作]，然後選取 [備份...]。
在 「備份資料庫」 對話方塊中，您選取的資料庫會出現在下拉式清單中。（您可以將資料庫變更為伺服器上的任何其他資料庫。
在 [備份類型] 清單中，選取備份類型。預設值為 Full。

重要

您必須先執行至少一個完整資料庫備份，才能執行差異或交易記錄備份。
在 [備份元件] 下方，選取 [資料庫] 。
在 [目的地] 區段中，檢閱備份檔案的預設位置 (位於 ../mssql/data 資料夾)。

您可以使用 [備份至] 清單來選取不同的裝置。選取 * 新增 * 以新增備份物件和/或目的地。您可以將備份組分散到多個檔案中，以提高備份速度。

若要移除備份目的地，請選取它，然後選取 [移除]。 若要檢視現有備份目的地的內容，請選取該目的地，然後選取 [內容]。
（選用）檢閱 [媒體選項] 和 [備份選項] 頁面上的其他可用設定。

如需各種備份選項的詳細資訊，請參閱備份資料庫（一般頁面）、備份資料庫（媒體選項頁面）和備份資料庫（備份選項頁面）。
選取 [確定] 以開始備份。
備份成功完成時，請選取 [ 確定 ] 以關閉 [SQL Server Management Studio] 對話方塊。

其他資訊

在建立完整的資料庫備份後，您可以建立差異資料庫備份或交易記錄備份。
（選用）您可以選取 [僅複製備份] 核取方塊，以建立僅複製備份。 僅限複製備份是與傳統 SQL Server 備份順序無關的 SQL Server 備份。如需詳細資訊，請參閱僅複製備份。僅限複製備份不適用於差異備份類型。
如果您要備份至 URL，則會在[媒體選項] 分頁上停用[覆寫媒體] 選項。

範例

針對後續範例，使用下列 Transact-SQL 程式碼建立測試資料庫：

USE [master]
GO

CREATE DATABASE [SQLTestDB]
GO

USE [SQLTestDB]
GO
CREATE TABLE SQLTest
   (
      ID INT NOT NULL PRIMARY KEY,
      c1 VARCHAR(100) NOT NULL,
      dt1 DATETIME NOT NULL DEFAULT getdate()
   );
GO

USE [SQLTestDB]
GO

INSERT INTO SQLTest (ID, c1) VALUES (1, 'test1')
INSERT INTO SQLTest (ID, c1) VALUES (2, 'test2')
INSERT INTO SQLTest (ID, c1) VALUES (3, 'test3')
INSERT INTO SQLTest (ID, c1) VALUES (4, 'test4')
INSERT INTO SQLTest (ID, c1) VALUES (5, 'test5')
GO

SELECT * FROM SQLTest
GO

A。完整備份至磁碟至預設位置

在此範例中， SQLTestDB 資料庫會備份至預設備份位置的磁碟。

連線到 SQL Server 資料庫引擎的適當實例之後，在 [物件總管] 中，展開伺服器樹狀結構。
展開[資料庫]，以滑鼠右鍵按一下 SQLTestDB，指向 [工作]，然後選取[備份]。
選擇 [確定]。
備份成功完成時，請選取 [ 確定 ] 以關閉 [SQL Server Management Studio] 對話方塊。

B. 完整備份到磁碟至非預設位置

在此範例中， SQLTestDB 資料庫會備份至您選擇的位置的磁碟。

連線到 SQL Server 資料庫引擎的適當實例之後，在 [物件總管] 中，展開伺服器樹狀結構。
展開[資料庫]，以滑鼠右鍵按一下 SQLTestDB，指向 [工作]，然後選取[備份]。
在 [ 一般 ] 頁面的 [ 目的地 ] 區段中，選取 [ 備份至 ] 清單中的 [磁碟]。
選取 [移除] ，直到移除所有現有的備份檔案為止。
選取 ，然後新增。選取 備份目的地 對話方塊隨即開啟。
在 [ 檔案名稱 ] 方塊中輸入有效的路徑和檔案名稱。使用 .bak 作為副檔名，可以簡化檔案的分類。
選取 [確定]，然後再次選取 [確定] 開始備份。
備份成功完成時，請選取 [ 確定 ] 以關閉 [SQL Server Management Studio] 對話方塊。

C. 建立加密備份

在此範例中， SQLTestDB 資料庫會以加密方式備份至預設備份位置。

連線到 SQL Server 資料庫引擎的適當實例之後，在 [物件總管] 中，展開伺服器樹狀結構。
展開 [資料庫]，展開 [系統資料庫]，以滑鼠右鍵按一下 master，然後選取 [新增查詢] 以開啟具有資料庫連線 SQLTestDB 的查詢視窗。

執行下列命令，在資料庫內建立資料庫主要金鑰和master。

-- Create the master key.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

-- If the master key already exists, open it in the same session that you create the certificate. (See next step.)
OPEN MASTER KEY DECRYPTION BY PASSWORD = '<password>'

-- Create the certificate encrypted by the master key.
CREATE CERTIFICATE MyCertificate
WITH SUBJECT = 'Backup Cert', EXPIRY_DATE = '20201031';

在 [物件總管] 的 [資料庫] 節點中，以滑鼠右鍵按一下 SQLTestDB、指向[工作]，然後選取 [備份]。
在 [媒體選項 ] 頁面的 [覆 寫媒體 ] 區段中，選取 [備份至新媒體集]，然後清除所有現有的備份集。
在 [備份選項 ] 頁面的 [ 加密 ] 區段中，選取 [ 加密備份]。
在 [演算法] 清單中，選取 [AES 256]。
在 「憑證」或「非對稱金鑰」 清單中，選取 MyCertificate。
選擇 [確定]。

D. 備份至 Azure Blob 儲存體

此範例會建立的完整資料庫備份 SQLTestDB 至 Azure Blob 儲存體。此範例的撰寫假設您已經有具有 Blob 容器的儲存體帳戶。此範例會建立共用存取簽章。如果容器有現有的共用存取簽章，則範例會失敗。

如果您在儲存體帳戶中沒有 Blob 儲存體容器，請先建立一個容器，再繼續。請參閱建立一般用途儲存體帳戶及建立容器。

連線到 SQL Server 資料庫引擎的適當實例之後，在 [物件總管] 中，展開伺服器樹狀結構。
展開[資料庫]，以滑鼠右鍵按一下 SQLTestDB，指向 [工作]，然後選取[備份]。
在 [ 一般 ] 頁面的 [ 目的地 ] 區段中，選取 [備份至 ] 清單中的 URL。
選取 ，然後新增。選取 備份目的地 對話方塊隨即開啟。
如果您先前已註冊要與 SQL Server Management Studio 搭配使用的 Azure 儲存體容器，請選取它。否則，請選取 [新增容器] 來註冊新的容器。
在 [ 連線到 Microsoft 訂用帳戶 ] 對話方塊中，登入您的帳戶。
在 [選取儲存體帳戶 ] 方塊中，選取您的儲存體帳戶。
在 [ 選取 Blob 容器 ] 方塊中，選取您的 Blob 容器。
在 [共用存取原則到期行事 曆] 方塊中，選取您在此範例中建立之共用存取原則的到期日。
選取 [建立認證] 來在 SQL Server Management Studio 中產生共用存取簽章和認證。
選取 [ 確定 ]，關閉 [ 連線到 Microsoft 訂用帳戶 ] 對話方塊。
在 [備份檔案 ] 方塊中，如果需要，請變更備份檔案的名稱。
選取 [ 確定 ] 以關閉 [ 選取備份目的地 ] 對話方塊。
選取 [確定] 以開始備份。
備份成功完成時，請選取 [ 確定 ] 以關閉 [SQL Server Management Studio] 對話方塊。

注意

目前不支援使用受控識別備份至 Blob 儲存體。

下載 Transact-SQL

執行陳述式來 BACKUP DATABASE 建立完整資料庫備份，並指定：

欲備份的資料庫名稱。
寫入完整資料庫備份的備份裝置。

完整資料庫備份的基本 Transact-SQL 語法如下：

BACKUP DATABASE <database>
TO <backup_device> [ , ...n ]
[ WITH <with_options> [ , ...o ] ];

選項	描述
`<database>`	要備份的資料庫。
`<backup_device> [ , ...n ]`	指定要用於備份作業的 1 到 64 個備份裝置清單。您可以指定實體備份裝置，也可以指定對應的邏輯備份裝置（如果已定義）。若要指定實體備份裝置，請使用 `DISK` or `TAPE` 選項： { `DISK` \| `TAPE` } =physical_backup_device_name 如需詳細資訊，請參閱 Microsoft Azure備份裝置 (SQL Server)。
`WITH <with_options> [ , ...o ]`	用來指定一或多個選項， o。以下是有關一些基本 `WITH` 選項的資訊。

或者，指定一或多個 WITH 選項。這裡說明一些基本 WITH 選項。如需所有WITH選項的相關資訊，請參閱 BACKUP。

基本備份集 WITH 選項：

{ 壓縮 |NO_COMPRESSION }。僅限在 SQL Server 2008 （10.0.x）企業版和更新版本中，指定是否在備份上執行備份壓縮，覆寫伺服器層級預設值。
加密（演算法、伺服器憑證 |ASYMMETRIC KEY）的 KEY。僅限在 SQL Server 2014 或更新版本中，指定要使用的加密演算法，以及用來保護加密的憑證或非對稱金鑰。
描述 = { '文本' | @text_variable }。指定描述備份集的自由格式文字。這個字串最多可有 255 個字元。
名稱 = { backup_set_name | @backup_set_name_var }。指定備份組的名稱。名稱最多可有 128 個字元。如果未指定，則 NAME 為空白。

根據預設，BACKUP 會將備份附加到現有的媒體集，以保留現有的備份組。若要明確指定此組態，請使用選項 NOINIT 。如需附加至現有備份集的相關資訊，請參閱媒體集、媒體系列和備份集（SQL Server）。

若要格式化備份媒體，請使用以下 FORMAT 選項：

格式 [ ，媒體名稱 = { media_name | @media_name_variable } ] [ ，媒體描述 = { 文字 | @text_variable } ]

當您第一次使用媒體或想要覆寫所有現有資料時，請使用該 FORMAT 子句。選擇性地為新的媒體指派媒體名稱和描述。

重要

當您使用 FORMAT 陳述式的 BACKUP 子句時，請小心，因為此選項會毀損先前儲存在備份媒體上的任何備份。

範例

針對後續範例，使用下列 Transact-SQL 程式碼建立測試資料庫：

USE [master]
GO

CREATE DATABASE [SQLTestDB]
GO

USE [SQLTestDB]
GO
CREATE TABLE SQLTest (
   ID INT NOT NULL PRIMARY KEY,
   c1 VARCHAR(100) NOT NULL,
   dt1 DATETIME NOT NULL DEFAULT GETDATE()
)
GO

USE [SQLTestDB]
GO

INSERT INTO SQLTest (ID, c1) VALUES (1, 'test1')
INSERT INTO SQLTest (ID, c1) VALUES (2, 'test2')
INSERT INTO SQLTest (ID, c1) VALUES (3, 'test3')
INSERT INTO SQLTest (ID, c1) VALUES (4, 'test4')
INSERT INTO SQLTest (ID, c1) VALUES (5, 'test5')
GO

SELECT * FROM SQLTest
GO

A。備份到磁碟裝置

下列範例會將完整的 SQLTestDB 資料庫備份至磁碟。它用來 FORMAT 建立新的媒體集。

USE SQLTestDB;
GO
BACKUP DATABASE SQLTestDB
TO DISK = 'c:\tmp\SQLTestDB.bak'
   WITH FORMAT,
      MEDIANAME = 'SQLServerBackups',
      NAME = 'Full Backup of SQLTestDB';
GO

B. 備份到磁帶裝置

下列範例會將完整的 SQLTestDB 資料庫備份至磁帶。它會將備份附加至先前的備份。

USE SQLTestDB;
GO
BACKUP DATABASE SQLTestDB
   TO TAPE = '\\.\Tape0'
   WITH NOINIT,
      NAME = 'Full Backup of SQLTestDB';
GO

C. 備份至邏輯磁帶設備

下列範例會為磁帶機建立邏輯備份裝置。然後，此範例會將完整的 SQLTestDB 資料庫備份至該裝置。

-- Create a logical backup device,
-- SQLTestDB_Bak_Tape, for tape device \\.\tape0.
USE master;
GO
EXEC sp_addumpdevice 'tape', 'SQLTestDB_Bak_Tape', '\\.\tape0'; USE SQLTestDB;
GO
BACKUP DATABASE SQLTestDB
   TO SQLTestDB_Bak_Tape
   WITH FORMAT,
      MEDIANAME = 'SQLTestDB_Bak_Tape',
      MEDIADESCRIPTION = '\\.\tape0',
      NAME = 'Full Backup of SQLTestDB';
GO

使用 PowerShell

使用 Backup-SqlDatabase Cmdlet。若要明確指出完整資料庫備份，請指定 -BackupAction 參數及其預設值 Database。此參數在完整資料庫備份下是選擇性的。

注意

這些範例需要 SqlServer 模組。若要判斷是否已安裝，請執行 Get-Module -Name SqlServer。若要安裝它，請在 PowerShell 的系統管理員會話中執行 Install-Module -Name SqlServer 。

如需詳細資訊，請參閱 SQL Server PowerShell Provider。

重要

如果您要從 SQL Server Management Studio （SSMS）內開啟 PowerShell 視窗，以連線到 SQL Server 執行個體，您可以省略認證部分，因為 SSMS 中的認證會自動用來建立 PowerShell 與 SQL Server 執行個體之間的連線。

範例

A。完整備份 (本機)

下列範例會在伺服器執行個體 <myDatabase> 的預設備份位置，建立 Computer\Instance資料庫的完整資料庫備份。或者，此範例會指定 -BackupAction Database。

如需完整的語法範例，請參閱 Backup-SqlDatabase。

$credential = Get-Credential

Backup-SqlDatabase -ServerInstance Computer[\Instance] -Database <myDatabase> -BackupAction Database -Credential $credential

B. 將完整備份至 Azure

下列範例會將執行個體上的<myDatabase>資料庫<myServer>完整備份至 Blob 儲存體。已建立具有讀取、寫入和清單權限的預存存取原則。 SQL Server 認證 https://<myStorageAccount>.blob.core.windows.net/<myContainer>是使用與預存存取原則相關聯的共用存取簽章來建立。此命令會使用參數 $backupFile 來指定位置（URL）和備份檔名。

$credential = Get-Credential
$container = 'https://<myStorageAccount>blob.core.windows.net/<myContainer>'
$fileName = '<myDatabase>.bak'
$server = '<myServer>'
$database = '<myDatabase>'
$backupFile = $container + '/' + $fileName

Backup-SqlDatabase -ServerInstance $server -Database $database -BackupFile $backupFile -Credential $credential

意見反應

此頁面對您有幫助嗎？

Last updated on 2025-08-26

共用方式為