只複製備份 (SQL Server)Copy-Only Backups (SQL Server)

適用於: 是SQL Server 是Azure SQL Database (僅限受控執行個體) 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database (Managed Instance only) noAzure SQL Data Warehouse noParallel Data Warehouse

「只複製備份」 是與傳統 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. 通常,進行備份會變更資料庫,而且會影響往後其他備份的還原方式。Usually, taking a backup changes the database and affects how later backups are restored. 不過,偶爾為了特殊目的在不影響資料庫整體備份及還原程序的情況下進行備份,相當有用。However, occasionally, it is useful to take a backup for a special purpose without affecting the overall backup and restore procedures for the database. 只複製備份即是供此目的之用。Copy-only backups serve this purpose.

只複製備份的類型如下所示:The types of copy-only backups are as follows:

  • 只複製完整備份 (所有復原模式)Copy-only full backups (all recovery models)

    只複製備份不能當做差異基底或差異備份,因此不會影響差異基底。A copy-only backup cannot serve as a differential base or differential backup and does not affect the differential base.

    還原只複製完整備份與還原任何其他完整備份相同。Restoring a copy-only full backup is the same as restoring any other full backup.

  • 只複製記錄備份 (僅完整復原模式和大量記錄復原模式)Copy-only log backups (full recovery model and bulk-logged recovery model only)

    只複製記錄備份會保留現有的記錄封存點,因此不會影響一般記錄備份的順序。A copy-only log backup preserves the existing log archive point and, therefore, does not affect the sequencing of regular log backups. 只複製記錄備份通常是沒有必要的。Copy-only log backups are typically unnecessary. 您反倒可以建立新的例行記錄備份 (使用 WITH NORECOVERY),且一併使用此備份與還原順序所需之任何先前的記錄備份。Instead, you can create a new routine log backup (using WITH NORECOVERY) and use that backup together with any previous log backups that are required for the restore sequence. 但是,只複製記錄備份有時相當利於進行線上還原。However, a copy-only log backup can sometimes be useful for performing an online restore. 如需這類範例,請參閱範例:線上還原讀取/寫入檔案 (完整復原模式)For an example of this, see Example: Online Restore of a Read-Write File (Full Recovery Model).

    交易記錄永遠不會在只複製備份之後截斷。The transaction log is never truncated after a copy-only backup.

只複製備份會記錄在 backupset 資料表的 is_copy_only 資料行中。Copy-only backups are recorded in the is_copy_only column of the backupset table.

若要建立只複製備份To Create a Copy-Only Backup

您可以使用 SQL Server Management StudioSQL Server Management StudioTransact-SQLTransact-SQL或 PowerShell 建立只複製備份。You can create a copy-only backup by using SQL Server Management StudioSQL Server Management Studio, Transact-SQLTransact-SQL, or PowerShell.

範例Examples

A.A. 使用 SQL Server Management StudioUsing SQL Server Management Studio

在此範例中,Sales 資料庫的只複製備份將會備份至預設備份位置的磁碟。In this example, a copy-only backup of the Sales database will be backed up to disk at the default backup location.

我們會持續聽取您的意見: 如果您發現本文中有過時或不正確的內容 (例如步驟或程式碼範例),請告訴我們。We are listening: If you find something outdated or incorrect in this article, such as a step or a code example, please tell us. 您可以按一下此頁面底部 [意見反應] 區段中的 [本頁] 按鈕。You can click the This page button in the Feedback section at the bottom of this page. 我們通常會在隔天閱讀有關 SQL 的每一個意見反應。We read every item of feedback about SQL, typically the next day. 謝謝。Thanks.

  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 Source section check the Copy-only backup checkbox.

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

B.B. 使用 Transact-SQLUsing Transact-SQL

此範例使用 COPY_ONLY 參數來建立 Sales 資料庫的只複製備份。This example creates a copy-only backup for the Sales database utilizing the COPY_ONLY parameter. 此外,也會擷取交易記錄的只複製備份。A copy-only backup of the transaction log is taken as well.

BACKUP DATABASE Sales
TO DISK = 'E:\BAK\Sales_Copy.bak'
WITH COPY_ONLY;

BACKUP LOG Sales
TO DISK = 'E:\BAK\Sales_LogCopy.trn'
WITH COPY_ONLY;

注意

指定 DIFFERENTIAL 選項時,COPY_ONLY 沒有任何作用。COPY_ONLY has no effect when specified with the DIFFERENTIAL option.

C.C. 使用 PowerShellUsing PowerShell

此範例使用 -CopyOnly 參數來建立 Sales 資料庫的只複製備份。This example creates a copy-only backup for the Sales database utilizing the -CopyOnly parameter.

Backup-SqlDatabase -ServerInstance 'SalesServer' -Database 'Sales' -BackupFile 'E:\BAK\Sales_Copy.bak' -CopyOnly

相關工作Related Tasks

若要建立完整備份或記錄備份To create a full or log backup

檢視只複製備份To view copy-only backups

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

另請參閱See Also

備份概觀 (SQL Server) Backup Overview (SQL Server)
復原模式 (SQL Server) Recovery Models (SQL Server)
使用備份與還原複製資料庫 Copy Databases with Backup and Restore
還原和復原概觀 (SQL Server)Restore and Recovery Overview (SQL Server)
BACKUP (Transact-SQL)BACKUP (Transact-SQL)
Backup-SqlDatabaseBackup-SqlDatabase