How to perform Point-in-Time Recovery (PITR)?

Restoring a database to a particular time just before any unwanted data changes using database backups is know as Point-in-Time Recovery of a database.

Point-in-Time Recovery is also know as incomplete database recovery because in PITR we will not restore and recover full database.

In SQL Server Point-in-Time Recovery is only possible under following recovery models:

1. Full recovery model
2. Bulk-Logged recovery model

NOTES:
1. Under Simple recovery model this PITR functionality will not work.
2. Under Bulk-Logged recovery model if we have any bulk logged operations such as create index or bulk insert then Point-in-Time recovery is not possible because these operations are minimally logged.

Real Scenario
A Database 'test' is configured in Full-recovery model and  is scheduled for full database backup every morning 4 AM and differnetial backup for every 3 hours and transactional log backup occurs every 30 minutes. At 10:58 AM a database user unfortuntly executed a TRUNCATE TABLE against this database and at 11:05 AM he ask you to restore and recover the database till 10:57 AM.

Below are the steps to be followed to perform database Point-in-Time Recovery

1. Firstly restore full backup WITH NORECOVERY option
2. Then restore the all differential backups WITH NORECOVERY option
3. Restore the transactional log backup WITH RECOVERY and STOPAT option

In this case we have full backup occur at 4 AM and then differential backups at 7 AM and 10 AM respectively and then transactional log backups at 10:30 AM and 11:00 AM.

RESTORE DATABASE test
FROM DISK = 'D:\Backup\test_full_backup_0400.bak'
WITH NORECOVERY

RESTORE DATABASE test
FROM DISK = 'D:\Backup\test_diff_backup_0700.bak'
WITH NORECOVERY

RESTORE DATABASE test
FROM DISK = 'D:\Backup\test_diff_backup_1000.bak'
WITH NORECOVERY

RESTORE LOG test
FROM DISK = 'D:\Backup\test_log_1030.trn'
WITH NORECOVERY

RESTORE LOG test
FROM DISK = 'D:\Backup\test_log_1100.trn'
WITH RECOVERY,
STOPAT = 'Sep 21, 2013 10:58:00 AM'

The last restore command is always WITH RECOVERY and STOPAT command.