COPY_ONLY Backup in SQL Server Step by Step Tutorial with Examples

COPY_ONLY backup is a special type of SQL Server Backup which is independent of the sequence of conventional SQL Server backups. Normally whenever you take a backup it will affect how later backups are restored. However, when you use COPY_ONLY backup feature of SQL Server one can take a backup of the database without affecting the overall backup and restore procedures for the database.

Practical Use of COPY_ONLY Backup Feature of SQL Server

One can use COPY_ONLY backup if there is a need to refresh the Development/QA environment with the latest backup of your Production database. In such a scenario you could run a FULL backup of the database WITH COPY_ONLY clause to take a backup without affecting the backup chain.

The transaction log is never truncated after a COPY_ONLY backup. A COPY_ONLY log backup preserves the existing log archive point and, therefore, does not affect the sequencing of regular log backups.

Types of COPY_ONLY backups are:-

• Using COPY_ONLY feature one can create FULL backup for any user databases irrespective of the recovery model used by the database. i.e., it supports FULL, BULK-LOGGED and SIMPLE recovery model. However, restoring a COPY_ONLY backup is same as restoring a normal FULL Backup.
• Using COPY_ONLY feature one can create DIFFERENTIAL backup for any user databases irrespective of the recovery model used by the database. i.e., it supports FULL, BULK-LOGGED and SIMPLE recovery model.
• Using COPY_ONLY feature one can create TRANSACTIONAL LOG backup for any user database which is in FULL or BULK-LOGGED recovery model.

Create a COPY_ONLY FULL backup Using TSQL command

BACKUP DATABASE MyTechMantra
TO DISK = 'C:\DBBackups\MyTechMantra.BAK'
WITH COPY_ONLY, COMPRESSION, STATS = 25 

/* Verify the Database Backup File Once the Backup has completed successfully */

RESTORE VERIFYONLY
FROM DISK = 'C:\DBBackups\MyTechMantra.BAK'
GO

Create a COPY_ONLY DIFFERENTIAL backup Using TSQL command

BACKUP DATABASE MyTechMantra
TO DISK = 'C:\DBBackups\MyTechMantra.DIF'
WITH DIFFERENTIAL, COPY_ONLY, COMPRESSION, STATS = 25 

/* Verify the Database Backup File Once the Backup has completed successfully */

RESTORE VERIFYONLY
FROM DISK = 'C:\DBBackups\MyTechMantra.DIF' 
GO

Create a COPY_ONLY TRANSACTION LOG backup Using TSQL command

BACKUP LOG MyTechMantra
TO DISK = 'C:\DBBackups\MyTechMantra.TRN'
WITH COPY_ONLY, COMPRESSION, STATS = 25 

/* Verify the Database Backup File Once the Backup has completed successfully */

RESTORE VERIFYONLY
FROM DISK = 'C:\DBBackups\MyTechMantra.TRN'
GO

Create a COPY_ONLY FULL Database Backup Using SQL Server Management Studio

• Open SQL Server Management Studio, connect to the appropriate SQL Server Database Instance, in Object Explorer, click the SERVER NAME to expand the server tree.
• Expand Databases node and then right click the database which you want to take a COPY_ONLY FULL backup
• Select Tasks, and then click Back Up…option as shown to open up Back Up Database dialog box.Select Backup Type as FULL and select the check box Copy-Only Backup and Select Destination as Disk
• Click on Add button to add a backup file and specify the backup file name and click OK to save the changes
• Finally to take COPY_ONLY FULL Backup click OK