Comparing consecutive datetime rows for schedules

  • @Grant Fletcher and @Michael L John.
    As suggested I looked up the backupset tables and I think we can still
    come up with schedules and timings for all databases and their respective
    kind of backups if we can use most recent sample data (last 30 days)
    assuming there is only one process(sql or 3rd party) tool which does backups.
    And where can I find the information regarding which tool is doing backups
    - sql or 3rd party.Is it available in the msdb backupset tables.I read a blog
    which says even if a 3rd party does backups sometimes the msdb tables
    can still carry sqlserver to be the one doing backups.

    My query gives me data in this format from the msdb backup tables.
    (sample data from last 30 days)

    MyServerA MyDB 10/24/20 1:00 AM D:\ABC.bak FULL
    MyServerA MyDB 10/17/20 1:00 AM D:\ABC.bak FULL
    MyServerA MyDB 10/10/20 1:00 AM D:\ABC.bak FULL
    MyServerA MyDB 10/3/20 1:00 AM D:\ABC.bak FULL
    MyServerA MyDB 10/23/20 1:00 AM D:\ABC.bak DIFF
    MyServerA MyDB 10/22/20 1:00 AM D:\ABC.bak DIFF
    MyServerA MyDB 10/21/20 1:00 AM D:\ABC.bak DIFF
    MyServerA MyDB 10/20/20 1:00 AM D:\ABC.bak DIFF
    MyServerA MyDB 10/19/20 1:00 AM D:\ABC.bak DIFF
    MyServerA MyDB 10/18/20 1:00 AM D:\ABC.bak DIFF
    MyServerA MyDB 10/16/20 1:00 AM D:\ABC.bak DIFF

    Full backups on Saturdays-weekly once
    Diffs all days but on saturdays
    And tlogs every 4 hours every day

    How can I use the above data form the backup tables in msdb and
    conclude as below in 3 rows for each database for each type of
    backup.I believe I need to compare consecutive datetime column
    rows and conclude as below for each database for each type of
    backup to comeup with the belwo format.

    MyServerA MyDB Weekly Full Backup on Saturdays at 1:00 AM D:\ABC.bak FULL
    MyServerA MyDB Daily Diff Backup at 1:00 AM D:\ABC.diff DIFF
    MyServerA MyDB Daily T-Log Backup every 4 hours D:\ABC.log T-Log

    And also I have to do this for all databases on the server to
    find out full/diff/tlog job schedules
    Any suggestions.

    I figured I can take sample data for any database from the
    last 30days and then I can come up with something like this.

    MyServerA MyDB Weekly Full Backup on Saturdays at 1:00 AM D:\ABC.bak FULL
    MyServerA MyDB Daily Diff Backup at 1:00 AM D:\ABC.diff DIFF
    MyServerA MyDB Daily T-Log Backup every 4 hours D:\ABC.log T-Log

    Any suggestions please.

    • This topic was modified 4 weeks, 1 day ago by  mtz676.
    • This topic was modified 4 weeks, 1 day ago by  mtz676.
    • This topic was modified 4 weeks, 1 day ago by  mtz676.
  • There's a lot here.  I'm not sure I am 100% clear about what you are looking for. I will take stab at what I think I understand you are looking for.

    As suggested I looked up the backupset tables and I think we can still

    come up with schedules and timings for all databases and their respective

    kind of backups if we can use most recent sample data (last 30 days)

    The backupset table is not a schedule.  A schedule implies what is supposed to occur, backupset lists what actually occurred.   I've attached a script that I give to the junior DBA's that lists the backups by type for each database.

    And where can I find the information regarding which tool is doing backups

    - sql or 3rd party.Is it available in the msdb backupset tables.I read a blog

    which says even if a 3rd party does backups sometimes the msdb tables

    can still carry sqlserver to be the one doing backups.

    Sorry.  This may sound nasty, but if a third party application is doing backups, someone in your organization should know about it.  There is likely an agent or service that is running on your servers that you can look for.  A typical third party setup would be a centralized backup management server, and agents on each individual server.  I suggest you track that down and find it.  It greatly concerns me that you are guessing about something as critical as backups.

    And also I have to do this for all databases on the server to

    find out full/diff/tlog job schedules

    Any suggestions

    Like a said above, the tool being used to take backups will give you the schedule.  A backup may be scheduled to run at midnight, but the actual backup of a database may not occur until much later.

     

    Attachments:
    You must be logged in to view attached files.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • All you can do with the information you have is infer the schedule and plan. That's it. You can't know what the intent is. You can't know what the Service Level Agreement is. You can't know what the Recovery Point Objective or Recovery Time Objective is. To know all that, you have to work with the business and with the people who set up the schedules, wherever they are, using whatever tool.

    What you have is not the intent or the plan. You have the results. That's all you'll ever have. You can infer the plan from those results, but it's just an inference. If you want the plan, you should go where it is.

    If I can ask, what are you trying to solve here? What's the concern? I'm a little unclear where the end game is. Will you somehow start making changes to backups based on this info? Are you planning to take this to the business to get an SLA and set the RPO & RTO? I'm just a little confused what you think you'll have at the end of this operation.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Ah... yeah.   I get what you're trying to do.  You're trying to look at "actuals" by database to ensure that  the backups are not only following a backup plan but you're also trying to determine what the plan is by database so that you can check on whether it's sufficient or not.  It's also great for proving things to auditors, as well.

    I like the idea a lot and so let's turn this into a two way street where we'll both benefit.  You post your actual code that you use to come up with the summary and I'll have a go at making the output you've asked for.  I'll use your code to create the "test data" that I need to pull this off using my own servers as the source of that data.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Close enough and yes .(excepting the auditors part !) I will post code once I make progress.

  • Yes, I am trying to draw the inference from the results. If necessary I may need to make changes but not yet there.

     

  • Apologies, I was not clear entirely in my first post.

    I got the table result from msdb.dbo.backupset, master.sys.sysdatabases and msdb.dbo.backupmediafamily.

    I am trying to draw inference from the results and see the backup schedules and then see if we need to make any changes. But first I need to know what is going on.

  • mtz676 wrote:

    Apologies, I was not clear entirely in my first post.

    I got the table result from msdb.dbo.backupset, master.sys.sysdatabases and msdb.dbo.backupmediafamily.

    I am trying to draw inference from the results and see the backup schedules and then see if we need to make any changes. But first I need to know what is going on.

    Does the script I attached not get you almost everything you need?

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • mtz676 wrote:

    Close enough and yes .(excepting the auditors part !) I will post code once I make progress.

    I'm not quite sure what you mean by that.  Your original post says you already have a script that creates the output you cited in your first post.  That's all I'm after.

    As a bit of a sidebar, all of your posts have seemed quite evasive when people ask for additional information they need to help you.  You're actually driving people away from helping you... like you just did me. 🙁

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Michael L John wrote:

    mtz676 wrote:

    Apologies, I was not clear entirely in my first post.

    I got the table result from msdb.dbo.backupset, master.sys.sysdatabases and msdb.dbo.backupmediafamily.

    I am trying to draw inference from the results and see the backup schedules and then see if we need to make any changes. But first I need to know what is going on.

    Does the script I attached not get you almost everything you need?

    From the wording in your reply, I didn't even look for an attachment. 😉  Perhaps the op ran into the same issue.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Yes I do. I thought you meant after I make progress on that script. Anyways here it is.

    SELECT
    @@servername [ServerName]
    ,master.sys.sysdatabases.name [DatabaseName]
    ,msdb.dbo.backupset.backup_start_date [BACKUP START]
    ,msdb.dbo.backupmediafamily.physical_device_name [FILE Location]
    ,CASE
    WHEN msdb.dbo.backupset.type = 'D'
    THEN 'FULL'
    WHEN msdb.dbo.backupset.type = 'I'
    THEN 'Diff'
    WHEN msdb.dbo.backupset.type = 'L'
    THEN 'Logs'
    END [BACKUP TYPE]
    FROM
    msdb.dbo.backupmediafamily,
    master.sys.sysdatabases
    LEFT OUTER JOIN
    msdb.dbo.backupset
    ON master.sys.sysdatabases.name = msdb.dbo.backupset.database_name
    WHERE msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
    and master.sys.sysdatabases.name = 'MyDB'
    and msdb.dbo.backupset.type = 'D'
    and CONVERT(date, backup_start_date) > getdate()-30
    ORDER BY [BACKUP START] DESC;

     

     

  • Does this get you closer?

    SELECT @@servername [ServerName], 
    SDB.name [DatabaseName],
    BS.backup_start_date [BACKUP START],
    BMF.physical_device_name [FILE Location],
    CASE
    WHEN BS.type = 'D'
    THEN 'FULL'
    WHEN BS.type = 'I'
    THEN 'Diff'
    WHEN BS.type = 'L'
    THEN 'Logs'
    END [BACKUP TYPE]
    FROM master.sys.sysdatabases SDB
    LEFT OUTER JOIN msdb.dbo.backupset BS ON SDB.name = BS.database_name
    LEFT OUTER JOIN msdb.dbo.backupmediafamily BMF ON BS.media_set_id = BMF.media_set_id
    WHERE SDB.name = 'YourDB'
    AND backup_start_date >= DATEADD(day, -30, GETDATE())
    ORDER BY BS.backup_start_date DESC;

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • Michael L John wrote:

    There's a lot here.  I'm not sure I am 100% clear about what you are looking for. I will take stab at what I think I understand you are looking for.

    As suggested I looked up the backupset tables and I think we can still

    come up with schedules and timings for all databases and their respective

    kind of backups if we can use most recent sample data (last 30 days)

    The backupset table is not a schedule.  A schedule implies what is supposed to occur, backupset lists what actually occurred.   I've attached a script that I give to the junior DBA's that lists the backups by type for each database.

    And where can I find the information regarding which tool is doing backups

    - sql or 3rd party.Is it available in the msdb backupset tables.I read a blog

    which says even if a 3rd party does backups sometimes the msdb tables

    can still carry sqlserver to be the one doing backups.

    Sorry.  This may sound nasty, but if a third party application is doing backups, someone in your organization should know about it.  There is likely an agent or service that is running on your servers that you can look for.  A typical third party setup would be a centralized backup management server, and agents on each individual server.  I suggest you track that down and find it.  It greatly concerns me that you are guessing about something as critical as backups.

    And also I have to do this for all databases on the server to

    find out full/diff/tlog job schedules

    Any suggestions

    Like a said above, the tool being used to take backups will give you the schedule.  A backup may be scheduled to run at midnight, but the actual backup of a database may not occur until much later.

    Michael,

    The aim you were trying to achieve with 3 scans of backup_set's could be easily achieved with a single LEFT JOIN and CASE statements in the SELECT part of the query.

  • Sergiy wrote:

    Michael L John wrote:

    There's a lot here.  I'm not sure I am 100% clear about what you are looking for. I will take stab at what I think I understand you are looking for.

    As suggested I looked up the backupset tables and I think we can still

    come up with schedules and timings for all databases and their respective

    kind of backups if we can use most recent sample data (last 30 days)

    The backupset table is not a schedule.  A schedule implies what is supposed to occur, backupset lists what actually occurred.   I've attached a script that I give to the junior DBA's that lists the backups by type for each database.

    And where can I find the information regarding which tool is doing backups

    - sql or 3rd party.Is it available in the msdb backupset tables.I read a blog

    which says even if a 3rd party does backups sometimes the msdb tables

    can still carry sqlserver to be the one doing backups.

    Sorry.  This may sound nasty, but if a third party application is doing backups, someone in your organization should know about it.  There is likely an agent or service that is running on your servers that you can look for.  A typical third party setup would be a centralized backup management server, and agents on each individual server.  I suggest you track that down and find it.  It greatly concerns me that you are guessing about something as critical as backups.

    And also I have to do this for all databases on the server to

    find out full/diff/tlog job schedules

    Any suggestions

    Like a said above, the tool being used to take backups will give you the schedule.  A backup may be scheduled to run at midnight, but the actual backup of a database may not occur until much later.

    Michael,

    The aim you were trying to achieve with 3 scans of backup_set's could be easily achieved with a single LEFT JOIN and CASE statements in the SELECT part of the query.

    Well, it’s an old script that I rarely use. It was part of a “here’s stuff, can you figure this out” training that I stuck together a while ago for some junior DBA’s.

    The exercise was to take that code, figure it out, add backup sizes and elapsed time to it, and automate it to send a morning report.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • Michael L John wrote:

    There's a lot here.  I'm not sure I am 100% clear about what you are looking for. I will take stab at what I think I understand you are looking for.

    As suggested I looked up the backupset tables and I think we can still

    come up with schedules and timings for all databases and their respective

    kind of backups if we can use most recent sample data (last 30 days)

    The backupset table is not a schedule.  A schedule implies what is supposed to occur, backupset lists what actually occurred.   I've attached a script that I give to the junior DBA's that lists the backups by type for each database.

    And where can I find the information regarding which tool is doing backups

    - sql or 3rd party.Is it available in the msdb backupset tables.I read a blog

    which says even if a 3rd party does backups sometimes the msdb tables

    can still carry sqlserver to be the one doing backups.

    Sorry.  This may sound nasty, but if a third party application is doing backups, someone in your organization should know about it.  There is likely an agent or service that is running on your servers that you can look for.  A typical third party setup would be a centralized backup management server, and agents on each individual server.  I suggest you track that down and find it.  It greatly concerns me that you are guessing about something as critical as backups.

    And also I have to do this for all databases on the server to

    find out full/diff/tlog job schedules

    Any suggestions

    Like a said above, the tool being used to take backups will give you the schedule.  A backup may be scheduled to run at midnight, but the actual backup of a database may not occur until much later.

    Wow.  That's some old and still working code, Michael.  It still has a call to dbo.sysdatabases in it that still works.  That's a great audit report, especially since it's so very simple.

    Unfortunately for me, I get a shedload of "Logfile is Missing" failures.  It's not the fault of the code.  It's my "fault" and it's not a fault... it's a feature.  It's like when I do the laundry... I only wash the dirty clothes and not the clean ones.  I do the same thing with transaction log backups... if the "log_reuse_wait_desc" in the sys.databases view contains "NOTHING" for a database, then nothing in the database has changed and I skip that backup.

    If you combine the latter tidbit of knowledge with your report, it turns out that I have several databases that haven't suffered any modifications for well over 30 days and I'm going to have to get after some people. 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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