Backing up and restoring SQL Server data

To protect content on a SQL Server server, first register the SQL Server instance so that IBM Spectrum® Protect Plus recognizes it. Then create jobs for backup and restore operations.

System requirements

Ensure that your SQL Server environment meets the system requirements in Microsoft SQL Server database backup and restore requirements.

Registration and authentication

Register each SQL Server server in IBM Spectrum Protect Plus by name or IP address. When registering a SQL Server Cluster (AlwaysOn) node, register each node by name or IP address. Note that the IP addresses must be public-facing and listening on port 5985. The fully qualified domain name and virtual machine node DNS name must be resolvable and route-able from the IBM Spectrum Protect Plus appliance.

The user identity must have sufficient rights to install and start the IBM Spectrum Protect Plus Tools Service on the node, including the Log on as a service right. For more information about this right, see Add the Log on as a service Right to an Account.

The default security policy uses the Windows NTLM protocol, and the user identity format follows the default domain\name format.

When you are using Windows group policy objects (GPO), the group policy object setting, Network security: LAN Manager authentication level must be set correctly. Set it with one of the following options:
  • Not Defined
  • Send NTLMv2 response only
  • Send NTLMv2 response only. Refuse LM
  • Send NTLMv2 response only. Refuse LM & NTLM

Privileges

On the SQL Server server, the system login credential must have public and sysadmin permissions enabled, plus permission to access cluster resources in a SQL Server AlwaysOn environment. If one user account is used for all SQL Server functions, a Windows login must be enabled for the SQL Server server, with public and sysadmin permissions enabled.

Every Microsoft SQL Server host can use a specific user account to access the resources of that particular SQL server instance.

To complete log backup operations, the SQL Server user registered with IBM Spectrum Protect Plus must have the sysadmin permission enabled to manage SQL Server agent jobs.

The Windows Task Scheduler is used to schedule log backups. Depending on a the environment, users may receive the following error: A specified logon session does not exist. It may already have been terminated. This is because of a Network access Group Policy setting that needs to be disabled. For more information on how to disable this GPO, please see the following Microsoft Support article: Task Scheduler Error “A specified logon session does not exist”