SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Cancel an SQL command
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

funkyspirit
Starting Member

United Kingdom
9 Posts

Posted - 10/08/2012 :  06:16:27  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8760 Posts

Posted - 10/08/2012 :  06:23:15  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/08/2012 :  07:07:55  Show Profile  Reply with Quote
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 TRAN

UPDATE MyMostImportantTable SET userId = 0;

ROLLBACK
COMMIT 
I have that ROLLBACK in between because if I accidentally execute the code, no harm would be done.

Edited by - sunitabeck on 10/08/2012 07:08:19
Go to Top of Page

funkyspirit
Starting Member

United Kingdom
9 Posts

Posted - 10/08/2012 :  10:10:39  Show Profile  Reply with Quote
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.
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8760 Posts

Posted - 10/08/2012 :  10:15:57  Show Profile  Visit webfred's Homepage  Reply with Quote
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 table

Maybe "another name" isn't clear/bad english?
What I meant was "a different name".


Too old to Rock'n'Roll too young to die.
Go to Top of Page

funkyspirit
Starting Member

United Kingdom
9 Posts

Posted - 10/10/2012 :  03:17:20  Show Profile  Reply with Quote
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 table

Maybe "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.
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8760 Posts

Posted - 10/10/2012 :  04:33:14  Show Profile  Visit webfred's Homepage  Reply with Quote
Is this helpful?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=112206


Too old to Rock'n'Roll too young to die.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000