asked
Wants to find the Transaction log file from backup history
Wants to find the Transaction log file from backup history. Pls help. Thr is no table where in i can find the TRN or Diff or BAK File
@mandara - we haven't heard from you on whether what we suggested helped. In my solution you will find the file name under the [Backup Destination] column.
Look in the MSDB database. The tables containing information about backups are named "backup..." Table "backupset" contains header information about the backup. Table "backupmediaset" and "backupmediafamily" contain information about the backup files themselves - this is what you're looking for.
I don't know about 2008, but in SQL Server 2000 and SQL Server 2005, I find table backupset to be poorly indexed. If the server installation is old, and msdb has not been cleared from historical backup data, querying the database can be painful.. I have created one index that speeds up my cleanup tasks: CREATE INDEX [backupset_mediaset_id] ON [dbo].[backupset] ( [media_set_id], [backup_set_id] ) I haven't dug very deep into it, but I guess the DB could benefit from a couple more indices.
This is what I use: SELECT BS.server_name AS [SQL Server] , BS.database_name AS [Database] , CAST((BS.backup_size/1024/1024/1024) AS DECIMAL(10,2)) AS [Size in GB] , CAST((BS.backup_size/1024/1024) AS DECIMAL(10,2)) AS [Size in MB] , CONVERT(CHAR(8), BS.backup_finish_date - BS.backup_start_date, 8) AS [Duration] , CASE BS.type WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Log' WHEN 'D' THEN 'Full' END AS [Type] , BS.backup_start_date AS [Start] , BS.backup_finish_date AS [Finish] , BMF.physical_device_name AS [Backup Destination] FROM msdb.dbo.backupset AS BS JOIN msdb.dbo.backupmediafamily BMF ON BMF.media_set_id = BS.media_set_id WHERE DATEDIFF(DAY, BS.backup_start_date, getdate()) ]]
