What is MSSQL Copy-only Backup and How to Create it

MSSQL Copy-Only Backup and Other Backup Types

When using MS SQL Server, we often backup the databases in order to ensure data security and prevent accidental loss, because using the backup files we can restore the lost data. To meet different needs, SQL Server provides various backup types. The following are the common backup types in SQL Server.

• Full Backup
• Differential Backup
• Transaction Log Backup
• Tail Log Backup
• Copy-only Backup
• File Backups
• Partial Backups

As you can see, SQL Server backup covers a wide range of knowledge. It is difficult to cover all types in the limited space of one article. Therefore, this article will only introduce MS SQL Copy-only Backup.

What is MSSQL Copy-only Backup

A MSSQL Copy-only backup is a standard copy of the original database, that is independent of the sequence of conventional SQL Server backups. When you create a SQL Server copy-only backup, you will not truncate the transaction log or interfere with regular backup tasks, therefore will not affect the later restore.

The Difference between Full Backup and Copy-only Backup

The only difference between a full backup and a copy-only backup is that a copy-only backup does not update the Database Backup LSN (Log Sequence Number), while a full backup automatically updates the LSN when performing.

✦ Let’s take an example to explain all this. When you perform the SQL Server differential backup, first you need to create a full backup as the differential base, and then a series of differential backups. When restoring, you need to restore this full backup and the latest differential backup.

The Database Backup LSN of the differential backup should match the full backup. If it does not match, then SQL Server will not join this full backup with this differential backup, which will cause the restore to fail.

However, if you manually perform a full backup in the middle of a regular differential backup task, the later differential backups will then base on this full backup you made, instead of the original one. When restoring, the latest differential backup will not match the original full backup, so you may fail.

Whereas copy-only backups do not update the LSN, so it will not affect the later differential backups, and you will not fail.

Types of MSSQL Copy-only backups

Basically, there are 2 types of MSSQL Copy-only backups, and each of them applicable to a different case.

Copy-only full backups: (Works on all recovery models): A copy-only full backup is a full backup that does not affect the differential base for differential backups, it does not update the Database Backup LSN. And the steps of restoring a copy-only full backup is exactly the same as restoring any other full backup.

Copy-only log backups: (Only works on full recovery model and bulk-logged recovery model): The same as the copy-only full backup, a copy-only log backup does not affect the sequencing of regular log backups. It does not truncate the transaction log. Usually, copy-only log backups are rarely used in practice, except for performing online restore sometimes.

How to Create MSSQL Copy-only Backup

The process of creating a copy-only backup is very simple. In SQL Server 2005 and later versions, we just need to check the Copy-only backup option in the regular backup process. Of course, you can also do it via T-SQL Commands or Powershell if you want.

Here I will provide you with the detailed steps for all 3 ways.

Way 1. Using SSMS GUI to create Copy-only Backup

To create a MS SQL Copy-only backup via SSMS GUI, except for checking the Copy-only Backup option on the tab, the rest steps are exactly the same as the regular backup process. Here are the specific steps.

1. Launch SQL Server Management Studio (SSMS) and connect to your instance.

2. Right-click the database name you want to back up, select Tasks > Back Up…

3. In the pop-up window, select the Backup type (Full or Transaction Log. You cannot create a copy-only differential backup).

4. Check Copy-only Backup option below Backup type.

5. Specify a Destination path to store backup files.

6. Click OK to execute the backup task.

Way 2. Creating Copy-only Backup with T-SQL Commands

T-SQL is an extension of the SQL language used by MS SQL Server to communicate with the databases. To create a MSSQL Copy-only backup, you only need to utilizing the COPY_ONLY parameter in the BACKUP DATABASE statement. The basic syntax is:

Copy-only full backups:

BACKUP DATABASE databasename TO DISK = 'filepath\filename_Copy.bak' WITH COPY_ONLY;

Copy-only log backups:

BACKUP LOG databasename TO DISK = 'filepath\filename_LogCopy.trn' WITH COPY_ONLY;

Here I give an example to explain how to customize them to your demands:

✦ I want to create a copy-only backup of database DAname, and store the backup file in D:\Backup. I will do the following steps:

1. Launch SSMS and connect to my instance.

2. Click New Query on the Standard bar, and input the following T-SQL Commands:

BACKUP DATABASE DAname TO DISK = 'D:\Backup\DA_Copy.bak' WITH COPY_ONLY;
BACKUP LOG DAname TO DISK = 'D:\Backup\DA_LogCopy.trn' WITH COPY_ONLY;

3. Click Execute on the toolbar to start backup.

Way 3. Performing MSSQL Copy-only Backup in PowerShell

To perform an MS SQL Copy-only backup in Powershell, you need to use the Backup-SqlDatabase cmdlet together with the -CopyOnly parameter. The basic syntax is:

Backup-SqlDatabase -ServerInstance 'instancename' -Database 'databasename' -BackupFile 'filepath\filename_Copy.bak' -CopyOnly

Just like the last method, I will give an example to show you how to use it:

✦ I want to create a copy-only backup of the database DAname, and store the backup file in D:\Backup. Here are the specific steps:

1. Launch Windows PowerShell (ISE) as administrator (You can use search box to find it).

2. Input the following T-SQL commands:

Backup-SqlDatabase -ServerInstance 'BF-202106171848\MSSERVER' -Database 'DAname' -BackupFile 'D:\Backup\DAname_Copy.bak' -CopyOnly

3. Click Run Script (green triangle) on toolbar to run it.

Error-free Way to Schedule and Restore Differential Backups

As you can see, if you accidentally perform a full backup in the middle of a scheduled differential backup task, you may fail in later restore, and resulting in data loss. Therefore, I recommend you to use the professional backup software AOMEI Cyber Backup as a less troublesome alternative.

When you use AOMEI Cyber Backup to perform a differential backup, even if you perform another full backup in the middle, it will not affect the restore of the differential backup. Besides, when restoring a database from a differential backup, you only need to select the corresponding differential backup in the list organized by backup type and date, no need to manually restore a full backup in advance.

In addition, with this software you can centrally backup VMware and Hyper-V virtual machines within LAN. When restoring, you can also choose to restore to original location or to another new location. This reduces errors and saves administrative costs.

Here's a 30-day free trial for you:

✍AOMEI Cyber Backup is a great solution to save the time and reduce errors of operating individually on different computers. It applies to 2005\2008\2008 R2\2012\2014\2016\2017\2019\2022 and corresponding SQL Server Express. Thus, you can use one software to backup all of your SQL Server within simple steps.

1. To properly use AOMEI Cyber Backup Trial Edition, especially if you want to manage multiple computers within LAN, you need to access to Source Device > Add Microsoft SQL. If the database exists and the version is supported, it will appear automatically. Otherwise, you can download proxy program to local first. Then click Already installed proxy and select the unadded proxies.

2. Click "..."--> Authentication to validate the database instance. You can choose Windows Authentication or SQL Authentication. Enter the credentials and click Verify.

How to Create MSSQL Full or Differential Backup Task

1. Click Backup Task on the left menu bar, then click Create New Task to start create database backup task. Please follow the simple steps to backup your SQL databases.

2. Choose backup type as Microsoft SQL Backup, and you could also try VMware Backup or Hyper-V Backup.

3. Enter a name for the backup task and select the databases you want to backup.

4. Select a location as the destination path. You can choose a local or network path as a storage end.

5. Click Schedule Backup to run your SQL database backups automatically and regularly. You can specify the backup methods as full/differential/incremental backup and backup time as daily/weekly/mothly.

6. (Optional) Enable Backup Cleanup to automatically remove history backup versions based on rule to save storage space.

(Optional) Email Notification: to receive email notifications when the task is abnormal or successful.

7. Finally, click "Start Backup" to create the backup task and perform backup.

How to Restore Full or Differential Backup

1. Click Backup Task on the left menu bar, locate the task you want to restore, and click ""--> Restore.

2. Select a differential or full backup of your SQL server database to restore.

3. Specify a target location that you want to restore to. You could restore to original/new location.

• Restore to original location: If you choose the option "Over the database with the same name", the detected database with the same name will be overwritten automatically when restoring. Otherwise, the database with the same name will be skipped.
• Restore to new location: Select target and specify the name of the new database. You can also modify the storage location.

Summary

MSSQL Copy-only backup is a very useful feature. The only difference between it and a full backup is that it does not affect subsequent differential backups, avoiding restore failures. When you need a database copy for testing or development, performing a copy-only backup may be a good choice.

You can also choose the professional backup software AOMEI Cyber Backup to perform backups. When you use it to perform a differential backup task, even if you perform a full backup in the middle, it won't affect the later restore, so you don't need to choose a copy-only backup additionally. Besides, when restoring, you only need to choose the differential backup directly, no need to manually restore a full backup in advance.

In addition, with this software you can centrally backup all devices within LAN from a single computer and remotely backup databases and virtual machines on these devices. When restoring, you can choose to restore to local or to any other device within LAN, which can reduce manual errors and significantly improve work efficiency.