By: Atif Shehzad | 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
- 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.
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.
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).
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
About the author
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
