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:
BACKUP DATABASE [TEST] TO DISK = N'C:\Backup\TEST.bak' WITH COPY_ONLY,
NOFORMAT, NOINIT, NAME = N'TEST-Full Database Backup', SKIP, NOREWIND,
NOUNLOAD, STATS = 10
Well you can see from your result set:
Well this was all about Copy-Only Backups.
And also comments on this!!