{"id":5072,"date":"2020-02-09T14:17:26","date_gmt":"2020-02-09T22:17:26","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/paul\/?p=5072"},"modified":"2020-02-09T15:10:42","modified_gmt":"2020-02-09T23:10:42","slug":"the-curious-case-of-restoring-a-table-into-a-different-database","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-restoring-a-table-into-a-different-database\/","title":{"rendered":"The Curious Case of\u2026 restoring a table into a different database"},"content":{"rendered":"

(The Curious Case of\u2026<\/em>\u00a0used to be part of our bi-weekly\u00a0newsletter<\/a>\u00a0but we decided to make it a\u00a0regular blog post instead so it can sometimes be more frequent. It covers something interesting one of us encountered when working with a client, doing some testing, or were asked in a random question from the community.)<\/p>\n

On an email distribution list I’m on, someone wondered today about the possibility of restoring a table into a different database that already has data in it. It’s an interesting thought exercise, so I wrote up a response for the list but then figured it would make a neat blog post.<\/p>\n

Restoring a table into a different database that already has data in is actually a lot harder than it may seem.<\/p>\n

There are a number of problems, including:<\/p>\n

    \n
  1. What if some of the allocation unit IDs used by the table are already in use in the new database?<\/li>\n
  2. What if some of the page IDs used by the table are already in use in the new database?<\/li>\n
  3. What if the table is involved in foreign-key constraints?<\/li>\n
  4. What if the table contains FILESTREAM data and some of the FILESTREAM GUIDs are already in use?<\/li>\n
  5. It would have to pull out any UDTs and other metadata related to the table<\/li>\n<\/ol>\n

    I can think of ways around these, but #2 is very challenging, as it would involve changing:<\/p>\n

      \n
    • Page locations themselves, so updating the page ID in the header<\/li>\n
    • Page linkages in the doubly-linked list at each index level<\/li>\n
    • Page linkages in index non-leaf pages<\/li>\n
    • Page linkages in heap forwarding pointers, and the back-pointer in each forwarded record<\/li>\n
    • Page linkages to off-row text pages<\/li>\n
    • Page linkages within text trees<\/li>\n
    • IAM page contents<\/li>\n
    • Keeping a mapping of old page ID to new page ID so the transaction log can be applied, and IAM page updates and would be especially difficult, as would LOP_MODIFY_COLUMNS (and anything touching the page header) as it would have to figure out whether a page pointer is being set or changed
      \nDoing all this in such a way that if the restore is interrupted, the database isn’t irretrievably corrupt<\/li>\n<\/ul>\n

      Restore of a single table into a new database would be a lot easier, but still challenging if you want to reduce disk space, unless you restore into the sparse equivalent of a database snapshot (otherwise if you have a table with, say, 10GB, but one of the pages is at offset 400GB in a file, you’d need 400GB of space for the file).<\/p>\n

      Bottom line: I wouldn’t hold your breath for either of these being implemented in SQL Server!<\/p>\n

      PS: Brent pointed out that non-SQL Server solutions like LiteSpeed do single table restore into a different database, which I didn’t know – I though they only did that into the same database, and from reading the docs, they don’t do what I said above either, as it would unfeasible. They extract the table pages from the backup into a separate location, and then use the table metadata to essentially ‘select’ from the temporary location and insert into a new SQL Server table, which avoids all the messy page ID manipulation. Pretty cool!<\/p>\n","protected":false},"excerpt":{"rendered":"

      (The Curious Case of\u2026\u00a0used to be part of our bi-weekly\u00a0newsletter\u00a0but we decided to make it a\u00a0regular blog post instead so it can sometimes be more frequent. It covers something interesting one of us encountered when working with a client, doing some testing, or were asked in a random question from the community.) On an email […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[13,48,62,115],"tags":[],"class_list":["post-5072","post","type-post","status-publish","format-standard","hentry","category-backuprestore","category-inside-the-storage-engine","category-on-disk-structures","category-the-curious-case-of"],"yoast_head":"\nThe Curious Case of\u2026 restoring a table into a different database - 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\/the-curious-case-of-restoring-a-table-into-a-different-database\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"The Curious Case of\u2026 restoring a table into a different database - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"(The Curious Case of\u2026\u00a0used to be part of our bi-weekly\u00a0newsletter\u00a0but we decided to make it a\u00a0regular blog post instead so it can sometimes be more frequent. It covers something interesting one of us encountered when working with a client, doing some testing, or were asked in a random question from the community.) On an email […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-restoring-a-table-into-a-different-database\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2020-02-09T22:17:26+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-02-09T23:10:42+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=\"3 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\/the-curious-case-of-restoring-a-table-into-a-different-database\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-restoring-a-table-into-a-different-database\/\",\"name\":\"The Curious Case of\u2026 restoring a table into a different database - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2020-02-09T22:17:26+00:00\",\"dateModified\":\"2020-02-09T23:10:42+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-restoring-a-table-into-a-different-database\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-restoring-a-table-into-a-different-database\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-restoring-a-table-into-a-different-database\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"The Curious Case of\u2026 restoring a table into a different database\"}]},{\"@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":"The Curious Case of\u2026 restoring a table into a different database - 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\/the-curious-case-of-restoring-a-table-into-a-different-database\/","og_locale":"en_US","og_type":"article","og_title":"The Curious Case of\u2026 restoring a table into a different database - Paul S. Randal","og_description":"(The Curious Case of\u2026\u00a0used to be part of our bi-weekly\u00a0newsletter\u00a0but we decided to make it a\u00a0regular blog post instead so it can sometimes be more frequent. It covers something interesting one of us encountered when working with a client, doing some testing, or were asked in a random question from the community.) On an email […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-restoring-a-table-into-a-different-database\/","og_site_name":"Paul S. Randal","article_published_time":"2020-02-09T22:17:26+00:00","article_modified_time":"2020-02-09T23:10:42+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-restoring-a-table-into-a-different-database\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-restoring-a-table-into-a-different-database\/","name":"The Curious Case of\u2026 restoring a table into a different database - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2020-02-09T22:17:26+00:00","dateModified":"2020-02-09T23:10:42+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-restoring-a-table-into-a-different-database\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-restoring-a-table-into-a-different-database\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-restoring-a-table-into-a-different-database\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"The Curious Case of\u2026 restoring a table into a different database"}]},{"@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\/5072","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=5072"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/5072\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=5072"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=5072"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=5072"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}