建立完整資料庫備份 (SQL Server)Create a Full Database Backup (SQL Server)

適用於: 是SQL Server 否Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

本主題描述如何使用 SQL Server 2017SQL Server 2017SQL Server Management StudioSQL Server Management Studio或 PowerShell,在 Transact-SQLTransact-SQL中建立完整資料庫備份。This topic describes how to create a full database backup in SQL Server 2017SQL Server 2017 using SQL Server Management StudioSQL Server Management Studio, Transact-SQLTransact-SQL, or PowerShell.

如需將 SQL Server 備份至 Azure Blob 儲存體服務的資訊,請參閱使用 Microsoft Azure Blob 儲存體服務進行 SQL Server 備份及還原SQL Server 備份至 URLFor information on SQL Server backup to the Azure Blob storage service, see SQL Server Backup and Restore with Microsoft Azure Blob Storage Service and SQL Server Backup to URL.

限制事項Limitations and Restrictions

  • 在明確或隱含的交易中,並不允許使用 BACKUP 陳述式。The BACKUP statement is not allowed in an explicit or implicit transaction.
  • 在舊版 SQL ServerSQL Server 中,無法還原較新的 SQL ServerSQL Server版本所建立的備份。Backups created by more recent version of SQL ServerSQL Server cannot be restored in earlier versions of SQL ServerSQL Server.
  • 如需備份概念和工作的概觀及深入探討,請參閱備份概觀 (SQL Server) 之後再繼續。For an overview of, and deeper dive into, backup concepts and tasks, see Backup Overview (SQL Server) before proceeding.

建議Recommendations

  • 資料庫的大小增加時,完整資料庫備份就需要更多的時間才能完成,同時也需要更多的儲存空間。As a database increases in size full database backups take more time to complete, and require more storage space. 若為大型資料庫,請考慮透過一系列的差異資料庫備份補充完整資料庫備份。For a large database, consider supplementing a full database backup with a series of differential database backups. 如需詳細資訊,請參閱 SQL Server Backup to URLFor more information, see SQL Server Backup to URL.
  • 使用 sp_spaceused 系統預存程序來估計完整資料庫備份的大小。Estimate the size of a full database backup by using the sp_spaceused system stored procedure.
  • 根據預設,每項成功的備份作業都會在 SQL ServerSQL Server 錯誤記錄檔與系統事件記錄檔中,加入一個項目。By default, every successful backup operation adds an entry in the SQL ServerSQL Server error log and in the system event log. 如果您經常備份,這些成功訊息將會快速累積,因而產生龐大的錯誤記錄檔!If you back up frequently, these success messages will accumulate quickly, resulting in huge error logs! 這會讓您難以尋找其他訊息。This can make finding other messages difficult. 在這類情況下,如果沒有任何指令碼相依於這些備份記錄項目,您就可以使用追蹤旗標 3226 來隱藏這些項目。In such cases, you can suppress these backup log entries by using trace flag 3226 if none of your scripts depend on those entries. 如需詳細資訊,請參閱追蹤旗標 (Transact-SQL)For more information, see Trace Flags (Transact-SQL).

安全性Security

資料庫備份上的 TRUSTWORTHY 是設為 OFF。TRUSTWORTHY is set to OFF on a database backup. 如需如何將 TRUSTWORTHY 設成 ON 的資訊,請參閱 ALTER DATABASE SET 選項 (Transact-SQL)For information about how to set TRUSTWORTHY to ON, see ALTER DATABASE SET Options (Transact-SQL).

SQL Server 2012 (11.x)SQL Server 2012 (11.x) 開始, PASSWORDMEDIAPASSWORD 選項無法再用於建立備份。Beginning with SQL Server 2012 (11.x)SQL Server 2012 (11.x) the PASSWORD and MEDIAPASSWORD options are discontinued for creating backups. 您仍然可以還原以密碼建立的備份。You can still restore backups created with passwords.

權限Permissions

BACKUP DATABASE 和 BACKUP LOG 權限預設為 sysadmin 固定伺服器角色以及 db_ownerdb_backupoperator 固定資料庫角色的成員。BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles.

備份裝置實體檔案的擁有權和權限問題可能會干擾備份作業。Ownership and permission problems on the backup device's physical file can interfere with a backup operation. SQL ServerSQL Server 必須能夠讀取和寫入裝置;執行 SQL ServerSQL Server 服務的帳戶 必須 具備寫入權限。must be able to read and write to the device; the account under which the SQL ServerSQL Server service runs must have write permissions. 不過,在系統資料表中加入備份裝置項目的 sp_addumpdevice並不會檢查檔案存取權限。However, sp_addumpdevice, which adds an entry for a backup device in the system tables, does not check file access permissions. 當您嘗試備份或還原時,存取實體資源之前不一定會出現備份裝置實體檔案的這些問題。Such problems on the backup device's physical file may not appear until the physical resource is accessed when the backup or restore is attempted.

使用 SQL Server Management StudioUsing SQL Server Management Studio

使用 SQL Server Management StudioSQL Server Management Studio 指定備份工作時,您按一下 [指令碼] 按鈕,再選取指令碼目的地,即可產生對應的 Transact-SQLTransact-SQL BACKUP 指令碼。When you specify a back up task by using SQL Server Management StudioSQL Server Management Studio, you can generate the corresponding Transact-SQLTransact-SQL BACKUP script by clicking the Script button and selecting a script destination.

備份資料庫Back up a database

  1. 連接到適當的 MicrosoftMicrosoft SQL Server Database EngineSQL Server Database Engine執行個體之後,在物件總管 中按一下伺服器名稱展開伺服器樹狀目錄。After connecting to the appropriate instance of the MicrosoftMicrosoft SQL Server Database EngineSQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.
  2. 展開 [資料庫] ,並選取使用者資料庫或展開 [系統資料庫] ,然後選取系統資料庫。Expand Databases, and either select a user database or expand System Databases and select a system database.
  3. 以滑鼠右鍵按一下資料庫,指向 [工作] ,然後按一下 [備份]Right-click the database, point to Tasks, and then click Back Up. 會出現 [備份資料庫] 對話方塊。The Back Up Database dialog box appears.
  4. 從下拉式清單中選取資料庫Select the Database from the drop-down list.
  5. 在 [備份類型] 下拉式清單中,選取 [完整] 。In the Backup type drop-down list, select Full.
  6. 在 [備份元件] 下方,選取 [資料庫] 。Under Backup component, select Database.
  7. 在 [目的地] 區段中,使用 [備份至] 下拉式清單選取備份目的地。In the Destination section, use the Back up to drop-down list to select the backup destination. 按一下 [新增] ,以新增其他備份物件和/或目的地。Click Add to add additional backup objects and/or destinations. 若要移除備份目的地,請選取目的地,然後按一下 [移除]To remove a backup destination, select it and click Remove. 若要檢視現有備份目的地的內容,請選取目的地,然後按一下 [內容] 。To view the contents of an existing backup destination, select it and click Contents.
  8. (選擇性) 檢閱 [媒體選項] 和 [備份選項] 頁面下方的其他可用設定。(Optionally) Review the other available settings under the Media Options and Backup Options pages. 如需各種備份選項的詳細資訊,請參閱一般頁面媒體選項頁面備份選項頁面For more information about the various backup options, see General page, Media options page, and Backup options page.

其他資訊Additional information

  • 您可以在建立完整資料庫備份之後,建立差異資料庫備份。如需詳細資訊,請參閱建立差異資料庫備份 (SQL Server)After creating a full database backup, you can create a differential database backup; for more information, see Create a Differential Database Backup (SQL Server).
  • 您可以選擇性地選取 [只複製備份] 核取方塊來建立僅限複製備份。Optionally, you can select the Copy-only backup checkbox to create a copy-only backup. 「只複製備份」 是與傳統 SQL ServerSQL Server 備份順序無關的 SQL ServerSQL Server 備份。A copy-only backup is a SQL ServerSQL Server backup that is independent of the sequence of conventional SQL ServerSQL Server backups. 如需詳細資訊,請參閱只複製備份 (SQL Server)For more information, see Copy-Only Backups (SQL Server). 僅限複製備份不適用於 [差異] 備份類型。A copy-only backup is not available for the Differential backup type.
  • 如果您要備份至 URL,則可能會在 [媒體選項] 頁面上停用 [覆寫媒體] 選項。The Overwrite media option may be disabled on the Media Options page if you are backing up to URL.

SSMS 範例SSMS Examples

針對後續範例,使用下列 Transact-SQL 程式碼建立測試資料庫:For the following examples, create a test database with the following Transact-SQL code:

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.A. 完整備份至預設位置的磁碟Full back up to disk to default location

在此範例中,SQLTestDB 資料庫將會備份至預設備份位置上的磁碟。In this example, the SQLTestDB database will be backed up to disk at the default backup location. 絕不會備份 SQLTestDBA backup of SQLTestDB has never been taken.

  1. 在物件總管 中,連接到 SQL Server Database Engine 的執行個體,然後展開該執行個體。In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.
  2. 展開 [資料庫] ,以滑鼠右鍵按一下 SQLTestDB,指向 [工作] ,然後按一下 [備份...] 。Expand Databases, right-click SQLTestDB, point to Tasks, and then click Back Up....
  3. 選取 [確定] 。Select OK.

進行 SQL 備份

B.完整備份至非預設位置的磁碟B. Full back up to disk to non-default location

在此範例中,SQLTestDB 資料庫將會備份至 F:\MSSQL\BAK 上的磁碟。In this example, the SQLTestDB database will be backed up to disk at F:\MSSQL\BAK. 先前已備份 SQLTestDBPrevious back ups of SQLTestDB have been taken.

  1. 在物件總管 中,連接到 SQL Server Database Engine 的執行個體,然後展開該執行個體。In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.
  2. 展開 [資料庫] ,以滑鼠右鍵按一下 Sales,指向 [工作] ,然後按一下 [備份...] 。Expand Databases, right-click Sales, point to Tasks, and then click Back Up....
  3. 在 [一般] 頁面之 [目的地] 區段的 [備份至] 下拉式清單中,選取 [磁碟] 。On the General page in the Destination section select Disk from the Back up to: drop-down list.
  4. 選取 [移除] ,直到移除所有現有的備份檔案為止。Select Remove until all existing backup files have been removed.
  5. 選取 [新增] ,[選取備份目的地] 對話方塊將隨即開啟。Select Add and the Select Backup Destination dialog box will open.
  6. 在 [檔案名稱] 文字方塊中,輸入 F:\MSSQL\BAK\Sales_20160801.bakEnter F:\MSSQL\BAK\Sales_20160801.bak in the file name text box.
  7. 選取 [確定] 。Select OK.
  8. 選取 [確定] 。Select OK.

變更 DB 位置

C.建立加密的備份C. Create an encrypted backup

在此範例中,SQLTestDB 資料庫將會使用加密備份至預設備份位置。In this example, the SQLTestDB database will be backed up with encryption to the default backup location.

  1. 在物件總管 中,連接到 SQL Server Database Engine 的執行個體,然後展開該執行個體。In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.

  2. 開啟 [新增查詢] 視窗,然後執行下列命令,以便在您的 SQLTestDB 資料庫內建立資料庫主要金鑰憑證Open a New Query window and execute the following commands to create a database master key and a certificate within your SQLTestDB database.

    USE [SQLTestDB]
    
    -- Create the database master key
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe';  
    
    
    -- Create the certificate
    CREATE CERTIFICATE MyCertificate   
    ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'  
    EXPIRY_DATE = '20201031';  
    GO  
    
  3. 在 [物件總管] 中,展開 [資料庫] 、以滑鼠右鍵按一下 SQLTestDB、指向 [工作] ,然後按一下 [備份] 。In Object Explorer, Expand Databases, right-click SQLTestDB, point to Tasks, and then click Back Up....

  4. 在 [媒體選項] 頁面的 [覆寫媒體] 區段中,選取 [備份至新的媒體集,並清除所有現有的備份組] 。On the Media Options page, in the Overwrite media section select Back up to a new media set, and erase all existing backup sets.

  5. 在 [備份選項] 頁面的 [加密] 區段中,選取 [加密備份] 核取方塊。On the Backup Options page in the Encryption section select the Encrypt backup check box.

  6. 從 [演算法] 下拉式清單中,選取 [AES 256] 。From the Algorithm drop-down list, select AES 256.

  7. 從 [憑證或非對稱金鑰] 下拉式清單中,選取 MyCertificateFrom the Certificate or Asymmetric key drop-down list select MyCertificate.

  8. 選取 [確定] 。Select OK.

加密的備份

D.備份到 Azure Blob 儲存體服務D. Back up to the Azure Blob storage service

下列範例會將 Sales 資料庫完整備份至 Microsoft Azure Blob 儲存體服務。The three examples below perform a full database backup of Sales to the Microsoft Azure Blob storage service. 儲存體帳戶名稱為 mystorageaccountThe storage Account name is mystorageaccount. 容器名稱為 myfirstcontainerThe container is called myfirstcontainer. 為求簡潔,前四個步驟只會在此列出一次,所有範例將從步驟 5 開始進行。For brevity, the first four steps are listed here once and all examples will start on Step 5.

  1. 在物件總管 中,連接到 SQL Server Database Engine 的執行個體,然後展開該執行個體。In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.
  2. 展開 [資料庫] ,以滑鼠右鍵按一下 Sales,指向 [工作] ,然後按一下 [備份...] 。Expand Databases, right-click Sales, point to Tasks, and then click Back Up....
  3. 在 [一般] 頁面之 [目的地] 區段的 [備份至:] 下拉式清單中,選取 [URL] 。On the General page in the Destination section select URL from the Back up to: drop-down list.
  4. 按一下 [加入] ,[選取備份目的地] 對話方塊隨即開啟。Click Add and the Select Backup Destination dialog box will open.

等量備份至 URL 且 SQL Server 認證已經存在Striped backup to URL and a SQL Server credential already exists

已建立具有讀取、寫入和列出權限的預存存取原則。A stored access policy has been created with read, write, and list rights. 使用與此預存存取原則相關聯的共用存取簽章建立了 SQL Server 認證 https://mystorageaccount.blob.core.windows.net/myfirstcontainerThe SQL Server credential, https://mystorageaccount.blob.core.windows.net/myfirstcontainer, was created using a Shared Access Signature that is associated with the Stored Access Policy.

  1. 從 [Azure 儲存體容器:] 文字方塊中選取 https://mystorageaccount.blob.core.windows.net/myfirstcontainerSelect https://mystorageaccount.blob.core.windows.net/myfirstcontainer from the Azure storage container: text box
  2. 在 [備份檔案:] 文字方塊中,輸入 Sales_stripe1of2_20160601.bakIn the Backup File: text box enter Sales_stripe1of2_20160601.bak.
  3. 按一下 [確定] 。Click OK.
  4. 重複步驟 45Repeat Steps 4 and 5.
  5. 在 [備份檔案:] 文字方塊中,輸入 Sales_stripe2of2_20160601.bakIn the Backup File: text box enter Sales_stripe2of2_20160601.bak.
  6. 按一下 [確定] 。Click OK.
  7. 按一下 [確定] 。Click OK.

共用存取簽章存在,但 SQL Server 認證不存在A shared access signature exists and a SQL Server Credential does not exist

  1. 在 [Azure 儲存體容器:] 文字方塊中,輸入 https://mystorageaccount.blob.core.windows.net/myfirstcontainerEnter https://mystorageaccount.blob.core.windows.net/myfirstcontainer in the Azure storage container: text box
  2. 在 [共用存取原則:] 文字方塊中,輸入共用存取簽章。Enter the shared access signature in the Shared Access Policy: text box.
  3. 按一下 [確定] 。Click OK.
  4. 按一下 [確定] 。Click OK.

共用存取簽章不存在A shared access signature does not exist

  1. 按一下 [新增容器] 按鈕,[連接至 Microsoft 訂用帳戶] 對話方塊隨即開啟。Click the New container button and the Connect to a Microsoft Subscription dialog box will open.
  2. 完成 [連接至 Microsoft 訂用帳戶] 對話方塊,然後按一下 [確定] 回到 [選取備份目的地] 對話方塊。Complete the Connect to a Microsoft Subscription dialog box and then click OK to return the Select Backup Destination dialog box. 如需其他資訊,請參閱連接到 Microsoft Azure 訂用帳戶See See Connect to a Microsoft Azure Subscription for additional information.
  3. 在 [選取備份目的地] 對話方塊中,按一下 [確定] 。Click OK at the Select Backup Destination dialog box.
  4. 按一下 [確定] 。Click OK.

使用 Transact-SQLUsing Transact-SQL

建立完整資料庫備份Create a full database backup

  1. 執行 BACKUP DATABASE 陳述式以建立完整資料庫備份,請指定:Execute the BACKUP DATABASE statement to create the full database backup, specifying:

    • 欲備份的資料庫名稱。The name of the database to back up.
    • 寫入完整資料庫備份的備份裝置。The backup device where the full database backup is written.
      完整資料庫備份的基本 Transact-SQLTransact-SQL 語法如下:The basic Transact-SQLTransact-SQL syntax for a full database backup is:

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

    選項Option [描述]Description
    databasedatabase 為要備份的資料庫。Is the database that is to be backed up.
    backup_device [ , ...n ]backup_device [ ,...n ] 指定一份清單,列出備份作業可使用的 1 到 64 個備份裝置。Specifies a list of from 1 to 64 backup devices to use for the backup operation. 您可以指定實體備份裝置,或者指定對應的邏輯備份裝置 (若已經定義)。You can specify a physical backup device, or you can specify a corresponding logical backup device, if already defined. 若要指定實體備份裝置,請使用 DISK 或 TAPE 選項:To specify a physical backup device, use the DISK or TAPE option:

    { DISK | TAPE } = physical_backup_device_name{ DISK | TAPE } =physical_backup_device_name

    如需詳細資訊,請參閱 備份裝置 (SQL Server)執行個體上建立資料庫備份,就需要這個選項。For more information, see Backup Devices (SQL Server).
    WITH with_options [ , ...o ]WITH with_options [ ,...o ] 或者,也可以指定一個或多個其他選項 oOptionally, specifies one or more additional options, o. 如需有關選項基本概念的詳細資訊,請參閱步驟 2。For information about some of the basic with options, see step 2.
  2. 選擇性地指定一或多個 WITH 選項。Optionally, specify one or more WITH options. 這裡描述的是一些基本的 WITH 選項。A few basic WITH options are described here. 如需所有 WITH 選項的資訊,請參閱 BACKUP (Transact-SQL)For information about all the WITH options, see BACKUP (Transact-SQL).

基本備份組 WITH 選項:Basic backup set WITH options:

  • { COMPRESSION | NO_COMPRESSION } :只有在 SQL Server 2008 EnterpriseSQL Server 2008 Enterprise 及更新的版本中,才會指定是否要在此備份上執行 備份壓縮 ,以覆寫伺服器層級的預設值。{ COMPRESSION | NO_COMPRESSION }: In SQL Server 2008 EnterpriseSQL Server 2008 Enterprise and later only, specifies whether backup compression is performed on this backup, overriding the server-level default.
  • ENCRYPTION (ALGORITHM, SERVER CERTIFICATE |ASYMMETRIC KEY) :只有在 SQL Server 2014 或更新的版本中,才能指定要使用的加密演算法以及憑證或非對稱金鑰來維護加密的安全。ENCRYPTION (ALGORITHM, SERVER CERTIFICATE |ASYMMETRIC KEY): In SQL Server 2014 or later only, specify the encryption algorithm to use, and the Certificate or Asymmetric key to use to secure the encryption.
  • DESCRIPTION = { ' text ' | @ text_variable }:指定描述備份組的自由形式文字。DESCRIPTION = { 'text' | @text_variable }: Specifies the free-form text that describes the backup set. 這個字串最多可有 255 個字元。The string can have a maximum of 255 characters.
  • NAME = { backup_set_name | @ backup_set_name_var } :指定備份組的名稱。NAME = { backup_set_name | @backup_set_name_var }: Specifies the name of the backup set. 名稱最多可有 128 個字元。Names can have a maximum of 128 characters. 如果未指定 NAME,它就是空白。If NAME is not specified, it is blank.

根據預設,BACKUP 會將備份附加到現有的媒體集,以保留現有的備份組。By default, BACKUP appends the backup to an existing media set, preserving existing backup sets. 若要明確指定這項,請使用 NOINIT 選項。To explicitly specify this, use the NOINIT option. 如需附加至現有備份組的資訊,請參閱 媒體集、媒體家族與備份組 (SQL Server)For information about appending to existing backup sets, see Media Sets, Media Families, and Backup Sets (SQL Server).

另外,若要格式化備份媒體,請使用 FORMAT 選項:Alternatively, to format the backup media, use the FORMAT option:

FORMAT [ , MEDIANAME = { media_name | @ media_name_variable } ] [ , MEDIADESCRIPTION = { text | @ text_variable } ]FORMAT [ , MEDIANAME**=** { media_name | @media_name_variable } ] [ , MEDIADESCRIPTION = { text | @text_variable } ]
當您第一次使用媒體或是想要覆寫所有現有的資料時,請使用 FORMAT 子句。Use the FORMAT clause when you are using media for the first time or you want to overwrite all existing data. 選擇性地為新的媒體指派媒體名稱和描述。Optionally, assign the new media a media name and description.

重要

當您使用 BACKUP 陳述式的 FORMAT 子句時要非常小心,因為它會破壞備份媒體先前所儲存的任何備份。Use extreme caution when you are using the FORMAT clause of the BACKUP statement because this destroys any backups that were previously stored on the backup media.

Transact-SQL 範例Transact-SQL Examples

針對後續範例,使用下列 Transact-SQL 程式碼建立測試資料庫:For the following examples, create a test database with the following Transact-SQL code:

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.備份到磁碟裝置A. Back up to a disk device

下列範例使用 SQLTestDB 建立新的媒體集,以將整個 FORMAT 資料庫備份至磁碟。The following example backs up the complete SQLTestDB database to disk, by using FORMAT to create a new media set.

USE SQLTestDB;  
GO  
BACKUP DATABASE SQLTestDB  
TO DISK = 'Z:\SQLServerBackups\SQLTestDB.Bak'  
   WITH FORMAT,  
      MEDIANAME = 'Z_SQLServerBackups',  
      NAME = 'Full Backup of SQLTestDB';  
GO  

B.備份到磁帶裝置B. Back up to a tape device

下列範例會將完整的 SQLTestDB 資料庫備份到磁帶上,並將備份附加到先前的備份中。The following example backs up the complete SQLTestDB database to tape, appending the backup to the previous backups.

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

C.備份到邏輯磁帶裝置C. Back up to a logical tape device

下列範例會為磁帶機建立邏輯備份裝置。The following example creates a logical backup device for a tape drive. 這個範例接著會將完整的 SQLTestDB 資料庫備份至該裝置。The example then backs up the complete SQLTestDB database to that device.

-- 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  

使用 PowerShellUsing PowerShell

使用 Backup-SqlDatabase Cmdlet。Use the Backup-SqlDatabase cmdlet. 為明確指出這是完整的資料庫備份,請以預設值 Database 指定 -BackupAction參數。To explicitly indicate that this is a full database backup, specify the -BackupAction parameter with its default value, Database. 此參數在完整資料庫備份下是選擇性的。This parameter is optional for full database backups.

PowerShell 範例Powershell Examples

A.A. 完整本機備份Full local backup

下列範例會在伺服器執行個體 MyDB 的預設備份位置,建立 Computer\Instance資料庫的完整資料庫備份。The following example creates a full database backup of the MyDB database to the default backup location of the server instance Computer\Instance. 這個範例指定了選擇性的 -BackupAction DatabaseOptionally, this example specifies -BackupAction Database.

Backup-SqlDatabase -ServerInstance Computer\Instance -Database MyDB -BackupAction Database  

B.B. 完整備份至 Microsoft AzureFull backup to Microsoft Azure

下列範例會將 MyServer 執行個體上的資料庫 Sales,完整備份至 Microsoft Azure Blob 儲存體服務。The following example creates a full backup of the database Sales on the MyServer instance to the Microsoft Azure Blob Storage service. 已建立具有讀取、寫入和列出權限的預存存取原則。A stored access policy has been created with read, write, and list rights. 使用與此預存存取原則相關聯的共用存取簽章建立了 SQL Server 認證 https://mystorageaccount.blob.core.windows.net/myfirstcontainerThe SQL Server credential, https://mystorageaccount.blob.core.windows.net/myfirstcontainer, was created using a Shared Access Signature that is associated with the Stored Access Policy. 此 PowerShell 命令會使用 BackupFile 參數指定備份檔案的位置 (URL) 和名稱。The PowerShell command uses the BackupFile parameter to specify the location (URL) and the backup file name.

import-module sqlps;
$container = 'https://mystorageaccount.blob.core.windows.net/myfirstcontainer';
$FileName = 'Sales.bak';
$database = 'Sales';
$BackupFile = $container + '/' + $FileName ;
  
Backup-SqlDatabase -ServerInstance "MyServer" -Database $database -BackupFile $BackupFile;

若要設定和使用 SQL Server PowerShell 提供者To set up and use the SQL Server PowerShell provider

相關工作Related Tasks

另請參閱See also