Copy-Only backups

Copy-only backup
You might have heard of this term, but you don’t know what it actually presumes.
 Read this next article to understand what a copy-only backup is and how it fits in the big picture of SQL Server.

A copy-only backup is a backup of SQL Server which is separate from the sequence of standard SQL Server backups.

What is a copy-only backup?

Whenever you make a backup of a database, there are some changes that influence how the following backups are going to be restored (differential backups and transaction log backups both influence how the restore operation will be).

But sometimes you might need a backup that will not affect the following processes of backup or restore for that database. This is where copy-only backups come into place.

Types of copy-only backups

There are different types of copy-only backups, which are used in different scenarios.

    • Copy-only FULL backups – are used for all recovery models
      • Such a backup cannot be used as a base for differential backups and also it does not affect the differential base.
      • Doing a restore of a copy-only full backup is the same as doing a restore on any other database that has been fully backed up.
    • Copy-only log backups – are used only for full recovery and bulk-logged recovery models
      • This type of backup will preserve the existing point of the log archive and thus it will have no impact on the sequence of regular log backups.
      • These copy-only log backups are rarely useful, but you can create a routine of backuping the ┬álog by using the WITH NORECOVERY option and use this backup along with any previous log backups that might have been required for a restore.
      • But, a log backup of this type can at some point be used for making an online restore.
      • In the case of a copy-only log backup the transaction log is never truncated.

How to create a Copy-Only Backup

There are multiple options of creating such a backup, but we will present one using T-SQL and one through an SSMS wizard.

Using T-SQL

With T-SQL is quite simple, as you just need to specify the COPY_ONLY option in the WITH clause.

BACKUP DATABASE myDatabase 
TO DISK = 'Z:\myDatabase.bak'
WITH COPY_ONLY
Using SSMS

Even through SSMS the process is quite simple as you just have to tick a checkbox during the standard backup process.

Copy-only backup

Below you can see where you have to check the tickbox and the rest of the process is standard.

Copy-only backup

About Radu Gheorghiu

Passionate SQL Developer on the journey of trying to become an expert in all things Data (storage, manipulation, gathering etc.) Information is power, and information can only be obtained from data. Thus, in order to harness the power of information, you must be a master of Data.

5 thoughts on “Copy-Only backups

  1. Valuable information and facts. Blessed myself I stumbled upon your internet-site by mistake, for astonished precisely why this kind of twist of fate could not occurred ahead of time! I actually saved this.

    1. Thank you for these great words of encouragement! Will definetly keep me going on writing articles on these topics.

      Please re-visit to get access to more knowledge in hopefully an easy to understand format.

Leave a Reply

Your email address will not be published. Required fields are marked *