{"id":844,"date":"2017-06-14T12:18:29","date_gmt":"2017-06-14T19:18:29","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/erin\/?p=844"},"modified":"2019-07-16T19:15:31","modified_gmt":"2019-07-17T02:15:31","slug":"dbcc-clonedatabase-cannot-insert-duplicate-key-error","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/dbcc-clonedatabase-cannot-insert-duplicate-key-error\/","title":{"rendered":"DBCC CLONEDATABASE Cannot insert duplicate key Error"},"content":{"rendered":"<p>If you&#8217;ve been using DBCC CLONEDATABASE at all, you might have run into a <strong>cannot insert duplicate key<\/strong> error (or something similar) when trying to clone a database:<\/p>\n<p style=\"padding-left: 30px;\">Database cloning for &#8216;YourDatabase&#8217; has started with target as &#8216;COPY_YourDatabase&#8217;.<br \/>\nMsg 2601, Level 14, State 1, Line 1<br \/>\nCannot insert duplicate key row in object &#8216;sys.sysschobjs&#8217; with unique index &#8216;clst&#8217;. The duplicate key value is (1977058079).<\/p>\n<p>If you do some searching, you&#8217;ll probably end up at this Connect item: <a href=\"https:\/\/connect.microsoft.com\/SQLServer\/feedback\/details\/2926086\/dbcc-databaseclone-fails-on-sys-sysowners\">DBCC DATABASECLONE fails on sys.sysowners<\/a>.<\/p>\n<p>The Connect item states that the problem exists because of user objects in model.\u00a0 That&#8217;s not the case here.<\/p>\n<p>I&#8217;m working with a database created in SQL Server 2000&#8230;now running on SQL Server 2016.\u00a0 It turns out that when you create new user tables in a SQL Server 2000 database, the first objects have the IDs 1977058079, 2009058193, and 2041058307.\u00a0 (Yes, we actually tested this&#8230;I had a copy of SQL 2000 in a VM but Jonathan installed one and dug into it to figure out the IDs.)\u00a0 There were <em>a lot<\/em> of changes between SQL Server 2000 and SQL Server 2005, and that included changes to model.\u00a0 In the model database in SQL Server 2005 and higher, the aforementioned IDs are used for QueryNotificationErrorsQueue, EventNotificationErrorsQueue and ServiceBrokerQueue respectively&#8230;Service Broker objects.\u00a0 As a result, my user database &#8211; created in SQL Server 2000 &#8211; has user objects with IDs that are the same as system objects in model (and of course when the system objects like QueryNotificationErrorsQueue, EventNotificationErrorsQueue and ServiceBrokerQueue got created in my user database, they got completely different IDs).<\/p>\n<p><strong>Edit 2017-06-15:<\/strong> This issue is fixed in CU3 for SQL Server 2016 SP1 and CU5 for SQL Server 2014 SP2! See <a href=\"https:\/\/support.microsoft.com\/en-us\/help\/4016238\" class=\"broken_link\">KB 4016238, FIX: DBCC CLONEDATABASE is unsuccesful if the source database has an object originally created in SQL Server 2000<\/a>.<\/p>\n<p>To determine if this is an issue for any of your user databases you can use the following queries (change user_database to the name of your DB):<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\n\/*\r\nlists tables in the user database that have an ID that matches an object in model\r\n*\/\r\nSELECT m.id, m.name, c.name, c.id, m.type\r\nFROM model.sys.sysobjects m\r\nFULL OUTER JOIN user_database.sys.sysobjects c\r\nON m.id = c.id\r\nJOIN user_database.sys.objects o\r\nON c.id = o.object_id\r\nWHERE o.is_ms_shipped != 1\r\nAND m.id IS NOT NULL;\r\n\r\n\/*\r\nlists system objects in the user database that do *not* match the id of the same object in model\r\n(FYI only)\r\n*\/\r\nSELECT m.id, m.name, c.name, c.id, m.type\r\nFROM model.sys.sysobjects m\r\nFULL OUTER JOIN user_database.sys.sysobjects c\r\nON m.name = c.name\r\nJOIN user_database.sys.objects o\r\nON c.id = o.object_id\r\nWHERE m.id != c.id\r\n<\/pre>\n<p>To workaround this, you need different IDs for the affected objects in the user database.\u00a0 Now, I could recreate my tables in the user database, confirm the ID isn&#8217;t used for any system object in model, and then move the data over&#8230;\u00a0 But I have some tables that are larger than 100GB, so it&#8217;s not an easy fix.<\/p>\n<p>If you&#8217;re interested in reproducing the issue, sample code is below (thanks Jonathan).\u00a0 You need SQL Server 2000 installed and at least SQL Server 2005 or 2008 or 2008R2.\u00a0\u00a0 You cannot restore the SQL 2000 backup to SQL Server 2016 directly, you must do an intermediate upgrade to 2005, 2008, or 2008R2, then go to 2016.\u00a0 Paul has tested upgrading a SQL Server 2005 database to a newer instance <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/you-can-upgrade-from-any-version-2005-to-any-other-version\/\">here<\/a>; but for SQL Server 2000 you have to perform the intermediate upgrade.<\/p>\n<h2>Code to create the issue<\/h2>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nDROP DATABASE New2000\r\nGO\r\n\r\nCREATE DATABASE New2000\r\nGO\r\n\r\nUSE New2000\r\nGO\r\n\r\nDECLARE @loop INT\r\nSET @loop = 0\r\n\r\nWHILE @loop  &lt; 3\r\nBEGIN\r\n\r\n\tDECLARE @table NVARCHAR(4000)\r\n\r\n\tSET @table = 'table_' + REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(30), GETDATE(), 121), ':', ''), '-', ''),  ' ', ''), '.', '')\r\n\r\n\tDECLARE @sql NVARCHAR(4000)\r\n\r\n\tSET @sql = 'CREATE TABLE ' + @table+ ' (RowID INT)';\r\n\r\n\tEXEC(@sql)\r\n\r\n\tSET @Loop = (select COUNT(*) FROM sysobjects WHERE id IN (\r\n\t\t1977058079,\r\n\t\t2009058193,\r\n\t\t2041058307)\r\n\t\t)\r\n\r\n\tIF @loop = 0\r\n\tBEGIN\r\n\t\tPRINT 'Dropping Table'\r\n\t\tSET @sql = 'DROP TABLE '+ @table\r\n\t\tEXEC(@sql)\r\n\tEND\r\n\r\n\tWAITFOR DELAY '00:00:00.010'\r\n\r\nEND\r\n\r\n\/*\r\n\tBackup the database in SQL Server 2000\r\n\tRestore in SQL Server 2016\r\n*\/\r\n\r\nDBCC CLONEDATABASE ('New2000', 'COPY_New2000');\r\nGO\r\n<\/pre>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>If you&#8217;ve been using DBCC CLONEDATABASE at all, you might have run into a cannot insert duplicate key error (or something similar) when trying to clone a database: Database cloning for &#8216;YourDatabase&#8217; has started with target as &#8216;COPY_YourDatabase&#8217;. Msg 2601, Level 14, State 1, Line 1 Cannot insert duplicate key row in object &#8216;sys.sysschobjs&#8217; with [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[13,43],"tags":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.9.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>DBCC CLONEDATABASE Cannot insert duplicate key Error - Erin Stellato<\/title>\n<meta name=\"description\" content=\"If you&#039;ve tried to use DBCC CLONEDATABASE against a database created in SQL Server 2000, you might have hit this &quot;Cannot insert duplicate key&quot; error.\" \/>\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\/erin\/dbcc-clonedatabase-cannot-insert-duplicate-key-error\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"DBCC CLONEDATABASE Cannot insert duplicate key Error - Erin Stellato\" \/>\n<meta property=\"og:description\" content=\"If you&#039;ve tried to use DBCC CLONEDATABASE against a database created in SQL Server 2000, you might have hit this &quot;Cannot insert duplicate key&quot; error.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/dbcc-clonedatabase-cannot-insert-duplicate-key-error\/\" \/>\n<meta property=\"og:site_name\" content=\"Erin Stellato\" \/>\n<meta property=\"article:published_time\" content=\"2017-06-14T19:18:29+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-07-17T02:15:31+00:00\" \/>\n<meta name=\"author\" content=\"Erin Stellato\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Erin Stellato\" \/>\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\/erin\/dbcc-clonedatabase-cannot-insert-duplicate-key-error\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/dbcc-clonedatabase-cannot-insert-duplicate-key-error\/\",\"name\":\"DBCC CLONEDATABASE Cannot insert duplicate key Error - Erin Stellato\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\"},\"datePublished\":\"2017-06-14T19:18:29+00:00\",\"dateModified\":\"2019-07-17T02:15:31+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\"},\"description\":\"If you've tried to use DBCC CLONEDATABASE against a database created in SQL Server 2000, you might have hit this \\\"Cannot insert duplicate key\\\" error.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/dbcc-clonedatabase-cannot-insert-duplicate-key-error\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/erin\/dbcc-clonedatabase-cannot-insert-duplicate-key-error\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/dbcc-clonedatabase-cannot-insert-duplicate-key-error\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"DBCC CLONEDATABASE Cannot insert duplicate key Error\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\",\"name\":\"Erin Stellato\",\"description\":\"The SQL Sequel\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\",\"name\":\"Erin Stellato\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g\",\"caption\":\"Erin Stellato\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/erin\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/author\/erin\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"DBCC CLONEDATABASE Cannot insert duplicate key Error - Erin Stellato","description":"If you've tried to use DBCC CLONEDATABASE against a database created in SQL Server 2000, you might have hit this \"Cannot insert duplicate key\" error.","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\/erin\/dbcc-clonedatabase-cannot-insert-duplicate-key-error\/","og_locale":"en_US","og_type":"article","og_title":"DBCC CLONEDATABASE Cannot insert duplicate key Error - Erin Stellato","og_description":"If you've tried to use DBCC CLONEDATABASE against a database created in SQL Server 2000, you might have hit this \"Cannot insert duplicate key\" error.","og_url":"https:\/\/www.sqlskills.com\/blogs\/erin\/dbcc-clonedatabase-cannot-insert-duplicate-key-error\/","og_site_name":"Erin Stellato","article_published_time":"2017-06-14T19:18:29+00:00","article_modified_time":"2019-07-17T02:15:31+00:00","author":"Erin Stellato","twitter_misc":{"Written by":"Erin Stellato","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/dbcc-clonedatabase-cannot-insert-duplicate-key-error\/","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/dbcc-clonedatabase-cannot-insert-duplicate-key-error\/","name":"DBCC CLONEDATABASE Cannot insert duplicate key Error - Erin Stellato","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website"},"datePublished":"2017-06-14T19:18:29+00:00","dateModified":"2019-07-17T02:15:31+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158"},"description":"If you've tried to use DBCC CLONEDATABASE against a database created in SQL Server 2000, you might have hit this \"Cannot insert duplicate key\" error.","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/dbcc-clonedatabase-cannot-insert-duplicate-key-error\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/erin\/dbcc-clonedatabase-cannot-insert-duplicate-key-error\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/dbcc-clonedatabase-cannot-insert-duplicate-key-error\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/erin\/"},{"@type":"ListItem","position":2,"name":"DBCC CLONEDATABASE Cannot insert duplicate key Error"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/","name":"Erin Stellato","description":"The SQL Sequel","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/erin\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158","name":"Erin Stellato","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g","caption":"Erin Stellato"},"sameAs":["http:\/\/3.209.169.194\/blogs\/erin"],"url":"https:\/\/www.sqlskills.com\/blogs\/erin\/author\/erin\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/844"}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/comments?post=844"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/844\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/media?parent=844"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/categories?post=844"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/tags?post=844"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}