Query backup history

As a quick script tip for today I’ll show you a script I use regularly for reporting purposes.
It shows you the backup history on your instance for successful backups ordered by the most recent first:

-- Get the latest successful backups
-- Shows databasename, backup start & end time, duration, backup file,
-- backup size, compressed backup size (if used) and backup type.

SELECT
bs.database_name AS 'Database Name',
bs.backup_start_date AS 'Backup Start',
bs.backup_finish_date AS 'Backup Finished',
DATEDIFF(MINUTE, bs.backup_start_date, bs.backup_finish_date) AS 'Duration (min)',
bmf.physical_device_name AS 'Backup File',
CASE
WHEN bs.[type] = 'D'
THEN 'Full Backup'
WHEN bs.[type] = 'I'
THEN 'Differential Database'
WHEN bs.[type] = 'L'
THEN 'Log'
WHEN bs.[type] = 'F'
THEN 'File/Filegroup'
WHEN bs.[type] = 'G'
THEN 'Differential File'
WHEN bs.[type] = 'P'
THEN 'Partial'
WHEN bs.[type] = 'Q'
THEN 'Differential partial'
END
AS 'Backup Type',
ROUND(((bs.backup_size/1024)/1024),2) AS 'Backup Size (MB)',
ROUND(((bs.compressed_backup_size/1024)/1024),2) AS 'Compressed Backup Size (MB)'
FROM msdb..backupmediafamily bmf
INNER JOIN msdb..backupset bs
ON bmf.media_set_id = bs.media_set_id
ORDER BY bs.backup_start_date DESC

71 comments to “Query backup history”

You can leave a reply or Trackback this post.
  1. Appreciative for the bewildering posting! I obviously partook in the experience of keeping an eye out for it, you are an extraordinary maker. I will recollect bookmarking your blog and will, when in doubt, return later on. I really need to demand that you happen with your astounding posts. I’m a specialist in objections progression as a subject matter expert. objections make our web premise work amazingly head. with a site, we can without a totally astonishing stretch sell or buy our things in incredibly direct ways. Experiencing nobody genuinely minds in any case, visit my page shakeelmumtaz.com, where you can find concerning web progression as an arranged capable. Thankful to You!

  2. you are truly a excellent webmaster. The website loading speed is incredible.
    It kind of feels that you are doing any distinctive trick.
    Moreover, The contents are masterpiece. you’ve performed a wonderful process in this matter!

  3. You can certainly see your skills in the article you write.
    The world hopes for even more passionate writers such as you who are not afraid to say how they believe.
    All the time follow your heart.

  4. Thanks a bunch for sharing this with all folks you actually recognize
    what you are speaking about! Bookmarked. Please additionally consult with my site
    =). We could have a link exchange contract between us

  5. What i do not realize is in truth how you are no longer really
    much more smartly-favored than you might
    be right now. You are so intelligent. You already know
    therefore considerably with regards to this subject, produced me in my view consider it
    from numerous numerous angles. Its like men and
    women aren’t interested unless it is something to accomplish with Girl gaga!
    Your personal stuffs excellent. At all times handle it up!

  6. Please let me know if you’re looking for a author for your site.
    You have some really good posts and I believe I would be a good
    asset. If you ever want to take some of the load off,
    I’d really like to write some articles for your blog in exchange for a link back to mine.
    Please shoot me an email if interested. Thank you!

  7. Hi to all, for the reason that I am really eager of
    reading this website’s post to be updated regularly. It includes good
    material.

  8. Thank you for any other great article. The place else may just anyone get
    that type of information in such an ideal approach
    of writing? I’ve a presentation next week, and I’m at the look for such information.

  9. Hmm is anyone else encountering problems with the images
    on this blog loading? I’m trying to determine if its a problem on my end or if it’s the blog.

    Any feed-back would be greatly appreciated.

  10. whoah this blog is wonderful i love reading your
    posts. Keep up the great work! You understand, a lot of people are searching around for
    this info, you could aid them greatly.

  11. Great site. Plenty of helpful information here. I am sending it to a few friends ans
    additionally sharing in delicious. And certainly, thanks on your effort!

  12. What i don’t understood is if truth be told how you’re
    now not actually much more smartly-favored than you
    might be now. You’re so intelligent. You recognize thus considerably relating to
    this matter, produced me in my view imagine it from so many various angles.
    Its like men and women don’t seem to be fascinated unless it’s one
    thing to accomplish with Girl gaga! Your individual stuffs excellent.
    At all times maintain it up!

  13. I delight in, result in I found exactly what I used to be taking a look
    for. You have ended my four day long hunt! God Bless you man. Have
    a nice day. Bye

  14. Pretty section of content. I just stumbled upon your website and in accession capital to assert that I acquire actually enjoyed account your blog posts.
    Anyway I’ll be subscribing to your feeds and even I achievement you access consistently rapidly.

  15. Please let me know if you’re looking for a author for your site.
    You have some really great posts and I think I would be a good asset.
    If you ever want to take some of the load off, I’d
    really like to write some content for your blog in exchange for a link back to mine.
    Please blast me an email if interested. Kudos!

  16. fascinating review. Moreover visit absolutely the site which can be gamed judi slot.
    This type of web-site has received appeal by game slot online video games members in Dalam negeri.

    There will be a lot of elegant promos if you ever join these
    days.

  17. important review. Moreover visit the best site to know
    game slot online. The following extraordinary web site
    has received recognition by using slot via pulsa video games avid gamers in Dalam negri.
    There are actually various interesting promos if you ever
    join today.

  18. really interesting review. On top of that visit this specific site to attain agen slot pulsa.
    Fantastic web site has received id from slot via pulsa games family
    in Negara sendiri. Now there are actually several stimulating
    promos if you ever join today.

  19. I’ve been exploring for a bit for any high quality articles or weblog posts on this
    sort of area . Exploring in Yahoo I finally stumbled upon this web site.
    Reading this info So i am satisfied to express that I have an incredibly excellent uncanny feeling I
    found out just what I needed. I most definitely will make sure
    to do not overlook this site and give it a look regularly.

Write a Reply or Comment

Your email address will not be published.