SQL Server restore sequence from multiple full, diff and log backups Suppose I have a lot of backups (may include full, differential and log backups) of a SQL Server database. I want to write a program that can generate a SQL script to take the database to its most recent state. Now I have difficult in finding the correct restore order of these backup files. I read some pages in MSDN and got some basic rules. They are:


Full backups not only include data, but also include enough log records. This makes the full backup can be used by itself (after restore, use the log records in the backup to roll forward the database to a consistent state)
Every backup file (include full, diff and log) has some common property

first lsn: specify the first log record that included in the backup
last lsn - 1: specify the last log record that included in the backup
checkpoint lsn: specify the log record that corresponds to the most recent checkpoint operation
database lsn: specify the log record that corresponds to the most recent full backup

The log backups that need to be restored consecutively may have overlap log records, that is to say, if log backup A needs to be restored just before log backup B, A.last_lsn may be greater than or equal to B.first_lsn
You need to restore the most recent full backup (we have just one for sure), then the most recent diff backup (we may or may not have this), last, restore the logs backups that were taken after the full or diff backup in turn


Now my question is, how to determine which full and diff backups is the most recent one? How to determine which log file should start to restore after the full or diff restore? I guess these information can be found from those lsns, but I'm not sure of the algorithm. Can anyone help to write a piece pseudocode to describe the algorithm? Thanks.
Personally, I like my backups to be timestamped something like MyDB-yyyymmddhhnn-Full.bak or MyDB-yyyymmddhhnn-Log.bak in the file name. I've never actually read the LSN from a backup file, neither have I read the LSN information from the system tables.

You can sort the files and pick out which ones you need quite easily.