How to undo SQL Server Updates

In this blog we will see how to undo SQL Server updates (DELETE, UPDATE, TRUNCATE) operations. Before we begin I want to underline this totally not for a production environment but something you could use on your DEV machine while testing your DML SQL scripts.
That being said let us see how we could undo our changes in action.
Well there is no a magic undo button or CTRL + Z shortcut like MS Word in SQL Server. However, we could workaround to create the same means to rollback the changes we made by our data manipulation script using transactions. Let us start with update operation

--====================UPDATE Operation=====================--
--check our table
SELECT P.BusinessEntityID ID,P.FirstName,P.LastName,E.JobTitle,E.VacationHours
FROM HumanResources.Employee E
	JOIN Person.Person P ON E.BusinessEntityID = P.BusinessEntityID;

--UPDATE All employees VationHous by 2 hours
BEGIN TRAN
UPDATE E SET E.VacationHours +=2
FROM HumanResources.Employee E;

--Check the new changes we made. Use WITH(NOLOCK) table hint
SELECT P.BusinessEntityID ID,P.FirstName,P.LastName,E.JobTitle,E.VacationHours
FROM HumanResources.Employee E WITH(NOLOCK)
	JOIN Person.Person P WITH(NOLOCK) 
		ON E.BusinessEntityID = P.BusinessEntityID;

--Select to commit or rollback. Since our demo is UNDO let us rollback
ROLLBACK TRAN;	
 
--Check check if we have the same data again.
SELECT P.BusinessEntityID ID,P.FirstName,P.LastName,E.JobTitle,E.VacationHours
FROM HumanResources.Employee E WITH(NOLOCK)
	JOIN Person.Person P WITH(NOLOCK) 
		ON E.BusinessEntityID = P.BusinessEntityID;

--====================DELETE Operation=====================--
--Check if all data is deleted
SELECT *
FROM Person.PersonPhone;

--delete all data in the table inside a transaction
BEGIN TRANSACTION
DELETE FROM Person.PersonPhone;
go

--Check if all data is deleted
SELECT *
FROM Person.PersonPhone;

--UNDO changes
ROLLBACK TRANSACTION;
 
 
--Check check if we undo delete operation.
SELECT *
FROM Person.PersonPhone;

--====================TRUNCATE Operation=====================-
BEGIN
TRUNCATE TABLE Person.PersonPhone;

--Check if all data is deleted
SELECT *
FROM Person.PersonPhone;

--UNDO changes
ROLLBACK TRAN;
 
--Check check if we undo the truncate operaton
SELECT *
FROM Person.PersonPhone;

This concludes our small experiment. Some points to take are you should rollback your transaction otherwise if you commit them there is no way to rollback again. Make sure you don’t try this on PROD and if you share your DEV server with out people be sure you are not blocking resources for them.