3

This one's got me a bit perplexed. How might one determine what process/program is making file-level backups via VSS/VDI and the SQL Writer service? I've tried using Profiler to trace backup/restore events, and that just shows me SQL Writer itself creating the backup events. I don't see anything in the Windows event log indicating what's talking to SQL Writer, nor does SQL Server put anything meaningful in its own error log.

I've got a situation where something is making snapshot backups and breaking the differential chain, but we're not sure what the culprit is. It's undoubtedly some kind of backup agent, or a virtualization assistant that talks to SQL Writer to allow for VM snapshots, but we need some way to pin it down.

Paul White
  • 94,921
  • 30
  • 437
  • 687
db2
  • 9,708
  • 4
  • 37
  • 58

2 Answers2

4

First, check the Windows event log. Most backup software logs there. (It's likely taking a whole-OS backup, not just SQL Server.)

Generally, when you see something taking a backup, it's got sysadmin-level permissions. (You can also use the db_backupoperator role, but that's fairly unusual.)

So to catch the thing doing the backups:

  • Make a list of logins with sysadmin level permissions
  • Start auditing their login events (like with login triggers, Profiler, Extended Events, etc.)
  • Correlate the login times with the backup events

And if you're like Evil Admin Brent, change the SA account's password while you're at it. You should probably be rotating it periodically - especially if it's been the same for years, and you've had turnover in the team. If the backup app uses that login, presto, it will stop working. (Of course, so will your backups, so hopefully you have another solution for backups like natives.)

Brent Ozar
  • 43,325
  • 51
  • 233
  • 390
1

Speaking from expierience, if your SQL Server is running on VMware, Hyper-V just have a look at the backup software itself. I had same issues because the SQL database was backed up on OS level and in between we had a backup running a full backup of the VM itself on .vmdk level and this will trigger the SQL writer to freeze the database e.g. create a snapshot as well and thus breaking the full inc/diff chain.

cyzczy
  • 111
  • 3