Click here to Skip to main content
13,000,469 members (64,521 online)
Click here to Skip to main content
Add your own
alternative version


18 bookmarked
Posted 25 Feb 2011

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


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.

Use TestDB

I have created an Employee Table in the TestDB database, and we will insert few sample records into this table.

Values ('Rambo','IT'),
/*This is a new feature in SQL Server 2008 for constructing your 
insert query to be able to handle multiple inserts in one go.  */
--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 
TO [MY BackUP]

One could alternatively go with the below code and take a backup on the C: drive:


We can validate & confirm our backups with the below code:

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');

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.


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.

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

/*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).

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.


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.


  • 25th February, 2011: Initial version


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


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".

You may also be interested in...


Comments and Discussions

GeneralMy vote of 4 Pin
saumya verma27-Feb-11 22:26
membersaumya verma27-Feb-11 22:26 
GeneralRe: My vote of 4 Pin
Keshav Singh21-Mar-11 8:03
memberKeshav Singh21-Mar-11 8:03 
GeneralMy vote of 5 Pin
ryukiy26-Feb-11 13:42
memberryukiy26-Feb-11 13:42 
GeneralRe: My vote of 5 Pin
Keshav Singh21-Mar-11 8:03
memberKeshav Singh21-Mar-11 8:03 
GeneralFew comments Pin
Mika Wendelius26-Feb-11 4:36
memberMika Wendelius26-Feb-11 4:36 

The article describes the mechanism to restore a database to a certain point of time. However there are few very important things that, in my opinion, are missing.
- You we're able to save your data since you we're able to do log backup, this is not always the case. I believe that this article should state and explain why the log backup was possible so the recovery models should be covered. Without understanding the basics behind the log backup the reader may end up to a really, really disasterous situation if he/she expects that the steps mentioned in this article will work on every database.
- Log chain (or better yet the transaction chain) handling should be explained. It's very easy to end up into a situation where you wouldn't have been able to do the log backup at all (and restore)
- And a detail about the formatting: The code block are not formatted well. There are spaces missing from the statements etc.

I believe that if you make these additions and modifications, this article will contain good basic information.


p.s. I'll give my vote later since I believe that it's going to be far better after the modifications.
The need to optimize rises from a bad design.My articles[^]

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

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

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170624.1 | Last Updated 26 Feb 2011
Article Copyright 2011 by Keshav Singh
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid