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 Centralized Backupper Database as a less troublesome alternative.

When you use AOMEI Centralized Backupper Database 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 control all laptops, workstations, and servers within LAN from a single computer, and remotely back up one or more databases. When restoring you can also choose to restore to original location or to any one of them. This reduces errors and saves administrative costs.

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

To properly use AOMEI Centralized Backupper Database, especially if you want to manage multiple computers within LAN, you need to go to the Computers page first. Click Install client program on the upper bar to download Agent on client computers manually or remotely.

For those computers which download client program manually, you need to request control over them for further operations.

Click Computers > Uncontrolled Computers, select single or multiple client computers and click Request Control on the upper bar.

A permission request window will pop up on their computers, once all permissions are received, you can manage them at any time.

How to Create SQL Full or Differential Backup Task

1. Click Tasks > New Task and select SQL Server Backup to create a new backup task.

In pop-up window, you can change the task name by moving your cursor to the task name and click it.

And then follow the 3 steps beneath the task name.

2. Click Add Computers to add the controlled client computer you want to back up with.

3. Click Add to select single or multiple SQL instances and databases you want to back up.

4. Select a location as the destination path. You can click Add Storage to add a share or NAS path as a storage end.

Click Settings. You can also enable backup encryption, email notification and select a compression level for backups.

5. Click Schedule next to Settings, you can also specify days and time to run this task automatically.

6. Click Advanced on Schedule Settings page, choose to perform Full or Differential Backup.

7. Now you can click Start Backup to select Only create the task, or Create and execute the task.

Once created, the tasks will be listed in Tasks separately for further edit or restore. When restoring, it will list all the backups, and distinguish them by the backup type and dates in their names.

How to Restore Full or Differential Backup

1. Click Advanced on the upper right of SQL Server Backup task, and select Restore.

2. Select the client computer and single or multiple databases you want to restore in prompt window.

3. Now you can see a list of backups named by backup types and dates at the right corner, choose one specific backup (full/differential) you want to restore.

If you want to restore database from differential backups, you don't need to manually restore the full backup in advance.

Click Next.

4. Choose to Restore to original location or Restore to a new location.

If you select the latter, you can restore the backup to another instance on same or another server within LAN.

5. Now you can click Start Restore.

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 Centralized Backupper Database Edition 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, files, disks, systems, partitions, etc. 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.