Solved

Taking SQL log backups once a day with Netbackup

Posted on 2014-10-13
7
388 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 48

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 48

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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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 48

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

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 query 18 42
xml files 7 29
Current Month Filter in Visual Studio 10 23
MS SQL + group by time 4 15
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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…

830 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