How to get the database backup location?

Question

Hi Expert,

I am not sure in which location the database backup is performing. I ran a query and it returned the following output. And also I don't see any database backup job in the SQL Server agent. But the backup is performing every day. Please look into the attach screenshot

Answer 1

Hi @Zahid. Ahamed ,

Please execute code as next to check your device type:

SELECT  
    database_name,  
    backup_finish_date,  
    CASE msdb..backupset.type  
        WHEN 'D' THEN 'Database'  
        WHEN 'L' THEN 'Log'  
    END AS backup_type,  
    physical_device_name,  
 device_type  
FROM msdb.dbo.backupmediafamily  
INNER JOIN msdb.dbo.backupset  
    ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  
--WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 1)  
ORDER BY database_name,backup_finish_date  

Quote from this doc. :SQL-Server-physical_device_name-in-the-Backup-log-shows-GUID
Please verify these rows have value 7 in the column "device_type" or not. The value 7 means "Virtual device". These rows are actually very useful for monitoring, for example it help to know that the Virtual Machine backups are running full database backups on a SQL Server instance. there's no trace of these backup files since they are above the scope of the virtual machine (above the level of your control) - these are triggered by the host,meaning azure in this case (Hyper-V or VMware for example triggers these).

In conclusion: These backups are not related to our backup in the level of the instance.

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

@m Thanks for you clarification. Your given query retuned value is 7 in my case.