Forum Discussion

KristjanS's avatar
KristjanS
Level 2
13 years ago

MS SQL 2005 Full and Log backups at the same time.

Hello,

Im in the process of testing and implementing the Netbackup 7.1 Enterprise solution in our enviroement. Specifically for backing up MS SQL 2005 databases.

Some of the databases require only daily or weekly full backups but a few more important ones require frequent log backups.

For example a specific database requires log backup every 15 minutes 24/7 and a daily full backup. MS SQL 2005 can backup a database and its log at the same time and our current solution is using SQL scripts that are scheduled using the SQL Agent.

Backing up the log or data with Netbackup works fine separately but if either is allready running when the other tries to start then Netbackup logs an error message "INFO Error <DB_Name> is involved in another operation." Just to clarify this is a problem only when trying to backup the data and log of the same database.

If I initiate a log backup by an adhoc SQL query at the same time netbackup is allready running a full backup of the database then everything works fine.

So my question is does Netbackup support what I am trying to do?

7.1.x and Earlier
Agent for Microsoft SQL Server
Backup and Recovery
NetBackup
SQL
Windows
  • Mark_Solutions's avatar
    Mark_Solutions
    13 years ago

    NetBackup supports a single operation at any one time

    You need to schedule your backups so logs stop running when the full backup runs and do not resume until that has completed.

    You also need to ensure that your SQL does not have a logs maintenance setup as if that ran during the day then all subsequent NetBackup logs backups would fail until after the next Full backup.

    What you want is fine and many people do it, you just need to schedule it carefully

    The minimum frequency of a job by default is 1 hour so if you need 15 minute scheduling you need to use a workaround - I will try and dig the link out for you

    Hope this helps

  • Mark_Solutions's avatar
    Mark_Solutions
    Level 6
    13 years ago

    NetBackup supports a single operation at any one time

    You need to schedule your backups so logs stop running when the full backup runs and do not resume until that has completed.

    You also need to ensure that your SQL does not have a logs maintenance setup as if that ran during the day then all subsequent NetBackup logs backups would fail until after the next Full backup.

    What you want is fine and many people do it, you just need to schedule it carefully

    The minimum frequency of a job by default is 1 hour so if you need 15 minute scheduling you need to use a workaround - I will try and dig the link out for you

    Hope this helps

  • KristjanS's avatar
    KristjanS
    Level 2
    13 years ago

    Hello,

    Thank you for your response. I allready have the solution for running backups every 15 minutes. I use the Windows Scheduler to start a script that runs the dbbackex.exe utility. That part works fine.

    However I think you misunderstood my desired end solution.

    Our SLA for this specific database requires a log backup be taken every 15 minutes and a daily full backup. MS SQL 2005 supports running a log and full backup of a database at the same time.

    I'll describe the solution we have now without Netbackup:

    SQL agent has a schedule that runs a log backup script every 15 minutes and another schedule that runs a full backup of the same database once per day. The full backup takes over 90 minutes but the log backups keep running every 15 minutes regardless. One backup type doesnt conflict with the other so we have a much lower potential data loss if there is a failiure during the full backup window. The other important factor is that we dont have to worry about scheduling conflicts and dont have to sacrifice data protection to prevent scheduling conflicts.

    So basically I want to achieve a similar end solution with netbackup.

  • Mark_Solutions's avatar
    Mark_Solutions
    Level 6
    13 years ago

    I dont believe you can run both at the same time

    There are retry options that can be added to the scripts but that may not be a great help

    You could use snapshots during which the database backup would snapshot the database almost instantly and then back up the snapshot so that should not interferr with the logs backups (haven't actually tried this)

    Or you can use SQL to dump the transaction logs and back up those dumps every 15 minutes so that again it does not interferr with NetBackup

    By default I dont think it will do what you want without errors but I am sure someone else will provide ,ore information or confirm what I am saying

  • KristjanS's avatar
    KristjanS
    Level 2
    13 years ago

    Thank you for the suggestions, I will look into them.

    However in the meantime maybe someone with a production enviroment that has similar high requirements for recoverablity can describe how they designed their backup when using Netbackup 7.1

  • Michael_G_Ander's avatar
    Michael_G_Ander
    Level 6
    13 years ago

    Wonder why that has been changed in 7.1 before you could run full & log backup at same time and this supported by MS SQL.

    • atrivedi's avatar
      atrivedi
      Level 1
      8 years ago

      Hi All,

      Is there any update on this?

      Is full and and transaction logs are running at same time?

      • Marianne's avatar
        Marianne
        Level 6
        8 years ago
        This is a limitation from MS-SQL.
        Not NetBackup.