IN TIME RECOVERY

What is a point in time recovery?
A point in time recovery is restoring a database to a specified date and time. When you have completed a point in time recovery, your database will be in the state it was at the specific date and time you identified when restoring your database. A point in time recovery is a method to recover your database to any point in time since the last database backup.
What does it take to do a point in time recovery?
In order to perform a point in time recovery you will need to have an entire series of backups (complete, differential, and transaction log backups) up to and/or beyond the point in time in which you want to recover. If you are missing any backups, or have truncated the transaction log without first performing a transaction log backup, then you will not be able to perform a point in time recovery. At a minimum, you will need a complete backup and all the transaction log backups taken following the complete backup. Optionally if you are taking differential backups, then you will need the complete backup, the last differential backup prior to the corruption, then all the transaction log backups taken following the differential backup.
What to consider once you know your database is corrupted
As soon as you know your database is corrupted, you need to consider a couple of things. The first thing to review is when were the last complete, differential, and/or transaction log backup taken. If there has not been a transaction log backup taken since the database was corrupted, then you should take one immediately. This is because the transactions prior to, plus the transaction that corrupted your database are contained in the current transaction log. Getting the current transaction log records into a transaction log backup will allow you to perform a point in time recovery up to when your database was corrupted.
A method to identify when the database got corrupted
Hopefully, you know the specific time frame when the database was corrupted. If you do not know exactly when the database was corrupted, but you would like to recover your database to the specific time just before it was corrupted, then what can you do? One method is to perform a series of point in time recoveries, then check the database for corruption after each recovery. You would keep doing recoveries until you narrowed down the time frame for when the corruption occurred. Keep in mind this method could be very time consuming. I would only consider doing this if you really need to recover up to the second prior to the corruption and you have unlimited time to perform multiple point in time restores to determine the exact time when the database was corrupted.
How to Perform a Point in Time Recovery (Simple example)


1 - Create a new database with name RecoverTest

CREATE DATABASE [RecoverTest] ON PRIMARY
( NAME =N'RecoverTest',FILENAME = N'D:\Database\Data\RecoverTest.mdf',
SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON ( NAME = N'RecoverTest_log',
FILENAME =N'D:\Database\Log\RecoverTest_log.ldf', SIZE = 1024KB ,
MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO


2- Take a full backup of database.
3 - Create 3 tables for doing the Truncate , delete and drop operations.
-- ==============================================
-- Create table Del1_15Dec for Truncate operation
-- ==============================================
create table Del1_15Dec
(
n1 int,
Date1 datetime default(Getdate())
)


Insert Into Del1_15Dec(n1) Values(1)
Insert Into Del1_15Dec(n1) Values(2)
Insert Into Del1_15Dec(n1) Values(3)
Insert Into Del1_15Dec(n1) Values(4)
Insert Into Del1_15Dec(n1) Values(5)
Insert Into Del1_15Dec(n1) Values(6)
Insert Into Del1_15Dec(n1) Values(7)
Insert Into Del1_15Dec(n1) Values(8)
Insert Into Del1_15Dec(n1) Values(9)
Insert Into Del1_15Dec(n1) Values(10)


-- ==============================================
-- Create table Del2_15Dec for delete operation
-- ==============================================
create table Del2_15Dec
(
n1 int,
Date1 datetime default(Getdate())
)


Insert Into Del2_15Dec(n1) Values(1)
Insert Into Del2_15Dec(n1) Values(2)
Insert Into Del2_15Dec(n1) Values(3)
Insert Into Del2_15Dec(n1) Values(4)
Insert Into Del2_15Dec(n1) Values(5)
Insert Into Del2_15Dec(n1) Values(6)
Insert Into Del2_15Dec(n1) Values(7)
Insert Into Del2_15Dec(n1) Values(8)
Insert Into Del2_15Dec(n1) Values(9)
Insert Into Del2_15Dec(n1) Values(10)


-- ==============================================
-- Create table Del2_15Dec for drop operation
-- ==============================================
create table Del3_15Dec
(
n1 int,
Date1 datetime default(Getdate())
)

Insert Into Del3_15Dec(n1) Values(1)
Insert Into Del3_15Dec(n1) Values(2)
Insert Into Del3_15Dec(n1) Values(3)
Insert Into Del3_15Dec(n1) Values(4)
Insert Into Del3_15Dec(n1) Values(5)
Insert Into Del3_15Dec(n1) Values(6)
Insert Into Del3_15Dec(n1) Values(7)
Insert Into Del3_15Dec(n1) Values(8)
Insert Into Del3_15Dec(n1) Values(9)
Insert Into Del3_15Dec(n1) Values(10)

4 – Make note of current time for example 9:20 am
5 – Perform some data removing activity on 9:25 am using script

-- ==============================================Perform truncate , delete and drop operation on Del1_15Dec, Del2_15Dec, Del3_15Dec
-- ==============================================

Truncate table Del1_15Dec
Delete from Del2_15Dec Where n1>5Drop table Del3_15Dec

RECOVERY PROCESS
Use point in time recovery, if you know the time when the table is delete/ drop / truncate
for example the table was delete/ drop / truncate today 9.20AM .
1 -  Very first take a Transaction log backup.
2 - Restore last full backup with norecovery option.
3 - Restore last differential backup if any with norecovery option.
4 - Apply all log files which backup before today 9.20AM with norecovery option.
5 - Apply last log backup which you have taken after the table dropped with stopat '9.24' with recovery.
Here the time 9.24 means the table was drop 9:25 so we recover the database till 9.24 in this time the table was in the database.

Follow the same procedure and restore the database with a new name or other destination till the time the table was in database then export the particular table and import it on expected location.