question

mandara avatar image
mandara 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
backuptransaction-log
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@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.
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
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.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
3 Likes 3 ·
RE Magnus comment - This is the case for a lot of the system tables. If you query them often enough (or even run SQL Agent Jobs normally), the missing indexes dmv shows up **lots** of possibilities.
0 Likes 0 ·
Blackhawk-17 avatar image
Blackhawk-17 answered
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()) ]]
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.