Sql-server – Take Tail-Log backup before restore option

I turned to my copy of SQL Server 2008 Internals and the DMV sys.database_recovery_status was pointed out to find the first LSN of the next log backup. Which going by BOL the column last_log_backup_lsn provides you with:

Log sequence number of the most recent log backup. This is the end LSN of the previous log backup and the starting LSN of the next log backup.
NULL= No log backup exists. The database is offline or the database will not start.

Just to mention as well that Kalen also brings up the point that you will get a NULL value if the database is in SIMPLE recovery mode (autotruncate mode) or if no log backup exist.

But how can I obtain the FirstLSN of the tail log?

Without actually backing up the tail log of a database (don't have an test instance to try this on) you could logically conclude that the value returned in the column mentioned would be the first LSN of the next log backup, in your case the tail.

So executing the following will return the value I believe you are looking for:

SELECT 
   last_log_backup_lsn
FROM 
   sys.database_recovery_status
WHERE 
   databse_id = DB_ID('MyDb')

This DMV is available starting in SQL 2005.

EDIT
Unless you read the BOL link, please note that this DMV will only return values to databases that are online, or opened as BOL references it. If a failure occurs that requires you to take a tail log backup of a database you will not be able to verify this value through the above code unless the database is accessible; which in a failure it probably would not be.

Paul Randal did a good piece on backing up the tail of the log that includes just this case. In it he says that the method to use when there is no data file is using NO_TRUNCATE. Based on that your command should be:

BACKUP LOG [AdventureWorks2012] TO
DISK = N'C:\sqldata\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorks2012.bak' 
WITH NAME = N'AW Tail Log Backup', NO_TRUNCATE

BOL does say

To perform a best-effort log backup that skips log truncation and then take the database into the RESTORING state atomically, use the NO_TRUNCATE and NORECOVERY options together.

So you could try using both of them together but I'm not sure if NORECOVERY can be used if the data file is missing or not, even with NO_RECOVERY.

BACKUP LOG [AdventureWorks2012] TO
DISK = N'C:\sqldata\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorks2012.bak' 
WITH NAME = N'AW Tail Log Backup', NO_TRUNCATE, NORECOVERY

It's certainly worth testing anyway.