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 # 2: 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.
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
@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.
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. "