Introduction
In most organizations backup plans are implemented using full differential and transactional log backups. The normal scenario would be take a full backup on Sunday (off peak hours), differential backup daily at mid-night and transactional log backups on hourly basis.
What if there is a requirement to refresh on the test/development environment with production data? In that case, you need to restore the last full backup, the last differential backup and subsequent transactional log backups. As you can imagine this might be a time consuming task, also, if you have scheduled a transactional log backup at intervals of longer than an hour then there is a chance that you would not get the latest data backup.
There is the option of taking a full backup and then restoring in the test/development environment. However, as differential backup is tightly attached to last full backup, taking another full backup will disturb your recovery planning procedure.
To handle this type of scenario, SQL Server has a feature called copy only backups which was introduced in SQL Server 2005. Copy only backups will not disturb the planned restore sequence for that database.
This feature is NOT restricted to any edition and so all editions can utilize this feature.
Create a Copy Backup
In the normal backup database dialog, you have an option (as shown in the following image) to create a copy only backup. If you select differential backup and select the Copy Only backup option, your backup still contains nothing but a differential backup. Simply, there is no use to enable Copy Only backup if you select differential backup.
The above option is not available in the SQL Server 2005 backup dialog, but you can still use this feature by running T-SQL code to create a copy only backup. The following is the T-SQL code for this:
BACKUP DATABASE COB
TO DISK = ‘C:BackupsCOB.bak’
WITH COPY_ONLY;
In the maintenance plans, you won’t use this option in the Back Up Database Task. If you want to create a Copy Only backup in maintenance plans, you need to use the Execute T-SQL Statement Task with above T-SQL.
Verification
Now let us verify this. Of course, you can take few backups and restoring them by your self you can verify this. Let’s us see this by jumping into the internal data of SQL Server.
I have a database (in this case it is COB) and I have taken few full backups – full backups with Copy Only and differential backups.
select NAME,
TYPE,
differential_base_lsn,
is_copy_only from msdb..backupset
WHERE database_name LIKE ‘COB%’
The above query will give following output:
You can see that at row 7 there is a differential backup with base lsn 20000000038200081. After that differential backup, at row 8 there is a full backup with the Copy Only option (is_copy_only =1). At row 9 there is another differential backup and you can see that it has the same base lsn which is 20000000038200081. This means that copy only backup has not changed the base lsn.
You can similarly prove this for transactional log backups.
Points to Note
Please note following points when considering copy only backups.
- The Copy Only option will also work for compatibility level 80 databases in a SQL Server 2005/2008 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. Hence log-shipping operations will not fail.
- A full backup with the Copy Only option cannot not be used as a base for restoring differential backups which will fail.
- A log backup with Copy Only option may be created for databases with recovery model set as full or bulk logged only while a full backup with the Copy Only option may be created for databases with any recovery model.