1

We are using 3rd party tool to backup SQL server databases. I am looking to setup an alert which gets generated whenever Ad-hoc backup is taken and it's not copy_only. We are using 3rd party monitoring tool and I would like to setup an alert using this 3rd party tool which pulls an information using T-SQL script.

Alert should generated whenever T-SQL query return result 0 or 1 if ad-hoc backup taken in last 1 hour and it's not copy_only database backup

1

First you want to retrieve the normal information from your backup history on the relevant SQL Server instance, to find out what you normal backup file location looks like. You can achieve this with something like this:

SELECT  
   msdb.dbo.backupset.database_name,  
   msdb.dbo.backupset.backup_start_date,  
   msdb.dbo.backupset.backup_finish_date, 
   CASE msdb..backupset.type  
       WHEN 'D' THEN 'Full'  
       WHEN 'I' THEN 'Diff'
       WHEN 'L' THEN 'Log'  
   END AS backup_type,  
   -- msdb.dbo.backupset.backup_size / 1024 / 1024 as [backup_size MB],  
   CASE  
       WHEN msdb.dbo.backupmediafamily.device_type = 2 THEN 'Disk'
       WHEN msdb.dbo.backupmediafamily.device_type = 5 THEN 'Tape'
       WHEN msdb.dbo.backupmediafamily.device_type = 7 THEN 'Virtual Device' 
       WHEN msdb.dbo.backupmediafamily.device_type = 9 THEN 'Azure Storage' 
       WHEN msdb.dbo.backupmediafamily.device_type = 105 THEN 'A permanent backup device'
       ELSE 'Unknown backup device code:' + CAST(msdb.dbo.backupmediafamily.device_type AS VARCHAR(3)) + '!'    
   END AS device_type_desc,
   msdb.dbo.backupmediafamily.physical_device_name,
   msdb.dbo.backupmediafamily.logical_device_name,
   msdb.dbo.backupset.name AS backupset_name, 
   msdb.dbo.backupset.description,
   msdb.dbo.backupset.is_copy_only,
   msdb.dbo.backupset.is_snapshot,   
   msdb.dbo.backupmediaset.name,
   msdb.dbo.backupmediaset.software_name,
   msdb.dbo.backupset.user_name,
   'EOR'  
FROM   msdb.dbo.backupmediafamily  
   INNER JOIN msdb.dbo.backupset 
       ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  
   INNER JOIN msdb.dbo.backupmediaset
       ON msdb.dbo.backupmediaset.media_set_id = backupmediafamily.media_set_id
WHERE 1 = 1
ORDER BY msdb.dbo.backupset.backup_start_date DESC;

A result set for when using Commvault as your backup solution could look like this:

database_name backup_start_date backup_type device_type_desc physical_device_name logical_device_name backupset_name description is_copy_only is_snapshot name software_name user_name
TVDTools 2021-10-25 08:04:14.000 Log Virtual Device 8e5bda3c-d453-4328-b180-dcee74549f68 NULL CommVault Galaxy Backup NULL 0 0 NULL Microsoft SQL Server DOMAIN\USER
TVDTools 2021-10-24 21:01:58.000 Log Virtual Device f40f904f-e197-4c2b-88fe-b9cdb5ee237f NULL CommVault Galaxy Backup NULL 0 0 NULL Microsoft SQL Server DOMAIN\USER
master 2021-10-24 20:16:02.000 Full Virtual Device 361ff522-8e39-406c-b971-5808ee51240b NULL CommVault Galaxy Backup NULL 0 0 NULL Microsoft SQL Server DOMAIN\USER
TVDTools 2021-10-24 20:06:44.000 Diff Virtual Device 3589d70e-af8d-469f-ad66-5e1ca52cef1e NULL CommVault Galaxy Backup NULL 0 0 NULL Microsoft SQL Server DOMAIN\USER
msdb 2021-10-24 20:06:44.000 Diff Virtual Device 12685e62-f688-4818-96d3-e20a802af6b9 NULL CommVault Galaxy Backup NULL 0 0 NULL Microsoft SQL Server DOMAIN\USER

So you have to determine with your backup solution which columns tell you when something is being run outside you backup solution.

In my case I'd just search for any backup set that didn't have CommVault Galaxy% in the backupset_name column and possibly which weren't using a device_type code of 7. Your mileage may vary.

Answering Your Questions

Combining the above query with a couple of WHERE clauses would result in the following query, which you could possibly plug into your monitoring tool:

SELECT  
   msdb.dbo.backupset.database_name,  
   msdb.dbo.backupset.backup_start_date,  
   msdb.dbo.backupset.backup_finish_date, 
   CASE msdb..backupset.type  
       WHEN 'D' THEN 'Full'  
       WHEN 'I' THEN 'Diff'
       WHEN 'L' THEN 'Log'  
   END AS backup_type,  
   -- msdb.dbo.backupset.backup_size / 1024 / 1024 as [backup_size MB],  
   CASE  
       WHEN msdb.dbo.backupmediafamily.device_type = 2 THEN 'Disk'
       WHEN msdb.dbo.backupmediafamily.device_type = 5 THEN 'Tape'
       WHEN msdb.dbo.backupmediafamily.device_type = 7 THEN 'Virtual Device' 
       WHEN msdb.dbo.backupmediafamily.device_type = 9 THEN 'Azure Storage' 
       WHEN msdb.dbo.backupmediafamily.device_type = 105 THEN 'A permanent backup device'
       ELSE 'Unknown backup device code:' + CAST(msdb.dbo.backupmediafamily.device_type AS VARCHAR(3)) + '!'    
   END AS device_type_desc,
   msdb.dbo.backupmediafamily.physical_device_name,
   msdb.dbo.backupmediafamily.logical_device_name,
   msdb.dbo.backupset.name AS backupset_name, 
   msdb.dbo.backupset.description,
   msdb.dbo.backupset.is_copy_only,
   msdb.dbo.backupset.is_snapshot,   
   msdb.dbo.backupmediaset.name,
   msdb.dbo.backupmediaset.software_name,
   msdb.dbo.backupset.user_name,
   'EOR'  
FROM   msdb.dbo.backupmediafamily  
   INNER JOIN msdb.dbo.backupset 
       ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  
   INNER JOIN msdb.dbo.backupmediaset
       ON msdb.dbo.backupmediaset.media_set_id = backupmediafamily.media_set_id
WHERE 1 = 1
-- Is Not CommVault Backup Check
AND (msdb.dbo.backupmediafamily.device_type <> 7 
     AND msdb.dbo.backupset.name NOT LIKE 'CommVault Galaxy%')
-- Last Hour Check 
AND   (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102)) >= DATEADD(hh,-1,GETDATE())
-- Is Not Copy_Only Check
AND msdb.dbo.backupset.is_copy_only <> 1
ORDER BY msdb.dbo.backupset.backup_start_date DESC;

Which results in the following output:

database_name backup_start_date backup_type device_type_desc physical_device_name logical_device_name backupset_name description is_copy_only is_snapshot name software_name user_name (No column name)
TVDTools 2021-10-25 09:21:07.000 Full Disk H:\adhoc\SERVERNAME\TVDTools_Full_CopyOnly_20211025_092107.bak NULL TVDTools-Full Backup NULL 0 0 NULL Microsoft SQL Server DOMAIN/User EOR

Gotcha!

The results can be used in your monitoring software to do whatever you want to do, e.g. send an e-mail with the results, trigger an alarm in the console, ...

0

Check out the backupset table in msdb. A sample query to get you started:

SELECT [database_name] AS [Database]
    , [user_name] AS [BackupCreator]
    , [backup_start_date] AS [BackupDate]
    , CASE [type]
        WHEN 'L' THEN 'Log'
        WHEN 'I' THEN 'Diff'
        WHEN 'D' THEN 'Full'
      END AS [BackupType]
    , [is_copy_only] AS [IsCopyOnly]
FROM msdb..backupset

You may need to include backupmediafamily if you also want to find the location that the errant backup was taken.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.