To perform federated backups using NMM, you must create a client resource for the Windows cluster name from which backup is performed, and dummy client resources for the other participating nodes in the cluster.
To create a client resource, you can use either of the following methods from the NMC:
l By using the Client Backup Configuration wizard on page 65
l By using the NetWorker Management Console on page 68
By using the Client Backup Configuration wizard
The Client Backup Configuration wizard simplifies the configuration of client resources for scheduled backup for SQL Server 2012 or later backups in a Windows cluster
environment. This wizard guides you through the creation of a client resource and save group. The Client Backup Configuration wizard creates dummy client resources for participating cluster nodes.
Note
To use the Client Backup Configuration wizard in a federated environment, ensure that the active node of the Windows cluster contains a SQL Server 2012 or later instance.
You must complete the required steps to configure a client resource using the Client Backup Configuration wizard.
Procedure
1. Open the NMC > NetWorker Administrator GUI.
2. Click Configuration and perform either of the following tasks:
l Select Client Backup Configuration > New.
l Right-click the client and from the menu that appears, click Client Backup Configuration > New Client Wizard.
The Specify the Client Name and Type page appears for SQL Sever 2012 or later federated backups.
3. Type the Windows cluster name in the Client Name field.
4. Click Next.
The wizard automatically detects the applications that are installed on the client specified in the Specify the Client Name and Type page, and displays the list from which you can select the backup type. Because a supported SQL Server version is
Federated Backup
Performing federated backups 65
installed, the backup option SQL Server appears in Select the Backup Configuration Type page.
The Select the Backup Configuration Type page appears. The client operating system and the NetWorker version being used in the configuration setup are automatically displayed in the Client Operating System field and NetWorker Version field
respectively.
5. Select SQL Server.
6. Click Next.
The Select the Client Backup Options page appears. This page displays the SQL Server version and the list of all the available components.
The Client Direct option is selected by default. The Client Direct support provided by NetWorker 8.2 and later is available in NMM. The NetWorker Module for Microsoft Administration Guide and NetWorker Administration Guide provide details about this feature.
7. Select a NetWorker volume pool or another target pool of your choice. If the Target Pool field is left blank, the client is not associated to any pool.
8. Select one of the following options under Deduplication:
l Select the None option, if you have not set up data deduplication.
l Select the Data Domain backup option, if you are using a Data Domain device for data deduplication.
l Select the Avamar backup option, if you are using an Avamar device for data deduplication.
9. Click Next.
The Select the Backup Objects page appears, containing a list of all instances and their components.
10.By default the entire cluster is selected for backup. You can perform either of the following steps:
l Clear the selection at the instance level and just select the individual databases that you would like to back up. If there are duplicate databases, ensure to select only one database for each AG even if it belongs to a different instance. Otherwise, an error message appears. For example, select one DB007 option and one DB008 option even if multiple nodes are present under AG004. Click Next.
l Click Next to continue to the next page of the wizard.
The Specify the Backup Options appears.
11.In the Specify the Backup Options page, select the following options:
l Select Compression/Encryption Options > NetWorker compression or 256-bit AES software encryption option to select if using 256-bit AES software encryption.
l If using SQL Server 2008 or later, select the SQL Server compression option.
l If using a SQL server 2005 or later, under Checksum Options, select the Perform checksum before writing to media option, and then if required, the Continue to backup even on checksum error option.
l Select Log Truncation Option to select when backing up transaction logs for a SQL server 2005.
l Under Skip Database Option, select the Skip simple databases during incremental backup option to select when performing instance level backup. If this option is not selected when performing database level backup, the database is not skipped
Federated Backup
The simple database is skipped when the NSR_SKIP_SIMPLE_DB environmental variable is set to TRUE in client Application Information. Otherwise, the
incremental backup request is promoted to full.
l Under Stripes Option, select Specify the number of stripes option to select when using stripes during backup.
l Under DBCC Consistency Check Option, select the Select all databases option to run consistency checks for all the databases. If the DBCC check fails, then the database backup is skipped and backup status of the group appears as failed.
This ensures that all backed up database is suitable for recoveries. This option is available with SQL Server 2005 or later instances.
To specify certain databases for the DBCC check, type the database names in the Databases field.
l Click Next.
The Select the NetWorker Client Properties page appears.
12.Select the browse policy, retention policy, and backup schedule for the backup.
13.Type a comment for the client in the Client comment field.
14.Leave the Remote cccess field blank.
15.Click Next.
The Specify the NetWorker Backup Group page appears.
16.Select the Add to existing group option to select a group from the existing list. Only groups without snapshot policies are available for selection.
17.Select the Create a new group option to create a new group, and provide the following information:
l Type the a group name.
l Select the client retries.
l Choose the schedule backup time in the Schedule Backup Start Time.
l Select the Automatically start backup at the scheduled time to start the backup automatically at the designated time.
l Click Next.
If a storage node is being used in your configuration setup, the Specify the Storage Node Options page appears.
To associate a backup group with a pool resource, go back to the pool resource, right-click and select Properties. Now select the newly created group listed under Basic > Data Source > Groups.
18.Under Backup Storage Nodes, select either of the following options and click Next:
l Select the Automatically select the storage node and devices for the client option to select the storage node or device that is available.
l Select the Backup to NetWorker server only option when a NetWorker storage node is not being used.
l Select the Backup to the following storage nodes options to select the NetWorker storage node name and other details.
The Backup Configuration Summary page appears.
19.Check the details in the Backup Configuration Summary page to make sure that the configuration choices made are correct. Select Back to revisit the previous pages and make changes, or select Create to configure the client resources.
Federated Backup
By using the Client Backup Configuration wizard 67
The Client Configuration Results page appears with details about the client resources that have been created for a required SQL Server.
20.To verify the details for the client, select the client, right-click and view the Client Propertiespage in the NetWorker Management Console.
21.To make changes to the configuration that was created earlier, right-click on that client resource and select Client Backup Configuration > Modify.
By using the NetWorker Management Console
When creating client resources manually, ensure that a client resource is created for Windows cluster name, and dummy client resources are created for the other participating nodes in the cluster. Do not assign a group for the dummy clients. Not creating dummy clients causes the backup to fail.
In the procedure provided in this section for creating a client resource, the AG name is AG1. AG1 is created out of the default instance using Windows Cluster named
MSCSALWAYSON.nmmdev.com. The cluster nodes for AG1 are named MW2K8X64SQL1 and MW2K8X64SQL2.
Procedure
1. Open the NMC > NetWorker Administrator GUI.
2. Click Configuration.
3. In the expanded left pane, select Clients.
4. From the File menu, select New.
5. Click the General tab.
6. In the Name field, the Windows Server cluster name appears.
7. In the Comment field, type a description.
If you are creating multiple client resources for the same NetWorker client host computer, use this field to differentiate the purpose of each resource.
8. In the Browse Policy field, select a browse policy from the list.
The browse policy determines the time period during which the rolled-over data is available for quick access.
9. In the Retention Policy field, select a retention policy from the list.
The retention policy determines the time period during which the rolled-over data is available, although not necessarily quickly.
10.Select the Scheduled Backups field.
11.In the Save Set field, specify the components to be backed up:
l MSSQL#AG1 — AlwaysOn Group referring SQL default instance
l MSSQL#AG1:Database1 — AlwaysOn Group referring SQL default instance
l MSSQL$NamedInstance#AG2 — AlwaysOn Group referring SQL named instance
l MSSQL$NamedInstance#AG2:Database2 — AlwaysOn Group referring SQL named instance
12.For the Group field, select the backup group to which this client resource will be added.
If client resources for the same NMM host are added to different backup groups, ensure that the Start Time field for each backup group is spaced such that the
Federated Backup
13.For the Schedule field, select a backup schedule.
14.Click the Apps & Modules tab.
15.Type the remote user name and password in the Remote user and Password fields respectively.
16.In the Backup command field, type the following command:
nsrsqlsv.exe
where server name is the Windows cluster name and for which Netbios name or FQDN must be used.
17.In the Application Information field, type the required value.
The simple database is skipped when the NSR_SKIP_SIMPLE_DB environmental variable is set to TRUE on the client to skip the backup for simple databases.
Otherwise, the incremental backup request is promoted to full.
18.Under Deduplication, select either of the following:
l Data Domain backup—For data deduplication by using a Data Domain device.
l Avamar deduplication backup—For data deduplication by using a Avamar device.
l Avamar deduplication node—Select the Avamar deduplication node from the choices provided, if you are using Avamar for deduplication. The Avamar node is previously configured and appears in the choices.
Ensure that the Avamar deduplication backup option is selected in the NetWorker client resource for successful Avamar deduplication backups.
19.Click the Globals (1 of 2) tab.
20.In the Remote Access field, enter the user IDs or hostnames of other clients for the Remote Access field. This grants copy restore type permission to the hosts, which enables the named hosts to access the NetWorker server and receive directed recover data. If this field is left empty, only administrators and users logged on to the SQL Server host have access.
21.In the Aliases field, type all known aliases for the SQL Server host in the Aliases field on the Preference tab, as in the following example:
marsmars.emc.com
Include both the DNS short name and long name for the SQL Server host.
22.Click OK.
23.Run the backup from the save group.
Federated Backup
By using the NetWorker Management Console 69
Federated Backup
CHAPTER 5 Data Recovery
This chapter includes the following sections:
l Overview... 72
l Prerequisites...72
l Performing SQL Server 2012 or later recovery... 73
l From the Microsoft SQL Server Management Studio GUI... 73
l From the NetWorker User for SQL Server GUI...78
l Performing recovery of federated backups...105
Data Recovery 71
Overview
To recover SQL Server data, use the NetWorker User for SQL Server GUI, the NetWorker SQL Adhoc Plugin, or the nsrsqlrc command. You cannot recover SQL Server data by using the NetWorker Management Console, or recover data that was backed up by third-party vendors.
To rebuild system databases for SQL Servers by using the Command Prompt, refer to the information provided at the following link: http://msdn.microsoft.com/en-us/library/
ms144259(v=sql.100).aspx
Prerequisites
Review the prerequisites in this section before performing a recovery.
l Read the SQL Server product documentation to understand the limitations associated with recover types on the various SQL Server versions.
l Ensure that the NetWorker server software is running on the appropriate host and the NetWorker Remote Exec Service is started on the SQL Server host.
l Restoring the SQL master database requires a restart of the SQL instance in single user mode followed by logging on to that instance. Single user mode only allows one administrator to log on. Ensure that no other applications or services are waiting for or attempting to log on to the SQL instance.
l If a backup of a database is in progress, wait for it to finish. SQL Server will not restore a database while another database backup is in progress.
l If a SQL Server startup is in progress, wait for it to finish before starting a restore operation.
l Review the ERRORLOG file to determine if a database is currently being recovered or search the ERRORLOG file for the “Recovery complete” string.
If the nsrsqlrc program is started while the SQL Server is recovering databases, the following error message appears:
Could not find database ID. Database may not be activated yet or may be in transition.
l Ensure that all database users are logged off the database. A restore fails if other users try to use the database during the restore operation.
Note
For the supported SQL Server versions, if the primary filegroup is not under restore, then the online (piecemeal) restore functionality allows user access to a database while backup or restore is in progress.
l Recovering filestream data of SQL Server 2008 or later, requires that the SQL Server filestream feature be enabled on the recovery instance.
l For piecemeal restore, SQL Server Enterprise Edition databases consisting of multiple filegroups can be restored in stages with piecemeal restore.
With piecemeal restore, the primary filegroup must be part of each piecemeal restore.
The database remains offline during the restore process. Piecemeal restore is a multistage process that restores a database to itself or to another location. The initial stage must include the primary filegroup and optionally other filegroups. Once the
Data Recovery
primary filegroup is restored, you can bring the database online and continue restoring the remaining filegroups in subsequent stages.