Introduction
This is required to understand that when we work on SQL Server Management Studio, all the DML operations are directly done on database and if any unexpected command is executed, one can experience data loss permanently.
To recover from this situation, one has to restore the backup, but this has its own problems:
- Database will be set back to the state where previous backup was done; this will result in loss of the changes done after the backup was taken.
- Need to contact DB admin to perform the backup restore.
- Whole database outage before restore completes and many more…
Using the Code
To overcome this problem, we can execute all our queries in a system created transaction per session; to make this setting work, you need to make the following changes in your SSMS:
Configure Auto-commit in SSMS
- Connect to your SQL Server using SSMS
- From the Menu bar, select Tools –> Options-> Query Execution –> SQL Server –> ANSI
- Set
SET IMPLICIT_TRANSACTIONS checked to open the Transaction automatically for you when you open a new query tab. - If you are sure, type “Commit Transaction” to commit your changes.
- If anything gone wrong, type ”Rollback Transaction” and you are safe; all changes made on the tab (i.e. current session) will be reverted.
Pros
Avoid accidental DML action
Cons
Transactions are open, so table may get locked for other users; until they are committed/rollback.
