建立完整資料庫備份

文章
01/10/2024

適用於：SQL Server

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

如需詳細資訊，請參閱 SQL Server 備份及還原與 Azure Blob 儲存體和對 URL 執行 SQL Server 備份。

限制事項

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

如需備份概念和工作的概觀及深入探討，請參閱Microsoft Azure備份概觀 (SQL Server) 之後再繼續。

建議

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

安全性

資料庫備份上的 TRUSTWORTHY 是設為 OFF。如需如何將 TRUSTWORTHY 設為 ON，請參閱 ALTER DATABASE SET 選項 (Transact-SQL)。

從 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 指令碼。

連線到適當的 Microsoft SQL Server 資料庫引擎執行個體之後，請在 [物件總管] 中展開伺服器樹狀目錄。
展開 [資料庫] ，並選取使用者資料庫或展開 [系統資料庫] ，然後選取系統資料庫。
以滑鼠右鍵按一下您希望備份的資料庫，指向工作，然後選取備份。
在 [備份資料庫] 對話方塊中，您選取的資料庫會顯示在下拉式清單內 (您可以變更為伺服器上任何其他的資料庫)。
在 [Microsoft Azure備份型別] 下拉式清單中，選取備份型別 (預設為 [完整])。

重要

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

您可使用 [備份至] 下拉式清單選取其他裝置。選取 [新增]以新增備份物件及目的地。您可將備份組等量劃分至多個檔案，藉此加快備份的速度。

若要移除備份目的地，請選取備份並選取 [移除]。若要檢視現有備份目的地的內容，請選取目的地，然後選取 [內容]。
(選擇性) 檢閱 [媒體選項] 和 [備份選項] 頁面下方的其他可用設定。

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

其他資訊

在建立完整的資料庫備份後，您可以建立差異資料庫備份或交易記錄備份。
(選擇性) 您可以選取僅限複製備份核取方塊來建立僅限複製備份。 「只複製備份」是一種 SQL Server 備份，與慣用的 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 資料庫將會備份至預設備份位置上的磁碟。

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

進行 SQL 備份

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

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

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

變更 DB 位置

C. 建立加密備份

在此範例中，SQLTestDB 資料庫將會使用加密備份至預設備份位置。

連線到適當的 Microsoft SQL Server 資料庫引擎執行個體之後，請在 [物件總管] 中展開伺服器樹狀目錄。
依序展開 [資料庫] 及 [系統資料庫]、以滑鼠右鍵按一下 master，然後選取 [新查詢] 來開啟查詢視窗並連線到您的 SQLTestDB 資料庫。

執行下列命令以在 master 資料庫中建立資料庫主要金鑰及憑證。

-- Create the master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe';  

-- 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 = '23987hxJ#KL95234nl0zBe'

-- 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 容器的儲存體帳戶。此範例會為您建立共用存取簽章；此範例會在已經現有共用存取簽章的容器上失效。

若您在儲存體帳戶中尚未擁有 Azure Blob 儲存體容器，請先建立一個再繼續。請參閱建立一般用途儲存體帳戶及建立容器。

連線到適當的 Microsoft SQL Server 資料庫引擎執行個體之後，請在 [物件總管] 中展開伺服器樹狀目錄。
展開[資料庫]，以滑鼠右鍵按一下 SQLTestDB，指向 [工作]，然後選取[備份]。
在 [一般] 頁面之 [目的地] 區段的 [備份至:] 下拉式清單中，選取 [URL] 。
選取 [新增] ，[選取備份目的地] 對話方塊將隨即開啟。
若您先前已向 SQL Server Management Studio 註冊您希望使用的 Azure 儲存體容器，請選取之。否則，請選取 [新增容器] 來註冊新的容器。
在 [連線到 Microsoft 訂用帳戶] 對話方塊中，登入您的帳戶。
在 [選取儲存體帳戶] 下拉式文字方塊中，選取您的儲存體帳戶。
在 [選取 Blob 容器] 下拉式文字方塊中，選取您的 Blob 容器。
在 [共用存取原則逾期] 下拉式行事曆方塊中，選取您在此範例中所建立共用存取原則的到期日。
選取 [建立認證] 來在 SQL Server Management Studio 中產生共用存取簽章和認證。
選取 [確定] 來關閉 [連線到 Microsoft 訂閱] 對話方塊。
在 [備份檔案] 文字方塊中，修改備份檔案的名稱 (選擇性)。
選取 [確定] 來關閉 [選取備份目的地] 對話方塊。
選取 [確定] 以開始備份。
備份成功完成後，請選取 [確定] 來關閉 SQL Server Management Studio 對話方塊。

注意

目前，不支援使用受控識別備份至 Azure 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 或 TAPE 選項： { DISK \| TAPE } =physical_backup_device_name 如需詳細資訊，請參閱 Microsoft Azure備份裝置 (SQL Server)。
WITH with_options [ , ...o ]	用來指定一或多個選項， o。如需有關選項基本概念的詳細資訊，請參閱步驟 2。

選擇性地指定一或多個 WITH 選項。這裡描述的是一些基本 WITH 選項。如需所有 WITH 選項的資訊，請參閱 BACKUP (Transact-SQL)。

基本備份組 WITH 選項：

{ COMPRESSION | NO_COMPRESSION }：只有在 SQL Server 2008 (10.0.x) Enterprise 及更新版本中，才指定是否要在此備份上執行備份壓縮，以覆寫伺服器層級的預設值。
ENCRYPTION (ALGORITHM, SERVER CERTIFICATE |ASYMMETRIC KEY) ：只有在 SQL Server 2014 或更新的版本中，才能指定要使用的加密演算法以及憑證或非對稱金鑰來維護加密的安全。
DESCRIPTION = { 'text' | @text_variable }：指定描述備份組的自由格式文字。這個字串最多可有 255 個字元。
NAME = { backup_set_name | @backup_set_name_var }：指定備份組的名稱。名稱最多可有 128 個字元。如果未指定NAME，則為空白。

根據預設，BACKUP 會將備份附加到現有的媒體集，以保留現有的備份組。若要明確指定，請使用 NOINIT 選項。如需附加至現有備份組的資訊，請參閱媒體集、媒體家族與Microsoft Azure備份組 (SQL Server)。

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

FORMAT [ , MEDIANAME = { media_name | @media_name_variable } ] [ , MEDIADESCRIPTION = { text | @text_variable } ]

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

重要

當您使用 BACKUP 陳述式的 FORMAT 子句時要非常小心，因為它會破壞備份媒體先前所儲存的任何備份。

範例

針對後續範例，使用下列 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。為明確指出完整的資料庫備份，請以預設值 Database 指定 -BackupAction 參數。此參數在完整資料庫備份下是選擇性的。

注意

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

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

重要

若您正在從 SQL Server Management Studio 開啟 PowerShell 視窗來連線到安裝的 SQL Server，您可以省略認證部分，因為會自動使用您在 SSMS 中認證來建立 PowerShell 與您 SQL Server 執行個體間的連線。

範例

A. 完整備份 (本機)

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

如需完整的語法範例，請參閱 Microsoft Azure備份-SqlDatabase。

$credential = Get-Credential

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

B. 完整備份到 Azure

下列範例會將資料庫 <myDatabase> 上的 <myServer> 執行個體完整備份至 Azure Blob 儲存體。已建立具有讀取、寫入和列出權限的預存存取原則。使用與此預存存取原則相關聯的共用存取簽章建立了 SQL Server 認證 https://<myStorageAccount>.blob.core.windows.net/<myContainer>。此 PowerShell 命令會使用 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

分享方式：

建立完整資料庫備份

限制事項

建議

安全性

權限

使用 SQL Server Management Studio

其他資訊

範例

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

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

C. 建立加密備份

D. 備份至 Azure Blob 儲存體

使用 TRANSACT-SQL

範例

A. 備份到磁碟裝置

B. 備份到磁帶裝置

C. 備份到邏輯磁帶裝置

使用 PowerShell

範例

A. 完整備份 (本機)

B. 完整備份到 Azure

相關工作

意見反映

更多資源

分享方式：

建立完整資料庫備份

限制事項

建議

安全性

權限

使用 SQL Server Management Studio

其他資訊

範例

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

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

C. 建立加密備份

D. 備份至 Azure Blob 儲存體

使用 TRANSACT-SQL

範例

A. 備份到磁碟裝置

B. 備份到磁帶裝置

C. 備份到邏輯磁帶裝置

使用 PowerShell

範例

A. 完整備份 (本機)

B. 完整備份到 Azure

相關工作

相關內容

意見反映

更多資源