I want to introduce new thing to recover “Truncated” or “Deleted” record from MS SQL Server. This is very simple way to recover our data without any cost. I have tired this with SQL server 2008, 2010 and 2012.
Note : If you want to recover your data, please do not perform any operation after “Truncate” or “Delete” operation.
Before performing any operations please run stored procedures, which I have attached at the end of the post.
Sample table with same data:
1) Create Table
CREATE TABLE [dbo].[User](
[Id] [int] NULL,
[Name] [varchar](50) NULL
) ON [PRIMARY]
Now I will enter some test data in the new table
2) Insert Record
INSERT INTO [USER] VALUES (1,‘User1’)
INSERT INTO [USER]VALUES (2,‘User2’)
INSERT INTO [USER] VALUES (3,‘User3’)
INSERT INTO [USER]VALUES (4,‘User4’)
INSERT INTO [USER] VALUES (5,‘User5’)
INSERT INTO [USER]VALUES (6,‘User6’)
Now I will delete some data so that we can try recover deleted data
3) Delete Record
DELETE from [User] where Id between 4 and 6
Now you can see that record from 4 to 6 are deleted
Now to recover the deleted data I did following step
4) Run Command for Recover Deleted data with required field
EXEC Recover_Deleted_Data_Proc‘DBName’,‘dbo.Table name’
You can also pass data range from and to. Date format should be yyyy/mm/dd.
You see in the attached screen shot that records are recovered once executed the stored procedure
Then I did following steps to recover truncated table
Note that You can also pass data range from and to. Date format should be yyyy/mm/dd.
Run Command for Recover Truncated data with required field
–Truncate table [User]
Now I executed following command to Recover Truncated table with required field
–EXEC Recover_Truncated_Data_Proc‘DBName’,‘dbo.Table name’
Click to view Stored procedure To Recover Truncated Data