Screwed Up, left where of update, messed up a few thousand records, recourse?

Question

Screwed Up, left where of update, messed up a few thousand records, recourse?

Sean Grebey-262535

SSCommitted

Points: 1724
More actions
March 4, 2011 at 11:57 am

#247569

I accidentally updated all my records instead of one right running an update query inside of MS Server Management Studio. Is there any way to roll that back without going to a backup?

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

nope; you'll have to grab a backup, restore it as a different db, and compare the two tables, migrating the good data to replace the unwanted changes;

SSMS uses implicit transactions, so unless you had changed that default seting, or explicitly issued a BEGIN TRAN command, it's the only option.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sean Grebey-262535 SSCommitted Points: 1724 More actions · Answer 2

As luck would have it, the SQL Server agent on this box apparently got shut off a month ago, so I have no Full back up of the data that is recent. I do have a backup of the actual .mdf/.ldf files for the database though from 2am. I should be able to set those up as a separate database and do a compare that way shouldn't I? Can I dump and .mdf/.ldf over an existing set of files by the same name? I have a test version of this database.

Lowell SSC Guru Points: 323518 More actions · Answer 3

you should be able to attach the mdl/ldf files as a new database name. I'm a little afraid that if the database was in use by SQL server, that the mdf/ldf might not be copied by your backup process correctly. usually, the service has to be stopped to copy mdf/ldf files correctly/cleanly.

files that are in use need special treatment(imagine editing a *.doc file, making changes to it...until that file is actually saved by clicking *save* , you cannot get the new paragraphs you were in the middle of adding when you copy via a disk)

copy to a different folder, and try to attach as a new database name; if that works you can just compare the two tables, and update based on primary key where the column you updated <> oldvalue

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sean Grebey-262535 SSCommitted Points: 1724 More actions · Answer 4

Knock on something the files were fine and I was able to attach them and restore my data. Apparently somebody changed the password of the account SQL Server Agent was running under and killed my backups last month.

Ron McCullough SSC Guru Points: 63877 More actions · Answer 5

Sean Grebey-262535 (3/4/2011)
Knock on something the files were fine and I was able to attach them and restore my data. Apparently somebody changed the password of the account SQL Server Agent was running under and killed my backups last month.

Hopefully this episode will get you into the habit:

1. When first arriving in the AM to run a bit of T-SQL to check if a backup was performed.

2. Periodically restoring a backup to insure that the back can be correctly restored.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]