SQL Backup updates msdb tables for backup history/location?

• 
  Hi Jerry,
  
  SQL Backup uses the SQL VDI as an interface to get the backup data, so the updates to the backup history tables in MSDB are still done by SQL Server in exactly the same way as when you do the backup natively.

  Brian Donahue November 03, 2009 11:18

  - 0 +
• 

  Brian Donahue wrote:

  Hi Jerry,
  
  SQL Backup uses the SQL VDI as an interface to get the backup data, so the updates to the backup history tables in MSDB are still done by SQL Server in exactly the same way as when you do the backup natively.

  
  That is great news to hear Brian
  
  I look forward to testing it and confirm back here shortly

  jerryhung November 03, 2009 13:09

  - 0 +
• 
  So indeed SQL Backup does update the MSDB tables
  
  However, I noticed this odd behaviour, and I want to confirm
  
  1. Does it insert 1 row PER THREAD?
  e.g. I had 3 thread for the DIFF backup and it created 3 rows?
  
  2. Does it insert 1 extra row if I chose the Network Copy/Mirror option?
  
  I changed thread=1, but still see 2 rows in msdb.dbo.backupset like below
  
  E:\Backup\msdb\DIFF_REPORTINGSERVICE_msdb_20091103_203603.sqb
  E:\Backup\msdb\DIFF_REPORTINGSERVICE_msdb_20091103_203603.sqb

  jerryhung November 03, 2009 15:42

  - 0 +
• 
  Hi Jerry,
  
  MSSQL doesn't have any concept of multithreaded interleaved backups, so a multithreaded backup "looks" exactly the same to SQL Server as a backup to multiple files.
  
  No idea about the second point, but I would assume you would only have one entry because SQL Server has no idea that SQL Backup is copying the file.

  Brian Donahue November 04, 2009 04:35

  - 0 +
• 
  I just did some testing with my Differential backup to confirm (should've done so yesterday)
  
  1. Copy to network does not generate additional entries in msdb.dbo.backupset
  
  2. Multi-thread does not create additional entries in msdb.dbo.backupset either
  
  However, it does generate more entries (3 threads = 3 entries, 2 threads = 2 entries) in the msdb.dbo.backupmediafamily, where I get my physical_device_name
  
  They would be using the same media_set_id, but different sequence # and GUID
  media_set_id family_sequence_number media_family_id
  3600 1 1A8CA95E-0000-0000-0000-000000000000
  3600 2 5E49F657-0000-0000-0000-000000000000
  
  My question is just if that is expected/correct behaviour?
  If that's supposed to happen, I guess I just need to SELECT DISTINCT or remove duplicate file path by selecting family_sequence_number = 1
  

  SELECT TOP 100
          a.backup_finish_date
         ,CASE a.[type]
            WHEN 'D' THEN 'FULL'
            WHEN 'I' THEN 'DIFF'
            WHEN 'L' THEN 'Trans Log'
            ELSE a.type
          END AS backuptype
         ,a.server_name
         ,a.database_name
         ,a.recovery_model AS database_recovery_model
         ,b.physical_device_name
  FROM    msdb.dbo.backupset a (NOLOCK)	
          LEFT JOIN msdb.dbo.backupmediafamily b (NOLOCK) ON a.media_set_id = b.media_set_id
  WHERE   1 = 1
  ORDER BY a.backup_finish_date DESC
  

  SELECT TOP 1000
          *
  FROM    msdb.dbo.backupmediafamily b (NOLOCK) 
  WHERE physical_device_name LIKE '%sqb%'
  ORDER BY physical_device_name
  

  media_set_id family_sequence_number media_family_id media_count logical_device_name physical_device_name device_type physical_block_size mirror
  3600 1 1A8CA95E-0000-0000-0000-000000000000 1 Red Gate SQL Backup (6.2.0.134):00000000003400000000000000026A000000145D000000000104 E:\Backup\AdventureWorks\DIFF_REPORTINGSERVICE_AdventureWorks_20091104_152716.sqb 7 65536 0
  3600 2 5E49F657-0000-0000-0000-000000000000 1 Red Gate SQL Backup (6.2.0.134):00000000003400000000000000026A000000145D000000000104 E:\Backup\AdventureWorks\DIFF_REPORTINGSERVICE_AdventureWorks_20091104_152716.sqb 7 65536 0

  jerryhung November 04, 2009 10:37

  - 0 +
• 
  Each thread uses its own VDI which is treated as separate media.
  
  So this is the sort of thing that happens behind the scenes if you perform a 3-thread backup: (from SQL Profiler)
  

  BACKUP DATABASE [small]  TO VIRTUAL_DEVICE = 'SQLBACKUP_7101A731-CB71-43DF-93E3-12CD2704FA0A', VIRTUAL_DEVICE = 'SQLBACKUP_7101A731-CB71-43DF-93E3-12CD2704FA0A01', VIRTUAL_DEVICE = 'SQLBACKUP_7101A731-CB71-43DF-93E3-12CD2704FA0A02' WITH BUFFERCOUNT = 18, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576, NAME = N'Database (small), 06/11/2009 11:52:58', DESCRIPTION = N'Backup on 06/11/2009 11:52:58  Server: TST-ROBIN  Database: small', FORMAT
  

  
  This means three entries into the media set table which is the correct behaviour.

  RBA November 06, 2009 06:58

  - 0 +
• 
  Thank you, that answered my question
  
  I would just change the JOIN condition filter

  jerryhung November 06, 2009 09:50

  - 0 +

Add comment

Please sign in to leave a comment.