Solved

Taking SQL log backups once a day with Netbackup

Posted on 2014-10-13
7
377 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 47

Expert Comment

by:Vitor Montalvão
ID: 40376541
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
ID: 40376579
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 47

Expert Comment

by:Vitor Montalvão
ID: 40376661
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 6

Expert Comment

by:LHT_ST
ID: 40377093
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
ID: 40377533
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 47

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40379894
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
ID: 40381849
Thanks for reaffirming my viewpoint.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server Designer 19 45
SQL Error - Query 6 26
CPU high usage when update statistics 2 30
Connecting to multiple databases to create a Dashboard 5 26
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

831 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