Rollback Table after Truncate Command in Sql Server


It is misconception among people that Truncate cannot be rolled back. But in reality Truncate operation can be Rolled Backed before Commit.Here is the Sql query

-- Create table 
CREATE TABLE Employee
                  ( EmpID INT PRIMARY KEY,
                    EmpSalary INT
                  )

-- Check data before Truncate
SELECT * FROM Employee









--Begin Transaction
BEGIN TRAN
--Truncate Table
TRUNCATE TABLE Employee
GO
--Check data after Truncate
SELECT * FROM Employee
GO







--Rollback Transaction
ROLLBACK TRAN
GO
--Check the data after Rollback
SELECT * FROM Employee
GO







Note:- If Transaction is done, mean Committed then we can not rollback Truncate command from log file but we can restore data using MSSQL Server Management Studio.

Check for difference between Delete and Truncate in next article.

Give your valuable comments it's encourage me to publish new articles.

Comments

Popular posts from this blog

Enable HTTP Transport Security (HSTS) in IIS 7

Extension Class in C#

When to use Truncate and Delete Command in SQL