Copy only backup mode

Copy backup only

 

Updated on:October 31, 2008

"Copy backup only"Is an SQL Server backup that is independent from the conventional SQL Server backup sequence. Generally, backup changes the database and affects the restoration mode of the subsequent backup.However, sometimes it is useful to back up a database for a special purpose without affecting the full backup and restoration processes of the database. Therefore, SQL Server 2005 introduces replication-only backup. The following table lists the types of copies.:

• Only copy full backup (all recovery modes)
  Copying a full backup only cannot be used as a differential or differential backup and does not affect the differential backup.
• Only copy log backup (only in full recovery mode and large-capacity log recovery mode)
  Only copy the log backup to retain the current log archiving point. Therefore, the sequence of regular log backup is not affected. Generally, you do not have to copy only log backups. Instead, you can create another routine, that is, the current log backup (with norecovery), and then use the backup together with all the other previous log backups required for the Restoration sequence. However, you can create a copy-only log backup for online restoration. For more information about this example, see Example: Online restoration of read/write files (full recovery mode ).

Transaction logs are never truncated after only the backup is copied. Copy only the backup records in the backupset tableIs_copy_onlyColumn.

Create copy-only backup (TRANSACT-SQL)

Note:
Since SQL Server 2008, SQL Server Management Studio supports only copying backups.

 

• How to back up a database (SQL Server Management studio)
• How to back up transaction logs (SQL Server Management studio)
• The syntax for copying only the complete backup is:
  Backup DatabaseDatabase NameTo <Backup Device>... With copy_only...

  Note:
  When the differential option is used, copy_only does not work.

   

• The basic Transact-SQL syntax for copying only log backups is:
  Backup logDatabase NameTo<Backup Device>... With copy_only...

Restore only copies backups

Restoring only copies the complete backup is the same as restoring any full backup.

Important:
To restore a database backup to another server instance, see replicate a database through backup and restoration and manage metadata when the database is available on another server instance.

 

Restore full database backup

• How to restore Database Backup (TRANSACT-SQL)
• How to restore Database Backup (SQL Server Management studio)

Restore files and file groups

• How to Restore files and file groups (TRANSACT-SQL)
• How to Restore files and file groups (SQL Server Management studio)

See

Concept

Backup in full recovery mode
Backup in simple recovery mode
Recovery mode Overview
Other resources

Backup (TRANSACT-SQL)
Restore (TRANSACT-SQL)