Transaction log restore - not in sequence?

• 
  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
  

  petey May 18, 2007 07:22

  - 0 +
• 
  Hi,
  
  You're right- there was a maintenance plan running transaction log backups that I didn't know about.
  
  Thanks for your help.
  
  Hazel

  wrighth May 21, 2007 03:37

  - 0 +
• 
  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 May 24, 2007 10:36

  - 0 +
• 

  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?

  csm May 24, 2007 11:03

  - 0 +
• 
  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

  Jason Cook May 24, 2007 12:25

  - 0 +

Add comment

Please sign in to leave a comment.