SQL Backup updates msdb tables for backup history/location?

jerryhung

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

Brian Donahue

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.

jerryhung

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

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

Brian Donahue

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.

jerryhung

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

RBA

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.

jerryhung

Thank you, that answered my question

I would just change the JOIN condition filter