Taking SQL log backups once a day with Netbackup

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?
Jay CaraxAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
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
Jay CaraxAuthor Commented:
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
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

LHT_STCommented:
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
Jay CaraxAuthor Commented:
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
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jay CaraxAuthor Commented:
Thanks for reaffirming my viewpoint.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.