Incremental backup records in msdb are shows as full backups

[ibarizz]

Hello,

So basically what the title says. I have my Veeam envinronment configured in a few jobs to take backups of my SQL Servers (1 Full on sundays and 6 incrementals) + application-aware.
Our backups in Veeam are perfectly done and are also shown with the specific type (full or incremental). Even the size fits that.

However, the DBAs are finding some strange scenearios that I can't explain by myself (maybe there is a trouble with the query they are throwing).

This is the query they are using to check the backup status type

Code: Select all

use master
go
SET NOCOUNT ON
GO
SET quoted_identifier OFF
DECLARE @dbname AS VARCHAR(80)
DECLARE @msgdb AS VARCHAR(100)
DECLARE @dbbkpname AS VARCHAR(80)
DECLARE @dypart1 AS VARCHAR(2)
DECLARE @dypart2 AS VARCHAR(3)
DECLARE @dypart3 AS VARCHAR(4)
DECLARE @currentdate AS VARCHAR(10)
DECLARE @server_name AS VARCHAR(30)
SELECT @server_name = @@servername
SELECT @dypart1 = DATEPART(dd,GETDATE())
SELECT @dypart2 = DATENAME(mm,GETDATE())
SELECT @dypart3 = DATEPART(yy,GETDATE())
SELECT @currentdate= @dypart1 + @dypart2 + @dypart3
PRINT "#####################################################################"
PRINT "# SERVERNAME : "+ @server_name + " DATE : "+ @currentdate +"#"
PRINT "#####################################################################"
PRINT "DatabaseName Full Diff TranLog"
PRINT "##########################################################################################################################################"
SELECT SUBSTRING(s.name,1,50) AS 'DATABASE Name',
b.backup_start_date AS 'Full DB Backup Status',
c.backup_start_date AS 'Differential DB Backup Status',
d.backup_start_date AS 'Transaction Log Backup Status'
FROM MASTER..sysdatabases s
LEFT OUTER JOIN msdb..backupset b
ON s.name = b.database_name
AND b.backup_start_date =
(SELECT MAX(backup_start_date)AS 'Full DB Backup Status'
FROM msdb..backupset
WHERE database_name = b.database_name
AND TYPE = 'D') -- full database backups only, not log backups
LEFT OUTER JOIN msdb..backupset c
ON s.name = c.database_name
AND c.backup_start_date =
(SELECT MAX(backup_start_date)'Differential DB Backup Status'
FROM msdb..backupset
WHERE database_name = c.database_name
AND TYPE = 'I')
LEFT OUTER JOIN msdb..backupset d
ON s.name = d.database_name
AND d.backup_start_date =
(SELECT MAX(backup_start_date)'Transaction Log Backup Status'
FROM msdb..backupset
WHERE database_name = d.database_name
AND TYPE = 'L')
WHERE s.name <>'tempdb'
ORDER BY s.name

Yet in the output, it is shown the incremental backups as full backups:

In the "Full DB Backup Status" column appears all the lastest incremental backups.

I tried the following query by my own and checked that the incremental backups are being created:

Code: Select all

SELECT DISTINCT database_name, TYPE
FROM msdb..backupset

So yeah, the issue seems to lie in how the dates of full, differential, and incremental backups are being selected in the original query as that query compares the start dates of backups (backup_start_date) of different types and selects the maximum date. But this may not work correctly if incremental backups have more recent dates than full backups.
So I adjusted the query provided by the DBAs in how the dates of full, differential, and incremental backups are selected by using correlated subqueries, which will allow me to select the most recent backup of each type for each database.

So I made this version:

Code: Select all

SELECT 
    SUBSTRING(s.name, 1, 50) AS 'DATABASE Name',
    (SELECT TOP 1 b.backup_start_date 
     FROM msdb..backupset b
     WHERE b.database_name = s.name AND b.type = 'D'
     ORDER BY b.backup_start_date DESC) AS 'Full DB Backup Status',
    (SELECT TOP 1 c.backup_start_date 
     FROM msdb..backupset c
     WHERE c.database_name = s.name AND c.type = 'I'
     ORDER BY c.backup_start_date DESC) AS 'Differential DB Backup Status',
    (SELECT TOP 1 d.backup_start_date 
     FROM msdb..backupset d
     WHERE d.database_name = s.name AND d.type = 'L'
     ORDER BY d.backup_start_date DESC) AS 'Transaction Log Backup Status'
FROM 
    MASTER..sysdatabases s
WHERE 
    s.name <> 'tempdb'
ORDER BY 
    s.name;

But guess what... I got the same output. It appears that the incremental backups are being recorded as full backups.

My question is:

- Am I doing something wront with the query that I cannot see the right dates for each type of backup?
- Since the backups are being managed through Veeam, the issue might be related to how Veeam interacts with SQL Server and records the backups in the msdb database. I couldn't find much information related this, but is this safe to go and say it's expected?

Hope somebody can help me with this!
Thank you in advance,
Facundo.

[PetrM]

Hello,

Yes, it's expected and I don't see an issue with your queries. The VSS framework leverages SQL writer during AAIP and the full db backup status field is updated during the BackupComplete stage unless Copy-Only flag is used, see more info about VSS workflow in this article. It has nothing to do with our incremental sessions and changed block tracking that works at the hypervisor level.

You may use Copy-Only mode which is based on the VSS_BS_COPY method for shadow copy creation but I'm not sure that you need this if you don't use 3rd party tools to take care of the SQL backup chain.

Thanks!