Copy-Only Backup in SQL Servers Explained

Although Structured Query Language (SQL) has been around for decades, it’s clearly still relevant for businesses and isn’t going anywhere. Apart from working smoothly for many IT teams around the globe to this day, it continues to evolve and remains the foundation of modern relational databases used both on-premises and in the cloud. As the saying goes, “If it isn’t broken, don’t fix it.”

Nonetheless, to ensure that the data stored in SQL servers remains protected, IT professionals back it up 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 need.

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 won’t 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 can’t be used as a base to conduct differential backups.

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 can’t use a copy-only backup as a base for a differential backup. Differential backups for SQL servers require full backups, and copy-only backups can’t 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. According to Microsoft, copy-only log backups are primarily used for troubleshooting or special restore scenarios and aren’t part of routine log backup strategies. Still, while a copy-only log backup isn’t used often, 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 affect these processes. With this in mind, using copy-only backups, IT teams can create backups that don’t affect the other original backups.

• Copy-only backup can be restored just like 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.

• 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 (TSQL), or
  • implementing

With copy-only backups, IT admins can choose the best way to perform the backup depending on their situation.

Cons:

• Copy-only backup can’t support differential backups

If an IT team wants to use differential backups for their SQL server data, they’ll first need to conduct a full backup. Users can’t 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. As mentioned earlier, even Microsoft admits, “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.”

3 ways to perform a copy-only backup

1) Use SQL Server Management Studio (SSMS)

If your organization uses SSMS, then you can conduct a copy-only backup with just a few clicks.

2) Try transact-SQL

Another method is to use TSQL, which is an extension of the SQL language. This method uses a COPY_ONLY parameter, and it’ll back up the transaction log.

3) Use PowerShell

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

Conclusion

Presently, 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 testing restores, audits, or external processes without disrupting existing backup chains.

Watch the video guide here: “Copy-Only Backup in SQL Servers Explained.”

If you’re looking to improve your business’s overall data backup strategy, NinjaOne Backup offers built-in backup and recovery for your devices and servers as well as flexible storage options for on-prem, cloud, and hybrid IT environments—all from a unified console. With NinjaOne Backup, you can also benefit from chainless image backup, cross-platform support (Windows and macOS), and remote restore.

Start your 14-day free trial of NinjaOne’s backup solution or watch a free demo.