Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

COPY_ONLY Backups with SQL Server


By:   |   Read Comments (4)   |   Related Tips: More > Backup

Problem

One issue with creating backups is that the LSNs (log sequence numbers) get stored in the backup files and these LSNs are referenced when you do a restore of your database. The reason the LSNs are stored with the backup file is to verify that the backup files are being restored in the proper sequence when you go to do your restores.  So if you try to restore your database files out of sequence you will get an error message from SQL Server such as the following:

The problem with having the LSNs stored with the backup file is that you can not do off schedule backups without affecting the LSNs of your regularly scheduled backups. 

Here is a scenario:

You normally run a full backup at midnight, transaction logs throughout the day and differentials every 3 hours.  Then you need to run a special full backup to load your test server.  If you then need to restore your production server you will need to use this most recent full backup, any differentials done after that and then any transaction log backups that were done after the differential.  Another approach to restore your database would be to restore your original backup and then just use only the transaction log backups, since the full backup does not affect the LSNs in the transaction log backup this will also work, but you loose the advantage of doing differential backups.

Solution

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 INITCOPY_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 INITDIFFERENTIAL
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 INITDIFFERENTIAL
BACKUP LOG AdventureWorks TO DISK='c:\AdventureWorks_log3.TRN' WITH INIT 

--step 2 
RESTORE DATABASE AdventureWorks FROM DISK='C:\AdventureWorks_full.BAK' WITH NORECOVERYREPLACE 
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 INITDIFFERENTIAL
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 INITCOPY_ONLY 
--resume normal backup process
BACKUP DATABASE AdventureWorks TO DISK='c:\AdventureWorks_diff2.BAK' WITH INITDIFFERENTIAL
BACKUP LOG AdventureWorks TO DISK='c:\AdventureWorks_log3.TRN' WITH INIT 

--step 2 
RESTORE DATABASE AdventureWorks FROM DISK='C:\AdventureWorks_full.BAK' WITH NORECOVERYREPLACE 
RESTORE DATABASE AdventureWorks FROM DISK='C:\AdventureWorks_diff2.BAK' WITH NORECOVERY 
RESTORE LOG AdventureWorks FROM DISK='C:\AdventureWorks_log3.TRN' WITH RECOVERY
Next Steps
  • Add this option to your list of tools
  • Make sure to use this option whenever you need to do an out of sequence backup for whatever reason


Last Update:






About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Monday, January 30, 2012 - 11:05:19 AM - Jeremy Kadlec Back To Top

Everyone,

If you work through the examples and get stuck with databases in a restoring state, issue the following command to bring the database back online:

 

USE Master;
GO

RESTORE DATABASE AdventureWorks WITH RECOVERY
GO

 

Thank you,
Jeremy Kadlec


Thursday, December 16, 2010 - 3:46:40 PM - jwong Back To Top

msdb.dbo.backupset does have a is_copy_only (bit, null). Now if you auto-generate restore script from file, you might want to be careful and denote the copy_only file to be recognized as "copy".


Thursday, December 16, 2010 - 3:38:10 PM - jwong Back To Top

Great article.

Would coy_only shows in msdb.dbo.backupset with copy_only attributes?

Otherwise, you will need a good naming system do differentiate what is copy_only and not copy_only in order to find the right files or copies after a disaster.


Saturday, June 14, 2008 - 5:58:24 AM - Balesh Back To Top

Hi

Good Script!!

I have one small query regarding Database backup with COPY_ONLY option.

Let's take an example : My backup stratagy is FULL & DIFF backup to tap and Tlog backup to disk.

I want to sync my Log shipping Secondary DB without using tape backup. Can i restore the Full backup taken with COPY_ONLY option and then apply Tlog backups? If yes will i loose transactions caused by diff backup between Full & Tlog backup ?

Regards,

Balesh..


Learn more about SQL Server tools