Or you can start speaking up and query the backupset…

Today I needed to get the file name for the latest full back. I am using it so I can restore a database using an SSIS packages onto another server. Since there are full, diffs and logs, I had to specify type “D” in the code. That sounds a little off, but L = Log, I = Diff and D = Full. I debating on putting the subquery into a CTE, but this is such a quick hit, I did it the dirty way. I hope it helps you!

SELECT physical_device_name,
backup_size/1024.0 AS BackupSizeKB
FROM msdb.dbo.backupset bINNER JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
WHERE database_name = 'MyDatabase' AND type = 'D' AND backup_start_date = (SELECT MAX(backup_start_date) FROM dbo.backupset WHERE database_name = 'MyDatabase' AND type = 'D')
ORDER BY backup_finish_date DESC

About andreaallred

SQL Server and helping people is my passion. If I can make someone laugh, I know I have made a difference.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s