Copy Only Backup for SQL Server

By:   |   Comments (5)   |   Related: > Backup


Problem

I have implemented a backup plan for my SQL Server databases. I take a daily full backup supported with hourly differential backups. A restore plan is documented based on this backup plan along with the location and time of the backups mentioned in the plan. Often I am required to update the test or development server with a recent copy of the production database. For this purpose I take a full backup of the required database. The problem is that such ad hoc backups interrupt my planned recovery sequence in case of a needed recovery. Is there any way that my ad hoc backups will not interrupt the sequence of my backup plan?

Solution

Fortunately in SQL Server 2005 and onwards we have a backup option for such a situation. This backup option is known as Copy Only backups. This option is specifically for creating an ad hoc backup that will not disturb the planned restore sequence for that database.

Copy Only backups can be used for creating a full backup or a transaction log backup. This option is not implemented for differential backups. In practical scenarios you will rarely need to create a Copy Only log backup, however the copy only option may be frequently used with full backups.

Although the Copy Only option is available for SQL Server 2005 there is not a way to create them using SSMS in SQL 2005. If you have SSMS 2008 you can use the GUI or you have to use a T-SQL statement to create Copy Only backups.

First let's look at how this can be done using SSMS 2008 to create Copy Only backup

  • Go to the backup database window as you would for a normal backup
Copy only backup through SSMS 2008
  • Just below the "Backup type" menu, you will find a check box for "Copy Only Backup"
  • Click this check box
  • Fill out all other related information as you would for a normal backup and click OK

Note: The check box for "Copy Only Backup" will also be active for differential backups, but it will have no affect other than creating a normal differential backup.

Second, to create a Copy Only backup with T-SQL you can issue the following command:

-- Create full backup with Copy Only option
BACKUP DATABASE AdventureWorks 
TO DISK = 'D:\WithoutCopyOnly_AdventureWorks.bak'
WITH COPY_ONLY
GO

Proving The Concept

Before going further it will be good to summarize the concept that a full backup with the copy only option is independent of the sequence of your other normal backups. So after you create a backup with the copy only option, you would be able to work with the recovery plan based on your scheduled backups without this impacting your restore process.

We will use LSN (log sequence number) information to track the full backup that is the base for the differential backups. First we will note the LSN for the differential base of the AdventureWorks database.

--Script # 1: Note current differential base LSN

SELECT DB_NAME(database_id) AS [DB Name],
differential_base_lsn AS 'Note differential base LSN'
FROM sys.master_files
WHERE database_id = DB_ID('AdventureWorks')
AND type_desc = 'ROWS'
GO

The differential_base_lsn affects the sequence in which combination of backups are to be restored in a recovery. You may also get detailed information about the LSN of any database from table msdb..backupset.

adventure works

Now we have to confirm that a full backup without the Copy Only option will update the differential_base_lsn. For this purpose we will issue a full backup command without the Copy Only option and we will note the change in the LSN to prove that the updated differential base LSN is the LSN of our last full backup.

--Script # 2: Create full backup and compare LSN information

-- Create full backup 
-- Run script after changing Backup path
BACKUP DATABASE AdventureWorks 
TO DISK = 'D:\WithoutCopyOnly_AdventureWorks.bak'
GO

-- Get differential_base_lsn after full backup
SELECT DB_NAME(database_id) AS [DB Name],
differential_base_lsn AS 'Updated differential base LSN'
FROM sys.master_files
WHERE database_id = DB_ID('AdventureWorks')
AND type_desc = 'ROWS'
GO

-- Get LSN of recent full backup for match purpose
SELECT database_name, backup_start_date, is_copy_only,
first_lsn as 'LSN of full bakup'
FROM msdb..backupset 
WHERE database_name = 'AdventureWorks'
ORDER BY backup_start_date DESC
GO

In the following result set, we can verify that LSN has been changed for the differential backup restore base and matches our full backup LSN.  Both marked LSNs below are the same which confirms that the last backup is our differential base.

updated differential base

Now we will create a full backup with the Copy Only option and it will be proved that the full backup with the Copy Only option will not affect the differential base LSN of our database. In other words the full backup with the Copy Only option will not affect the base full backup for the differential backups.

--Script # 3: Create full backup with copy only option and compare LSN information

-- Create full backup with copy only option
-- Run script after changing Backup path
BACKUP DATABASE AdventureWorks 
TO DISK = 'D:\CopyOnly_AdventureWorks.bak'
WITH COPY_ONLY
GO

-- Get differential_base_lsn after full backup with copy only option
SELECT DB_NAME(database_id) AS [DB Name],
differential_base_lsn AS 'Un changed differential base LSN'
FROM sys.master_files
WHERE database_id = DB_ID('AdventureWorks')
AND type_desc = 'ROWS'
GO

-- Get LSN of recent full backup with copy only option for match purpose
SELECT database_name, backup_start_date, is_copy_only,
first_lsn as 'LSN of last full bakup'
FROM msdb..backupset 
WHERE database_name = 'AdventureWorks'
ORDER BY backup_start_date DESC
GO

In the following image we can verify that after a full backup with the Copy Only option the differential base LSN is unchanged and it matches the LSN of the previous full backup (both are marked red). Also note that the last full backup with the Copy Only option is also there (marked green).

differntial base

Following considerations will be helpful while using backups with the Copy Only option.

  • Copy Only option will also work for compatibility level 80 databases in a SQL Server 2005 instance
  • Transaction log backups with the Copy Only option preserves the existing log archive point, hence it will not truncate the transaction logs of that database.
  • There is no enhanced consideration required while restoring a backup created with the Copy Only option.
  • A full backup with Copy Only option can not be used as a base for restoring differential backups
  • A log backup with Copy Only option may be created for databases with recovery model full or bulk logged only.
  • A full backup with Copy Only option may be created for databases with any recovery model.
Next Steps

While taking any backup out of your backup plan consider using the Copy Only option to avoid any confusion and disturbance of your recovery plan. Keeping in mind that the Copy Only backups are independent of normal backups.

  • So the next time you need to refresh your test or development environments consider using the Copy Only option to you do not interrupt your recovery process
  • For information about database recovery models click here or here
  • Here is related tip about COPY ONLY backups


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Atif Shehzad Atif Shehzad is a passionate SQL Server DBA, technical reviewer and article author.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Wednesday, May 17, 2023 - 8:45:13 AM - ssk Back To Top (91200)
VERY GOOD EXPLENATION ...

Thursday, August 20, 2015 - 4:05:47 AM - Atif Shehzad Back To Top (38493)

@Pardeep. The points that you refered from BOL may be confuding but not contradictory. You can restore log backups in sequence while ignoring any full backup among these. However full backup can be used to skip all previous log backups while restoring these. For example if you want to restore last log backup of 2 PM then you can restore full backup of 1 PM before it and skip to restore all previous backups. If you do not use full backup of 1 PM then you have to include all previous log backups in restore chain.

 


Wednesday, August 19, 2015 - 7:49:17 AM - Pradeep Back To Top (38474)

Hi,

As per my Understanding SQL Server Maintain Log Backup Chain depends upon full backup/last log backup. When ever we took Full backup a new LSN chain will start and next log backups will depend on this full backup. For Ex:

Full Backup---6 AM

TLOG Backup---9 AM

TLOG Backup---11 AM

Again Full Backup--- 1 PM

TLOG Backup---2 PM

 

While Restoring I tested below Scenario

Restored---Full Backup---6 AM

Restored--- TLog Backup---9 AM

Restored--- TLog Backup---11 AM

Successfully Restored Tlog Backup---2 PM(Which is not considering 1 PM Full Backup)

As per my Understanding When ever u take a Full backup a new LSN chain gets create and the next TLOG Backup will depends on that recent Full backup.

But in the above Example am not able to understand why SQL Server is allowing us apply a 2 PM Log backup directly on 11 AM TLog Backup(Why its not considering 1 PM Full backup).

Below are the contradicting statements found in Microsoft Official Technical Article.

"If a log backup becomes missing or damaged, start a new log chain by creating a full or differential database backup and then backing up the transaction log to start a new log chain. "

On the same site They have mentioned below thing also.

"A continuous sequence of log backups is called a log chain. A log chain starts with a full backup of the database. Usually, a new log chain is only started when the database is backed up for the first time, or after the recovery model is switched from simple recovery to full or bulk-logged recovery. "

I Followed below Link

https://technet.microsoft.com/en-us/library/ms190440(v=sql.90).aspx

It would be great is some one explains me the log chain strategy.


Monday, August 12, 2013 - 5:52:16 AM - Karthik Back To Top (26240)

Excellent Info!! Thanks Atif!!


Tuesday, April 23, 2013 - 6:44:40 AM - WaqarTanoli Back To Top (23505)

Brilliant work done by Author

 















get free sql tips
agree to terms