CODEWITHSUNDEEP
sqlsql-serversql-server-2008transactionsssms
Stefan Steiger
Stefan Steiger

Reputation: 82296

How to rollback an implicit SSMS transaction (statement with go at the end)?

Question:
Normally, you can undo a sql command with rollback.

BEGIN TRY
  BEGIN TRANSACTION
  /* run all your SQL statements */
  COMMIT TRANSACTION
END TRY
BEGIN CATCH
  ROLLBACK TRANSACTION
END CATCH

My question now:
If 'one' did this

UPDATE TABLE_X SET FIELD_X = 'bla'
GO

in SSMS (notice the go at the end) and forgot to specify the WHERE clause, is it possible (and how) to rollback the implicit transaction that SSMS executed this command in (statements with go at the end are implicitly executed in a transaction) ?

Note:
I didn't do that, but a colleague of mine did a few days ago (without go).
I undid the damage he did (fortunately I made a backup 0.5 hours before he did that), but for the future, it would be good to know this, because this happened to me once, too.

Upvotes: 5

Views: 11545

Answers (3)

Josien
Josien

Reputation: 13877

GO does not specify the end of an implicit transaction, but the end of a batch. That's why you won't be able (unfortunately) to ROLLBACK your UPDATE after a GO.

From the MSDN page on GO:

GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor.

SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to an instance of SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO.

The UPDATE command will only be seen as the start of an implicit transaction if you have specified SET IMPLICIT_TRANSACTIONS ON; (see here). In that case, a number of commands (CREATE, DELETE, UPDATE etcetera) will automatically start a new implicit transaction, and that transaction will not end until you issue a ROLLBACK or a COMMIT.

(See for more info on the difference between transactions and batches in SQL Server for example this question on ServerFault: SQL Server: Statements vs. Batches vs. Transactions vs. Connections.)

Upvotes: 1

Arun Antony
Arun Antony

Reputation: 36

see the link below, I think it will help you

How to recover the old data from table

thanks Arun

Upvotes: 2

podiluska
podiluska

Reputation: 51504

No, you can't, not easily. Restoring from backup is the best option.

Upvotes: 4

Related Questions