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

Hi @Zahid. Ahamed ,

Execute code as next:

Use master  
go  
  
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  
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  

BR,
Mia

---

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

Answer 3

The following script should list the database backup history:

SELECT *
FROM msdb.dbo.backupmediafamily AS f
INNER JOIN msdb.dbo.backupset AS b ON f.media_set_id = b.media_set_id;

Answer 4

Thanks for replying. I don't know about this backup device. I ran this above query and it returned the below output:--

Answer 5

@Zahid. Ahamed The physical-device_name as Hexadecimal number represents a tape or Snapshot backup not a backup on local or shared disk. So you would not be able to find it on local disk. The backup is either being performed by some third part backup tool taking backup on tape or some backup tool which takes snapshot backup. In this case you need to get in touch with backup team to get information about the same. Please see similar answer given by me on Stackexchange Thread