Point In Time Restore






4.60/5 (5 votes)
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