SQL SERVER – SQL Daily Tips – Backup your database without affect the normal backup sequence.

Sometimes is necessary take a full backup or log backup for a database without affect the backup normal sequence for the database and log as well. This backup can be with different purpose as testing, development, etc. For this cases SQL Server provides an option for the backup statement, this is COPY_ONLY.

BACKUP DATABASE db_name TO disk='C:\db_name.bkp' WITH COPY_ONLY

This option COPY_ONLY, can be performed for any backup type. COPY_ONLY has certains behaviors depending of the backup type:

* A log backup taken using copy_only, does not truncate the transaction log.

* A data backup taken using copy_only cannot be used as base backup for differential or incremental backups.

* A differential backup has not effect with copy_only option.