Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 128
  • Last Modified:

T-SQL - Rollback or Undo Changes

I am relatively new to T-SQL... coming from years of using MS Access.
'Un-doing' a change in Access was relatively easy, even backing it up and restoring, if needed.

What is the easiest way to un-do something in MS SQL, without a full restore?

Thanks,

E.D.
0
Frank Grimes
Asked:
Frank Grimes
3 Solutions
 
lcohanDatabase AnalystCommented:
Easiest is not the best..you need to wrap each and any of your transactions in a explicit BEGIN/COMMIT but again..just do NOT do that as you'll be looking for trouble in MSSQL server.
https://www.mssqltips.com/sqlservertutorial/3305/what-does-begin-tran-rollback-tran-and-commit-tran-mean/

Best approach in my opinion would be to run a BEGIN/TRY/CATCH sequence and rollback on any error. More learning and coding but infinitely better and faster and less (actually nothing at all) trouble.
https://www.simple-talk.com/sql/database-administration/handling-errors-in-sql-server-2012/
https://technet.microsoft.com/en-us/library/ms179296(v=sql.105).aspx


Other than that you could play with different type of connections/transactions isolation levels but again, that's maybe troublesome if you don't know exactly what you are doing.
https://msdn.microsoft.com/en-CA/library/ms173763.aspx
0
 
Eugene ZCommented:
it is a very similar to VBA.
..additionally to Rollback (above posts)
you can try
SET XACT_ABORT
/
When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.
/
more
https://msdn.microsoft.com/en-us/library/ms188792.aspx
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
UNDO doesn't exist in SQL Server.
You can always work with transactions so at the end you can decide to COMMIT (accept) or ROLLBACK (discard) the changes.

Backup and Restore exists in all DBMS so SQL Server isn't an exception but we should be aware of the different backup types and the way to restore them.
0
 
Frank GrimesAuthor Commented:
Thanks guys!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now