Post reply

Where is my backup?

  • April 14, 2016 at 5:24 pm

    #315696

    Hi guys,

    I have scheduled full SQL backup on 1AM every morning and then two differential backups on 7AM and 1PM. I have also put hourly log backup in place around a week ago.

    But I found my full backup file was not there even the job activity monitor say it was successful. Is that because the log was running at the same time which was 1AM? I am thinking change the full backup time to be 1:30AM, but if that what will happen to the log back up on 2AM as the full backup will take around 1 and half hour?

    Thanks heaps.

    Daniel

  • April 15, 2016 at 3:22 am

    #1872254

    Hi,

    Run this script and see what you get .. !

    SELECT DISTINCT TOP (100)

    bss.database_name ,

    bm.physical_device_name,

    bss.backup_size ,

    bss.backup_start_date ,

    bss.backup_finish_date

    FROM

    msdb.dbo.backupmediafamily AS bm

    INNER JOIN msdb.dbo.backupmediaset AS bs ON bm.media_set_id = bs.media_set_id

    INNER JOIN msdb.dbo.backupfile AS bf

    INNER JOIN msdb.dbo.backupset AS bss ON bf.backup_set_id = bss.backup_set_id ON bs.media_set_id = bss.media_set_id

    WHERE

    bss.type in ( 'D','I','L')

    ORDER BY

    bss.backup_start_date DESC

  • April 15, 2016 at 8:44 pm

    #1872547

    dxu (4/14/2016)

    Hi guys,

    I have scheduled full SQL backup on 1AM every morning and then two differential backups on 7AM and 1PM. I have also put hourly log backup in place around a week ago.

    But I found my full backup file was not there even the job activity monitor say it was successful. Is that because the log was running at the same time which was 1AM? I am thinking change the full backup time to be 1:30AM, but if that what will happen to the log back up on 2AM as the full backup will take around 1 and half hour?

    Thanks heaps.

    Daniel

    Do you have a task or job somewhere that deletes old backups? Might just be a timing 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.

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

  • April 17, 2016 at 5:04 pm

    #1872630

    Thanks guys. I think I was correct. I change the full backup to start on 1:30AM everyday and log backup to run every two hours start on 3AM. It looks ok in last two days. So it is most likely there was some conflicts when the full backup and log backup start at exactly same time.

    Thanks

  • April 18, 2016 at 9:40 am

    #1872803

    dxu (4/17/2016)

    Thanks guys. I think I was correct. I change the full backup to start on 1:30AM everyday and log backup to run every two hours start on 3AM. It looks ok in last two days. So it is most likely there was some conflicts when the full backup and log backup start at exactly same time.

    Thanks

    By themselves, there is NO conflict between FULL and Transaction Log backups. There's no reason to juggle schedules there because there's no conflict unless the transaction logs are absolutely huge and cause disk chatter on the backup disks. Personally, I'd switch the log file backups to at least once every half an hour. That will reduce such chances for such disk chatter, allow you to reduce the sizes of your log files (really important for faster restores), and improve your RPO during a DR situation.

    --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.

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

  • April 18, 2016 at 4:59 pm

    #1872919

    Hi Jeff,

    Thanks for your reply.

    The full back up normally take over than an hour. For example full backup start on 1AM, and finish around 2:30AM. The log is backed up every hour. So the log backup on 2AM is still looking at old full backup from previous day?

    I do have a couple of lines of code to delete old backup. But I only delete the backup from three days ago or earlier. It has been running ok for month so should not be the issue.

    Regards

    Daniel

  • April 18, 2016 at 7:01 pm

    #1872927

    dxu (4/18/2016)

    Hi Jeff,

    Thanks for your reply.

    The full back up normally take over than an hour. For example full backup start on 1AM, and finish around 2:30AM. The log is backed up every hour. So the log backup on 2AM is still looking at old full backup from previous day?

    I do have a couple of lines of code to delete old backup. But I only delete the backup from three days ago or earlier. It has been running ok for month so should not be the issue.

    Regards

    Daniel

    Yes and no. You have to understand the log chain. If you took only one proper full backup 5 years ago and never broke the chain, you could restore to a point in time. If you had any of a hundred full backups and never broke the log chain, ANY of the full backups and the log files that follow (in LSN order) could be used to do a restore.

    Once the log file chain starts, it matters little when you take subsequent full backups provided the log chain isn't broken.

    If you take more frequent log file backups, then the log file backups will be shorter. If the full backup you're taking fails, then the previous full backup and all the log files that follow will effectively get you to a point in time restore.

    All that being said, I'll say it again... there is absolutely no reason to delay taking log file backups while full backups are running. Make it easy on yourself... schedule full backups without regard to when log file backups occur and vice versa. Log files don't look back at anything. They're based on log sequence number and it doesn't have to be the latest full backup to use them for a restore.

    Difs are a different story. They record only the extents that have changed since the last full backup.

    --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.

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

Viewing 7 posts - 1 through 6 (of 6 total)

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