GUID like entry for physical_device_name

Rob

Running Redgate backup 5.2...

When querying msdb.dbo.backupmediafamily.physical_device_name I find entries that are guid-Like ... example:

{A2E046C7-CCD6-4875-9CA4-69EF16033133}3

Any ideas why this is happening?

petey

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.

Rob

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

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?

petey

Sorry, should've been b.backup_set_uuid.

AFAIK, SQL Backup does not use the VSS writer service.

anthonyNilsson

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

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.

 

ryanwmccauley

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/