Post reply

last backup date query

  • June 9, 2009 at 6:31 am

    #131038

    Does anyone know how SQL Server Management Studio knows when the last backup took place?

    The specific query I am looking for is the the one that would populate the properties page if you right click on a database and select properties.

    preferably this query will have nothing to do with sysjob tables, as I am trying to avoid working with these...:hehe:

  • June 9, 2009 at 6:36 am

    #1006635

    i found the answer...:-P and thought i'd share it for those of you who r interested:

    select database_name,max(backup_finish_date) as backup_finish_date from msdb..backupset

    where database_name='Libra'

    group by database_name

    There is also a useful article at:

    http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=LastBackUpDate

  • June 9, 2009 at 8:14 am

    #1006713

    Just a little thing, but may I suggest you write the query more like this:

    select

    database_name,

    max(backup_finish_date) as backup_finish_date

    from

    msdb.dbo.backupset -- really should specify owner (SQL 2000)/schema (SQL 2005/2008)

    where

    database_name = 'Libra'

    group by

    database_name

  • October 6, 2010 at 7:42 am

    #1232525

    Hi! The query is great, but I miss something that I want to use in my environment. I would also like to see the size and the set_id of that last (log) backup.

    Tried this one:

    select Database_name,

    COALESCE(Convert(varchar(20), MAX(backup_finish_date), 113),'Backup Not Taken') as

    LastBackUpTakenDate,

    COALESCE(Convert(float, MAX(backup_size), 101),'NA') as BUsize,

    COALESCE(Convert(float, MAX(backup_set_id), 101),'NA') as BUid

    from msdb.dbo.backupset where type='L'

    GROUP BY Database_name

    But this gives me the last backup date, the maximum backup size, and the maximum backup_set_id. The first and the last are the right values, but I want to see the real size of that last backup. Anyone can help please?

    Thanks, Roland.

    If you pay peanuts you'll get monkey's...

  • October 6, 2010 at 7:58 am

    #1232553

    Roland

    Try this.

    John

  • October 6, 2010 at 8:05 am

    #1232564

    Thanks John for this (complex) query. It gives me some of the correct output, but I have no idea how to filter out only the L backups...

    If you pay peanuts you'll get monkey's...

  • October 6, 2010 at 8:08 am

    #1232569

    Mmmm.... surely by adding the line [font="Courier New"]WHERE r.type = 'L'[/font] at the end?

    John

  • October 6, 2010 at 8:47 am

    #1232639

    Thanks! That worked. Sorry for my newbie question, the query was a little too complex for me....

    Regards,

    Roland

    If you pay peanuts you'll get monkey's...

  • March 23, 2011 at 5:36 am

    #1302400

    Hi All and thanks John this is a great script i will be using this from now on, but is there a way of outputting this in to a more printable format??

    Thanks in advance!

  • March 23, 2011 at 5:38 am

    #1302401

    extremenovice (3/23/2011)

    Hi All and thanks John this is a great script i will be using this from now on, but is there a way of outputting this in to a more printable format??

    Thanks in advance!

    In conjunction with my previous post, how would i be able to run this for all my servers at once?

    Once again thanks in advance!

  • March 23, 2011 at 5:45 am

    #1302403

    What do you mean by a more printable format?

    To run on all your servers at once, I think you can set up a job and target it at more than one server. I've never actually done this myself, so I would advise you to go and read about it and start a new thread if you find yourself still needing help in setting it up.

    John

  • March 23, 2011 at 9:13 am

    #1302541

    Thanks for that John.

    I was thinking of something that i could automate and have it output to a report as apposed to running the query each and every time i needed that information. At the moment I've not found anything like that within sql apart from this tip (http://www.mssqltips.com/tip.asp?tip=2012&home) it is different to each version of sql, but the two reports i have used are ok. I will use your script though that's for sure and will research further for running the job across many servers. 🙂

  • March 23, 2011 at 9:22 am

    #1302543

    Typically you want to be aware of issues, not making checks to see what has run. That's a bit of a waste of your time. Assume the backups are working correctly and set up monitoring to let you know if they aren't working.

    I would suggest that you setup a small database on each instance and then have a job that runs this query and stores the results in that database. You can setup a query in a job to alert you if something in the result is amiss and needs to be looked at.

  • March 23, 2011 at 9:28 am

    #1302549

    Steve Jones - SSC Editor (3/23/2011)

    Typically you want to be aware of issues, not making checks to see what has run. That's a bit of a waste of your time. Assume the backups are working correctly and set up monitoring to let you know if they aren't working.

    And make sure that the monitoring will tell you if the backup hasn't run, not just if it ran and failed.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 23, 2011 at 9:34 am

    #1302555

    Yes. We use the query to report from all our servers to a central server. That is then compared against a list of databases that should have been backed up. Finally, we check that the backup files exist in the specified locations.

    John

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply