Database disaster recovery ( tail log restore )

1) Recover database to last committed point.

Scenario 1

10AM FULL backup.
10.30AM Differantial backup
11AM Log file backup ( may be schedule every hour etc..)


If the database crashed 11AM  , you can recover database up to 11AM as below

Restore full backup as NORECOVERY
RESTORE DATABASE [azure_test] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\azure_test.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5

Restore differantial backup as NORECOVERY
RESTORE DATABASE [azure_test] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\azure_test_diff.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5

Restore log backup as RECOVERY
RESTORE LOG [azure_test] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\azure_test_log.bak' WITH  FILE = 1,  RECOVERY,  NOUNLOAD,  STATS = 5

Note: if you have multiple log backups, set RECOVERY option only for last log backup restore command, all the previous restore command should be restored with  NORECOVERY option.

OR Restore log backup for specific time you know
RESTORE LOG [azure_test] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\azure_test_LogBackup_2016-02-05_10-32-48.bak' WITH  NOUNLOAD,  STATS = 5,  STOPAT = N'2016-02-05T10:33:57'


Scenario 2

10AM FULL backup.
10.30AM Differantial backup
11AM Log file backup ( may be schedule every hour etc..)

Database crashed at 11.30AM, in here you have only 11AM log backup. but user have done transactions ( insert,update,create table etc.. ) after 11AM, now you need to recover database 11.29AM point.

Tail Log requirement 

The tail of the log contains the transaction log that’s been generated since the most recent log backup was taken. If you want to be able to recover right up to the point of the disaster, you need to be able to get those log records so they can be part of the restore sequence.

The above case required Tail log backup in order to restore database up to 11.29AM

Tail-of-the-log backup when server is available ( SQL server running but your database not online ) 

BACKUP LOG azure_test TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\azure_test_tail_LogBackup.bak' WITH INIT, NO_TRUNCATE;

GO

As long as you have tail log backup you can follow below the steps to restore database to 11.29AM

Restore full backup as NORECOVERY

RESTORE DATABASE [azure_test] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\azure_test.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5

Restore differantial backup as NORECOVERY

RESTORE DATABASE [azure_test] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\azure_test_diff.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5

Restore log backup as RECOVERY

RESTORE LOG [azure_test] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\azure_test_log.bak' WITH  FILE = 1,  RECOVERY,  NOUNLOAD,  STATS = 5

Note: if you have multiple log backups set RECOVERY only for last log backup, all the previous should be resotred with  NORECOVERY

Restore tail log backup for specific time you know

RESTORE LOG [azure_test] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\azure_test_tail_LogBackup.bak' WITH  NOUNLOAD,  STATS = 5,  STOPAT = N'2016-02-05T11:29:57'