{"id":903,"date":"2011-03-08T16:30:44","date_gmt":"2011-03-08T14:30:44","guid":{"rendered":"https:\/\/www.sqlinthewild.co.za\/?p=903"},"modified":"2011-03-20T23:12:12","modified_gmt":"2011-03-20T21:12:12","slug":"full-backups-the-log-chain-and-the-copy_only-option","status":"publish","type":"post","link":"https:\/\/www.sqlinthewild.co.za\/index.php\/2011\/03\/08\/full-backups-the-log-chain-and-the-copy_only-option\/","title":{"rendered":"Full backups, the log chain and the COPY_ONLY option."},"content":{"rendered":"

There have been a large number of posts made regarding the interaction between full database backups and the log chain (like this one<\/a>). However there still seems to be a lot of confusion around regarding how they interact. I doubt I can clear up the confusion with yet another blog post, but I’ll try.<\/p>\n

One that seems to come up now and again is the COPY_ONLY Option on backups. Typically the comment is something like ‘Use the COPY_ONLY option when taking ad-hoc full backups so that you don’t impact the log backups.’ Now we know from the blog posts linked above that full backups don’t ever break the log chain (and I’m not going to run yet more tests to prove it) so what is the copy only option there for?<\/p>\n

Books Online states the following regarding the COPY_ONLY option for backups – “Specifies that the backup is a copy-only backup, which does not affect the normal sequence of backups. A copy-only backup is created independently of your regularly scheduled, conventional backups. A copy-only backup does not affect your overall backup and restore procedures for the database.”<\/p>\n

Well, that doesn’t clear things up much. It does however go on to say this: “When used with BACKUP DATABASE, the COPY_ONLY option creates a full backup that cannot serve as a differential base. The differential bitmap is not updated, and differential backups behave as if the copy-only backup does not exist. Subsequent differential backups use the most recent conventional full backup as their base.”<\/p>\n

So it’s not the log chain that copy only is there to not affect, it’s the differential base. Let’s test and see how it works.<\/p>\n

<\/p>\n

CREATE DATABASE TestingBackups\r\nGO\r\n\r\nUSE TestingBackups\r\nGO\r\n\r\nCREATE TABLE Testing (\r\nID INT IDENTITY PRIMARY KEY,\r\nSomeValue CHAR(4)\r\n);\r\nGO\r\n\r\nBACKUP DATABASE TestingBackups TO DISK = 'C:\\Databases\\Backup\\TestingBackups_Full1.bak'\r\nGO\r\n\r\nINSERT INTO Testing (SomeValue)\r\nVALUES ('abc')\r\n\r\nBACKUP DATABASE TestingBackups TO DISK = 'C:\\Databases\\Backup\\TestingBackups_Diff1.bak' WITH DIFFERENTIAL\r\nGO\r\n\r\nINSERT INTO Testing (SomeValue)\r\nVALUES ('def')\r\n\r\nBACKUP DATABASE TestingBackups TO DISK = 'C:\\Databases\\Backup\\TestingBackups_Full2.bak'\r\nGO\r\n\r\nINSERT INTO Testing (SomeValue)\r\nVALUES ('ghi')\r\n\r\nBACKUP DATABASE TestingBackups TO DISK = 'C:\\Databases\\Backup\\TestingBackups_Diff2.bak' WITH DIFFERENTIAL\r\nGO<\/pre>\n
Right, so that’s a database with two full backups and two differential backups and a couple data changes in between. Let’s drop the database and test some restore strategies. The goal is to restore the database to the point that it was at the time the second differential backup was taken.<\/p>\n
First the obvious route. Full backup 2 and differential backup 2.<\/p>\n
RESTORE DATABASE TestingBackups FROM DISK = 'C:\\Databases\\Backup\\TestingBackups_Full2.bak' WITH NORECOVERY\r\nGO\r\nRESTORE DATABASE TestingBackups FROM DISK = 'C:\\Databases\\Backup\\TestingBackups_Diff2.bak' WITH NORECOVERY\r\nGO\r\nRESTORE DATABASE TestingBackups WITH RECOVERY<\/pre>\n
That works. That’s the most obvious and likely the one that will be used the most. What happens though if we don’t have full backup 2? What if it was a full backup that an evil developer took without asking, used to restore a development\/test database somewhere and then deleted the backup file?<\/p>\n
There are two options there that might work:<\/p>\n