I have a SQL Server database that is creating full backups once a week, differential backups once a day, and transaction log backups hourly. These are being FTP'd to another box, where I would like an automated system (which I am writing) to pick them up and restore them into a working database.

So basically every hour when a new transaction log file comes in, I want to find the appropriate full backup file, differential backup file, and other log txn files for that day use them to restore the database.

The issues is that this machine will have several weeks of backup files on it so I need a way to group them together appropriately. What is the best way to do this?

When you go into Management Studio and do a database restore, it gives you a listing off all the full, diffs, and logs grouped together so clearly it's storing this grouping data somewhere.

I've seen the RESTORE HEADERONLY command, which seems to provide some information but I'm not 100% sure how to use it.

It looks like you could make the CheckpointLSN of the full backup to the DatabaseBackupLSN of all the other files, but I'm not totally sure about that.

My log files to have date/time stamps in the name but I would rather not rely on parsing file names to group everything together.

This article does provide some insights but I would prefer some sanctioned documentation from Microsoft before I embark on a solution.



Browse other questions tagged or ask your own question.