SQL 2000 database - almost everything erased

Question

Post reply

SQL 2000 database - almost everything erased

Russell Shilling

SSCarpal Tunnel

Points: 4349
More actions
October 11, 2008 at 12:38 pm

#192040

We had the oddest thing happen Friday. A test database (thankfully) that was a copy of our production data, with around 500 tables and an equal number of stored procedures, spontaneously lost about half of the tables (as in gone), and the other half were emptied of all data. Every user stored procedure was gone except for two, and all the views, triggers and constraints were gone.
I looked at everything, ran CHECKDB, and such, and can find no rational explanation for this. Does anyone have an idea how something like this could ever happen?
So long, and thanks for all the fish,
Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Someone ran a script with a whole lot of truncate and drop statements in it?

SQL doesn't randomly go and drop tables. Someone would have had to do it.

I've seen a similar thing when one or the admins, who was testing out a procedure to recreate the dev environment with a partial copy of prod, accidentally ran a half written script. Fortunately it was on the test environment, not the production.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 2

I have never seen SQL Server, in 17 years, delete objects. Someone did it.

If you have logs or log backups, download Log Rescue from Red gate and view them.

Russell Shilling SSCarpal Tunnel Points: 4349 More actions · Answer 3

The only thing that happened was one of the developers ran a DTS export of table data from production to test. There was definitely NOT a script that was run. There are only four of us, and we're all stumped, and astonished.

So long, and thanks for all the fish,

Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Are you sure there's no one else with sufficient rights? Nothing got accidentally scheduled or run? The DTS package didn't perhaps have the wrong connection on a drop table task?

It sounds like an import script (to copy prod to test) half ran (the drop and truncate half) and then failed.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Russell Shilling SSCarpal Tunnel Points: 4349 More actions · Answer 5

Thanks for the tip about Log Rescue. I didn't realize it is now a free tool. (I've been trying to get my employer to spring for the whole SQL Toolbelt package, but no luck, so far... *sigh*)

Unfortunately, all I found were a bunch of drop table statements run by the user "dbo". I'll just have to assume that there was a DTS Copy Objects that was mistakenly created with the copy all options selected, started, then cancelled.

So long, and thanks for all the fish,

Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3