1

I'm working on a Maintenance Plan for doing Backups on my server. I just want to know if SQL Server maintains a list of the backup files that were created during the backup process?

Thanks a lot!

Smiley
  • 3,207
  • 13
  • 49
  • 66
  • `Sql Server Agent` --> `Jobs` Node --> Right click the `Backup job` --> `View History` OR `Management` Node --> `Maintenance plans` --> Right click the `Backup job` --> `View History`. – M.Ali Jan 22 '14 at 19:32
  • Hi! Thanks for your response. But I'm not looking for the Run History, instead, I am looking for a list of the actual backup files that were created by the plan. Thanks! – Smiley Jan 22 '14 at 19:44

2 Answers2

2
USE DatabaseName
GO

SELECT 
s.database_name,
m.physical_device_name,
CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
s.backup_start_date,
CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,
CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = DB_NAME() -- Remove this line for all the database
ORDER BY backup_start_date DESC, backup_finish_date
GO

By Pinal Dave SOURCE

M.Ali
  • 67,945
  • 13
  • 101
  • 127
  • 1
    I was checking SQLAuthority earlier but didn't see this one! This is exactly what i'm looking for! Thanks so much! – Smiley Jan 22 '14 at 20:09
1

For get this information using SQL you can run the following query

USE msdb
Go
SELECT *
FROM backupfile

complete list of backup history tables and explation of each one can be foound on MSDN http://technet.microsoft.com/en-us/library/ms188062.aspx