I was recently working with a student in a one on one format and one the scenarios he wanted to learn to handle in SQL Server (he was an Oracle DBA cross training) was how to recover from the proverbial accidentally dropped table when there wasn't enough space to do a parallel restore and then move the missing object/data back to the original database. Have to say I'd never been in the situation where I didn't have the disk space for that, and of course the challenge is that even though you have missing data, you also typically have a lot of good data you don't want to lose.

What do you do? (humor for today)

Here's the steps:

• Run a final log backup (tail log) to capture all the transactions right up through now

• Restore the database to just prior to the incident (SSMS makes this incredibly easy)

• Copy out the data to another database (or CSV, or ...) and if you need the structure too, script it out, being sure to get the triggers, indexes, etc, etc, that you may need

• Restore the database again, this time to the most recent possible time - the final log backup we ran above

• Run your object script if needed, then move your data back into the database

It can be a little more complicated if multiple objects were affected, say in the case of a foreign key with cascading deletes enabled, but the overall process stays the same. Time to recover is basically 2X what it should be if you had space available on that server or another one.

Maybe something to think about and bake into your next DR practice session.