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.

This is Part 11 of 16 Part SQL Server Database Backup Tutorial. Click here to read it from the beginning….

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.

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

Clicking Next Page button to continue reading the topics and click on the Previous Page button to revisit the previous topic.

Related Articles

• SQL Server WHERE Clause T-SQL Tutorial with Examples
• SQL Server ORDER BY Clause T-SQL Tutorial with Examples
• SQL Server SELECT TOP Clause T-SQL Tutorial with Examples
• SQL SELECT | SQL QUERY | SQL SELECT Statement T-SQL Tutorial with Examples
• SERVERPROPERTY Function T-SQL Enhancement in SQL Server 2016
• FORMATMESSAGE Statement T-SQL Enhancement in SQL Server 2016
• STRING_SPLIT Function and STRING_ESCAPE Function in SQL Server 2016
• COMPRESS and DECOMPRESS T-SQL Enhancement in SQL Server 2016
• ALTER DATABASE SET AUTOGROW_ALL_FILES T-SQL Enhancement in SQL Server 2016
• ALTER DATABASE SET AUTOGROW_SINGLE_FILE T-SQL Enhancement in SQL Server 2016
• ALTER TABLE WITH (ONLINE=ON | OFF) T-SQL Enhancement in SQL Server 2016
• DROP IF EXISTS SQL Server T-SQL Enhancement in SQL Server 2016
• TRUNCATE TABLE WITH PARTITIONS T-SQL Enhancement in SQL Server 2016
• T-SQL Enhancements in SQL Server 2016 for Developers and DBAs
• TAIL-LOG Backup in SQL Server Step by Step Tutorial with Examples
• PARTIAL Backup in SQL Server Step by Step Tutorial with Examples