Solved

T-SQL - Rollback or Undo Changes

Posted on 2016-09-09
4
26 Views
Last Modified: 2016-09-14
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
Comment
Question by:edalzell01
4 Comments
 
LVL 39

Accepted Solution

by:
lcohan earned 250 total points
ID: 41791758
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
 
LVL 42

Assisted Solution

by:EugeneZ
EugeneZ earned 125 total points
ID: 41793289
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
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 125 total points
ID: 41793846
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
 

Author Closing Comment

by:edalzell01
ID: 41798374
Thanks guys!
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now