Copy-Only backups

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 a 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 backing up 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 for 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 though SSMS the process is quite simple as you just have to tick a checkbox during the standard backup process.

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