If you are a production dba and already spent enough years on the field, probably this question is very familiar to you.
Most of the time you will be alerted that differential backups are failing or if you are monitoring backups history (I hope you do), you will notice extra backups in your records.
It is crucial to identify and prevent the secondary process that takes backup of your databases to ensure your backup maintenance plan will be executed successfully and restores will be performed without an error if needed in future. Otherwise, the backup chain will be broken, restores will fail and no dba should find themselves in such difficult position. Thus, you need to act asap and stop pirate backup takers!
What/Who is taking these mysterious backups?
We realized that there is another process backing up our databases, so it’s time to identify it now.
We will query SQL Server dmvs to get a clear picture about backup details:
SELECT
database_name,
user_name,
server_name,
backup_start_date,
backup_finish_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Diff'
WHEN 'L' THEN 'Log'
END AS [Backup Type],
msdb..backupset.is_snapshot,
physical_device_name,
CASE device_type
WHEN 2 THEN 'Disk'
WHEN 5 THEN 'Tape'
WHEN 7 THEN 'Virtual Device'
WHEN 9 THEN 'Azure Storage'
END AS [Device Type]
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset
ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, backup_start_date, 102) >= GETDATE() - 7)
ORDER BY backup_set_id DESC
This query will give you a nice overview for all the backups performed in the last 7 days assuming that secondary backup process has been identified quickly, you can adjust it based on your case and needs.
You can see which user initiated the backups, backup type, backup path, device type, snapshot and so on. This should provide adequate information to resolve most of the cases, but in case you need more information, please go to the bottom of the post and refer to Microsoft documentation to comprehend and see what you can get more out of system dmvs.
There are various possibilities about who/what may take backups but I’d like to give one specific case I recently experienced.
GUID values shown as physical device name, what is this?
After I notice that there is a new process taking backups, I immediately queried system backup dmvs to get information and tried to locate the backup files. First, I saw that physical device name column shows guid values. This may be due to:
- Third party backup software taking backups to a tape
- VM / Snapshot backup
Device type was a virtual device and backups were running under Local system account. Server name was the local database host. Additionally, “is_snapshot” was 1 as I assumed, so this clearly indicates that there is an external process/service/application on the database host which takes snapshot backups using VSS.
To prove my point, I started a trace and capture backup events. Application name was “Microsoft SQL Server VSS Writer” so the theory is validated. This can be a Hyper-V / VMware backup or Third Party Tool such as NetBackup, Tivoli, Arcserve and so on.
Surprise, it is a Security Application!
In my case, it was none of above mentioned processes, it was a security application that is installed on database servers and it decided to take full backups every 4 hours.
There is an effective command that list vss writer services, open a command line and execute:
vssadmin list writers
That’s how we identified the security application. After stopping security application services on database hosts, backups are stopped and issue is resolved.
If you have time to read more
For more details , refer to Micosoft Documentation:
backupmediafamily (Transact-SQL) – SQL Server | Microsoft Docs
Bahti Samet Coban 
One thought on “Wait, Who is taking backup of SQL Server database?”