August 19, 2009 at 2:21 pm
I'm trying to automate restoring a source server's full and differential backups to a different destination server. Both database servers are running SQL Server 2005 SP3. Since the source and destination databases can not talk to each other directly, I'm using the "RESTORE HEADERONLY" command to read relevant backup information. Is there a "full proof" way to determine which differential backup corresponds to a full backup? In other words, is there a field/set of fields that can be used to link a differential backup with the correct full backup?
August 19, 2009 at 4:39 pm
There are couple of ways of doing this..
However, the easiest way will be to select the list from backupset table in msdb db ....
select * from msdb..backupset
where type = ''
type can be one of these:
D = Full Database
I = Differential database
L = Log
F = File or filegroup
G =Differential file
P = Partial
Q = Differential partial
...........
Swarndeep
http://talksql.blogspot.com
August 19, 2009 at 5:38 pm
Thanks for the info. However, I'm interested in figuring out which differential database backup corresponds to which full database backup.
For example, let's say I have 5 backup files to the same database:
Full Backup A
Differential Backup A1
Full Backup B
Differential Backup B1
Differential Backup B2
How would I know that backup A1 corresponds to backup A, and backup B1 and backup B2 correspond to backup B?
August 19, 2009 at 5:51 pm
The backupset table has the necessary information. Try running this query. You can exclude the where criteria for all database backup operations.
select TYPE, media_set_id, first_lsn, database_backup_lsn, backup_start_date, database_name, differential_base_lsn
from msdb.dbo.backupset
where database_name =
order by backup_start_date
August 20, 2009 at 7:45 am
vstitte (8/19/2009)
The backupset table has the necessary information. Try running this query. You can exclude the where criteria for all database backup operations.select TYPE, media_set_id, first_lsn, database_backup_lsn, backup_start_date, database_name, differential_base_lsn
from msdb.dbo.backupset
where database_name =
order by backup_start_date
Assuming the database_name matches, would I just look for the [latest incremental backup].differential_base_lsn = [latest full backup].first_lsn? Or will there be a potential problem using that criteria?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply