建立完整資料庫備份Create a Full Database Backup

適用於: 是SQL Server否Azure SQL Database否Azure Synapse Analytics (SQL DW)否平行處理資料倉儲APPLIES TO: YesSQL Server NoAzure SQL Database NoAzure Synapse Analytics (SQL DW) NoParallel Data Warehouse

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

如需將 SQL Server 備份至 Azure Blob 儲存體服務的資訊,請參閱使用 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 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 large databases, consider supplementing full database backups with a series of differential database backups.
  • 使用 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).

SecuritySecurity

資料庫備份上的 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 DATABASEBACKUP LOG 權限預設為 系統管理員固定伺服器角色以及 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 服務之帳戶必須具備寫入備份裝置的權限。The SQL ServerSQL Server service must be able to read and write to the device which means that the account under which the SQL ServerSQL Server service runs must have write permissions to the backup device. 不過,在系統資料表中加入備份裝置項目的 sp_addumpdevice並不會檢查檔案存取權限。However, sp_addumpdevice, which adds an entry for a backup device in the system tables, does not check file access permissions. 其結果為當您嘗試備份或還原時,存取實體資源之前不一定會出現備份裝置實體檔案的這些問題。As a result, 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 backup 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.

  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, 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 that you wish to backup, point to Tasks, and then click Back Up....

  4. 在 [備份資料庫] 對話方塊中,您選取的資料庫會顯示在下拉式清單內 (您可以變更為伺服器上任何其他的資料庫)。In the Back Up Database dialog box, the database that you selected appears in the drop-down list (which you can change to any other database on the server).

  5. 在 [備份類型] 下拉式清單中,選取需要的備份類型 (預設為 [完整] )。In the Backup type drop-down list, select the desired backup type - the default is Full.

    重要

    您必須執行至少一次完整的資料庫備份,才可以執行差異或交易記錄備份。You must perform at least one full database backup before you can perform a differential or a transaction log backup.

  6. 在 [備份元件] 下方,選取 [資料庫] 。Under Backup component, select Database.

  7. 在 [目的地] 區段中,檢閱備份檔案的預設位置 (位於 ../mssql/data 資料夾)。In the Destination section, review the default location for the backup file (in the ../mssql/data folder).

    若要備份到不同裝置,請使用 [備份至] 下拉式清單來變更選取項目。To backup to a different device, change the selection using the Back up to drop-down list. 若要將備份組分割成多個檔案以增加備份速度,請按一下 [新增] 來新增其他備份物件和/或目的地。To stripe the backup set across multiple files for increased backup speed, 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. (選擇性) 檢閱 [媒體選項] 和 [備份選項] 頁面下方的其他可用設定。(optional) 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.

  9. 按一下 [確定] 來起始備份。Click OK to initiate the backup.

  10. 備份成功完成後,請按一下 [確定] 來關閉 Microsoft SQL Server Management Studio 對話方塊。When the backup completes successfully, click OK to close the SQL Server Management Studio dialog box.

其他資訊Additional information

  • 在建立完整的資料庫備份後,您可以建立差異資料庫備份交易記錄備份After creating a full database backup, you can create a differential database backup or a transaction log backup.

  • (選擇性) 您可以選取 [只複製備份] 核取方塊來建立僅限複製備份。(optional) 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 is disabled on the Media Options page if you are backing up to a URL.

範例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.

  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, expand the server tree.

  2. 展開 [資料庫] ,以滑鼠右鍵按一下 SQLTestDB,指向 [工作] ,然後按一下 [備份...] 。Expand Databases, right-click SQLTestDB, point to Tasks, and then click Back Up....

  3. 按一下 [確定] 。Click OK.

  4. 備份成功完成後,請按一下 [確定] 來關閉 Microsoft SQL Server Management Studio 對話方塊。When the backup completes successfully, click OK to close the SQL Server Management Studio dialog box.

進行 SQL 備份

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

在此範例中,SQLTestDB 資料庫會備份到磁碟上您選擇的位置。In this example, the SQLTestDB database will be backed up to disk at a location of your choice.

  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, expand the server tree.

  2. 展開 [資料庫] ,以滑鼠右鍵按一下 SQLTestDB,指向 [工作] ,然後按一下 [備份...] 。Expand Databases, right-click SQLTestDB, 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. 在 [檔案名稱] 文字方塊中輸入有效的路徑和檔案名稱,然後使用 .bak 作為副檔名來簡化此檔案的分類。Enter a valid path and file name in the File name text box and use .bak as the extension to simplify the classification of this file.

  7. 按一下 [確定] ,然後按一下 [確定] 來起始備份。Click OK and then click OK again to initiate the backup.

  8. 備份成功完成後,請按一下 [確定] 來關閉 Microsoft SQL Server Management Studio 對話方塊。When the backup completes successfully, click OK to close the SQL Server Management Studio dialog box.

變更 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. 連線到適當的 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, expand the server tree.

  2. 依序展開 [資料庫] 及 [系統資料庫] 、以滑鼠右鍵按一下 master,然後按一下 [新查詢] 來開啟查詢視窗並連線到您的 SQLTestDB 資料庫。Expand Databases, expand System Databases, right-click master, and click New Query to open a query window with a connection to your SQLTestDB database.

  3. 執行下列命令以在 master 資料庫中建立資料庫主要金鑰憑證Execute the following commands to create a database master key and a certificate within the master database.

    -- 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';  
    
  4. 在 [物件總管] 的 [資料庫] 節點中,以滑鼠右鍵按一下 SQLTestDB、指向 [工作] ,然後按一下 [備份...] 。In Object Explorer, in the Databases node, right-click SQLTestDB, point to Tasks, and then click Back Up....

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

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

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

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

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

加密的備份

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

以下範例會執行 SQLTestDB 的完整資料庫備份到 Azure Blob 儲存體服務。The example below performs a full database backup of SQLTestDB to the Azure Blob storage service. 此範例假設您已擁有具備 Blob 容器的儲存體帳戶。This example assumes that you already have a storage account with a blob container. 此範例會為您建立共用存取簽章;此範例會在已有現有共用存取簽章的容器上失敗。This example creates a shared access signature for you; this example fails of the container has an existing shared access signature.

若您在儲存體帳戶中尚未擁有 Azure Blob 容器,請先建立一個再繼續。If you do not have an Azure blob container in a storage account, create one before continuing. 如需詳細資訊,請參閱建立一般用途儲存體帳戶建立容器For more information, see Create a general purpose storage account and Create a container.

  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, expand the server tree.

  2. 展開 [資料庫] ,以滑鼠右鍵按一下 SQLTestDB,指向 [工作] ,然後按一下 [備份...] 。Expand Databases, right-click SQLTestDB, 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.

  5. 若您先前已向 SQL Server Management Studio 註冊您希望使用的 Azure 儲存體容器,請選取它。If you have previously registered the Azure storage container that you wish to use with SQL Server Management Studio, select it. 否則,請按一下 [新增容器] 來註冊新的容器。Otherwise, click New container to register a new container.

  6. 在 [連線到 Microsoft 訂用帳戶] 對話方塊中,登入您的帳戶。In the Connect to a Microsoft Subscription dialog box, sign in to your account.

  7. 在 [選取儲存體帳戶] 下拉式文字方塊中,選取您的儲存體帳戶。In the Select Storage Account drop-down text box, select your storage account.

  8. 在 [選取 Blob 容器] 下拉式文字方塊中,選取您的 Blob 容器。In the Select Blob Container drop-down text box, select your blob container.

  9. 在 [共用存取原則逾期] 下拉式行事曆方塊中,選取您在此範例中所建立共用存取原則的到期日。In the Shared Access Policy Expiration drop-down calendar box, select an expiration date for the shared access policy that you create in this example.

  10. 按一下 [建立認證] 來在 SQL Server Management Studio 中產生共用存取簽章和認證。Click Create Credential to generate a shared access signature and credential in SQL Server Management Studio.

  11. 按一下 [確定] 來關閉 [連線到 Microsoft 訂用帳戶] 對話方塊。Click OK close the Connect to a Microsoft Subscription dialog box.

  12. 在 [備份檔案] 文字方塊中,修改備份檔案的名稱 (選擇性)。In the Backup File text box, modify the name of the backup file (optional).

  13. 按一下 [確定] 來關閉 [選取備份目的地] 對話方塊。Click OK to close the Select a backup destination dialog box.

  14. 按一下 [確定] 來起始備份。Click OK to initiate the backup.

  15. 備份成功完成後,請按一下 [確定] 來關閉 Microsoft SQL Server Management Studio 對話方塊。When the backup completes successfully, click OK to close the SQL Server Management Studio dialog box.

使用 Transact-SQLUsing Transact-SQL

執行 BACKUP DATABASE 陳述式以建立完整資料庫備份,請指定:Create a full database backup by executing 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 database TO backup_device [ , ...n ] [ WITH with_options [ , ...o ] ] ;BACKUP DATABASE database TO backup_device [ ,...n ] [ 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.

選擇性地指定一或多個 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.

範例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 = 'c:\tmp\SQLTestDB.bak'
   WITH FORMAT,
      MEDIANAME = '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.

注意

這些範例需要 SqlServer 模組。These examples require the SqlServer module. 若要判斷是否已安裝,請執行 Get-Module -Name SqlServerTo determine if it is installed, run Get-Module -Name SqlServer. 若要安裝此模組,請在 PowerShell 的系統管理員工作階段中執行 Install-Module -Name SqlServerTo install this module, run Install-Module -Name SqlServer in an administrator session of PowerShell.

如需詳細資訊,請參閱 SQL Server PowerShell ProviderFor more information, see SQL Server PowerShell Provider.

重要

若您正在從 SQL Server Management Studio 開啟 PowerShell 視窗來連線到安裝的 SQL Server,您可以省略此範例的認證部分,因為會自動使用您在 SSMS 中認證來建立 PowerShell 與您 SQL Server 執行個體間的連線。If you are opening a PowerShell window from within SQL Server Management Studio to connect to an installation of SQL Server, you can omit the credential portion of this example as your credential in SSMS is automatically used to establish the connection between PowerShell and your SQL Server instance.

範例Examples

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

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

如需完整的語法和其他範例,請參閱 Backup-SqlDatabaseFor the full syntax and additional examples, see Backup-SqlDatabase.

$credential = Get-Credential

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

B.B. 完整備份到 AzureFull backup to Azure

下列範例會將資料庫 <myDatabase> 上的 <myServer> 執行個體完整備份至 Azure Blob 儲存體服務。The following example creates a full backup of the database <myDatabase> on the <myServer> instance to the 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/<myContainer>The SQL Server credential, https://<myStorageAccount>.blob.core.windows.net/<myContainer>, 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.

$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

另請參閱See also