The Curious Case of… restoring a table into a different database

(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:

  1. What if some of the allocation unit IDs used by the table are already in use in the new database?
  2. What if some of the page IDs used by the table are already in use in the new database?
  3. What if the table is involved in foreign-key constraints?
  4. What if the table contains FILESTREAM data and some of the FILESTREAM GUIDs are already in use?
  5. 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!

Free Pluralsight this weekend and 500,000 hours of SQLskills viewing!

Back in 2012 we started recording online courses for Pluralsight, with our debut being my course on SQL Server: Myths and Misconceptions. Fast forward to the end of 2019 and our 65th published course was Jonathan’s SQL Server: Using SentryOne Plan Explorer, a complete revamp of his 2013 course.

At the end of January this year, we passed a major milestone: people around the world have watched more than 500,000 hours of our courses!

That’s really cool, as one of the things we all love to do is teach people and have them really grasp what we’re teaching. And a big thank you to everyone who’s watched some of our courses!

And on that note, the entire Pluralsight site will be completely free this coming weekend – sign up here.

If you can’t make it this weekend, and want to give our courses a try, I’ll give out a free 30-day pass for SQLskills courses to the first 50 people who send me an email.

Enjoy!

The Curious Case of… a sudden latency and memory usage increase

(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.)

As part of our remote DBA service, we constantly monitor a client’s servers for anomalous behavior, and last week Tim noticed a sudden latency and memory usage increase at one of the client’s he’s responsible for. He reached out to their in-house DBA and asked about any recent changes. The DBA quickly replied back that they were not aware of any changes however they’d been struggling with that server due to reports of things running slow and overall poor response times. Furthermore, the developers and application team all stated that no new code had been deployed.

As they are one of our Remote DBA customers, they’d  been provided with monthly reports by Tim that include system response times and other baseline information. Tim quickly reviewed the numbers with them from previous months showing how much better the system had been performing – so *something* had changed.

Now Tim knew this was a virtual machine, and in situations like this, it’s common for there to have been a change at the VM level instead of a change at the SQL Server level. On reaching out to the VM admin to see if the VM had been moved to another host, Tim found that sure enough, there had been an event of some kind and this larger SQL Server VM had been moved to a very busy host that was struggling to keep up.

The VM admin quickly moved the VM to a better suited host and performance went back to normal – simple solution. Later on Tim found out the VM admin had been doing maintenance on the VM infrastructure over the weekend and had just forgotten to move the SQL Server back to its normal environment!

Take away: if you have a SQL Server running in a VM, and it has a sudden, catastrophic performance degradation, check whether something has changed at the VM level before spending a lot of time troubleshooting at the SQL Server level.

PS I had a question about the increased memory usage: The thinking is that things were so much slower that connections were staying active for longer, so many more connections = many more threads being used and memory grants being held for longer = more memory being used, and a feedback loop that made it worse and worse compared to normal connection load.