仅复制备份Copy-Only Backups

适用对象:是SQL Server 是Azure SQL 数据库(仅限托管实例)否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database (Managed Instance only) noAzure Synapse Analytics (SQL DW) 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.

重要

在 Azure SQL 托管实例中,无法为使用服务管理的透明数据加密 (TDE) 加密的数据库创建仅复制备份。In Azure SQL managed instance copy-only backup cannot be created for a database encrypted with service-managed Transparent Data Encryption (TDE). 服务管理的 TDE 使用内部密钥对数据进行加密,并且该密钥无法导出,因此你无法在其他任何地方恢复备份。Service-managed TDE uses internal key for encryption of data, and that key cannot be exported, so you could not restore the backup anywhere else. 请考虑改用客户管理的 TDE 来创建加密数据库的仅复制备份,但请确保具有加密密钥供以后还原。Consider using customer-managed TDE instead to be able to create copy-only backups of encrypted databases, but make sure to have encryption key available for later restore.

创建仅复制备份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.

  1. 在“对象资源管理器” 中,连接到一个 SQL Server 数据库引擎实例,然后展开该实例。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-SQL”Using 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