0

I've noticed from the event log, that something is backing up SQL Server databases, separately to our maintenance plan backups. We can see something running SQL backups at 03:00. Our intended backups only kick off at 04:05.

The backup logs look like this:

Database backed up. Database: master, creation date(time): 2015/11/15(09:39:37), pages dumped: 490, first LSN: 743:441:73, last LSN: 743:472:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{9BCE7BA5-E8AB-4459-8655-365988420F35}12'}).

There seems to be no other backup of the database files themselves which might trigger a volume shadow copy (VSS writer) that we can see, so it's a mystery what's causing these.

The worry is of course, that these backups will interfere with the restore process for point-in-time restore as we have no access to these full backups, yet they are presumably resetting the transaction log.

The server is a Virtual Machine and I originally theorised that perhaps something was backing up the entire VM at this time, and perhaps that was causing VSS to create the backups, but apparently there is no machine imaging backup set up on this server - only file based backups of certain folders and the main SQL data folder is not backed up.

Is there anything I can set up which could provide any clues as to what's running these backups?

NickG
  • 131
  • 6

2 Answers2

3

This looks like Veeam backups or some other third-party backup tool. Ask your systems guys if they take VM snapshots and backup the SQL server. Make sure you are VERY careful as Veeam can take FULL backups and break your differential base and possibly hose up your restore chain. I believe in Veeam they can do COPY_ONLY backups or set it to not truncate the transaction log, you'll want to make sure they aren't causing your well-planned restore strategy to go down the toilet in the event of disaster.

Kris Gruttemeyer
  • 3,834
  • 1
  • 21
  • 43
  • 2
    I had this exact same problem. The IT team said that it was completely normal, and argued to management that these backups were necessary. A week later I requested one of these backups to be restored. After a a full day of fiddling, they gave up and turned off the backups. – datagod Dec 08 '15 at 18:17
  • 2
    Absolutely! They mean well and they just want to take their backups but they need to let SQL do it's thing. At the end of the day, the VM snapshots give you a potential data loss of 1 week (if they snapshot weekly). SQL backups can get point-in-time recovery if you're in FULL mode. That's really the argument that you need to bring up. Whose strategy is going to give you the least amount of possible data loss. :) – Kris Gruttemeyer Dec 08 '15 at 18:19
  • My 'systems guy' swears blind there are no other backups being performed :( They said that the SAN storage is replicated invisibly, and doesn't perform any VSS backups. – NickG Dec 08 '15 at 22:01
  • Might be worth doing some sp_whoisactive collection during that time and try to find out where it's coming from, what username it's using, etc. – Kris Gruttemeyer Dec 09 '15 at 14:28
2

If you run this query against that instance, it will give you some details as to what the backup is. At the very least you can determine where the backup is going and which login is executing the backup. 

select b.database_name
     , m.physical_device_name
     , b.server_name
     , b.machine_name
     , b.user_name
     , ms.software_name
from msdb.dbo.backupset b
join msdb.dbo.backupmediafamily m
  on b.media_set_id = m.media_set_id
join msdb.dbo.backupmediaset ms
  on ms.media_set_id = b.media_set_id
Ian Chamberland
  • 433
  • 2
  • 5