Recovering from a dropped or truncated table

All right, so how about someone dropped or truncated a table? What are you going to do?

There’s the joke about a child named little Bobby Tables.

He is the SQL injection guy who’s parents named him.

Robert’); DROP TABLE Students; —

And what that name does, is it SQL injection that basically closes out the current SQL statement, then adds the script to drop the students table.

It’s kind of a famous SQL injection style hack, but Bobby did not do well in school because of that.

In reality, you could end up in a situation where someone has dropped a table, either it was an accident, or it was SQL injection.  For whatever reason you had this table, and now you don’t, and it’s an important part of your system, and you need to get it back.

So one of the things I’m going to look at is, is the system still functioning?  how critical is this table?

What parts of the system are working? Are we 100% down until it’s restored?

Now, how would we go about getting it back?

Well, could we do a full database restore, and call it good or have other parts of the system been continuing to run since that table was dropped?.

Are you going to lose data, if you just do a full restore prior to the drop and overwrite the existing database?

Can we restore a copy of that full database, and replace the missing table? Basically, restore the copy and then script the creation of that table and move it over.

I was away for a weekend earlier in the year and received a call. The customer had dropped the table. Since it was a weekend, I was on my boat and I had good internet and my laptop, I was able to do a restore, and copy the table that had been dropped.

There are  lots of different ways to do that. So really, what I’d like to do is have you think through the following question:

If this happened to you on a really busy, important table on your database, how would you go about getting it back?

And if you don’t know the answer to that, that’s okay, we can help at Stedman Solutions.

 

---

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!