Point In Time Restore

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');
/*This is a new feature in SQL Server 2008 for constructing your 
insert query to be able to handle multiple inserts in one go.  */
SELECT*FROM Employee;
--The output of our select statement shows three records inserted.
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.

--Logical Backup 
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.

/* This sets the database in a single user mode and 
all the active sessions are terminated */
USE master
GO
ALTERDATABASE TestDB
SETSINGLE_USER
WITHROLLBACKIMMEDIATE;

RESTOREDATABASE TestDB
FROM [MY BackUP]
WITHFILE=3,NORECOVERY;
/*Our initial full database restoration is complete, 
this restores our Employee table to the initial 3 records */

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

• 25^th February, 2011: Initial version