February 6, 2012 at 5:35 am
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:
1. 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)
2. 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
3. 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
4. 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.
February 6, 2012 at 7:54 am
same issue at link below
February 6, 2012 at 8:01 am
Sorry i forget to send you another important link
http://en.allexperts.com/q/MS-SQL-Server-1801/backup-restore.htm
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply