COPY_ONLY Backups with SQL Server 2005

By: Greg Robidoux | Read Comments (4) | Print Greg is the President of Edgewood Solutions and a co-founder of MSSQLTips.com. Related Tips: More

(9/01/2006 this tip was revised based on input from one of our subscribers)

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
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

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
• Look for other new SQL Server 2005 features that you can take advantage of

Related Tips: More | Become a paid author

Last Update: 8/31/2006

Share:

Share

Tweet

Free SQL Server Learning Introduction to SQL Azure Join Herve Roggero on Wednesday, June 13 @ 3:00 PM EST to learn more.

Comments and Feedback:

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

Read The Tip

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..

Thursday, December 16, 2010 - 3:38:10 PM - jwong	Read The Tip
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.

Thursday, December 16, 2010 - 3:46:40 PM - jwong	Read The Tip
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".

Monday, January 30, 2012 - 11:05:19 AM - Jeremy Kadlec	Read The Tip
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

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

"Amazing, Amazing, Amazing! SQL doctor is truly one of the most powerful tools I have seen." Time-strapped database professionals use SQL Monitor to look after their servers. Try it online. SQL Servers keeping you up at night? Contact the Edgewood SQL Server DBAs for a Health Check. Prices start at $995. Free Trial: Get Proactive Insight with Spotlight® for SQL Server Enterprise. Join the over million SQL Server Professionals who get their issues resolved daily. Free Learning - Introduction to SQL Azure Delivered by Herve Roggero on Wednesday, June 13 @ 3:00 PM EST