Solved

Taking SQL log backups once a day with Netbackup

Posted on 2014-10-13
7
350 Views
Last Modified: 2014-10-15
Hello

We currently take a log backup once an hour, a differential once a day and a full backup once a week. Previously, we did this locally on each SQL Server but we have since migrated to using Netbackup as that stores the backups off the SAN.

However, we encountered a problem whereby retrieving the numerous log backups would simply fail via the Netbackup Agent GUI and Symantec have been less than helpful in this regard. A colleague managed to create a PowerShell script as a workaround which works fine.

Moving forward, it has been proposed that we reduce the number of log backups to once a day on Netbackup and take copy_only log backups on the SQL Server. Can anyone see a major problem with this?
0
Comment
Question by:Jay Carax
  • 3
  • 3
7 Comments
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
I know that's possible to run a BACKUP LOG with COPY_ONLY option but I can't see how that's an advantage since doesn't truncate the transaction log file, meaning that will grow forever or at least until fill the disk.
What's the error from Netbackup?
0
 

Author Comment

by:Jay Carax
Comment Utility
The non copy_only backups will run nightly on netbackup and will truncate the logs from there. We have enough storage to deal with that.

And there is no real error in netbackup, it just hangs trying to enumerate the files. Their solution isn't designed to retrieve backups from large consolidated environments as there is no way to filter out Sql instances or databases. It simply retrieves everything on the server.

Symantec have said that there is plans to resolve this in 2016 which leaves us with a problem.

I'm just not sure you can restore multiple copy_only logs so I am going to test that out now, I just thought I'd ask on here to see if anyone had experienced this before.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
The non copy_only backups will run nightly on netbackup and will truncate the logs from there.
Only if they are transaction log backups.
And there is no real error in netbackup, it just hangs trying to enumerate the files.
Did you try restore a file by each time keeping the database on recover mode until apply the last restore?
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 6

Expert Comment

by:LHT_ST
Comment Utility
We have come across a similar issue with NetBackup directly acting as the backup manager. We do hourly log backups with 2 full per day.

As a workaround we now have a SQL script back up the databases to a network share (in a different datacentre) and then use NetBackup to back up the share.
0
 

Author Comment

by:Jay Carax
Comment Utility
Thanks for responding.

Vitor, yes we run log backups from netbackup and indeed I am aware that the database must be in no-recovery mode in order to apply further transaction logs. Unfortunately, Netbackup simply isnt designed to cater for (relatively) large consolidated environments (we have over a thousand databases).

The real questions are:

1) Is a single nightly transaction log backup enough?

Every article I have ever read has stated that this should be in line with you RPO - ours is pretty much zero as it is all financial data. I made this point clear to the storage team but they countered that in the event of a disaster, we would still have the (synchronised) mirrored databases on the second data centre. Obviously this puts a lot of pressure on our ability to monitor our mirrored databases and I have placed alerts on the send/redo queues and the mirroring state of the databases.

Also, Netbackup has a tendency to to abort any backups that follow an error (for example: it has found a newly installed SQL instance but cannot access it) and this makes that single log backup incredibly crucial.

2) Is using multiple copy_only log backups a good idea in conjunction with a single non copy_only backup from netbackup?

I have always had the perspective that copy_only backups were an adhoc form and therefore not suitable to be an integral part of your backup strategy, but maybe I'm missing something?

I'm still not sure if you can do multiple restores of copy_only transaction logs? I didnt get a chance to test anything yet but I'll see what I can do this evening.

LHT_ST, we did the same with a powershell script and I dont mind using it. I'm still not sure we should be taking less backups just because it means the GUI is unusable.
0
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 500 total points
Comment Utility
Unfortunately, Netbackup simply isnt designed to cater for (relatively) large consolidated environments (we have over a thousand databases).
I used Netbackup before in a company with more than 3000 databases plus Exchange, File Servers and Application Servers needed to be part of the backup plans and I didn't realize that.
For sure you need to have a good infrastructure, specially network and storage.
1) Is a single nightly transaction log backup enough?
NO
Every article I have ever read has stated that this should be in line with you RPO - ours is pretty much zero as it is all financial data.
See why a single tlog backup isn't enough? If you RPO is near zero it's better you have tlog backups more often.
Also, Netbackup has a tendency to to abort any backups that follow an error (for example: it has found a newly installed SQL instance but cannot access it) and this makes that single log backup incredibly crucial.
Check with Symantec support.
I have always had the perspective that copy_only backups were an adhoc form and therefore not suitable to be an integral part of your backup strategy, but maybe I'm missing something?
You are 200% right.
0
 

Author Closing Comment

by:Jay Carax
Comment Utility
Thanks for reaffirming my viewpoint.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Microservice architecture adoption brings many advantages, but can add intricacy. Selecting the right orchestration tool is most important for business specific needs.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now