GUID like entry for physical_device_name
Rob
Posts: 4 New member
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?
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?
Comments
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.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
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.
AFAIK, SQL Backup does not use the VSS writer service.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
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.
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.
http://sqlryan.com/2019/12/sql-differential-backups-failing-with-current-database-backup-does-not-exist-error/