Copy Only Backups for Adhoc Backups
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
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:
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.
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.
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
You can similarly prove this for transactional log backups.
Points to Note
Please note following points when considering copy only
- 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
- 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