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.
If your database is in the full recovery model, you should immediately start taking log backups along with the full backups. For simple backup recovery model, you will not need log backups. The frequency depends on your required SLA (RTO & RPO), more on it here.
Second, you should definitely be automating your backup tasks - both full and log backups (if you are taking log backups). There are hundreds of reasons that can justify automating the tasks, my favorite one is I don't have to run/repeat the task again in a well planned automation.
In order to automate your backups using SQL Server Agent jobs, you can either write your won scripts, test it and implement to suit your environment, or pick up one of the freely available scripts and modify to suit your need. My favorite is Ola Hallengren's free sets of scripts, which he publishes and updates on his website. These scripts have been around for a while, and are well tested in various SQL Server environments.
Restore
Assuming your database is in full recovery mode, and you have taken your full backups and log backups, following are a simplified steps for database recovery.
Perform the tail-of-the-log backup to backup with NORECOVERY
option. This is the log generated since the last log backup was
executed.
Restore the most recent full backup with NORECOVERY
option.
Restore all the log backups since last full backup beginning the
first log backup done after the full backup to the most recent log
backup with NORECOVERY
option.
Restore the tail-of-the-log backup with NORECOVERY
option.
Recover the database with RECOVERY
option.
Note: If differential backup is part of your backup plan, you may have to restore the most recent differential backup before you restore the log backups, if the most recent differential backup was taken after the most recent full backup.
The database recovery could be more complex depending on the physical layout of your database files, and your recovery objectives etc. You can learn more about backup and recovery in SQL Server book online. Aslo, Paul Randal has a series of blog post on backup and recovery on his Accidental DBA series.
Best Answer
Something like this should get you the output that you're looking for. The below query pulls the most recent backup of type full, differential, or log backup for each database.