Copy only backup option

copy_only option for backup

Its a useful feature but very few are aware of this option .

As we all are aware of backups and its different types

We are aware of backup strategies which include backup chain of full,differential and log backups

But some times we need to go out of box and need a separate backup.

Lets say we need a copy of live db to test servers but if we take a full backup it will break the sequence

of backup. Lets see the example

FULL-1

      Diff1

      Diff2

FULL-2

      Diff3

 Full-3 (we need this full backup to restore db on test server)

           If not specify as copy_only this will break current sequence and will work as differential base for next differential backup

      Diff4

                 

But if we specify as copy_only for FULL-3 full backup , it will not update bitmap for differential

and will not work as differential base. Diff4 will have all changed data  after Full-2 instead of Full-3

Here we have database backup_test_db

we take first its full backup

SELECT *

FROM   sys.database_files

Here differential_base_guid,differential_base_lsn,differential_base_time values are null

 

Now we will take first full backup

 BACKUP DATABASE [backup_test_db] TO

DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\backup_test_db_1.bak'

WITH NOFORMAT, NOINIT, NAME = N'backup_test_db-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

Here differential_base_guid,differential_base_lsn,differential_base_time values are updated

SELECT FILE_ID,

       name,

       differential_base_guid,

       differential_base_lsn,

       differential_base_time

FROM   sys.database_files

Now we will add another table

 

CREATE TABLE test

    (

         id INT

    )

 

Now again we will take full backup

Here differential_base_guid,differential_base_lsn,differential_base_time will be changed as database is changed

BACKUP DATABASE [backup_test_db] TO

DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\backup_test_db_2.bak'

WITH NOFORMAT, NOINIT, NAME = N'backup_test_db-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

SELECT FILE_ID,

       name,

       differential_base_guid,

       differential_base_lsn,

       differential_base_time

FROM   sys.database_files

Now again we will add new table

CREATE TABLE test1

    (

         id INT

    )

 

Now we want to take full backup again but its for purpose of restoring on test server 

and don't want to break backup sequence with new backup .

Here we will specify copy_only option , so value of   differential_base_guid, differential_base_lsn, differential_base_timewill not be changed.

BACKUP DATABASE [backup_test_db] TO

DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\backup_test_db_3.bak'

WITH COPY_ONLY , NOFORMAT, NOINIT, NAME = N'backup_test_db-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

SELECT FILE_ID,

       name,

       differential_base_guid,

       differential_base_lsn,

       differential_base_time

FROM   sys.database_files

So here if we take differential backup again it will behave like copy_only backup does not exist and backup

 all data since second backup