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:
RESTORE DATABASE [production] FROM DISK = N'C:\sqlskills\production-diff12pm.bck' WITH NORECOVERY; GO
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:
SELECT
[name],
[backup_start_date],
[type],
[first_lsn],
[database_backup_lsn]
FROM
[msdb].[dbo].[backupset]
WHERE
[database_name] = N'production';
GO
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…
26 thoughts on “BACKUP WITH COPY_ONLY – how to avoid breaking the backup chain”
The stealth backup is yet another reason to keep well-meaning developers, managers, and network admins out of production admin roles. :) If keeping people out isn’t possible, a job that checks for rogue backups could also be used.
However, in this case, if the DBA still has his transaction log backups, things should be OK. The developer’s full backup will ruin all subsequent differential backups, but won’t invalidate the transaction log backups. As long as the DBA has an uninterrupted chain of transaction log backups from any full backup, the data will be restorable up to the last available transaction log. In this case the developer would only owe the DBA a lunch, and not a job reference.
Mark
Hey Mark – absolutely correct. My mistake comes from changing my scenario to only have differential backups and forgetting to go back to my description of the backup strategy to remove the log backups. It’s fixed now :-) Thanks!
Found something that loos like a bug. If you back a database up with Copy_only, then try to use the management studio restore database command, the dialog lists no entries in the backup set list box.
Restoring via script works. This is management studio with SP2 installed.
Hi there,
I want to preface my question by saying that I’m very new to SQL Server and am first and foremost an Oracle DBA.
In this scenario you’ve provided, I’m not entirely sure why this is a problem. I will give you that it will increase the time needed to restore the database because you’d need to wait for the restore to fail, investigate why it failed and then fix the problem, but here’s my question: If you got this error, wouldn’t you simply restore the developer’s full backup from 10:29am, then the 12pm differential based off that full, the 4pm differential and then any log backups up to 7pm? Is this not a possibility in SQL Server?
And in regards to Mark’s comment, why will the developer’s full backup "ruin all subsequent differential backups"? Aren’t they simply based off of a different baseline?
Hopefully someone is still monitoring this thread since it’s almost 2 years old at this point, but I’d appreciate the clarification.
Thanks for the great post!
Hi Vanessa – yes, what you say is of course possible – but only if the developer still has their full backup. And, in a disaster recovery situation, what’s the likelihood that the developer is available at short notice to provide access to the backup? Possibly low. Thanks
Ah yes, I forgot that you had specified the developer had removed the full backup. Thanks for pointing that out!
Don’t feel too bad Vanessa, I was scratching my head thinking exactly the same thing. Unfortunate but not disastrous. Restore the dev backup followed by the diffs….. then I saw your comment and realised the dev had done a runner with his backup!
But even then you should still have your normal full backups + log backups so still no data loss, just more time consuming
Reply to 8 year old blog :D
Ah – but in this case they were only doing full and diff backups – so no log backups to fall back on.
Hi Paul Sir, I am so new to this SQL server management studio. I have two questions:
1- When to use BACKUP WITH COPY_ONLY option.
2- What are the essential things to learn in MS SQL server management studio other than creating database, relationships, and backing up a database.
Thanks in advance.
#1 I talk about that in the post.
#2 Isn’t a serious question that can be answered in a blog post comment. See http://technet.microsoft.com/en-us/library/ms174173.aspx
I have an odd situation I was hoping to clarify regarding this error.
I recently was handed a task on getting a VLDB (7tb) into an A.G.
The database also had some other not so ideal business requirements that resulted in primary filegroup only backups being taken along with partial differentials and logs.
I had taken a full backup (which took two days)…restored it to another server and then took a subsequent normal diff.
Upon trying to apply the diff I was met with the following error:
This differential backup cannot be restored because the database has not been restored to the correct earlier state.
Checking both files (full and diff) restoring the header only I found the following
FULL first lsn / checkpointLSN: 6700275000000660100118
DIFF DATABASE BackupLSN: 6700275000000660100118
Shouldn’t the lsn chain still be in sync?
End story is I can’t restore the diff so I need to start over and run the full again, I am thinking that the partial primary filegroup backup messed this up somehow but I can’t figure it out. Any input is appreciated.
Yup – any full data backup after your full database backup means the diff is based on a combination of the two.
Paul,
I am working my way through your excellent volumes of useful info. I took on the DBA role as an extension of being a developer and data analyst and have been developing an extensive backup/restore/checkdb process. Your articles have explained and clarified so much. This article on the backup chain was clear and exact. Thank you
Cool – glad to have helped!
Hi Paul –
Great post, thank you.
We had a vendor ran VSS bkup against of the databases that caused broken LSN. We stopped the bkup, and ran the maintenance plan again to correct the it. However what we are encountering is that after BKUPs ran, we are only seeing FULL and LOG backup set while attempting restore from SSMS than seeing all BKUPs. DIFF BKUP is not showing!!! Below is the log chains, do you know what is causing the issue? and how this can be fixed? Thank you.
Full BKUP taken NULL 2016-04-11 15:07:28.000 D 1268183000001656400001 1266849000003630400106
Logbkup NULL 2016-04-11 15:23:00.000 L 1268183000001656400001 1268184000004715800088
DIFF NULL 2016-04-11 21:00:32.000 I 1268228000005920700001 1268184000004715800088
Don’t know what SSMS is doing behind the scenes I’m afraid – but it could be that another full backup was taken after the first one, so the diff is based off the second one, not the 15:07 one.
Paul – Per you, both database_backup_LSN are same of the DIFF bkup are same as the first_lsn of the full bkup. The only different is that the full and the first diff bkups were taken yesterday at 7 AM manually to reset the chain and the rest of logs/last night DIFF bkup were taken through maintenance plan. Still only the full/diff bkup taken at 7/all other logs are shown in the restore dialog but not the diff from last night! Any clues?
database_name backup_start_date type first_lsn last_lsn checkpoint_lsn database_backup_lsn
DB_NAME 4/13/16 9:00 PM I 1272859000003560000000.00 1272860000001330000000.00 1272859000003560000000.00 1272838000031540000000.00
DB_NAME 4/13/16 7:48 AM I 1272838000041660000000.00 1272838000041840000000.00 1272838000041660000000.00 1272838000031540000000.00
DB_NAME 4/12/16 9:00 PM I 1268571000005860000000.00 1268572000004960000000.00 1268571000005860000000.00 1268184000004710000000.00
DB_NAME 4/11/16 9:00 PM I 1268228000005920000000.00 1268234000003640000000.00 1268234000002580000000.00 1268184000004710000000.00
DB_NAME 4/13/16 7:37 AM D 1272838000031540000000.00 1272838000040760000000.00 1272838000031540000000.00 1268184000004710000000.00
You need to check that the differentialbaseguid for the diff backup matches the backupsetguid from the full backup. If they don’t, that diff is not based of that full.
Correction: Sorry I meant both database_backup_LSN of the DIFF bkup are the same…
Hi Paul,
One thing I noticed if you perform a ‘copy only’ to a full recovery model db that has never had a trans log backup.. you cannot backup the tail (the trans log) .
If a DB is in full recovery mode and there has never been a transaction log backup ever. But Nightly full backups are occurring (say it’s been running fine for weeks in full recovery)
Scenario we want to recover to early in the morning by backing up tail of trans log (all the trans log for past weeks gets backed up there is assumption)
Before backing up the tail, we want to take a quick snapshot(backup) with “copy_only”
Now perform a backup log …. with no recovery
It fails stating a full backup does not exist.
In this scenario copy_only is affecting the ‘backup’ process? Can you explain?
If you’re in the full recovery mode, doing full backups, then a copy_only full backup, then a log backup (with or without norecovery), it works no problem. What I’ve just reproed, and I’m guessing you’re doing is running in simple and taking full backups, then switching to full and doing a copy_only full backup, and then trying to do a log backup. You can’t do that. You need to do a non-copy_only backup after switching to full. And then the only log you’ll back up is the log generated since that full backup (i.e. not all the log over the last few weeks). If you’re in simple, you cannot switch to full and then back up all the log. To have all the log, you need to be running in full and taking regular log backups. Otherwise, every time there’s an automatic checkpoint in simple, the log is being cleared.
Hi Paul thank you for the reply! I will try to reproduce to validate my scenario is accurate as I stated.
In my case “my DB”:
* DB in full recovery
* Daily Full DB backup ups were occurring (There were 3 nights of full backups)
* However Log backups have never been performed
* DB is small so log growth was small
Since in full recovery model, I decided to utilize ability of trans logs to go back to an earlier time.
Before I started as an extra precautionary step I performed a “COPY_ONLY” that seemed to stop the ability to perform a “log backup”
I created a test to simulate the pre-reqs as “my DB”
and was able to successfully perform restores to a point in time.
.. what I did not try was performing a “COPY ONLY”
I did “COPY ONLY” backupup and it failed prevented my ability and got a message
something like cannot perform a log backup because a full backup has not occurred
But that’s not true as I have “FULL BACKUPS” in the backup folder.
I’ll run through the scenario again though.. just odd. Thanks again for reply!
Finding that backing up a high availability group of 3 nodes (2 synch and one asynch) isn’t trivial and doesn’t appear to have a KISS solution. Each node uses local disk storage. What configurations of these options should be used: 1) with copy_only, 2) also under options a checkbox For availability database ignore replica priority for backup and backup on primary settings 3) Backup preferences. We’ve had an issue continuing backup when the primary switches to the other synchronous server. Also have concerns about being able to restore with a copy only bak file and trn files.
Yes – I understand that scenario is complex. I’m not an expert there though so can’t answer – please post to a forum. Thanks