COPY_ONLY Backups with SQL Server

There was different scenario in Copy_only backup.

Starting with SQL Server 2005, a new backup option has been added that allows you to take full and transaction log backups in between your regularly scheduled backups without affecting the LSNs and therefore the sequence of files that would need to be restored.  Since Differential backups backup all data pages since the last full backup, these types of backups do not affect the LSNs and there is no difference when using the COPY_ONLY feature.

This new option is called COPY_ONLY.  To use this option you would write your backup command as follows:

BACKUP LOG AdventureWorks TO DISK='C:\AdventureWorks_log1.TRN' WITH COPY_ONLY

Now when we go to do a restore using the most recent full backup file along with all of the transaction logs except this COPY_ONLY version, the restore still works as planned.

Full and Transaction Log Backups

Here is a simple way of testing this out with just full and transaction log backups.  You can run the following backup commands and then the restore commands

-- step 1 USE master
GO
BACKUP DATABASE AdventureWorks TO DISK='c:\AdventureWorks_full.BAK' WITH INIT
BACKUP LOG AdventureWorks TO DISK='c:\AdventureWorks_log1.TRN' WITH INIT
BACKUP LOG AdventureWorks TO DISK='c:\AdventureWorks_log2.TRN' WITH INIT

--step 2 RESTORE DATABASE AdventureWorks FROM DISK='C:\AdventureWorks_full.BAK' WITH NORECOVERY, REPLACE
--RESTORE LOG AdventureWorks FROM DISK='C:\AdventureWorks_log1.TRN' WITH NORECOVERY
RESTORE LOG AdventureWorks FROM DISK='C:\AdventureWorks_log2.TRN' WITH RECOVERY

At this point your restore will fail and the database will be in a LOADING mode. 

To test the same process out, but this time using the COPY_ONLY option, you can run the commands below.

-- step 1 USE master
GO
BACKUP DATABASE AdventureWorks TO DISK='c:\AdventureWorks_full.BAK' WITH INIT
BACKUP LOG AdventureWorks TO DISK='c:\AdventureWorks_log1.TRN' WITH INIT, COPY_ONLY
BACKUP LOG AdventureWorks TO DISK='c:\AdventureWorks_log2.TRN' WITH INIT

--step 2 RESTORE DATABASE AdventureWorks FROM DISK='C:\AdventureWorks_full.BAK' WITH NORECOVERY, REPLACE
--RESTORE LOG AdventureWorks FROM DISK='C:\AdventureWorks_log1.TRN' RESTORE LOG AdventureWorks FROM DISK='C:\AdventureWorks_log2.TRN' WITH RECOVERY

As you can see the restore process worked this time even though we had a backup that was not used for the restore process.

Full, Differential and Transaction Log Backups

Here is another way of testing full, differential and transaction log backups. 

--step 1  USE master
GO
BACKUP DATABASE AdventureWorks TO DISK='c:\AdventureWorks_full.BAK' WITH INIT
BACKUP LOG AdventureWorks TO DISK='c:\AdventureWorks_log1.TRN' WITH INIT
BACKUP DATABASE AdventureWorks TO DISK='c:\AdventureWorks_diff.BAK' WITH INIT, DIFFERENTIAL
BACKUP LOG AdventureWorks TO DISK='c:\AdventureWorks_log2.TRN' WITH INIT
--run special full backup BACKUP DATABASE AdventureWorks TO DISK='c:\AdventureWorks_full2.BAK' WITH INIT
--resume normal backup process BACKUP DATABASE AdventureWorks TO DISK='c:\AdventureWorks_diff2.BAK' WITH INIT, DIFFERENTIAL
BACKUP LOG AdventureWorks TO DISK='c:\AdventureWorks_log3.TRN' WITH INIT

--step 2  RESTORE DATABASE AdventureWorks FROM DISK='C:\AdventureWorks_full.BAK' WITH NORECOVERY, REPLACE  RESTORE DATABASE AdventureWorks FROM DISK='C:\AdventureWorks_diff2.BAK' WITH NORECOVERY
RESTORE LOG AdventureWorks FROM DISK='C:\AdventureWorks_log3.TRN' WITH RECOVERY

If we try to restore our original full backup, our latest full backup and any transaction log backup after the differential we get this error.

Msg 3136, Level 16, State 1, Line 1
This differential backup cannot be restored because the database has not been restored to the correct earlier state.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

If we rerun the process, but this time use the COPY_ONLY option for our special full backup, the restore process works as planned.

--step 1 USE master
GO
BACKUP DATABASE AdventureWorks TO DISK='c:\AdventureWorks_full.BAK' WITH INIT
BACKUP LOG AdventureWorks TO DISK='c:\AdventureWorks_log1.TRN' WITH INIT
BACKUP DATABASE AdventureWorks TO DISK='c:\AdventureWorks_diff.BAK' WITH INIT, DIFFERENTIAL
BACKUP LOG AdventureWorks TO DISK='c:\AdventureWorks_log2.TRN' WITH INIT
--run special full backup BACKUP DATABASE AdventureWorks TO DISK='c:\AdventureWorks_full2.BAK' WITH INIT, COPY_ONLY
--resume normal backup process BACKUP DATABASE AdventureWorks TO DISK='c:\AdventureWorks_diff2.BAK' WITH INIT, DIFFERENTIAL
BACKUP LOG AdventureWorks TO DISK='c:\AdventureWorks_log3.TRN' WITH INIT

--step 2  RESTORE DATABASE AdventureWorks FROM DISK='C:\AdventureWorks_full.BAK' WITH NORECOVERY, REPLACE  RESTORE DATABASE AdventureWorks FROM DISK='C:\AdventureWorks_diff2.BAK' WITH NORECOVERY
RESTORE LOG AdventureWorks FROM DISK='C:\AdventureWorks_log3.TRN' WITH RECOVERY