cancel
Showing results for 
Search instead for 
Did you mean: 

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

KristjanS
Level 2

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?

1 ACCEPTED SOLUTION

Accepted Solutions

Mark_Solutions
Level 6
Partner Accredited Certified

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

View solution in original post

7 REPLIES 7

Mark_Solutions
Level 6
Partner Accredited Certified

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
Level 2

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
Level 6
Partner Accredited Certified

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
Level 2

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
Level 6
Certified

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.

The standard questions: Have you checked: 1) What has changed. 2) The manual 3) If there are any tech notes or VOX posts regarding the issue

Hi All,

Is there any update on this?

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

Marianne
Moderator
Moderator
Partner    VIP    Accredited Certified
This is a limitation from MS-SQL.
Not NetBackup.