GUID like entry for physical_device_name

RobRob Posts: 4 New member
edited August 27, 2009 12:17PM in SQL Backup Previous Versions
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?

Comments

  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • RobRob Posts: 4 New member
    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.
  • RobRob Posts: 4 New member
    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?
  • peteypetey Posts: 2,358 New member
    Sorry, should've been b.backup_set_uuid.

    AFAIK, SQL Backup does not use the VSS writer service.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • 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.

     
  • 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.

     
  • 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/
Sign In or Register to comment.