Transaction log restore - not in sequence?

wrighth

Hi,

I do a weekly full SQL 2000 DB backup and daily transaction log backups using Red-Gate SQL Backup v 4.6.

I can restore the DB and the first transaction log backup, but get an error saying that the transaction logs aren't in sequence when I try to restore the next transaction log backup. There aren't any transaction log backups between the two i'm trying to restore.

Please help?!

Cheers,

Hazel

petey

First check that there were no unexpected trx log backups made e.g.

SELECT a.backup_start_date, b.physical_device_name, * 
FROM msdb..backupset a
INNER JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id
WHERE type = 'L' AND database_name = <database name>
  AND a.backup_start_date > '17-May-2007'
ORDER BY a.backup_start_date

wrighth

Hi,

You're right- there was a maintenance plan running transaction log backups that I didn't know about.

Thanks for your help.

Hazel

csm

I just performed that same query to better understand what is occuring behind the scene.

SELECT a.backup_start_date, b.physical_device_name, *
FROM msdb..backupset a
INNER JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id
WHERE type = 'L' AND database_name = 'KVZ'
  AND a.backup_start_date > '22-May-2007'
ORDER BY a.backup_start_date

Is it normal to see 3 entries for each backup performed by SQL Backup?

For instance, the 10am transaction log backup shows up in 3 rows, with the following differences:

physical_device_name:

d:\SQL Backups\LOG_(local)_KVZ_20070524_100000.sqb SQLBACKUP_D4283421-E149-4BA4-B0AA-2B61B115864701 SQLBACKUP_D4283421-E149-4BA4-B0AA-2B61B115864702

family_sequence_number:

1 2 3

media_family_id:

FAF0F730-0000-0000-0000-000000000000 FCEE3063-0000-0000-0000-000000000000 DF802B2A-0000-0000-0000-000000000000

logical_device_name:

Red Gate SQL Backup (5.0.0.2770):000000000CBA00000000000002441800000073DF0000000A0102 NULL NULL

If I look at entries dated before our cutover to SQL Backup, I only see 1 entry per transaciton log backup from the MSSQL backup process.

I suspect this may be related to have 3 threads... if so is this normal? and is this ok/safe?

csm

csm wrote:

I suspect this may be related to have 3 threads... if so is this normal? and is this ok/safe?

I just switched the transaction log backup to use single thread, and only 1 entry appears for that backup. So my suspicious appears to be correct.

Is this normal? Ok? Safe?

Jason Cook

The purpose of the msdb..backupmediafamily and msdb..backupset tables is to list the backup 'devices' and 'media' used when performing the backup.

Each file (in SQL Server or SQL Backup) or thread (in SQL Backup) makes use of its own backup device via SQL Server to extract the data from the SQL Server instance.

Therefore, using multiple threads or files will result in multiple entries in the msdb..backupmediafamily table (and will give a number higher than one in the "last_family_number" column of the msdb..backupset table)... that's quite safe and is the expected behaviour.

More gory details on the SQL Server backup and restore tables can be found on MSDN at http://msdn2.microsoft.com/en-us/library/ms188062.aspx

Hope that helps,
Jason