(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!
7 thoughts on “The Curious Case of… restoring a table into a different database”
Err – you know most SQL Server backup products offer this though, right? For example, we had it in Litespeed when I worked at Quest a decade ago. Everybody has it today, usually called object level recovery.
Instead of thinking in terms of data pages, think about restoring the table schema under a new name (indexes, etc) then inserting the rows, then finally adding the triggers back in.
As long as you have basic exclusions (no foreign key relationships restored, no file table (which is a super obscure feature anyway)), then it’s really not that challenging.
I didn’t know that it could do it into a different database – very cool – post updated.
Maybe my memory is failing, but wasn’t this a feature within SQL Server before SQL 7.0? I remember having the “table-level restore” option and was a bit disappointed when it went away. I kind of like the idea of restoring into a “snapshot” and then copying over to the database as an option, though. That object-level recovery is well-worth it when you need it. Accidents happen and restoring single objects would be less intrusive than restoring an entire database.
I like the thinking around the challenges involved, though. It’s easy to say “just do it”, but we don’t often think of the crazy ways that this can be made very complex in some situations.
I believe it let you restore into the same database, but 6.5 was before I started working with SQL Server – joined the team a month after they shipped 7.0. Glad you enjoyed the post!
Table restores were possible in 6.5. Restoring a table was a “great” way to get rid of msg 605 and other doubly linked problems between allocation pages. How it worked was to read the objid in each page header of the backup, to create a new allocation structure for the new table (as a heap), restore pages with newly created pnext and pprev in page headers (overwting the old values), drop the old allocation structure, and bobs your uncle. Couple of problems remained – referential integrity was ignored (you can kiss any semblance of ACID transactions good-bye) and row-level corruption remained (some of the weirdest row level corruption errors I ever saw, only happened after this table-restore “trick” was uncovered by a bartd customer). So, while a dying man will grasp at straws, I assume after 6.5 Microsoft left it to 3rd parties to offer this beguiling feature. I suspect that the decision in part based upon the fact that such a “repaired” database won’t stand 1 day in court (after any sharp lawyer learns what was done).
Is it possible to restore a table (to the same or different database) from a database backup natively? Also, do the third party products mentioned in Brent’s comments restore the table from a database backup?
No, and I don’t know if those products use native backups or not.