![\"Backup](\"https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/11\/Backup-Full-Diff-1.png\")
<\/p>\nIn order to understand how to operate the “COPY_ONLY” option and its nature, let’s consider the following example. Assume that a full database backup starts at 00:00 once a day and a differential backup starts every six hours. In the picture below you can see what database changes (marked as pages) are included into each of database backups shown on the picture.<\/p>\n
<\/p>\n
Assume that there is a need to make an extra full database backup to retrieve it on\u00a0another server. Suppose that this backup is done after the database change #4. If the full backup is made without T-SQL option “COPY_ONLY”, \u00a0the differential backup that was made after such full backup will no longer contain changes #1, #2, #3 and #4:<\/p>\n
![\"Without](\"https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/11\/Without-COPY_ONLY-1.png\")
<\/p>\n
The picture above shows\u00a0that the full backup that was made after the database change #4 has disrupted the\u00a0backup schedule set by the user (marked as green arrows). The following two differential backups that were made according to the schedule at 12:00 and 18:00 completely depended on the last extra full backup (marked as red arrows). If the last full database backup that was made after database change #4 is not saved and the database failure occurs, the best way out\u00a0is to restore the database to the database change#3 state (06:00). All changes that are highlighted in red will be lost.<\/p>\n
To make a full database backup and thus keep backups schedule safe and sound, it is necessary to use “COPY_ONLY” option. In this case, the database backup sequence\u00a0will not be broken and the full backup will be available.![\"With](\"https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/11\/With-COPY_ONLY-1.png\")
<\/p>\n
You can use the COPY_ONLY option with transaction log backups as well (note though that they are suitable for full or bulk-logged recovery models). If you need to make an extra transaction log backup and not to break the backup chain, then use the “WITH COPY_ONLY” option, otherwise if the backup file is lost the restore process will not be possible. It has to be admitted that the “WITH NO_TRUNCATE” option does not truncate the transaction log.<\/p>\n
![\"\"](\"https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/11\/SQLBackupAndFTP-Copy-Only-648x389.png\")
<\/p>\n","protected":false},"excerpt":{"rendered":"