Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
funkyspirit
Starting Member
9 Posts |
 Posted - 2012-10-08 : 06:16:27
|
| Hello,I made a mistake on an SQL command. It applied changes to a table where it shouldn't have.Is there a way to cancel the effects of the command?Thank you. |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-10-08 : 06:23:15
|
Not on a committed transaction.Do you have a backup?If so then it is possible to restore the database with another name and then take the data from the needed table...
Too old to Rock'n'Roll too young to die. |
 |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-10-08 : 07:07:55
|
Probably not of much help to you now, but usually when I have to make updates to critical production tables, I do something like shown below. First I will execute only the parts shown in green. Then quickly look over the number of rows affected to make sure that it is what I expect it to be, and then run the code in red.On critical production tables, it has to be done quickly because between the two executions you are keeping the tables locked (or at the very least the rows affected)BEGIN TRANUPDATE MyMostImportantTable SET userId = 0;ROLLBACKCOMMIT I have that ROLLBACK in between because if I accidentally execute the code, no harm would be done. |
|
|
|
funkyspirit
Starting Member
9 Posts |
Posted - 2012-10-08 : 10:10:39
|
quote:
Originally posted by webfred
Not on a committed transaction.Do you have a backup?If so then it is possible to restore the database with another name and then take the data from the needed table...
Too old to Rock'n'Roll too young to die.
Thank you for your message. How would I take data from just one table?Thanks. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-10-08 : 10:15:57
|
quote:
Originally posted by funkyspirit
quote:
Originally posted by webfred
Not on a committed transaction.Do you have a backup?If so then it is possible to restore the database with another name and then take the data from the needed table...
Too old to Rock'n'Roll too young to die.
Thank you for your message. How would I take data from just one table?Thanks.
That was my point.  restore the database with another name and then take the data from the needed tableMaybe "another name" isn't clear/bad english?What I meant was "a different name".
Too old to Rock'n'Roll too young to die. |
|
|
|
funkyspirit
Starting Member
9 Posts |
Posted - 2012-10-10 : 03:17:20
|
quote:
Originally posted by webfred
quote:
Originally posted by funkyspirit
quote:
Originally posted by webfred
Not on a committed transaction.Do you have a backup?If so then it is possible to restore the database with another name and then take the data from the needed table...
Too old to Rock'n'Roll too young to die.
Thank you for your message. How would I take data from just one table?Thanks.
That was my point. restore the database with another name and then take the data from the needed tableMaybe "another name" isn't clear/bad english?What I meant was "a different name".
Too old to Rock'n'Roll too young to die.
Thank you for your response.I have tried creating a new database using another name and then restoring the backup of the database that has the problem to the one I have newly created. However, SQL server tells me that it cannot do it because the nf file cannot be overwritten.Is there another method I could use? Sorry, I am fairly new to SQL server.Thank you. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
|
|
|
|
|
Cancel an SQL command