After posting last week about a BACKUP feature that I don’t like (WITH NO_LOG – see here), I thought I’d do a quick post this week about a new backup feature that was introduced in SQL Server 2005 that I DO like – the COPY_ONLY option to BACKUP DATABASE and BACKUP LOG.

Here’s a situation I’ve seen several times that really messes people up. A savvy DBA of a busy web-fronted sales business has a rigorous backup schedule setup – daily full backups at midnight and differential backups every 4 hours. Everything’s working perfectly. One day a disaster strikes at 7pm and the storage for the database is destroyed. The DBA starts restoring the backups using WITH NORECOVERY, gets to the noon differential backup and gets the following message:

FROM DISK = N'C:\sqlskills\production-diff12pm.bck'
Msg 3136, Level 16, State 1, Line 1
This differential backup cannot be restored because the database has not been restored to the correct earlier state.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Uh-oh. That’s not good. That says that the 12pm differential backup does not have the same differential base as the 4am and 8am ones. How can that have happened? The DBA takes a look in the backup history tables in msdb:

    [database_name] = N'production';
name                           backup_start_date       type first_lsn            database_backup_lsn
------------------------------ ----------------------- ---- -------------------- --------------------
production Full 10/14/07       2007-10-14 00:00:00.000 D    88000000025300001    0
production Diff 4am 10/14/07   2007-10-14 04:00:00.000 I    118000000003000160   88000000025300001
production Diff 8am 10/14/07   2007-10-14 08:00:00.000 I    144000000070500160   88000000025300001
NULL                           2007-10-14 10:29:50.000 D    161000000056100147   88000000025300001
production Diff 12pm 10/14/07  2007-10-14 12:00:00.000 I    161000000062800034   161000000056100147
production Diff 4pm 10/14/07   2007-10-14 16:00:00.000 I    173000000054100144   161000000056100147

Aha! Look at the backup_start_date on line 6 in the output – someone took a full database backup of the database at 10.29am. The DBA checks and finds that one of the developers wanted a copy of the production database to play with so took a database backup. He restored the backup and then deleted both it and the database. Looking at the database_backup_lsn fields of lines 4-5, we can see that all the backups up till the accidental backup have the differential base (the database_backup_lsn) equal to the first_lsn of the full backup from midnight. The two backups after (lines 7-8) that have the differential base equal to the first_lsn of the accidental full backup.

Oops! That means that the production database cannot be rolled forward any further than the last differential backup before the accidental full backup was taken – losing more than 8 hours of data completely. Even though all the subsequent backups are intact, the initial full backup for them no longer exists so they’re useless!

So how can a developer get a copy of the database without messing up a potential recovery from a disaster? Using the new COPY_ONLY option. Taking a full backup with this option does not make the new backup a differential base – it does not clear any of the differential bitmaps and basically doesn’t interfere with the regularly scheduled backups. Apart from that, it’s a regular full backup of the database. One thing to bear in mind is that it’s a one-off – you can’t use one of these backups as a differential base, so you can’t take COPY_ONLY differential backups. If you specify COPY_ONLY with DIFFERENTIAL, the option is ignored.

One other cool thing is that you can specify this option for a BACKUP LOG command too. This behaves the same way – it takes a log backup, but does not change the transaction log at all (i.e. it doesn’t make any portion of the log inactive and permit log clearing), and does not form part of the log backup chain. This is useful for doing online file restores without having the necessary backup of the tail of the log affect the log backup chain. More on that in a later post…