Hello Folks,

You would have heard this with the arrival of SQL Server 2005.

Well I have made several keynotes on  SQL Server copy only backups so please see it carefully:

  • A  SQL Server copy only backups is a SQL Server backup that is independent of the sequence of conventional SQL Server backups.
  • It is useful to take a backup for a special purpose without affecting the overall backup and restore procedures for the database.
  • The Copy Only Backup is especially useful for taking “one-off” backups for development or testing -the advantage is it doesn’t affect transaction log backups or differential backups.
  • It also cannot serve as a base for differential or transaction log backups when restoring either.
  • There are basically two types of copy-only backups:
    • Copy-Only Full Backups (all recovery models) cannot serve as a differential base or differential backup and does not affect the differential base.
    • Copy-only log Backups (full recovery model and bulk-logged recovery model) preserves the existing log archive point and, therefore, does not affect the sequencing of regular log backups.
  • The transaction log is never truncated after a copy-only backup. Copy-only backups are recorded in the is_copy_only column of the backupset table.
  • Copy-Only Backups can also be performed in the following two ways:
    • With the help of SQL Server Management Studio.
    • With the help of T-SQL.

With the help of SQL Server Management Studio:

Step 1: In the SQL Server Management Studio GUI, perform this:


Step 2: Perform the operations as soon as the dialog box opens up:


With the help of T-SQL:

Well you can see from your result set:


Well this was all about Copy-Only Backups.

And also comments on this!!



Piyush Bajaj

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook