Can you tell me the drive we were backing up server #58 to exactly 8 months ago, and, if possible, it would really help if you could give me the exact file name for me to pull the backup from tape for you.  Or, last night’s backup didn’t finish until after the tapes started, did those backups always take that long and is it taking longer because the databases are actually growing that much?

The answer to those questions tends to be “Ok, no problem.”

SELECT TOP 100 bs.database_name
    , bs.backup_start_date
    , bs.backup_finish_date
    , backup_duration_minutes = (DateDiff(second, backup_start_date, backup_finish_date)+30)/60
    , backup_duration_seconds = DateDiff(second, backup_start_date, backup_finish_date)
    , backup_size_mb = Cast(bs.backup_size / 1024 / 1024 as Int)
    --, compressed_backup_size_mb = Cast(bs.compressed_backup_size / 1024 / 1024 as Int) --SQL 2008+ only
    , backup_type = CASE bs.type 
                        WHEN 'D' THEN 'FULL' 
                        WHEN 'I' THEN 'Diff' 
                        WHEN 'L' THEN 'Log' 
                        ELSE 'UNKNOWN' 
                    END
    , bmf.physical_device_name
FROM msdb..backupset bs
    INNER JOIN msdb..backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
--WHERE bs.database_name like 'msdb'
    --AND bs.type <> 'L'
    --AND backup_start_date BETWEEN '2013-05-13 00:00' AND '2013-05-13 08:00'
ORDER BY Backup_Start_Date DESC

It’s common in my scripts for me to put 10 things in the where clause and comment them out. My problem is that I don’t want to look around for field names to filter my scripts, but I don’t always want to filter my scripts the same way. It works for me, and you’ll either get used to it or rewrite it for your own use.

Filed under: Backups, Scripts, SQL Server Tagged: backup, File, recovery