Find Database Backup URL

• alex.sqldba

  SSChampion

  Points: 10254

  More actions

  October 28, 2019 at 3:05 pm

  #3692410

  Hi All,

  I have some databases that are being backed up to a URL (Azure Blob Storage) with a Credentia.

  I've always used the following (or roudabout) query to identify where the backups are:

  SELECT *
  FROM msdb.dbo.backupset b
  JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
  WHERE database_name = 'MyDB'
  and type = 'd'
  ORDER BY backup_finish_date DESC

  However, with the URL backups the physcial_device_name field is populated with a GUID + INT

  Example: {ABB61E9D-E1C8-4128-AC12-69D48AB096B9}12

  Is there a way to get a URL from this?

  Cheers

  Alex

   

  • This topic was modified 5 years, 7 months ago by alex.sqldba. Reason: Added formatting to code block

• Site Owners

  SSC Guru

  Points: 80342

  More actions

  October 29, 2019 at 3:10 pm

  #3692778

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.

• prettsons

  SSCertifiable

  Points: 7453

  More actions

  November 6, 2019 at 3:32 am

  #3695342

  No, you cannot get URL from GUID. The backup most probably would be done on Blob contained in the Azure storage account. You can reverse engineer to find it out

  SQL Database Recovery Expert

• prettsons

  SSCertifiable

  Points: 7453

  More actions

  November 11, 2019 at 8:34 am

  #3696616

  You could use the following command which returns the URL of the backup file:

  SELECT *

  FROM managed_backup.fn_available_backups ('databasename')

  For more details, please refer to https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/managed-backup-fn-available-backups-transact-sql?view=sql-server-ver15.

  Hope this could help you.

  SQL Database Recovery Expert