We had a spammer post a bunch of random, silly posts last week. I knew it was an issue when my email went crazy with posts. I think 10 or 12 people sent me notes about different posts. We also had some issues the same day with inappropriate posts and I was doing a few things at once.

And I think I deleted a thread.

At least that’s all I can think of. Someone reported a short time later that a thread appeared to be missing, and sure enough, I couldn’t find it. Old Google cached URLs returned errors and a search of the forum database didn’t find anything.

So I went to restore things. I VPN’d to the server, started SSMS, and clicked restore, picking a new database name, changed the MOVE options, and clicked "restore.”

And it failed. It couldn’t open the .stb file. That threw me for a minute until I realized it was a SQL Backup file. Makes sense that our IT group would use a Red Gate product for backups. No problem, I fire up the SQL Backup interface, register the server, and find the full backup from last night. I go to start the restore, but there’s a password on the file.

Grrrrrrrr.

I have to send a request to IT. It’s annoying, but they run the servers and I’ve let that responsibility go. The servers are also co-located, so it makes sense to protect the backup files. IT responds fairly quickly, and in an hour the database is restored with a new name.

From there it’s a fairly simple method to restore. I have to prep the table

SET IDENTITY_INSERT  THREADS ON

And then since I need to include all the column names specifically (no insert. select * allowed), I use the SSMS Script feature to script an insert. I find the thread ID and include that in a WHERE clause. To be safe, I run the SELECT first.

SELECT ThreadID

   , Title

   , PostDate

    , UserID

from Threads

Where ThreadID = 555555

It looked good and I then added the insert before this, ran it, and I had the thread back.

But not the posts themselves. Those are actually in another table, so I had to repeat this process for a new table, first setting identity insert off for the first table and on for the second.

SET IDENTITY_INSERT  THREADS OFF

This took longer than expected, mostly because I wasn’t as familiar with the database design (it’s not mine) and I had to go slow with the SQL since I didn’t want any issues on a production system.

Improvement

What could I have done better? Two things.

The first is that I should have restored some transaction logs to get closer to the time when I deleted the post. I was worried about the IT guys getting confused, but I should have tried. It would be good practice for them and we weren’t in a time crunch. Working with someone on the other side of the Atlantic Ocean might have been slow, but it would be a good exercise.

The other thing is that some user points were lost. The points system is a little weird, and I’m not completely sure how it works since I don’t have someone that can decode the formulas in the front end. I’ve submitted a request to get that formula, but for now I didn’t want to mess with it.

Summary

Things worked out, and this type of exercise is something we ought to practice more. I had been working with database snapshots in a test environment recently and was tempted to actually set up a drop and then create of a new snapshot each night so I could do this without a restore. That is an interesting idea for me.

I also want to remind you that testing your SELECT before doing the INSERT is important. You do not want to compound your mistakes.