Copy Only Backups for Adhoc Backups

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.



Array

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |