"Phantom" backups listed in Activity History

chris_parker

Hello,

I'm a SQL Server beginner so this question is asked with a heaping spoonful of ignorance. Having said that, I've written some basic SQL scripts, created Stored Procedures and Views, and have set security permissions so I'm not a complete beginner.

A VAR setup our SQL Server 2017 VM and it's now mine to manage. They originally created to SQL Server Agent jobs that did full backups and log backups. Now that SQL Backup is in place, I disabled those jobs mid-last week.

When I look at the Activity History in SQL Backup I see multiple items that are stating backups (fulls and logs) are being performed outside of the SQL Backup jobs. I can't figure out where these backups are being initiated from nor can I find the files themselves.

1. When I search my data drive (where the original .bak and .trn files were being stored) there are no new files. Those files all end back on the day I disabled those jobs last week.
2. The phantom backup activities shown in SQL Backup are happening at times that do not align with the original jobs that are now disabled.
3. SQL Server Management Studio's Log File View does list some activity that I can correlate to these phantom jobs but the entries are extremely sparse on information. Additionally, these entries indicate that no data was actually backed up.

What else could possibly be initiating these "backups"?

petey2

Best guess at this point is that it's a snapshot backup performed by your virtual machine, seeing as how they are virtual device backups and they all kick off at the exact same moment.  

petey2

Try running this query to see if it uncovers anything useful:

SELECT a.backup_start_date, a.database_name, a.type, b.device_type, b.logical_device_name, b.physical_device_name, a.name, a.user_name FROM msdb..backupset a

INNER JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id
WHERE a.backup_start_date >= '2020-5-26 5:38' AND a.backup_start_date < '2020-5-26 5:39'

ORDER BY a.backup_start_date

chris_parker

Well I don't know if it's useful but it did return some records: One for every database including the system databases.

chris_parker

Interesting. Thanks for the idea.

Most importantly, however, is that these are not interfering in anyway with the "real" backups, are they? Or my ability to restore from the SQB files?

petey2

They will break differential backups i.e. any differential backups taken after these snapshot backups will not be restorable, until you take a full backup with SQL Backup or SQL Server.

chris_parker

Does that include Log backups or only Differential?

petey2

Only differentials.  As long as you have an unbroken chain of log backups, you can still use the last full database backup taken with SQL Backup or SQL Server, and restore the transaction logs.