GUID like entry for physical_device_name

• 
  Could you please run the following:
  

  SELECT a.physical_device_name, a.logical_device_name,  a.backup_set_uuid, a.device_type, b.backup_start_date, b.backup_finish_date
  FROM msdb..backupmediafamily a
  INNER JOIN msdb..backupset b ON a.media_set_id = b.media_set_id
  

  Where the physical_device_name are GUID-like, do the logical_device_name indicate that they are SQL Backup backups? If they are, could you please use the backup_set_uuid and backup_finish_date values, and try locating the SQL Backup log file for that backup? By default, the log files are stored in the 'C:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log\<instance name>' folder.
  
  Are there any errors recorded in the log file?
  
  Thanks.

  petey August 27, 2009 00:44

  - 0 +
• 
  I ran the code and get an error that a.backup_set_uuid does not exist. I removed that column and get results.
  
  I checked the logs and there are no errors. The logs match entries that have proper physical device names. The others are odd:
  
  Sample:
  physical_device_name: {1EC50781-9173-4658-A83A-53049FDB244A}8
  logical_device_name: null
  device_type: 7
  backup_start_date:8/26/09 6:45 PM
  backup_finish_date : 8/26/09 6:45 PM
  
  Not sure what to make of it.

  Rob August 27, 2009 11:22

  - 0 +
• 
  I have found the mysterious culprit making backups. These are VM machines and a vmware server back up occurs at the time of the entry. It uses the sql server vss writer service and actually makes a sql server backup at that time of the server backup. I would like to turn off the vss writer service... will this affect redgate backups?

  Rob August 27, 2009 12:09

  - 0 +
• 
  Sorry, should've been b.backup_set_uuid.
  
  AFAIK, SQL Backup does not use the VSS writer service.

  petey August 27, 2009 12:17

  - 0 +
• 
  I'm running Azure IaaS with SQL Server.  I'm using the https://ola.hallengren.com backup maint solution in an Agent job.  When running the agent job, the physical_device_name will populate with the ex: '.\path\filename.bak'.
  
  During unexpected non-sql-agent scheduled times, a random GUID would appear within the physical_device_name field.
  
  After opening a premier support case with Microsoft, I was advised that this was caused by VSS (Rob you were very close).
  Here is the link Microsoft shared with me: 
  https://docs.microsoft.com/en-us/azure/backup/backup-azure-vms-introduction
  
  What happening is the Azure IaaS backup/snapshot will by default backup everything on the OS and as well SQL databases.  The 'SQL database' backups were being backed up in a logical form to the OS/snapshot only.
  On top of that, it would also by default run these what I call 'rogue/ghost' backups with the parameter as 'copy-only = 0' what that means it no copy-only enabled..  Which means break the log chain. So you can't run diff backups.
  
  The fix was to add a registry key to the OS that explicitly tells VSS to not run these SQL backups with 'copy-only = 0':
  REG ADD "HKLM\SOFTWARE\Microsoft\BcdrAgent" /v USEVSSCOPYBACKUP /t REG_SZ /d TRUE /f
  (The MSFT engineer advised that no restart was required for adding this regkey.  I later confirmed that to be true).
  
  After making this regkey change, I stopped seeing the GUID in the physical_device_name field and finally my SQL instance was back to normal.
  
  

  anthonyNilsson November 08, 2019 09:13

  - 0 +
• 
  I'm running Azure IaaS with SQL Server.  I'm using the https://ola.hallengren.com backup maint solution in an Agent job.  When running the agent job, the physical_device_name will populate with the ex: '.\path\filename.bak'.
  
  During unexpected non-sql-agent scheduled times, a random GUID would appear within the physical_device_name field.
  
  After opening a premier support case with Microsoft, I was advised that this was caused by VSS (Rob you were very close).
  Here is the link Microsoft shared with me: 
  https://docs.microsoft.com/en-us/azure/backup/backup-azure-vms-introduction
  
  What happening is the Azure IaaS backup/snapshot will by default backup everything on the OS and as well SQL databases.  The 'SQL database' backups were being backed up in a logical form to the OS/snapshot only.
  On top of that, it would also by default run these what I call 'rogue/ghost' backups with the parameter as 'copy-only = 0' what that means it no copy-only enabled..  Which means break the log chain. So you can't run diff backups.
  
  The fix was to add a registry key to the OS that explicitly tells VSS to not run these SQL backups with 'copy-only = 0':
  REG ADD "HKLM\SOFTWARE\Microsoft\BcdrAgent" /v USEVSSCOPYBACKUP /t REG_SZ /d TRUE /f
  (The MSFT engineer advised that no restart was required for adding this regkey.  I later confirmed that to be true).
  
  After making this regkey change, I stopped seeing the GUID in the physical_device_name field and finally my SQL instance was back to normal.
  
  

  anthonyNilsson November 08, 2019 09:13

  - 0 +
• 
  Just wanted to share that this registry key fixed the problem perfectly for me. I wrote it up as well so others can find it a bit easier next time
  
  http://sqlryan.com/2019/12/sql-differential-backups-failing-with-current-database-backup-does-not-exist-error/
  

  ryanwmccauley January 03, 2020 09:26

  - 0 +

Add comment

Please sign in to leave a comment.