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.

What is Tail-Log backup and how to take?

The tail-log backup is simply a regular transaction log backup that's been generated since the most recent log backup was taken just before when you’re going to restore a database to the point of failure.

Tail-log backups are new to SQL Server 2005. Tial-log backup is relevant to only following recovery models
1. Full Recovery Model
2. Bulk-logged Recovery Model

NOTE: The only exception is when there has been a minimally-logged operation performed in the BULK_LOGGED recovery model since the previous log backup – in that case a tail-log backup is not possible at all, and you'll have lost all transaction log generated since the last log backup.

There are two cases to consider here
1) SQL Server still available
2) SQL Server not available

CASE 1. SQL Server still available
It is simple to take the tial-log backup of database if the server is available.

Create Database DBTestTLog;
GO
Use DBTestTLog;
GO
CREATE TABLE myTrans 

    (ID INT IDENTITY,
     DATE CHAR(20));
GO

Now backup the database

BACKUP DATABSE DBTestTLog TO DISK = 'C:\DBTestTLog\DBTestTLog_Full backup.bak' WITH INIT;
GO

INSERT INTO myTrans VALUES ('Data1')
INSERT INTO myTrans VALUES ('Data2') 

BACKUP LOG DBTestTLog TO DISK = 'C:\DBTestTLog\DBTestTLog_Log1.trn' WITH INIT;

INSERT INTO mytrans VALUES ('Data3')
INSERT INTO myTrans VALUES ('Data4')

To create the disaster situation i will do the following:

1) ALTER DATABASE DBTestTLog SET OFFLINE;
2) Delete the data file

ALTER DATABASE DBTestTLog SET ONLINE;

When you try to bring the database online you will get the following error message

Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DBTestTLog.mdf". Operating system error 2: "2(The system cannot find the file specified.)".
Msg 945, Level 14, State 2, Line 1
Database 'DBTestTLog' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

BACKUP LOG DBTestTLog TO DISK = 'C:\DBTestTLog\DBTestTLog_LOG2.trn' WITH INIT;

When to try to take the log backup of database you will encounter the following error

Msg 945, Level 14, State 2, Line 1
Database 'DBTestTLog' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.

It doesn't work – as the data files aren't all accessible.

The trick is to use the NO_TRUNCATE option, which allows the log backup even if the database files aren't there:

BACKUP LOG DBTestTLog TO DISK = 'C:\DBTestTLog\DBTestTLog_LOG2.trn' WITH INIT, NO_TRUNCATE; 

Processed 2 pages for database 'DBTestTLog', file 'DBTestTLog_log' on file 1.
BACKUP LOG successfully processed 2 pages in 0.012 seconds (0.773 MB/sec).

I can then use this tail-log backup as the final backup in the restore sequence, saving transactions 3 and 4 from above.

I will update this document with the CASE 2 : SQL Server not available