Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 473
  • Last Modified:

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?
0
Jay Carax
Asked:
Jay Carax
  • 3
  • 3
1 Solution
 
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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
 
Jay CaraxAuthor Commented:
Thanks for reaffirming my viewpoint.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now