Click here to Skip to main content
Click here to Skip to main content

Point In Time Restore

, 25 Feb 2011
Rate this:
Please Sign up or sign in to vote.
A very exciting feature in Microsoft SQL Server

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

  • 25th February, 2011: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Keshav Singh
Database Developer
India India
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".

Comments and Discussions

 
GeneralMy vote of 4 Pinmembersaumya verma27-Feb-11 22:26 
Good article
GeneralRe: My vote of 4 PinmemberKeshav Singh21-Mar-11 8:03 
GeneralMy vote of 5 Pinmemberryukiy26-Feb-11 13:42 
GeneralRe: My vote of 5 PinmemberKeshav Singh21-Mar-11 8:03 
GeneralFew comments PinmemberMika Wendelius26-Feb-11 4:36 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140821.2 | Last Updated 26 Feb 2011
Article Copyright 2011 by Keshav Singh
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid