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