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
