SQL Server restore sequence from multiple full, diff and log backups

Question

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.

Answer 1

Typically, backups to file are appended with a timestamp (e.g. MyDatabase_FULL_201202060900.bak) so you have the option of using a script to generate the restore sequence given a directory of files. Google will give you dozens of examples, as will the SSC script library. I have this script in my library currently.

If the server you've taken the backups from is available, you can generate the restore script from the msdb tables dbo.backupset and dbo.backupmediafamily, example here. Note SSMS will generate the correct script for you and you can use Profiler to capture the queries it uses to do so.

Answer 2

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.

Answer 3

If you have backups that do not have the timestamp in the file name, then you can use the restore headeronly command to get information about the backup file.

RESTORE HEADERONLY
FROM DISK = N’e:\stemp\backup_file.bak’

This will give you information like the ChekpointLSN, DBLSN, LatLSN, and the backup time. There is also information on the Servername, the DB name and the user that did the backup.