MSSQL cluster backups and restore

[C_Moisan]

Is there a way for the backup history of a clustered SQL backup to show up with the cluster name on all nodes? My DBAs are complaining that when they want to do a restore, they have to check both nodes of the cluster to see on which physical node the SQL instance was backed up. It's also a pain to have to restore a backup from one node and then restore logs from another.

The way we do our backup is like this: We use the SQL instance name as the client name in a MSSQL policy. That policy calls a script that looks this:

OPERATION BACKUP
DATABASE $ALL
SQLHOST "something-instance_name"
SQLINSTANCE "instance_name"
NBSERVER "master_name"
MAXTRANSFERSIZE 5
BLOCKSIZE 7
POLICY other_policy
ENDOPER TRUE

nowhere do we use the MS cluster name. Should we? The "other_policy" is the one where all the physical node names are. Are we doing this properly?

Thanks!

[Marianne · Accepted solution]

The backup should be performed using the Virtual Hostname, not local nodenames.

If this is how backups are done, they need to follow steps in the NetBackup for Microsoft SQL Server Administrator's Guide    for a redirected restore.

You need to ensure that browse and restore for alternate client names is allowed with altname entries on the master server.

Cluster backups and restores are covered in above NBU guide.

[C_Moisan]

When you say the backup should be performed using virtual hostname, do you mean the client in the policy or the sqlhostname in the script?

[Marianne]

Both.

See chapter 11 in the NBU for SQL manual

Use Virtual hostname in the policy and script.

Sample backup script on p. 150 in the manual:

OPERATION BACKUP
DATABASE "ACCOUNTING"
SQLHOST "VIRTSQL"
NBSERVER "THOR"
BROWSECLIENT "VIRTSQL"
MAXTRANSFERSIZE 0
BLOCKSIZE 7
ENDOPER TRUE

Sample restore script on p. 151.

Ignore references to Public and Private names if you don't have a private network.

[C_Moisan]

I don't see SQLINSTANCE in your script. Also, what is BROWSECLIENT? Is this what allows us to see all backups, regardless of which physical node you're in?

[Marianne]

Please go through chapter 11 in  NetBackup for Microsoft SQL Server Administrator's Guide   .

The sample scripts are exactly that - samples.
You can add instance name(s).

Important the SQLHOST must be the virtual hostname.

BROWSECLIENT is actually only necessary for restores - it tells NBU master which client name to browse for backups.

[C_Moisan]

I'm waiting on the DBAs to provide me with something to test on. I will update this when I'm done.