Category: Uncategorized

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.

I used to build the Database Weekly newsletter and write the editorial every week. It was a chore, and it’s a grind. I have to go through hundreds of blog entries and news feeds, pick up the 40 or 50 ones that I think DBAs are interested in, and load them into our publishing system. They appear on the Database Weekly site as well.

It’s a grind, and last year I was able to convince my boss that it would be good for others to understand the process, and practice as well. As a result there are 4 or 5 of us that take turns building the newsletter. However it’s not even and I end up doing every other one, or close to it.

So it’s my week again this week. I skipped last week, and was glad, but this week I’m dreading it. It feels like a grind, especially with a bunch of other stuff to do this week. Just thinking about what the RSS reader looks makes me want to just skip it and claim no news this week!

It’s not always like that. I learn a lot from going through things, keep in touch with the pulse of what others are blogging about, and I find interesting things to write about. It’s actually a good exercise, and many weeks I look forward to seeing what it brings.

Just not this week.

I had an issue with a user recently. Actually it has been going on over a few weeks, due to both of us being busy and slow to respond to the other, but basically a user had two accounts. They had lost the password, changed jobs, and registered a new account rather than changing the email of the old one. As a result their history (QOD, posts, etc.) were in two separate places. They realized this and asked me to change the name on the old account. I did that, but I changed the email on the old account to move to the new one, and got things tangled up.
First, a little background. In order to be more “service oriented”, we actually use 3 databases. We have one for sign on, one for forums, one for everything else. However developers built this, and they didn’t think a few things through, like cross DB RI. So we have not only some linkage IDs, but also emails stored in a few places.
As a result, when you change your email, or when I do it on the web side, it doesn’t change the email in the forums db or the general SSC db. For the SSC db they must be referencing the IDs since those stay in lockstep with the signon ID, but in the forum DB, since we don’t create a user there until you post, they must reference by email. So when I changed things and they were looking for your profile (I assume you mean Linked In contact there), it was pulling the “first” one, which was the old one.
They also aren’t changing the name on the posts, which is a great non-RI implementation by InstantASP. Your name is stored in the post.  So I had to manually update a number of posts with the new name as well as fix some IDs in new posts to match the old one.
I also had to fix a few other places where things were mis-linked when they were changed somewhere else.
Keeping summarized data in sync can be hard, but when you are dealing with cross linked databases, and you don’t have constraints or strict interfaces that ensure updates propagate throughout all places, it’s easy to get out of sync.
I think I’ve fixed this issue, and saved the script for future use, but we’ll know when I hear back from the user.

We booked a vacation over the weekend, so I’m marking off the week before Christmas as vacation this year. Hoping not to get caught again with everyone but me taking time off.