Udostępnij za pośrednictwem

Copy-Only Backups (SQL Server)

  • Artykuł

A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL 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. 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.

Copy-only backups are recorded in the is_copy_only column of the backupset table.

To Create a Copy-Only Backup

You can create a copy-only backup by using SQL Server Management Studio, Transact-SQL, or PowerShell.

Using SQL Server Management Studio

  1. On the General page of the Back Up Database dialog box, select the Copy Only Backup option.

Using Transact-SQL

The essential Transact-SQL syntax is as follows:

  • For a copy-only full backup:

    BACKUP DATABASE database_name TO <backup_device> … WITH COPY_ONLY …

    [!UWAGA]

    COPY_ONLY has no effect when specified with the DIFFERENTIAL option.

  • For a copy-only log backup:

    BACKUP LOG database_name TO <backup_device> … WITH COPY_ONLY …

Using PowerShell

  1. Use the Backup-SqlDatabase cmdlet with the -CopyOnly parameter.

To create a full or log backup

To view copy-only backups

To set up and use the SQL Server PowerShell provider

[Top]

Zobacz także

Koncepcje

Backup Overview (SQL Server)

Recovery Models (SQL Server)

Copy Databases with Backup and Restore

Restore and Recovery Overview (SQL Server)