Copy-Only Backup in SQL Servers Explained

Although SQL (Structured Query Language) has been around for decades, it’s clear that SQL is still relevant for businesses and isn’t going anywhere. As a matter of fact, over 80% of businesses still use SQL and don’t plan on changing it in the near future. As the saying goes, “If it isn’t broken, don’t fix it.” Although SQL is a bit outdated, it is a common database query language that still works smoothly for many IT teams around the globe.

To ensure that the data stored in SQL servers remains protected, IT professionals back up the information on a regular basis. There are several ways to do this, and some well-known backup methods for SQL servers are differential backups and copy-only backups. If you want to conduct a backup that’s independent of your sequence of conventional backups, then a copy-only backup is exactly what you’ve been looking for.

What is a copy-only backup for a SQL server?

A copy-only backup is a type of backup for SQL servers that is separate from the sequence of conventional backups. In essence, a copy-only backup creates a backup that will not affect the other original backups or restore processes. It’s useful for making experimental changes to backed-up information without affecting the original data. Unlike other full backups, a copy-only backup cannot be used as a base to conduct differential backups.

As Microsoft clarifies, “A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups. Usually, taking a backup changes the database and affects how later backups are restored. However, occasionally, it is useful to take a backup for a special purpose without affecting the overall backup and restore procedures for the database. Copy-only backups serve this purpose.”

Types of SQL server copy-only backups

Copy-only for full backups (all recovery models)

Although copy-only can be used to conduct a full backup, keep in mind that you cannot use a copy-only backup as a base for a differential backup. Differential backups for SQL servers require full backups, and copy-only backups cannot be used in their place. When restoring a copy-only backup, it’s the same process as restoring any other type of SQL backup.

Copy-only for log backups (full recovery models and bulk-log recovery models only)

The purpose of a copy-only log backup is to back up the existing log archive point, but most of the time, this particular backup method is unnecessary. Instead of creating copy-only backups, users “Prefer creating a new routine for log backup (by using WITH NORECOVERY) or by using that new backup together with old or previous log backups which are necessary for sustaining the restore sequence,” DataNumen clarifies. A copy-only log backup isn’t used often, but it can be helpful to perform an online restore in certain situations.

Pros and cons of copy-only backup for SQL servers

Pros:

• Copy-only backup doesn’t affect backup sequence

The main benefit of copy-only backup is that it doesn’t affect the backup sequence or regular restore processes. Usually, most SQL server backup methods will affect the database and restore processes. Using copy-only backups, IT teams can create backups that do not affect the other original backups and restore processes.

• Copy-only backup can be restored the same as any other backup

Fortunately, there are no additional skills or knowledge required to restore a copy-only backup, provided that you understand SQL. This means that the restoration processes for copy-only backup are the same as normal data restore processes. It’s a quick, easy, and smooth process to restore data from a copy-only backup.

• Copy-only backup methods are easy to perform

There are multiple ways to perform a copy-only backup, including using SQL Server Management Studio (SSMS), setting up transact-SQL, or implementing a PowerShell. With copy-only backups, IT admins are able to choose the best way to perform the backup depending on their situations.

Cons:

• Copy-only backup cannot support differential backups

If an IT team wants to use differential backups for their SQL server data, they will first need to conduct a full backup. Users cannot use copy-only backups in place of a full backup to support differential backups.

• Copy-only backup can be unnecessary

Since a copy-only backup saves the existing log archive point, it doesn’t affect the sequence of regular backups. However, Microsoft admits that copy-only backups aren’t always needed, saying, “Copy-only log backups are typically unnecessary. Instead, you can create a new routine log backup (using WITH NORECOVERY) and use that backup together with any previous log backups that are required for the restore sequence.”

• Copy-only backup does not allow backup history to be recorded centrally

After conducting a copy-only backup, the backup history will not be recorded centrally. When conducting other forms of SQL backup, such as full or differential backups, you can record the backup history centrally.

3 ways to perform a copy-only backup

1) Use SQL Server Management Studio (SSMS)

If your organization uses SQL Server Management Studio (SSMS), then you are able to conduct a copy-only backup with just a few clicks.

2) Try transact-SQL

Another method to conduct a SQL copy-only backup is to use transact-SQL (TSQL), which is an extension of the SQL language. This method uses a COPY_ONLY parameter, and it will back up the transaction log.

3) Set up a PowerShell

Finally, users can use a PowerShell to perform a copy-only backup using the -CopyOnly parameter.

Conclusion

Right now, many businesses store their critical business data in SQL server databases. Out of the many backup methods for SQL servers, copy-only backups are rarely used, but they can come in handy for certain situations. While a copy-only backup should never take the place of full and differential backups, it can be useful for experimenting with information without altering the original data.