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.