Today, we will discuss, how to recover the deleted data from SQL Server. We have two methods to do it.
- Through SQL SERVER LOG
- Through Backup
The first method I have already discussed in my previous article “How to recover deleted data from SQL Server”.
Today we will discuss how to recover it via database backup.
Mostly, we have a perception that if we have a FULL BACKUP before deletion we can easily restore it and can get the old data.
But this is wrong perception, even if you have an earlier full backup was taken about 4 to 8 hours before, you can still recover your deleted records.
I presume your database is in FULL Recovery model.
Let proceed this with the Step by Step approach. But remember date and time is very important in all the recovery process.
Step 1 :
First of all, take a full backup of the database.
BACKUP DATABASE test TO DISK = 'c:\TEST_FULL_BACKUP.bak' GO
Lets say, we took this full back up at 6:00 AM.
Step 2 :
Lets create one table and insert few records in it. But remember we took the full backup before doing all these transactions.
Create Table Test_Table ( [ID] int, [Designation] varchar(50) ) Insert into Test_Table ([ID],[Designation]) Values(1,'Officer') Insert into Test_Table ([ID],[Designation]) Values(2,'Manager') Insert into Test_Table ([ID],[Designation]) Values(3,'Director')
We did all these transaction at 9:00 AM.
Step 3 :
Lets Delete the record. Lets say at 10.00 AM
Delete from Test_Table
Step 4 :
Lets check the records in the table
Select * from Test_Table
Step 5 :
Now, lets start the process of recovery.
First take a transaction log backup after deletion.
BACKUP LOG TEST TO DISK = 'C:\TEST_TRANSACTION_LOG.bak' WITH NORECOVERY; GO
Step 6 :
After that restore the database FULL BACKUP, that we took at 6.00 AM
RESTORE DATABASE test FROM DISK = 'c:\TEST_FULL_BACKUP.bak' WITH NORECOVERY; GO
Step 7 :
After that, lets restore the transaction log backup (we took after deletion) and specify the time. In the above example we deleted records at 10.00 AM. So we will restore the transaction log by 9:59 AM.
RESTORE LOG test FROM DISK = 'C:\TEST_TRANSACTION_LOG.bak' WITH RECOVERY, STOPAT = 'Oct 09, 2012 09:59:00 AM'
Step 7 :
In the final step, you can browse the table and view the records what you have deleted. Now the records are recovered.
Select * from Test_Table
Recovering deleted records from Full backup is a good technique. Thanks for sharing this informative article.
Thanks for sharing this useful information. It will help to solve the data loss problems. Recently, I was also experiencing data loss problem due to corruption in my sql database and fixed with the help of a third party tool: http://www.serversdatarecovery.com/sqldatabase.html
Thanks
Smith
Thanks Imran… thanks for sharing great article.
Hi Imran,
Is it work after commit the transaction.
Hi Govind,
Yes, it works after commit transaction also.
Thank you.
Imran
It’ really a great job.
keep it buddy.
Thank you for sharing this article.
Great …
i m a big fan of u imran
Hello! sir i’ve read ur article but while executing the code in sql server 2008 it gave me exception, so plz can u gave me idea of how to to recover deleted records here is my
database name : NSS_DB and
table name as Applicant
with table structure as:
CREATE TABLE [dbo].[Applicant](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[appName] [nvarchar](60) NULL,
[appRollNo] [bigint] NULL,
[appClass] [nvarchar](10) NULL,
[appdivision] [nvarchar](2) NULL,
[appDate] [date] NULL,
[usrUserId] [nvarchar](50) NULL,
[appCollgeCode] [nvarchar](10) NOT NULL,
[appStudCode] [nvarchar](10) NOT NULL,
[appYear] [int] NOT NULL,
[appDegree] [nvarchar](30) NULL,
[appImage] [image] NULL,
[appPersonalMobNo] [nvarchar](12) NULL,
[AdminId] [nvarchar](36) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
waiting for ur suggestions,
Thank You
Hi,
Can you please share the exception message. Also please share some sample data.
Imran
Thanks Imran for the useful tips to recover sql server. However, there are some situations when CHECKDB and DBCC command line tools do not work. In such cases, a third party sql database recovery application may help to repair and recover data.
One such globally used tool that worked in our case: http://www.recoverydeletedfiles.com/sql-database-recovery-software.html
Hope it will also help to those who are also facing similar problem.