Copy-Only Backup in SQL Servers Explained

Copy-only backup SQL servers blog banner

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.

Strengthen your data security with NinjaOne’s flexible, automated backup management software.

Watch a demo or try it for free.

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.

Protect your organization from data loss. Leverage automation tools and set up a SQL Server backup schedule.

→ Read our guide.

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.

Next Steps

Protecting and securing important data is a crucial component in every organization. With NinjaOne Backup, you can protect your critical business data with flexible solutions designed for your modern workforce.

Learn more about NinjaOne Backup, check out a live tour, download our Backup Buyer’s Guide, or start your free trial of the NinjaOne platform.

You might also like

Ready to become an IT Ninja?

Learn how NinjaOne can help you simplify IT operations.

Watch Demo×
×

See NinjaOne in action!

By submitting this form, I accept NinjaOne's privacy policy.

Start your 14-day trial

No credit card required, full access to all features

NinjaOne Terms & Conditions

By clicking the “I Accept” button below, you indicate your acceptance of the following legal terms as well as our Terms of Use:

  • Ownership Rights: NinjaOne owns and will continue to own all right, title, and interest in and to the script (including the copyright). NinjaOne is giving you a limited license to use the script in accordance with these legal terms.
  • Use Limitation: You may only use the script for your legitimate personal or internal business purposes, and you may not share the script with another party.
  • Republication Prohibition: Under no circumstances are you permitted to re-publish the script in any script library belonging to or under the control of any other software provider.
  • Warranty Disclaimer: The script is provided “as is” and “as available”, without warranty of any kind. NinjaOne makes no promise or guarantee that the script will be free from defects or that it will meet your specific needs or expectations.
  • Assumption of Risk: Your use of the script is at your own risk. You acknowledge that there are certain inherent risks in using the script, and you understand and assume each of those risks.
  • Waiver and Release: You will not hold NinjaOne responsible for any adverse or unintended consequences resulting from your use of the script, and you waive any legal or equitable rights or remedies you may have against NinjaOne relating to your use of the script.
  • EULA: If you are a NinjaOne customer, your use of the script is subject to the End User License Agreement applicable to you (EULA).