Database reckless modifications. ROLLBACK and COMMIT cure.
One day you might be forced to run some T-SQL scripts on a production database and accidentally delete/update all records in a table instead of specified in a where statement. It happened to me in the past it might happen to you as well. Usually, you think that the world is over. Then you remember about backup, but still, there is a possibility that some data would be lost forever. The best solution is as usual prevention of a disaster.
There is a reason why SSMS Tools pack by default creates the new queries:
BEGIN TRAN ... ROLLBACK
Use transaction for all your modifications. Inside a transaction, you can test if your update/delete does exactly what you want it to do.
Here is a little illustration:
CREATE TABLE TestTable ( Id int identity primary key , [Description] varchar(max) ) GO BEGIN TRAN insert into TestTable values ('Test1'), ('Test2'), ('Test3') select * from TestTable -- First query, returns all records delete TestTable where Id=2 select * from TestTable -- Second query, returns two records ROLLBACK -- COMMIT select * from TestTable -- Third query, no records because of ROLLBACK
After you are done with your testing, just change ROLLBACK to COMMIT.
BEGIN TRAN and ROLLBACK protects you from logical mistakes and syntax errors. However, some exceptions stop execution leaving the transaction open. For example:
BEGIN TRAN CREATE TABLE Clients ( Id INT PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50) ) INSERT INTO Clients VALUES (1,'Viktar','Karpach') INSERT INTO Clients_No_Such_Table VALUES (2,'Joe', 'Doe') INSERT INTO Clients VALUES (3,'Mike', 'Skott') SELECT * FROM Clients ROLLBACK
(1 row(s) affected)
Msg 208, Level 16, State 1, Line 11
Invalid object name ‘Clients_No_Such_Table’.
As you can see SELECT statement was never executed:
SELECT * FROM Clients SELECT @@TRANCOUNT
|(No column name)|
This SQL Server behavior can be fixed by using SET XACT_ABORT ON, so a default query snippet becomes:
SET XACT_ABORT ON BEGIN TRAN ... ROLLBACK
Or you can use a longer version snippet with BEGIN TRY / END TRY:
BEGIN TRY BEGIN TRANSACTION SET NOCOUNT ON ... --ROLLBACK COMMIT TRAN -- Transaction Success! END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRAN --RollBack in case of Error DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); -- Use RAISERROR inside the CATCH block to return -- error information about the original error that -- caused execution to jump to the CATCH block. RAISERROR (@ErrorMessage, -- Message text. @ErrorSeverity, -- Severity. @ErrorState -- State. ); END CATCH