Determining the Time Interval Contained in a Log Backup

 This topic is relevant only for databases that are using the full or bulk-logged recovery models.

Sometimes you must know the time interval that is contained in a log backup. This is especially true for point-in-time recovery. The first log backup in a log backup chain captures the interval between the start of the first full database backup and the start of the log backup. Each subsequent log backup in the chain captures the log written between the start of the previous log backup and the start of the current log backup.

You can calculate this interval from information in the msdb.backupset table. The backup_start_date column contains the date and time that a backup operation started, and the backup_finish_date column contains the date and time the backup operation finished. The interval contained in a log is as follows:

• The interval contained in the first log backup in a log backup chain extends between the backup_start_date of the first full backup and the backup_start_date of the log backup.
• The interval contained in a subsequent log backup extends between the backup_start_date of the previous log backup and the backup_start_date of the current log backup.

The following illustration shows the intervals captured by the first and second log backups in a chain.

Examples

The following examples show the backup start dates of the first full database backup and of the first and second log backups in a chain. The examples also show the resulting time intervals captured by the first and second log backups.

Time Interval in the First Log Backup

This example illustrates backup start dates for the first full database backup and the first log backup in a chain and shows the length of time captured by this log backup.

The time interval contained in the first log backup (Log_1) begins at Data_1.backup_start_date and extends to Log_1.backup_start_date.

For example:

Data_1.backup_start_date = 3/4/2004 8:00:09 AM

Log_1.backup_start_date = 3/4/2004 8:15:03

Time interval in Log_1 = 12 hours, 14 minutes, 54 seconds

Time Interval in the Second Log Backup

This example illustrates backup start dates for the first and second log backups in a log chain and shows length of time captured by the second log backup.

The time interval contained in the second log backup (Log_2) begins at Log_1.backup_start_date and extends to Log_2.backup_start_date.

For example:

Log_1.backup_start_date = 3/4/2004 8:15:03 PM

Log_2.backup_start_date = 3/5/2004 8:01:15 PM

Time interval in Log_2 = 23 hours, 46 minutes, 3 seconds

See Also

Concepts

Planning and Performing Restore Sequences (Full Recovery Model)

Help and Information

Getting SQL Server 2005 Assistance