backupset

[qsac]

Does anyone know of a way i can get the physical backup file name, eg Backup.bak from the msdb.dbo.backupset table?

 

[hmckillop]

Its not exactly what you want but should help

SELECT  
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
   msdb.dbo.backupset.database_name,  
   msdb.dbo.backupset.backup_start_date,  
   msdb.dbo.backupset.backup_finish_date, 
   msdb.dbo.backupset.expiration_date, 
   CASE msdb..backupset.type  
       WHEN 'D' THEN 'Database'  
       WHEN 'L' THEN 'Log'  
   END AS backup_type,  
   msdb.dbo.backupset.backup_size,  
   msdb.dbo.backupmediafamily.logical_device_name,  
   msdb.dbo.backupmediafamily.physical_device_name,   
   msdb.dbo.backupset.name AS backupset_name, 
   msdb.dbo.backupset.description 
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() - 7)  
ORDER BY  
   msdb.dbo.backupset.database_name, 
   msdb.dbo.backupset.backup_finish_date

"I'm living so far beyond my income that we may almost be said to be living apart

 

[mutley1]

Pretty much along the same lines as Hmc's. I use this for the last known full backup.

USE MSDB
GO
SELECT
	a.database_name,
	a.backup_size,
	convert (decimal(30,2), (a.backup_size + SUM(1536)) / 1024) As SizeInKB,
	convert (decimal(30,2), (a.backup_size + SUM(1536)) / 1024 / 1024) As SizeInMBs,
	convert (decimal(7,3), (a.backup_size + SUM(1536)) /1024 / 1024 / 1024) As SizeInGBs,
	a.backup_finish_date,
	b.physical_device_name
	    
	    
FROM (
	SELECT database_name, MAX (backup_finish_date) as backup_finish_date
	FROM backupset
	WHERE backup_finish_date > '2005-12-31'
	AND media_set_id IN
	(SELECT media_set_id FROM backupmediafamily
	 WHERE type = 'D')
	GROUP BY database_name) z

JOIN

backupset a on z.database_name = a.database_name
	    and z.backup_finish_date = a.backup_finish_date
JOIN
backupmediafamily b on a.media_set_id = b.media_set_id

GROUP BY a.database_name, a.backup_size, a.backup_finish_date, b.physical_device_name
ORDER BY a.database_name

HTH,

M.