Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Disaster Recovery at SQLServerCentral

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.”

sqlbackuppro[1] 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.

Comments

Posted by Jerry Hung on 24 February 2010

I agree!!! SELECT before INSERT/UPDATE statement is always good!!!

I was curious, would you be able to run SQL Data Compare (for Threads/Posts tables only) to recover the missing data, without doing all the manual work?

note: you can do this against .sqb directly too, or against your restored db

You should have free copies of all Red Gate software right ;-p?

Posted by Steve Jones on 24 February 2010

I could possibly have used Data Compare, but I'm not sure it's on the remote server. The issue for me is VPN access to the remote machine. I don't have the network working for me from my desktop, and I do this rarely, so it's not worth my time to debug it. I RDP to the machine, so I use what's on the server.

Posted by Seth Phelabaum on 25 February 2010

As a side note, SQL Prompt can script that insert for you as well.  Here are the scripts generated by SSMS and SQL Prompt for the same table.  Slightly different.

-- SSMS

INSERT INTO [Northwind].[dbo].[Categories]

          ([CategoryName]

          ,[Description]

          ,[Picture])

    VALUES

          (<CategoryName, nvarchar(15),>

          ,<Description, ntext,>

          ,<Picture, image,>)

GO

-- SQL Prompt (Formatting can change according to prefs I believe.

INSERT INTO Northwind.dbo.Categories (CategoryName, Description, Picture)

VALUES  (NULL, -- CategoryName - nvarchar(15)

        NULL, -- Description - ntext

        NULL  -- Picture - image

        )

Leave a Comment

Please register or log in to leave a comment.