MS SQL Server New Users - Can I undo an update done using query analyzer?

Author

Can I undo an update done using query analyzer?

Jim in Arizona

2005-11-04, 8:24 pm

The other day I did update query using query analyzer.

UPDATE ttickets
SET assing=NULL

I ran the query then realized that I forgot to put:
WHERE PK=118
at the end of the statements.

So, instead of changing a single row, I changed 155 rows.

When a mistake like this is made, is there some way to undo the mistake?

TIA,
Jim

SQL

2005-11-04, 8:24 pm

restore a backup as a different DB
grab the old values from the restore and update your table

or restore to a point in time (5 minutes before you did the update)
------------------------------------------------------------------------------------------
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/

Hugo Kornelis

2005-11-04, 8:24 pm

On Fri, 4 Nov 2005 13:19:30 -0700, Jim in Arizona wrote:

>The other day I did update query using query analyzer.
>
>UPDATE ttickets
>SET assing=NULL
>
>I ran the query then realized that I forgot to put:
>WHERE PK=118
>at the end of the statements.
>
>So, instead of changing a single row, I changed 155 rows.
>
>When a mistake like this is made, is there some way to undo the mistake?

Hi Jim,

If you were in an open transaction and didn't commit it yet:
ROLLBACK TRANSACTION

Otherwise, see the message by "SQL". Note that the second option he
mentions (retore to point in time) is only possible if you use the full
recvovery model, take regular log backups and follow the correct
procedure for a point-in-time restore (as described in Books Online).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Jim in Arizona

2005-11-04, 8:24 pm

I did some messing around with the ROLLBACK TRANSACTION and it work but only
if I started my mistake out with a BEGIN TRANSACTION statement, which I have
never done (but will start doing).

This database is new and no backups have been set up yet (they will be soon
though!). I corrected my mistake in a more painful way when it happened but
I'll be sure to take better safeguards in the future, such as BEGIN
TRANSACTION.

Thanks.

"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:gcjnm1hihl9gkkf
n6p39d0vhob44kerec4@
4ax.com...
> On Fri, 4 Nov 2005 13:19:30 -0700, Jim in Arizona wrote:
>
>
> Hi Jim,
>
> If you were in an open transaction and didn't commit it yet:
> ROLLBACK TRANSACTION
>
> Otherwise, see the message by "SQL". Note that the second option he
> mentions (retore to point in time) is only possible if you use the full
> recvovery model, take regular log backups and follow the correct
> procedure for a point-in-time restore (as described in Books Online).
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Kevin3NF

2005-11-08, 4:10 pm

if you are in full recovery mode, and the transaction log had not been
truncated (by a backup), you might have been able to use a log reading
utility such as www.lumigent.com 's Log Explorer or a similar too from
www.red-gate.com (don't recall the name offhand). Red Gate's toll has a
fully functional 14 day trial. I have not used it, but I love their other
utilities :-)

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm

"Jim in Arizona" <tiltowait@hotmail.com> wrote in message
news:uqnwkTZ4FHA.1416@TK2MSFTNGP09.phx.gbl...
>I did some messing around with the ROLLBACK TRANSACTION and it work but
>only if I started my mistake out with a BEGIN TRANSACTION statement, which
>I have never done (but will start doing).
>
> This database is new and no backups have been set up yet (they will be
> soon though!). I corrected my mistake in a more painful way when it
> happened but I'll be sure to take better safeguards in the future, such as
> BEGIN TRANSACTION.
>
> Thanks.
>
> "Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
> news:gcjnm1hihl9gkkf
n6p39d0vhob44kerec4@
4ax.com...
>
>

Sponsored Links