Skip to content

copy-only-backups-sql-server.md

Latest commit

 

History

History
144 lines (95 loc) · 7.34 KB

copy-only-backups-sql-server.md

File metadata and controls

144 lines (95 loc) · 7.34 KB
title Copy-Only Backups
description A copy-only backup is a SQL Server backup that is independent of the sequence of SQL Server backups. It doesn't affect how later backups are restored.
author MashaMSFT
ms.author mathoma
ms.reviewer randolphwest
ms.date 06/03/2025
ms.service sql
ms.subservice backup-restore
ms.topic conceptual
helpviewer_keywords
copy-only backups [SQL Server]
COPY_ONLY option [BACKUP statement]
backups [SQL Server], copy-only backups
monikerRange =azuresqldb-mi-current || >=sql-server-2016 || >=sql-server-linux-2017

Copy-only backups

[!INCLUDE SQL Server SQL MI]

A copy-only backup is a [!INCLUDE ssNoVersion] backup that is independent of the sequence of conventional [!INCLUDE ssNoVersion] backups. Usually, taking a backup changes the database and affects how later backups are restored. However, occasionally, it's 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.

Types of copy-only backups

The types of copy-only backups are as follows:

Copy-only full backups (all recovery models)

  • A copy-only full backup can't serve as a differential base or differential backup and doesn't 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, doesn't affect the sequencing of regular log backups. Copy-only log backups are typically unnecessary. Instead, you can create a new routine log backup, and restore that backup (using the WITH NORECOVERY option) together with any previous log backups that are required for the restore sequence.

    A copy-only log backup can sometimes be useful for performing an online restore. For more information, follow the instructions in the article Example: Online restore of a read-write file (full recovery model), using the copy-only backup files instead.

  • The transaction log is never truncated after a copy-only backup.

Remarks

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

In [!INCLUDE ssazuremi-md], copy-only backups can't be created for a database encrypted with service-managed Transparent Data Encryption (TDE). Service-managed TDE uses internal key for encryption of data, and that key can't be exported, so you couldn't restore the backup anywhere else. 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.

Create a copy-only backup

You can create a copy-only backup with [!INCLUDE ssManStudioFull], [!INCLUDE tsql], or PowerShell.

A. Use SQL Server Management Studio

In this example, a copy-only backup of the Sales database is backed up to disk at the default backup location.

  1. In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.

  2. Expand Databases, right-click Sales, point to Tasks, and then select Back Up....

  3. On the General page in the Source section, check the Copy-only backup checkbox.

  4. Select OK.

B. Use Transact-SQL

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;

Note

COPY_ONLY has no effect when specified with the DIFFERENTIAL option.

C. Use Transact-SQL and Azure SQL Managed Instance

Azure SQL Managed Instance supports taking COPY_ONLY full backups. The example performs a COPY_ONLY backup of MyDatabase to the Microsoft Azure Blob Storage. The storage Account name is mystorageaccount. The container is called myfirstcontainer. A storage access policy is created with read, write, delete, and list rights. The [!INCLUDE ssNoVersion] credential, https://mystorageaccount.blob.core.windows.net/myfirstcontainer, was created using a Shared Access Signature that is associated with the Storage Access Policy secret. For information on [!INCLUDE ssNoVersion] backup to the Microsoft Azure Blob Storage, see SQL Server backup and restore with Azure Blob Storage and SQL Server backup to URL for Microsoft Azure Blob Storage.

-- Prerequisite to have write permissions
CREATE CREDENTIAL [https://mystorageaccount.blob.core.windows.net/myfirstcontainer]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sp=...'; -- Enter your secret SAS token here.

BACKUP DATABASE MyDatabase
TO URL = 'https://mystorageaccount.blob.core.windows.net/myfirstcontainer/MyDatabaseBackup.bak'
WITH STATS = 5, COPY_ONLY;

To take a copy-only backup divided into multiple stripes, use this example:

BACKUP DATABASE MyDatabase
TO URL = 'https://mystorageaccount.blob.core.windows.net/myfirstcontainer/MyDatabase-01.bak',
URL = 'https://mystorageaccount.blob.core.windows.net/myfirstcontainer/MyDatabase-02.bak',
URL = 'https://mystorageaccount.blob.core.windows.net/myfirstcontainer/MyDatabase-03.bak',
URL = 'https://mystorageaccount.blob.core.windows.net/myfirstcontainer/MyDatabase-04.bak'
WITH COPY_ONLY;

D. Use PowerShell

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

Create a full or log backup

View copy-only backups

Set up and use the SQL Server PowerShell provider

Related content