2

I set up a complete set of Maintenance Plan.

  1. Full backup of all databases at 3AM.
  2. Differential backup at 6h30, 12h30, 18h30 and 00h30.
  3. Log backup at each hour, at 45 minutes (1h45, 2h45...)

In my SQL history list, (I use this script to find it: http://luvit.me/1GAfpPG ) I see a full backup a 1AM, with a GUID as the "physical_device_name" and a NULL value at as the "backupset.name".

WHO start the backup? How can I found if another guy use another tool for backup? Did this backup is started by SQL Server himself?

The problems is currently, we have some tools that do a database conversion for a new and not yet ready ERP, and the conversion tool change the Recovery mode: if the recovery mode is changed while this weird backup is running, the conversion tool crash...

Sorry if it's a noob question, but I didn't find anything ...

Thank you

  • Is there any other backup software on that server? I've seen that happen with Symantec Backup Exec or HP Data Protector, don't remember which one but it could be something comparable – Tom V - Team Monica Jun 11 '15 at 13:22
  • I assume its a virtual machine? Are you taking snapshots? Please check the log yourself in SSMS on the left side under management SQL Server logs. there you see all backups taken and the timings. IF its super fast it might be a snapshot. – RayofCommand Jun 11 '15 at 13:33
  • Some virtual server backup software will issue a full database backup after the snapshot. We had this happen, and it interfered with an emergency point in time recovery. We had easy access to our regular scheduled full backup, and all the transaction logs, but this mysterious full backup was off on a tape somewhere, which took a few extra hours of explain to the ops team that we needed it. – datagod Jun 11 '15 at 14:11
  • 1
    Yes, it's a virtual server machines. We finally found that my SysAdmin start a Full windows backup of every virtual machines, at 1 am. He will changes it for 20h00. Thank you for your assistance. – Bestter Jun 11 '15 at 14:14
3

Add columns: msdb.dbo.backupset.software_vendor_id, msdb.dbo.backupset.user_name

to your query.

If your software vendor id = 4608 then its sql native, anything else is a third party software. The user name should give you some clues as to what is occurring.

Also check SQL Agent for jobs executing at around that time.

  • No maintenance plan, no SQL agent Jobs, and for those backups, the vendor id is 4608 and the user_name is "NT AUTHORITY\SYSTEM". It seems that a Full windows backup start a SQL server backup too... – Bestter Jun 11 '15 at 14:14
  • 2
    It will do so if you have the "SQL Server VSS Writer" service running, which is running by default on SQL2008 R2 plus. If it is runinng all windows backup software that uses snapshot will take backups of the SQL databases. Just disable the service and Windows Backup, VEEAM and any other backup software that utilizes snapshots will not take extra backups of your database – Spörri Jun 11 '15 at 14:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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