Introduction
I was back from office after the day’s work when my phone beeped. My pal at the office had accidently executed a DELETE
query without the where
clause resulting in all the records from the table being deleted. He asked me if there was any way if I could help him get all the records back in place. I asked him to take a transaction log back up immediately, and we would do a point in time restore. Let’s learn about this very exciting feature in Microsoft SQL Server.
In this example, my Setup is with Microsoft SQL Server 2008 R2 (Enterprise Edition) and I have created a TestDB
for our demo purpose.
CREATEDATABASE TestDB;
Use TestDB
GO
CREATETABLE Employee(
EID INTIDENTITY(1,1)PRIMARYKEY,
ENAME VARCHAR(50),
DEPT VARCHAR(20)
);
I have created an Employee
Table in the TestDB
database, and we will insert few sample records into this table.
INSERTINTO Employee
Values ('Rambo','IT'),
('Jason','Finance'),
('Brad','HR');
SELECT*FROM Employee;
EID ENAME DEPT
1 Rambo IT
2 Jason Finance
3 Brad HR
(3 row(s) affected)
Let’s take full back up at this point. Since I have a logical backup device (MY BackUP) added on my server, I would use it and take the full back up on it.
BACKUPDATABASE TestDB
TO [MY BackUP]
One could alternatively go with the below code and take a backup on the C: drive:
BACKUPDATABASE TestDB
TODISK='C:\TestDBFullBackup.BAK';
We can validate & confirm our backups with the below code:
SELECTBS.database_name,BS.backup_finish_date,BMF.logical_device_name,
BMF.physical_device_name
FROM msdb..backupset BS
INNERJOIN msdb..backupmediafamily BMF
ON BS.media_set_id=BMF.media_set_id
database_name backup_finish_date logical_device_name physical_device_name
SampleDB 2011-02-25 11:51:22.000 MY BackUPC:\Keshav\BackUPs\MY_BackUP.BAK
SampleDB 2011-02-25 12:15:04.000NULL C:\SampleDBFullBackup.BAK
(3 row(s) affected)
Looks like we are all set.
Let us insert few more records into our database.
INSERTINTO Employee Values('Fedrer','IT'),('Nadal','HR');
SELECT*FROM Employee;
EID ENAME DEPT
1 Rambo IT
2 Jason Finance
3 Brad HR
4 Fedrer IT
5 Nadal HR
(5 row(s) affected)
We have inserted 2 more records into our Employee
table.
Now, consider the scenario where I execute a delete
statement, let’s say at 02:13 PM 02/25/2011.
DELETEFROM Employee
This deletes all the records from the Employee
table. We now need to get all the records back prior to the point of deletion.
Solution: The idea is to perform a point in time restore and restore the database TestDB
prior to the deletion.
We take a transaction log backup of our TestDB
.
BACKUPLOG TestDB
TO [MY BackUP];
Let's perform a point in time restore now.
Step 1: We restore the full back up.
USE master
GO
ALTERDATABASE TestDB
SETSINGLE_USER
WITHROLLBACKIMMEDIATE;
RESTOREDATABASE TestDB
FROM [MY BackUP]
WITHFILE=3,NORECOVERY;
Step 2: Next let’s restore our transaction log back up and stop the restoration at 02:12 PM (just before we executed the delete
query).
RESTORELOG TestDB
FROM [MY BackUP]
WITHFILE=4,RECOVERY,STOPAT='Feb 25, 2011 02:12 PM';
It’s important to note that we have stopped the restore at precisely 02:12 PM. This will eliminate all the changes that happened to our DB after 02:12 PM.
Step 3: Lastly, we set the database in the Multi user mode so that it’s available for all the users.
ALTERDATABASE TestDB
SETMULTI_USER;
Step 4: Let’s validate the result.
Select*from TestDB..Employee
EID ENAME DEPT
1 Rambo IT
2 Jason Finance
3 Brad HR
4 Fedrer IT
5 Nadal HR
(5 row(s) affected)
As we can observe, we have been able to bring up the database to a point just before the ‘delete
’ query accidentally truncated the entire table.
In my prior organization, accidently almost 81,000 insurance claims where deleted on account of a very simple error by the developer. He provided an incorrect script to the DBA for execution in production. It somehow got approved and was executed resulting in a massive loss of 81,000 important trasactions. This feature came as a saviour and helped us restore the DB to a consistent state.
Hope this article adds a little value and helps beginner administrators in taking a call in such crunch situations.
History
- 25th February, 2011: Initial version
I am a Microsoft certified Technology Specialist in MS SQL Server 2008 and 2005.I have fair amount of experience and expertise in MS database development, administration and modeling and MS BI. Started my career in 2007 and primarily into MS databases, and later diversified into MS BI especially SSIS. I also have a little exposure in Oracle 10g while working on one of the migration projects. But MS SQL Server is my passion!
Firm believer in knowledge grows from sharing, I really like reading books, trying new features and sharing the little that I know. Unless we're willing to have a go, fail miserably, have another go, success won't happen. The best thing I have discovered about myself is the "never say die" attitude. The best comment I have ever received is when my manager at my first job said "when this guy works he throws away his watch".