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

No comments:

Post a Comment

Amazon Contextual Product Ads

Popular Posts