List SQL backups

,

During the process of developing a script to make copies of local SQL database and transaction log backups, I needed to come up with an easy way of locating the backup files. This script queries the SQL2000 backup tables in the msdb database and returns backup start dates, types (D=database, L=Log, I=Differential) and the location of the backup file.

The variable @DBName should be set to the name of the database you want to query on. It is not case sensitive unless your collation is.

The variable @Days should be set to how many days back in the records you want to list backups for.

DECLARE @DBName sysname
DECLARE @Days int

SET @DBName='Northwind'
SET @Days=2

SELECT  S.backup_start_date,
S.type,
M.physical_device_name 
FROM msdb..backupset S 
JOIN msdb..backupmediafamily M ON M.media_set_id=S.media_set_id
WHERE S.database_name = @DBName
AND DATEDIFF(DAY,S.backup_start_date,GETDATE()) < @Days

Rate

Share

Share

Rate