Database Administrators (DBAs) care about protecting their databases arguably more than backup admins. DBAs are the backbone and lifeblood to a company's most important data. In my time spent with DBAs, I've learned the three most important factors they care about from a database protection standpoint are:
Peace of mind - verifying backups are usable for restores
Restore performance - ability to control the restores and ensure recovery time objectives (RTOs) are satisfactory to spin up environments for test/dev
Visibility - DBAs arguably care about backup just as much if not more than the backup admin. It is crucial they can easily monitor backups
Peace of Mind
In a previous post, we covered how DBAs can still verify their backups even if Veeam is in charge of database protection. Not only is this a common task in the DBA world to ensure backups are usable, but also Veeam's publish feature allows DBAs to run their CHECKDB commands on storage separate from the storage their production database is running on. This is a great way to provide peace of mind to the DBA that backups are usable, while at the same time not having to contend for resources.
Restore Performance
Which leads to how Veeam achieves fast RTOs for DBAs. Veeam can backup to any repository to achieve fast recoveries. The Veeam repository can be a NAS share, attached disk to the database server itself or leverage Veeam to recover from primary storage snapshots. The flexibility Veeam offers from a repository perspective allow DBAs to still leverage the same infrastructure they did prior to using Veeam, giving them similar if not better restore performance as using native database protection tools.
Visibility
The last factor is visibility, which is the focus of this blog post. It is key DBAs can easily monitor backups of their database. This is a topic that is covered rarely and there is little to no documentation on it, so I thought it might be helpful to share a few things on how SQL and Oracle DBAs can monitor backups even if Veeam is taking them.
Monitoring for SQL
Let's start with SQL first. There are many SQL queries and PowerShell scripts DBAs can run to monitor database backups even if they aren't in control of the backups anymore. Here are a few that I like.
Databases with backups older than 24 hours is one my favorite SQL queries to monitor backups. You can put this into a SQL Server Agent job so that it runs every morning, and sends the DBA group an email of any databases missing backups over the last 24 hours. There are several system databases (databases that are created upon install) for SQL. One of them is the MSDB database that logs all backups and restores. Veeam logs its backups in this same database when it creates a VSS snapshot. Below is both the query and snippet of what the result looks like.
MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date,
DATEDIFF(hh, MAX(msdb.dbo.backupset.backup_finish_date), GETDATE()) AS [Backup Age (Hours)]
FROM msdb.dbo.backupset
WHERE msdb.dbo.backupset.type = 'D'
GROUP BY msdb.dbo.backupset.database_name
HAVING (MAX(msdb.dbo.backupset.backup_finish_date) < DATEADD(hh, - 24, GETDATE())) 
Last database backup is a good query to run if you discover in the previous report that databases have not been backed up in the last 24 hours. This will show you the last time that database had a successful backup. As you can see below, Veeam is logging its backups in MSDB.
SELECT
A.[Server],
A.database_name,
A.last_db_backup_date,
B.backupset_name,
B.has_backup_checksums,
B.is_damaged,
B.backup_start_date,
B.backup_size,
B.physical_device_name,
B.description
FROM
(
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE msdb..backupset.type = 'D'
GROUP BY
msdb.dbo.backupset.database_name
) AS A
LEFT JOIN
(
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupset.has_backup_checksums,
msdb.dbo.backupset.is_damaged,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE msdb..backupset.type = 'D'
) AS B
ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date]
ORDER BY
A.database_name 
PowerShell script for last database backup is similar to the previous SQL query, but this offers a way to achieve the same thing via PowerShell instead. The only change that has to be made is to edit the name of the SQL server. This will report on the last TLOG and full backup.
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "<insert SQL Server name>"
$dbs=$s.Databases
#Retrieves the last backup dates - both for FULL and LOG backups
$dbs | SELECT Name,LastBackupDate, LastLogBackupDate | Format-Table -autosize
Restore history and who did a restore is a great report for audit purposes to confirm who did restores, and when they did them.
SELECT
[rs].[destination_database_name],
[bs].[database_name] as [source_database_name],
[rs].[restore_date],
[rs].[user_name],
[bmf].[physical_device_name] as [backup_file_used_for_restore]
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id]
ORDER BY [rs].[restore_date] DESC
Monitoring for Oracle
Similar to SQL there are several commands native to Oracle Recovery Manager (RMAN) that can be executed to monitor backups. It is important to note that unlike SQL though, Veeam has a RMAN plug-in. Meaning that any backup, restore, validate, duplicate, flashbackup, etc command an Oracle DBA ran in RMAN prior to Veeam still works with the Veeam RMAN plug-in. From a 10,000 foot view all the Veeam plug-in does is route the database backups to a Veeam repository and give the data protection admin visibility into the backups. Below are a few RMAN commands I like for monitoring backups.
List of all backups provides a nice summary of the database backups.
list backup summary;
List of database files missing backups over the last day is a great way to confirm your Oracle database has a backup in the last 24 hours.
REPORT NEED BACKUP RECOVERY WINDOW OF 1 DAYS DATABASE DEVICE TYPE sbt;
Detailed backup history will include the size of the backup, type of backup and the compression ratio. This command needs to run on the actual database itself by connecting to SQLPLUS.
COL in_size FORMAT a10
COL out_size FORMAT a10
SELECT SESSION_KEY,
INPUT_TYPE,
COMPRESSION_RATIO,
INPUT_BYTES_DISPLAY in_size,
OUTPUT_BYTES_DISPLAY out_size
FROM V$RMAN_BACKUP_JOB_DETAILS
ORDER BY SESSION_KEY;
In summary, Veeam is able to meet the most important needs DBAs have from a database protection standpoint. For Oracle, there is absolutely zero change to a DBAs day-today as the Veeam RMAN plug-in functions the same as without Veeam. For SQL, DBAs have given up control of their backups to the backup admin, but they can still verify, monitor and restore in a similar way as before.
Comments