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

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 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;
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.