Backup file storage path

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 462
Sometimes, DBAs need to know where the back up files stored and what is the path of back files.... This helps them to verify whether the backup files are healthy and they can use them for restoring databases ( replicating PRODCUTION databases/data to DEV/TESTing environments)

mainly, backupmediafamily contains the details of backup file paths

SELECT  @@Servername AS ServerName ,
d.Name AS DatabaseName ,
b.Backup_finish_date ,
bmf.Physical_Device_name BackupDeviceName
FROM sys.databases d
INNER JOIN msdb..backupset b ON b.database_name = d.name
AND b.[type] = 'D'
INNER JOIN msdb.dbo.backupmediafamily bmf ON b.media_set_id = bmf.media_set_id
ORDER BY d.NAME ,
b.Backup_finish_date DESC;
GO

Comments or Responses

Login to post response