Forum Discussion

jayoub's avatar
jayoub
Level 2
13 years ago
Solved

SQL transaction log backup during Full backup

I am working in an environment where we are using Backup Exec 2010 on one network and 12 on another.  I am the new DBA on staff and realized that the backup administrators are running full database backup and transaction log backup and they are overlapping.  The transaction log backup is set to run every 4 hours and then the full backup starts at 8:00 pm so there is a transaction log backup running during the full backup.  My concern is

A: Is this a Symantec best practice will the two conflict

B: What will happen if a restore is required (which transaction log do I stop at)

Any help is appreciated

Jeff

12.x and Earlier
Agent for Microsoft SQL Server
Backup and Recovery
Backup Exec
  • pkh's avatar
    pkh
    13 years ago

    B: What will happen if a restore is required (which transaction log do I stop at)

    In your case, you would stop at the one just after the time which you want to restore the database to.  For example, you want to do a point in time restore to 5 p.m. and there is a log backup at 6 p.m.  You would stop at the 6 p.m. log backup.  You don't have to apply the 10 p.m. log backup.

    There is no need to backup the transaction logs if the databases are not being used.  Normally you would only backup the logs during office hours and you would use a time window to restrict the log backups to these hours.

    Take a look at your restore requirements.  If there is no need, you might want to do just one log backup a day.  Do it after your database backup.

     

  • Donald_Eady's avatar
    Donald_Eady
    Level 6
    13 years ago

    The two jobs overlapping do have the potential to cause issue with one another. Are these two jobs in a policy.. if so the policy should force a supercede in that job B should not kick off until job A has completed. if the jobs are in a policy i would suggest editing the policy and adding a template rule that says "if start times conflict <Template A "being the Inc" supercedes <Template B "Being the full">" That way there is no confusion or issue as to which job should be running at 8:00 when the full job kicks off. Are you seeing any type of failures or errors associated with the job. 

  • Kiran_Bandi's avatar
    Kiran_Bandi
    Level 6
    13 years ago

    Incase of standalone jobs: You can set a time window for transaction log backup to ensure that it will not start when full backup is in progress.

  • pkh's avatar
    pkh
    Moderator
    13 years ago

    B: What will happen if a restore is required (which transaction log do I stop at)

    In your case, you would stop at the one just after the time which you want to restore the database to.  For example, you want to do a point in time restore to 5 p.m. and there is a log backup at 6 p.m.  You would stop at the 6 p.m. log backup.  You don't have to apply the 10 p.m. log backup.

    There is no need to backup the transaction logs if the databases are not being used.  Normally you would only backup the logs during office hours and you would use a time window to restrict the log backups to these hours.

    Take a look at your restore requirements.  If there is no need, you might want to do just one log backup a day.  Do it after your database backup.

     

  • jayoub's avatar
    jayoub
    Level 2
    13 years ago
    Thank you very much for the help. Below is a reply to your questions Are these two jobs in a policy No. Are you seeing any type of failures or errors associated with the job. No. Do you suggest that I go through the task of asking the backup administrators to make a change. They are nice people but not sure if they are interested in changing anything.