SQL Backup updates msdb tables for backup history/location?

jerryhungjerryhung Posts: 138
edited November 6, 2009 9:50AM in SQL Backup Previous Versions
I am happy that we got 30 licenses!!

As we have SSRS reports setup for all SQL Backups, audits, DRP (customized log shipping) etc..

A lot of them depend on the msdb tables like
msdb.dbo.backupset
msdb.dbo.backupmediafamily

It's been a year since I last used SQL Backup 5.4, my question is
Does SQL Backup insert into those msdb tables as do SQL native backup do?

Or the entire SSRS reports need to be changed to read from SQL Backup's own history tables?

Thanks
Jerry Hung
DBA, MCITP

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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.
  • 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
    Jerry Hung
    DBA, MCITP
  • 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
    Jerry Hung
    DBA, MCITP
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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.
  • 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
    Jerry Hung
    DBA, MCITP
  • 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.
    Robin Anderson
    Development
    Red-Gate Software
  • Thank you, that answered my question

    I would just change the JOIN condition filter
    Jerry Hung
    DBA, MCITP
Sign In or Register to comment.