About COPY_ONLY Backups in SQL server 2005.

Thanks to Microsoft for introducing the COPY_ONLY Backups in SQL server 2005.

As all we know that  is an special Backup and it won’t affects/Interrupt to the regular Backups/Restore sequences .

For example consider that your Backup strategy as mentioned below:

1.Full Backup ->Daily at 18PM EST

2.Differential Backup ->Thrice an day I,e 10AM, 13PM, 16PM

3.Log backup ->Every 10 Minute(s).

Now consider  that your customer asked that they need full back of the database from the production

Server at 14PM EST(just an example).

Consider now the time is  14PM EST .

So you have to give backups from last night full backup (+)All differential backup(+ )additional transaction log backups so this will be lengthy steps for the restoration on test server.

So if you take the full backup manually at 14PM est  then your next differential backups depends tightly on this full backup(I,e which we have taken full Backup at 14 PM EST) so your Backup strategy has been disturbed and for your restore recovery Plan as well (I,e you have very well plan of backup/restore sequences  incase of recovery of the database to point in time) & you need to restore the database according to same strategy, so thanks to Microsoft for  Introducing  new feature I,e called COPY_ONLY Backups.So this option will not disturb your planned Backup/restore sequence. 

 Now in your mind questions may arise what about the LSN sequences?

If yes, please see the below example.

Example:

I have taken full Backup of the database: AdventureWorksDW   so the LSN sequence as mentioned below-

See LSN’s

Now consider that trn log backup status-

(I have the Maintenance plan job which backups transactions every 10 Minute(s) )

so see below my next trn backup completed status:

The Lsn sequence as shown below-

See below the next trn backup run status as mentioned below-

The 08:10 AM LSN’s as mentioned below-

Now let take the Log backup manually using the COPY_ONLY Backup-

See the physical log backup file 

Now check the LSN’s-

So now My another log backup which runs at 08:20AM EST.

See the Log backup completed at 08:20AM EST and check the LSN’s-

So now you compare the COPY_ONLY backups and 08:20AM EST Log backups LSN’s sequence

Both are same (as shown below).

Now see how your restore works

1.I need to restore first full Backup .

2.Next I need to restore Trn Log backups I,e the  restore sequence is 08:01 TRN , 08:10 AM  and 08:20AM but note that I took log backup at 08:15AM EST by manually using COPY_ONLY Backup

Let see whether the restoration works or not(It has to work because I have already showed of LSN sequences ).

Step1:Restored the first  full Backup

So now you need to execute below queries:

Restore Log [AdventureWorksDW] FROM  DISK = N'C:\TRN Backups\AdventureWorksDW_backup_201111140801.trn' with Norecovery   --08:01AM

go

Restore Log [AdventureWorksDW] FROM  DISK = N'C:\TRN Backups\AdventureWorksDW_backup_201111140810.trn' with Norecovery   --08:10AM

go

Restore Log [AdventureWorksDW] FROM  DISK = N'C:\TRN Backups\AdventureWorksDW_backup_201111140820.trn' with recovery     --08:20AM

go

see below I have executed 08:01 & 08:10 AM EST log backups –those are Completed successfully and finally executed the 08:20AM.

output of 08:20 AM EST as mentioned below-

See below the database status àIt is running fine without any issues-

So this is how the  COPY_ONLY Backups works( Just FYI.. you can also test in your test boxes performing DML operations then do the test how copy_only backup works).

------------------------********--------------------------------------*******------------------------

Bottom Line:

1.Next question may arise that can  I do same restoration activity sequence Log backup taken using COPY_Backup T-sql  I,e restoring 08:15AM log backup instead of 08:20AM

Instead of 08:20AMWill Copy trn restore will work for restoration incase my 08:20 AM log backup deleted I,e I want restore the log backup which I used the COPY trn.

àI have tested and it is worked fine.

2. Also this may ask in the interview -àLike  without breaking the log shipping  how can you take the log backup at particular time?

I,e if you take log backup manually on primary database then the transaction log backup for log shipping will disturb  & Restore jobs will get fail –To resolve this you again manually need to restore the log backup which you taken log backup manually, hence Incase if you have fall in this situation COPY_ONLY Backup plays important role.

3.Note the full backup and log backup activity both are different it is not similar.

4.As per BOL –you need to keep below things in the mind

You can create a copy-only backup for any type of backup:

·         Copy-only data backups (all recovery models)

To create a copy-only data or differential backup, use the COPY_ONLY option in your BACKUP DATABASE statement. A data backup taken with the COPY_ONLY option cannot be used as a base backup and does not affect any existing differential backups.

·         Copy-only differential backups

These are identical to regular differential backups.

·         Copy-only log backups

To create a copy-only log backup, use the COPY_ONLY option in your BACKUP LOG statement; doing so causes backup to retain the current log archive point. Also, the transaction log is not truncated by a log backup taken using the COPY_ONLY option. Without COPY_ONLY, a BACKUP LOG WITH NORECOVERY would advance the log archive point.

Note:Copy-only backups can be created or restored only by using the BACKUP and RESTORE Transact-SQL statements. SQL Server Management Studio does not support these backups.

    COPY_ONLY backups are recorded in the backupset table in the is_copy_only column.