08-22-2017 04:05 AM
Hi,
My name is Anders and I am working on migrating our old database setup to a new one and going from SQL server 2008 R2 with database mirroring to 2016 with AGs. Unfortunately I have some problems with the backups of the new SQL server databases (Availability groups). The problem is that when I trigger a backup from the secondary node (preferred replica) the backup seems to go throught alright with a “successful” statement at the end but when I search for the backup on the server I get a red dot in front of respective backup and the error message that can be seen below and when I click again on one of the backups I again get a “communicating with server” window. If I try to restore from the client he cannot find any backups at all:
Log from triggered backup from secondary Creplica:
GROUPSIZE 2
OPERATION BACKUP
DATABASE "Test1"
SQLHOST "MSSQL4"
NBSERVER "NETBACKUP1"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
POLICY SQL-MSSQL
NUMBUFS 2
ENDOPER TRUE
OPERATION BACKUP
DATABASE "Test2"
SQLHOST "MSSQL4"
NBSERVER "NETBACKUP1"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
POLICY SQL-MSSQL
NUMBUFS 2
ENDOPER TRUE
INF - Database <Test1> is hosted on an Availability Group secondary replica. Converting backup to copy_only.
INF - BACKUP STARTED USING
Microsoft SQL Server 2016 (SP1-GDR) (KB4019089) - 13.0.4206.0 (X64)
Jul 6 2017 07:55:03
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 6.3 <X64> (Build 14393: ) (Hypervisor)
Batch = C:\Program Files\Veritas\NetBackup\dbext\mssql\temp\__12_51_18_673_00.bch, Op# = 1
INF - Using backup image mssqlcluster.hs.local:MSSQL4/MSSQL7/AG1/db/Test1/~/7/001of001/20170817105228//C
INF - backup database "Test1" to VIRTUAL_DEVICE='VNBU0-10140-6480-1502967172' with copy_only, stats = 10, blocksize = 65536, maxtransfersize = 4194304, buffercount = 2
INF - Number of stripes: 1, Number of buffers per stripe 2.
INF - Availability group database detected. Setting backup catalog name to: mssqlcluster.hs.local
INF - Created VDI object for SQL Server instance <MSSQL4>. Connection timeout is <300> seconds.
Backup started Thu Aug 17 12:54:09 2017
INF - Thread has been closed for stripe #0
INF - OPERATION #1 of batch C:\Program Files\Veritas\NetBackup\dbext\mssql\temp\__12_51_18_673_00.bch SUCCEEDED with STATUS 0 (0 is normal). Elapsed time = 145(2) seconds
INF - Database <Test2> is hosted on an Availability Group secondary replica. Converting backup to copy_only.
INF - BACKUP STARTED USING
Microsoft SQL Server 2016 (SP1-GDR) (KB4019089) - 13.0.4206.0 (X64)
Jul 6 2017 07:55:03
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 6.3 <X64> (Build 14393: ) (Hypervisor)
Batch = C:\Program Files\Veritas\NetBackup\dbext\mssql\temp\__12_51_18_673_00.bch, Op# = 2
INF - Using backup image mssqlcluster.hs.local:MSSQL4/MSSQL7/AG1/db/Test2/~/7/001of001/20170817105530//C
INF - backup database "Test2" to VIRTUAL_DEVICE='VNBU0-10140-6120-1502967355' with copy_only, stats = 10, blocksize = 65536, maxtransfersize = 4194304, buffercount = 2
INF - Number of stripes: 1, Number of buffers per stripe 2.
INF - Availability group database detected. Setting backup catalog name to: mssqlcluster.hs.local
INF - Created VDI object for SQL Server instance <MSSQL4>. Connection timeout is <300> seconds.
INF - Thread has been closed for stripe #0
INF - OPERATION #2 of batch C:\Program Files\Veritas\NetBackup\dbext\mssql\temp\__12_51_18_673_00.bch SUCCEEDED with STATUS 0 (0 is normal). Elapsed time = 142(1) seconds
INF - Results of executing <C:\Program Files\Veritas\NetBackup\dbext\mssql\temp\__12_51_18_673_00.bch>:
<2> operations succeeded. <0> operations failed.
The setup is:
I think I have done everything that I should do from the admin manual for HA support (7.7.2 manual). One thing I come to think of that I have not done yet is to configure permission for redirect restores. What can be wrong here?
Regards Anders
Solved! Go to Solution.
08-23-2017 01:29 AM
Please look at the restore section in the manual, what you have posted is the backup setup section.
Start the Netbackup MS SQL Client to do the SQL restores, not the Backup, Archive and Restore GUI.
08-22-2017 04:44 AM
Think there is some confusion here, you need to use the NetBackup SQL Agent GUI to restore SQL databases as far as I know.
Have not worked with AG, but assume the restore procedure is described in the Netbackup SQL Admin guide.
08-22-2017 05:48 AM
Hi @a04andno,
Hi,
I agree with @Michael_G_Ander said, there is some confusion here, look below there are some articles that can explain you, step by step how to perform NetBackup restores for SQL
You cannot perform SQL restores via BAR...Except if the backups are SQL DUMPS, and you only copy the .bak files in the backup, but this is not the your case.
Take a look.
http://www.veritas.com/docs/000033126 - Step-by-step procedure for using NetBackup to restore a Microsoft SQL Full Backup using a MOVE script
https://www.veritas.com/support/en_US/article.000028372 - NetBackup for Microsoft SQL Server: Performing a database move to the same SQLHOST or to an alternate SQLHOST
https://www.veritas.com/support/en_US/article.TECH74475 – A comprehensive list of solutions for the most common NetBackup for Microsoft SQL Server database agent backup and restore issues
https://www.veritas.com/support/en_US/article.000028188 - How to perform a NetBackup for SQL Server alternate server restore operation of a Microsoft SQL Server database file/filegroups backup
Regards,
Thiago
08-23-2017 01:19 AM
Hi Michael_G_Ander and thank you for answering. As I mentioned I have tried to restore from the client but no backups are found at all after a successful backup. According to the manual it should work with the code "preferredreplica true" in front of every backup command in the batch script when dealing with AGs. I also have created the policy with the two AG clients in the WSFC on the netbackup server. I have read the manual a lot and cant see anything I missed and I have also searched this forum and the Internet for answers. Now I have created a "altnames" directory with a empty No.Restrictions file but that was also in vein. One theory I have is that the client dont find anything because the backup is under "mssqlcluster" and not under the secondary sql nodename (mssql3.hs.local)? As you can see from my previous mail the backup is put in a mssqlcluster.hs.local directory during the process. But as I said I have followed the manuals when I did the setup so it should work.
Regards Anders
These are the steps from the manual I have followed.
08-23-2017 01:29 AM
Please look at the restore section in the manual, what you have posted is the backup setup section.
Start the Netbackup MS SQL Client to do the SQL restores, not the Backup, Archive and Restore GUI.
09-01-2017 12:15 AM
Thank you guys for your help. It works when I use the client and put the cluster name as the source client, previously I also put the SQL-host there. So now it seems to work fine with my legacy created batch policys.
Regards Anders
10-23-2017 05:08 AM