Transaction log restore - not in sequence?

wrighthwrighth Posts: 3
edited May 24, 2007 12:25PM in SQL Backup Previous Versions
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
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    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
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • wrighthwrighth Posts: 3
    Hi,

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

    Thanks for your help.

    Hazel
    · Share on Twitter
  • csmcsm Posts: 17
    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?
    · Share on Twitter
  • csmcsm Posts: 17
    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?
    · Share on Twitter
  • Jason CookJason Cook Posts: 185
    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
    · Share on Twitter
Sign In or Register to comment.