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.

Improve this question
2
  • 1
    Disable the SQL VSS Writer service and see who comes running.
    – Hannah Vernon ♦
    Commented Mar 8, 2017 at 20:40
  • @MaxVernon That's definitely one of the approaches I'm considering. ;)
    – db2
    Commented Mar 8, 2017 at 20:49

2 Answers 2

Reset to default
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.)

Improve this answer
1
  • Good point about sysadmin; I hadn't thought about auditing those logins, but it stands to reason the agent is probably logging into SQL Server or otherwise checking things prior to snapshotting, so we might be able to find a smoking gun there.
    – db2
    Commented Mar 8, 2017 at 20:51
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.

Improve this answer
1
  • That's my main suspicion at this point. The client has mentioned they haven't been able to track down what's doing it, but I have a feeling they're only looking inside the VM at this point. If it's the virtualization tools "helpfully" freezing I/O with SQL Writer, that would probably be pretty subtle to spot from inside the VM.
    – db2
    Commented Mar 10, 2017 at 12:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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