What is Tail log backup ? Why is it important on Restoration?

What is Tail log backup ? Why is it important on Restoration?

if some one needs some demo before going to read what is Tail log backup kindly go through my previous article about Tail log backup

http://sqltechi.blogspot.in/2016/06/restore-database-with-tail-log-backup.html

What is a Tail-Log Backups?

The tail-log backup captures records on the transaction log that were written since the last transaction log backup. If you’re going to restore a database to the point of failure, then you need to take a tail-log backup before you start the restore operation.

In which situation Tail-Log Backups is not required?

You do not need a tail-log backup if the recovery point is contained in an earlier log backup.
A tail-log backup is unnecessary if you are moving or replacing (overwriting) a database and do not need to restore it to a point of time after its most recent backup.

Why Tail-Log Backups have an incomplete Backup Metadata?

Tail log backups capture the tail of the log even if the database is offline, damaged, or missing data files. This might cause incomplete metadata from the restore information commands and msdb. However, only the metadata is incomplete; the captured log is complete and usable.

Can you take Tail-Log Backup in simple recovery model?

No, you can take Tail-Log backup only with full or bulk-logged recovery models.

In which scenarios, you require a Tail-Log Backup?

If the database is online and you plan to perform a restore operation on the database, begin by backing up the tail of the log.
If a database is offline and fails to start and you need to restore the database, first back up the tail of the log.
If a database is damaged, try to take a tail-log backup by using the WITH CONTINUE_AFTER_ERROR option of the BACKUP statement.

So

let see some examples

kindly see below Demo db with Customer Table in MSSQL 2012

 you can see CustomerID,AccountNumber,TerrortoryID columns has seen in Customer Table in Demo db
in SQL 2012

You can see table value

 Now we need take Backup in Demo db as see below

Now you changed some data correction on customer table like see below

Now you have taken Transaction log backup as see below

Now after few hours back you have just changed some correction on Table as see below

Now if you decided to go Restore Table or if DB Was in failure Mode or not started well kind of situation tail log backup should be take to recover point in time

If you are going to Restore DB last changes like ALTER TABLE & Update Statuss column changes should not be recovered. So from SQL 2012 Will intimate you when tried to take RESTORE as see below

Kindly check if any session have using our restoring database or kindly use master in all session otherwise you will get below error as see below

so kindly use master in T-SQL in Restore command also ensure no connections use RESTORE DB

like use Currently Running Query to check whether any query or objects using Restoring db or not

use master
SELECT 'Currently Running Query'as RunningQuery,DATEDIFF(MINUTE, a.last_batch, GETDATE()) RunningTime,
a.spid, a.blocked, a.waittime, db_name(a.dbid) As dbname,b.text as sqlstatement,
a.hostname, a.loginame,a.program_name,a.last_batch,a.nt_username
FROM sys.sysprocesses A CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) B
WHERE A.dbid > 4 

then after ensure there in no connection using Restoring db then use below command

use master

RESTORE DATABASE Demo
FROM DISK='F:/Demo_bk.bak' WITH NORECOVERY

So you need to take Tail Log Backup Before you start your Restoration Process

So Now db in Restorin mode after Tail log backup

Now you can start to Restore to recover to one by one

Now you needs to restore Transaction log backup as see below

So if you decided to point in time recovery last transaction(alter table add column with update) then you should restore Tail log backup

Now you can see last transaction details as see below

That is it!!!!!!!

Note:

1)
Whenever Database in Restoring Mode you can able to Recover immediately using following command

RESTORE DATABASE DBNAME WITH Recovery 


 2)
You can't able to Database with Restoring mode with following command it is not valid it give message as see below

 RESTORE DATABASE DBNAME WITH NoRecovery

 3)
Restore database Should Start/Initiate with Full backup
that is Restore with following Scenario

1)Full Backup + Transaction Log Backup etc 

2)Full Backup + Differential Backup+Transaction Log Backup1+Transaction Log Backup2 etc

3)Full Backup + Differential Backp1+Transaction Log Backup1+Full Backup2+Transaction Log Backup2+Transaction Log Backup2 etc  


 You can't able to Restore only start with Transaction Log Backup/Differential Backup it will give error as see below
That is start with transaction log backup without full backup
Restore DB with transaction log backup  as see below