(The Curious Case of… used to be part of our bi-weekly newsletter but we decided to make it a regular 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 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.
Restoring a table into a different database that already has data in is actually a lot harder than it may seem.
There are a number of problems, including:
- What if some of the allocation unit IDs used by the table are already in use in the new database?
- What if some of the page IDs used by the table are already in use in the new database?
- What if the table is involved in foreign-key constraints?
- What if the table contains FILESTREAM data and some of the FILESTREAM GUIDs are already in use?
- It would have to pull out any UDTs and other metadata related to the table
I can think of ways around these, but #2 is very challenging, as it would involve changing:
- Page locations themselves, so updating the page ID in the header
- Page linkages in the doubly-linked list at each index level
- Page linkages in index non-leaf pages
- Page linkages in heap forwarding pointers, and the back-pointer in each forwarded record
- Page linkages to off-row text pages
- Page linkages within text trees
- IAM page contents
- 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
Doing all this in such a way that if the restore is interrupted, the database isn’t irretrievably corrupt
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).
Bottom line: I wouldn’t hold your breath for either of these being implemented in SQL Server!
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!