{"id":1150,"date":"2007-10-15T21:37:13","date_gmt":"2007-10-15T21:37:13","guid":{"rendered":"\/blogs\/paul\/post\/BACKUP-WITH-COPY_ONLY-how-to-avoid-breaking-the-backup-chain.aspx"},"modified":"2017-08-06T10:57:40","modified_gmt":"2017-08-06T17:57:40","slug":"backup-with-copy_only-how-to-avoid-breaking-the-backup-chain","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/backup-with-copy_only-how-to-avoid-breaking-the-backup-chain\/","title":{"rendered":"BACKUP WITH COPY_ONLY – how to avoid breaking the backup chain"},"content":{"rendered":"

After posting last week about a BACKUP<\/em> feature that I don’t like (WITH NO_LOG<\/em> – see here<\/a>), 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<\/em> option to BACKUP DATABASE<\/em> and BACKUP LOG<\/em>.<\/p>\n

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\u00a0differential 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<\/em>, gets to the noon differential backup and gets the following message:<\/p>\n

\r\nRESTORE DATABASE [production]\r\nFROM DISK = N'C:\\sqlskills\\production-diff12pm.bck'\r\nWITH NORECOVERY;\r\nGO\r\n<\/pre>\n
\r\nMsg 3136, Level 16, State 1, Line 1\r\nThis differential backup cannot be restored because the database has not been restored to the correct earlier state.\r\nMsg 3013, Level 16, State 1, Line 1\r\nRESTORE DATABASE is terminating abnormally.\r\n<\/pre>\n
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?\u00a0The DBA\u00a0takes a look in the backup history tables in msdb<\/em>:<\/p>\n
\r\nSELECT\r\n    [name],\r\n    [backup_start_date],\r\n    [type],\r\n    [first_lsn],\r\n    [database_backup_lsn]\r\nFROM\r\n    [msdb].[dbo].[backupset]\r\nWHERE\r\n    [database_name] = N'production';\r\nGO\r\n<\/pre>\n
\r\nname\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 backup_start_date\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 type first_lsn\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 database_backup_lsn\r\n------------------------------ ----------------------- ---- -------------------- --------------------\r\nproduction Full 10\/14\/07\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2007-10-14 00:00:00.000 D\u00a0\u00a0\u00a0 88000000025300001\u00a0\u00a0\u00a0 0\r\nproduction Diff 4am 10\/14\/07\u00a0\u00a0 2007-10-14 04:00:00.000 I\u00a0\u00a0\u00a0 118000000003000160\u00a0\u00a0 88000000025300001\r\nproduction Diff 8am 10\/14\/07\u00a0\u00a0 2007-10-14 08:00:00.000 I\u00a0\u00a0\u00a0 144000000070500160\u00a0\u00a0 88000000025300001\r\nNULL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2007-10-14 10:29:50.000 D\u00a0\u00a0\u00a0 161000000056100147\u00a0\u00a0 88000000025300001\r\nproduction Diff 12pm 10\/14\/07\u00a0 2007-10-14 12:00:00.000 I\u00a0\u00a0\u00a0 161000000062800034\u00a0\u00a0 161000000056100147\r\nproduction Diff 4pm 10\/14\/07\u00a0\u00a0 2007-10-14 16:00:00.000 I\u00a0\u00a0\u00a0 173000000054100144\u00a0\u00a0 161000000056100147\r\n<\/pre>\n
Aha! Look at the backup_start_date<\/em> 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<\/em> 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<\/em>) equal to the first_lsn<\/em> of the full backup from midnight. The two backups after (lines 7-8) that have the differential base equal to the first_lsn<\/em> of the accidental full backup.<\/p>\n
Oops! That means that the production database cannot be rolled forward any further than the last differential\u00a0backup 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!<\/p>\n
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<\/em> 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<\/em> differential backups. If you specify COPY_ONLY<\/em> with DIFFERENTIAL<\/em>, the option is ignored.<\/p>\n
One other cool thing is that you can specify this option for a BACKUP LOG<\/em> 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…<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[13,35,38],"tags":[],"class_list":["post-1150","post","type-post","status-publish","format-standard","hentry","category-backuprestore","category-disaster-recovery","category-example-scripts"],"yoast_head":"\nBACKUP WITH COPY_ONLY - how to avoid breaking the backup chain - Paul S. Randal<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/backup-with-copy_only-how-to-avoid-breaking-the-backup-chain\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"BACKUP WITH COPY_ONLY - how to avoid breaking the backup chain - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"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 […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/backup-with-copy_only-how-to-avoid-breaking-the-backup-chain\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2007-10-15T21:37:13+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-08-06T17:57:40+00:00\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/backup-with-copy_only-how-to-avoid-breaking-the-backup-chain\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/backup-with-copy_only-how-to-avoid-breaking-the-backup-chain\/\",\"name\":\"BACKUP WITH COPY_ONLY - how to avoid breaking the backup chain - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2007-10-15T21:37:13+00:00\",\"dateModified\":\"2017-08-06T17:57:40+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/backup-with-copy_only-how-to-avoid-breaking-the-backup-chain\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/backup-with-copy_only-how-to-avoid-breaking-the-backup-chain\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/backup-with-copy_only-how-to-avoid-breaking-the-backup-chain\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"BACKUP WITH COPY_ONLY – how to avoid breaking the backup chain\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"BACKUP WITH COPY_ONLY - how to avoid breaking the backup chain - Paul S. Randal","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/paul\/backup-with-copy_only-how-to-avoid-breaking-the-backup-chain\/","og_locale":"en_US","og_type":"article","og_title":"BACKUP WITH COPY_ONLY - how to avoid breaking the backup chain - Paul S. Randal","og_description":"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 […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/backup-with-copy_only-how-to-avoid-breaking-the-backup-chain\/","og_site_name":"Paul S. Randal","article_published_time":"2007-10-15T21:37:13+00:00","article_modified_time":"2017-08-06T17:57:40+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/backup-with-copy_only-how-to-avoid-breaking-the-backup-chain\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/backup-with-copy_only-how-to-avoid-breaking-the-backup-chain\/","name":"BACKUP WITH COPY_ONLY - how to avoid breaking the backup chain - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2007-10-15T21:37:13+00:00","dateModified":"2017-08-06T17:57:40+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/backup-with-copy_only-how-to-avoid-breaking-the-backup-chain\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/backup-with-copy_only-how-to-avoid-breaking-the-backup-chain\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/backup-with-copy_only-how-to-avoid-breaking-the-backup-chain\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"BACKUP WITH COPY_ONLY – how to avoid breaking the backup chain"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/1150","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=1150"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/1150\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=1150"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=1150"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=1150"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}