SQL Backup File location and date time


So, you want to see when you’ve backuped up what/where and when. Certainly something that’s worth keeping an eye on, but how do you find it? Here’s how

select server_name,
    backupset.database_name,
    backupset.backup_start_date,
    backupset.backup_finish_date,
    backupset.backup_size,
    backupmediafamily.physical_device_name
from msdb.dbo.backupset
    inner join msdb.dbo.backupmediafamily
    on backupmediafamily.media_set_id = backupset.media_set_id
order by backupset.backup_finish_date desc

 

So, if you run your backups like I do (just to a named file and not so much media sets) you could use this to do a restore!

Advertisements

About jrich

I am the Solutions Architect for Apex Learning in Seattle WA. I've been working with computers since I was 13. Started programming when I was 14. Had my first IT job as tech support at an ISP at the age of 15 and became a network admin at the age of 17. Since then I've worked at a variety of small to mid size companies supporting, maintaining and developing all aspects of IT. Mostly working with Windows based networks but have recently been working with Solaris system as well. I created this blog mostly as a place for me to take my own notes, but also share things that I had a hard time finding the info for.

Posted on August 3, 2011, in SQL Server and tagged , , . Bookmark the permalink. Leave a comment.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: