Contents

Dell PowerProtect 19.6 Data Manager SQL Server User Guide PDF

1 of 138
1 of 138

Summary of Content for Dell PowerProtect 19.6 Data Manager SQL Server User Guide PDF

PowerProtect Data Manager 19.6 Microsoft Application Agent SQL Server User Guide

Version 19.6

March 2022 Rev. 02

Notes, cautions, and warnings

NOTE: A NOTE indicates important information that helps you make better use of your product.

CAUTION: A CAUTION indicates either potential damage to hardware or loss of data and tells you how to avoid

the problem.

WARNING: A WARNING indicates a potential for property damage, personal injury, or death.

2020 - 2022 Dell Inc. or its subsidiaries. All rights reserved. Dell, EMC, and other trademarks are trademarks of Dell Inc. or its subsidiaries. Other trademarks may be trademarks of their respective owners.

Figures..........................................................................................................................................6

Preface.........................................................................................................................................................................................7

Chapter 1: PowerProtect Data Manager for Microsoft Application Agent Overview...................... 10 PowerProtect Data Manager overview........................................................................................................................ 10 Introducing the Microsoft application agent for SQL............................................................................................... 10 Prerequisites........................................................................................................................................................................ 11 Firewall and port considerations..................................................................................................................................... 11 PowerProtect Data Manager new deployment overview........................................................................................ 12 PowerProtect Data Manager existing deployment overview..................................................................................13

Chapter 2: Enabling the Microsoft Application Agent for SQL...................................................... 14 Microsoft SQL Server data protection and replication requirements................................................................... 14 Protecting a stand-alone SQL Server........................................................................................................................... 14 Protecting SQL Server clustered environments........................................................................................................ 15 Install and configure the application agent.................................................................................................................. 15

Prerequisites ................................................................................................................................................................ 15 Install the Microsoft application agent................................................................................................................... 16 Upgrade the Microsoft application agent...............................................................................................................17 Uninstall the Microsoft application agent with the setup file........................................................................... 18 Required privileges for Application Direct backup and recovery...................................................................... 18 Stagger SQL discovery jobs in host scale-out environments............................................................................19 Configure the database backup stripe level.......................................................................................................... 19 Enable multi-stream backups for SQL protection policy................................................................................... 20

Manage the Microsoft application agent for SQL......................................................................................................21 Support for existing Microsoft application agent backups with PowerProtect Data Manager..................... 22

Supporting existing Microsoft application agent backups with PowerProtect Data Manager................ 22 Using the backup discovery tool for PowerProtect Data Manager management of existing

backups......................................................................................................................................................................23 Enabling the deletion of onboarded backup copies.............................................................................................24

Microsoft application agent for SQL Server application-aware protection........................................................25 Troubleshooting self-service T-SQL backups with an IO error message............................................................ 26

Chapter 3: Managing Storage, Assets, and Protection................................................................. 27 Add protection storage ................................................................................................................................................... 27

Troubleshooting protection policy for DD storage unit......................................................................................28 Viewing the DD Boost storage unit password......................................................................................................28

Enable an asset source.................................................................................................................................................... 29 Disable an asset source..............................................................................................................................................29

Setting the sysadmin privilege for Microsoft SQL hosts.........................................................................................30 Discover a SQL application host....................................................................................................................................30 Add a protection policy for SQL database protection.............................................................................................. 31 Edit the retention period for backup copies............................................................................................................... 34 Delete backup copies........................................................................................................................................................34

Retry a failed backup copy deletion........................................................................................................................35

Contents

Contents 3

Export data for deleted backup copies.................................................................................................................. 36 Remove backup copies from the PowerProtect Data Manager database.................................................... 36

Manage the PowerProtect agent service................................................................................................................... 37 About the PowerProtect agent service................................................................................................................. 37 Start, stop, or obtain the status of the PowerProtect agent service............................................................ 38 Troubleshoot the PowerProtect agent service installation...............................................................................38 Troubleshoot the PowerProtect agent service operations...............................................................................38 Register the PowerProtect agent service to a different server address on Windows.............................. 39 Recovering the PowerProtect agent service from a disaster.......................................................................... 39

Manage the cloud tier operations with PowerProtect Data Manager................................................................. 40 Add a cloud tier schedule to a protection policy................................................................................................. 40 Tier the PowerProtect Data Manager backups from Data Domain to the cloud......................................... 41 Restore the cloud tier backups to Data Domain.................................................................................................. 42

Chapter 4: Performing Self-Service Backups of Microsoft SQL Databases.................................. 43 Performing self-service SQL database backups........................................................................................................43 Overview of Application Direct with SQL Server backups......................................................................................43

Federated backups of Always On availability groups..........................................................................................44 Distributed segment processing.............................................................................................................................. 44 Configuring distributed segment processing........................................................................................................ 45

Best practices to back up SQL Server with Application Direct............................................................................. 45 Configuring usage limits of Data Domain resources........................................................................................... 46 Configure the database backup stripe level..........................................................................................................49

Naming conventions for backups with Application Direct...................................................................................... 50 Circumstances that promote SQL Server backups to level full ............................................................................ 51 Scheduling backup jobs.................................................................................................................................................... 51

Scheduling SQL Server backups by using SQL Server Agent...........................................................................51 Scheduling SQL Server backups by using Windows Task Scheduler............................................................. 57

Performing manual backups .......................................................................................................................................... 57 Perform backups with the Microsoft app agent for Application Direct SSMS plug-in.............................. 57 Back up SQL Server with the Application Direct backup command...............................................................63 Perform backups with T-SQL scripts.....................................................................................................................69

Chapter 5: Performing Self-Service Restores of Microsoft SQL Databases..................................72 Restoring a SQL application host.................................................................................................................................. 72 Best practices to restore SQL Server with Application Direct...............................................................................72 Restoring SQL Server databases ................................................................................................................................. 73

Prerequisites................................................................................................................................................................. 73 Restore a database with the Microsoft app agent for Application Direct plug-in.......................................74 Perform database restores with the Microsoft application agent for Application Direct recover

command....................................................................................................................................................................81 Perform database restores with T-SQL scripts................................................................................................... 87

Performing table-level recovery.................................................................................................................................... 89 Table-level restore workflow....................................................................................................................................90 Launching the Microsoft app agent for Application Direct SSMS plug-in.................................................... 90 Configure general table restore settings...............................................................................................................90 Configure optional table restore settings..............................................................................................................92 Monitor the table restore mount operation ......................................................................................................... 93 Restore table-level data using ItemPoint.............................................................................................................. 94

Performing SQL Server disaster recovery.................................................................................................................. 95

4 Contents

Perform SQL Server disaster recovery..................................................................................................................95 Perform disaster recovery from the Data Domain Cloud Tier..........................................................................96

Chapter 6: Performing Self-Service Restores of SQL Virtual Machine Backups........................... 97 Restoring a SQL Server virtual machine backup....................................................................................................... 97 Overview of SQL Server virtual machine restore operations ................................................................................97 Prerequisites.......................................................................................................................................................................97 Restoring SQL Server databases to a virtual machine............................................................................................ 98

Restore SQL Server databases with the VM Direct SSMS plug-in................................................................ 99 Restore a SQL Server database with the VM Direct recover command..................................................... 107

Performing SQL Server table-level recovery to a virtual machine........................................................................111 Restore SQL Server tables with the VM Direct SSMS plug-in....................................................................... 112 Restore SQL Server tables with the VM Direct recover command............................................................... 118

Performing an instant access recovery...................................................................................................................... 121 Instant access recovery overview .........................................................................................................................121 Instant access recovery use cases........................................................................................................................ 122 Perform an instant access restore with the VM Direct SSMS plug-in.........................................................122

Chapter 7: Performing Centralized Restores of SQL Virtual Machine Backups............................ 131 Centralized restores of SQL Server virtual machine backups...............................................................................131 Considerations for SQL centralized restores............................................................................................................ 132 Centralized restore of SQL system databases......................................................................................................... 132 Centralized restore of a SQL stand-alone database............................................................................................... 134 Centralized restore of a SQL AAG database............................................................................................................ 135 Centralized restore of multiple SQL databases........................................................................................................136 Troubleshooting of SQL centralized restore operations........................................................................................ 138

Contents 5

1 Extended Properties page with backup stripe levels...................................................................................... 20

2 Federated backup command and data flow...................................................................................................... 44

3 Extended Properties page with backup stripe levels......................................................................................50

4 Application Direct - Backup-General page........................................................................................................ 58

5 Data Domain connection list and lockbox settings..........................................................................................59

6 Data Domain - Add Data Domain server details............................................................................................... 60

7 Application Direct - Backup-Options page.........................................................................................................61

8 Application Direct - Backup-Monitor page........................................................................................................ 63

9 Application Direct - Database Restore-General page..................................................................................... 75

10 Specifying the restore point..................................................................................................................................76

11 Application Direct - Database Restore-Files page........................................................................................... 77

12 Application Direct - Database Restore-Options page..................................................................................... 78

13 Application Direct - Database Restore-Monitor page.................................................................................... 80

14 Application Direct - Table Restore-General page.............................................................................................91

15 Application Direct - Table Restore-Options page............................................................................................ 92

16 Application Direct - Table Restore-Monitor page............................................................................................93

17 ItemPoint Data Wizard: select the source files................................................................................................ 94

18 ItemPoint Data Wizard: select the target server.............................................................................................95

19 VM Direct: Database Restore General page....................................................................................................100

20 Specifying the restore point.................................................................................................................................101

21 VM Direct: Database Restore Files page..........................................................................................................103

22 VM Direct: Database Restore Options page................................................................................................... 104

23 VM Direct: Database Restore Monitor page................................................................................................... 107

24 VM Direct: Table Restore General page............................................................................................................113

25 VM Direct: Table Restore Options page............................................................................................................114

26 VM Direct: Table Restore Monitor page........................................................................................................... 116

27 ItemPoint Data Wizard: select the source files................................................................................................117

28 ItemPoint Data Wizard: select the target server............................................................................................ 117

29 ItemPoint Data Wizard: select the source files...............................................................................................120

30 ItemPoint Data Wizard: select the target server........................................................................................... 120

31 VM Direct: Instant Access General page..........................................................................................................123

32 Specifying the access point.................................................................................................................................124

33 VM Direct: Instant Access Options page......................................................................................................... 125

34 VM Direct: Instant Access Active Mounts page............................................................................................. 127

35 VM Direct: Instant Access Active Mounts page.............................................................................................128

36 VM Direct: Instant Access Active Mounts page.............................................................................................129

37 VM Direct: Instant Access Active Mounts page.............................................................................................130

Figures

6 Figures

Preface As part of an effort to improve product lines, periodic revisions of software and hardware are released. Therefore, all versions of the software or hardware currently in use might not support some functions that are described in this document. The product release notes provide the most up-to-date information on product features.

If a product does not function correctly or does not function as described in this document, contact a technical support professional.

NOTE: This document was accurate at publication time. To ensure that you are using the latest version of this document,

go to the Support website https://www.dell.com/support.

Data Domain (DD) is now PowerProtect DD. References to Data Domain or Data Domain systems in this documentation, in

the user interface, and elsewhere in the product include PowerProtect DD systems and older Data Domain systems. In many

cases the user interface has not yet been updated to reflect this change.

Purpose This document describes how to configure and use the PowerProtect Data Manager with the Microsoft application agent to back up and restore Microsoft SQL Server. The PowerProtect Data Manager Administration and User Guide provides additional details about configuration and usage procedures.

Audience This document is intended for the host system administrator who configures and uses the PowerProtect Data Manager with the Microsoft application agent to back up and restore Microsoft SQL Server.

Revision history The following table presents the revision history of this document.

Table 1. Revision history

Revision Date Description

02 March 2, 2022 Removed the topic "Multiple virtual networks (MVLANs) for SQL Server application-aware protection" in Chapter 2.

01 October 27, 2020 Initial release of this document for PowerProtect Data Manager version 19.6.

Compatibility information Software compatibility information for the PowerProtect Data Manager software is provided in the eLab Navigator, available at https://elabnavigator.emc.com/eln/modernHomeDataProtection.

Related documentation The following publications are available on Dell EMC Online Support and provide additional information:

PowerProtect Data Manager Administration and User GuideDescribes how to configure the software. PowerProtect Data Manager Deployment GuideDescribes how to deploy the software. PowerProtect Data Manager Release NotesContains information on new features, known limitations, environment, and

system requirements for the software. PowerProtect Data Manager Security Configuration GuideContains security information.

Preface 7

PowerProtect Data Manager AWS Deployment GuideDescribes how to deploy the software to Amazon Web Services (AWS).

PowerProtect Data Manager Azure Deployment GuideDescribes how to deploy the software to Microsoft Azure. PowerProtect Data Manager Cloud Disaster Recovery Administration and User GuideDescribes how to deploy Cloud DR,

protect VMs in the AWS or Azure cloud, and run recovery operations. PowerProtect Data Manager for Cyber Recovery User GuideDescribes how to install, upgrade, patch, and uninstall the

Dell EMC PowerProtect Cyber Recovery software. PowerProtect Data Manager for File System Agent User GuideDescribes how to configure and use the software with the

File System agent for file system data protection. PowerProtect Data Manager for Microsoft Application Agent Exchange Server User GuideDescribes how to configure

and use the software in a Microsoft Exchange Server environment. PowerProtect Data Manager for Microsoft Application Agent SQL Server User GuideDescribes how to configure and use

the software in a Microsoft SQL Server environment. PowerProtect Data Manager for Oracle RMAN Agent User GuideDescribes how to configure and use the software in an

Oracle Server environment. PowerProtect Data Manager for SAP HANA Agent User GuideDescribes how to configure and use the software in an SAP

HANA Server environment. PowerProtect Data Manager for Storage Direct Agent User GuideDescribes how to configure and use the software with

the Storage Direct agent to protect data on VMAX storage arrays through snapshot backup technology. PowerProtect Data Manager API documentation: https://developer.dellemc.comContains the PowerProtect Data

Manager APIs and includes tutorials to guide to you in their use.

Typographical conventions The following type style conventions are used in this document:

Table 2. Style conventions

Formatting Description

Bold Used for interface elements that a user specifically selects or clicks, for example, names of buttons, fields, tab names, and menu paths. Also used for the name of a dialog box, page, pane, screen area with title, table label, and window.

Italic Used for full titles of publications that are referenced in text.

Monospace Used for: System code System output, such as an error message or script Pathnames, file names, file name extensions, prompts, and syntax Commands and options

Monospace italic Used for variables.

Monospace bold Used for user input.

[ ] Square brackets enclose optional values.

| Vertical line indicates alternate selections. The vertical line means or for the alternate selections.

{ } Braces enclose content that the user must specify, such as x, y, or z.

... Ellipses indicate non-essential information that is omitted from the example.

You can use the following resources to find more information about this product, obtain support, and provide feedback.

Where to find product documentation https://www.dell.com/support https://www.dell.com/community

8 Preface

Where to get support The Support website https://www.dell.com/support provides access to product licensing, documentation, advisories, downloads, and how-to and troubleshooting information. The information can enable you to resolve a product issue before you contact Support.

To access a product-specific page:

1. Go to https://www.dell.com/support. 2. In the search box, type a product name, and then from the list that appears, select the product.

Knowledgebase The Knowledgebase contains applicable solutions that you can search for either by solution number (for example, KB000xxxxxx) or by keyword.

To search the Knowledgebase:

1. Go to https://www.dell.com/support. 2. On the Support tab, click Knowledge Base. 3. In the search box, type either the solution number or keywords. Optionally, you can limit the search to specific products by

typing a product name in the search box, and then selecting the product from the list that appears.

Live chat To participate in a live interactive chat with a support agent:

1. Go to https://www.dell.com/support. 2. On the Support tab, click Contact Support. 3. On the Contact Information page, click the relevant support, and then proceed.

Service requests To obtain in-depth help from Licensing, submit a service request. To submit a service request:

1. Go to https://www.dell.com/support. 2. On the Support tab, click Service Requests.

NOTE: To create a service request, you must have a valid support agreement. For details about either an account or

obtaining a valid support agreement, contact a sales representative. To find the details of a service request, in the

Service Request Number field, type the service request number, and then click the right arrow.

To review an open service request:

1. Go to https://www.dell.com/support. 2. On the Support tab, click Service Requests. 3. On the Service Requests page, under Manage Your Service Requests, click View All Dell Service Requests.

Online communities For peer contacts, conversations, and content on product support and solutions, go to the Community Network https:// www.dell.com/community. Interactively engage with customers, partners, and certified professionals online.

How to provide feedback Feedback helps to improve the accuracy, organization, and overall quality of publications. You can send feedback to DPAD.Doc.Feedback@emc.com.

Preface 9

PowerProtect Data Manager for Microsoft Application Agent Overview

Topics:

PowerProtect Data Manager overview Introducing the Microsoft application agent for SQL Prerequisites Firewall and port considerations PowerProtect Data Manager new deployment overview PowerProtect Data Manager existing deployment overview

PowerProtect Data Manager overview Use PowerProtect Data Manager with the application agent to perform the following operations:

Automate the configuration of the application agent backup policy and protection storage settings. Create a catalog of backups that are produced by the application agent, and then monitor that catalog data to determine if

retention policies are being adhered to. Manage the life cycle of backups that are created by the application agentthat is, ensure that the backups are marked for

garbage collection based on the rules of the retention policy.

PowerProtect Data Manager does not change the way that the application agent works. DBAs or system/backup administrators create the backups and perform the restores.

Introducing the Microsoft application agent for SQL The Microsoft application agent enables an application administrator to protect and recover the SQL application data on the application host. PowerProtect Data Manager integrates with the Microsoft application agent to check and monitor back up compliance against protection policies. PowerProtect Data Manager also enables central scheduling for backups.

You can install the Microsoft application agent on a Windows SQL Server host by using the install wizard. Install and configure the application agent on page 15 provides instructions.

NOTE:

PowerProtect Data Manager supports the coexistence of the Microsoft application agent and the File System agent on

Windows.

To enable the discovery and scheduling of backups with PowerProtect Data Manager, you must approve the client in the

PowerProtect Data Manager UI. Manage the Microsoft application agent for SQL on page 21 provides more information.

In the PowerProtect Data Manager UI, when you select Infrastructure > Assets > SQL Databases and click View

Copies, the size of a Microsoft SQL database that is backed up through application-aware protection is displayed as 0

bytes. The correct size is displayed at the SQL database asset level. For a transaction log backup, the correct size is

displayed at the Protection Copy Set (PCS) level.

Software compatibility information for the PowerProtect Data Manager software and application agents is provided in the eLab Navigator, available at https://elabnavigator.emc.com/eln/modernHomeDataProtection.

1

10 PowerProtect Data Manager for Microsoft Application Agent Overview

Prerequisites Ensure that your environment meets the requirements for a new deployment or upgrade of PowerProtect Data Manager.

Requirements: A list of hosts that write backups to DD systems is available. DD OS version 6.1 or later and the DD Management Console (DDMC). All models of DD systems are supported.

NOTE: DDMC is required with a DD OS version earlier than 6.1.2. With DD OS version 6.1.2 or later, you can add and use

a DD system directly without DDMC.

Application agent 19.6 or earlier. License: A trial license is provided with the PowerProtect Data Manager software. DPS Applications, Backup, and Enterprise

customers can contact Dell EMC Licensing Support for assistance with a permanent PowerProtect Data Manager license. Large environments require multiple PowerProtect Data Manager instances. Contact Champions.eCDM@emc.com for

assistance with sizing requests. The PowerProtect Data Manager 19.6 download file requires the following:

ESXi version 6.5, 6.7, or 7.0. 8 vCPUs, 18 GB RAM, one 100 GB disk, and one 500 GB disk. The latest version of the Google Chrome browser to access the PowerProtect Data Manager UI. TCP port 7000 is open between PowerProtect Data Manager and the application agent hosts.

VMware ESXi server that hosts PowerProtect Data Manager meets the following minimum system requirements: 10 CPU cores 18 GB of RAM for PowerProtect Data Manager Five disks with the following capacities:

Disk 1100 GB Disk 2500 GB Disk 310 GB Disk 410 GB Disk 55 GB

One 1-GB NIC Each SQL Server host meets the following minimum system requirements:

In a scaled environment: 8 CPU cores 16 GB of RAM

In a stand-alone environment: 4 CPU cores 4 GB of RAM

NOTE: It is recommended to use stripes only in the case of large databases.

Firewall and port considerations The latest version of the PowerProtect Data Manager Security Configuration Guide provides more details about the port requirements.

Table 3. PowerProtect Data Manager port requirements

Description Communication Port

SSH communications Bidirectional communication between the SSH client and the PowerProtect Data Manager appliance.

22 TCP/UDP

SQL, Oracle, Exchange, SAP HANA, File System

Bidirectional communication between the PowerProtect Data Manager agent and the PowerProtect Data Manager appliance.

Requirement applies to Application Direct and VM Direct.

7000 TCP

REST Server Bidirectional communication between the HTTP client and the PowerProtect Data Manager appliance.

8443 TCP

PowerProtect Data Manager for Microsoft Application Agent Overview 11

Table 3. PowerProtect Data Manager port requirements (continued)

Description Communication Port

RESTAPI Server - VM Direct

Bidirectional communication between the PowerProtect Data Manager agent and the PowerProtect Data Manager appliance.

Requirement applies to SQL VM application-aware.

8443 TCP

UI redirect Inbound only. 80 TCP

443

LDAP Outbound only. 389 TCP/UDP

636 TCP

Discovery (devices) Outbound between the PowerProtect Data Manager appliance and the device.

3009 TCPStorage Direct and DD system

5989 TCPSMI-S

443 TCPXtremIO

7225 TCPRecoverPoint

PowerProtect Data Manager agent

Bidirectional communication between the database hosts and the PowerProtect Data Manager appliance.

This requirement applies to both Application Direct and VM Direct.

7000 TCP

Embedded VM Direct service

Outbound. 9090 TCP

PowerProtect Data Manager new deployment overview Familiarize yourself with the high-level steps required to install PowerProtect Data Manager with the application agent.

Steps

1. Design how to group the backups based on the storage requirements and retention policies.

The account team can help with backup storage design.

2. Install DD Management Center (DDMC).

PowerProtect Data Manager uses DDMC to connect to the DD systems. The DD Management Center Installation and Administration Guide provides instructions.

NOTE: DDMC is required with a DD OS version earlier than 6.1.2. With DD OS version 6.1.2 or later, you can add and use

a DD system directly without DDMC.

3. Install PowerProtect Data Manager from the download file.

The PowerProtect Data Manager Deployment Guide provides instructions.

4. Add external DD systems or DDMC to PowerProtect Data Manager.

The PowerProtect Data Manager Administration and User Guide provides instructions on how to add protection storage.

5. Install the application agent on the appropriate hosts and connect them to PowerProtect Data Manager according to the instructions in the next "Enabling" chapter.

This operation should be performed by DBAs.

6. Add new or approve pending agent requests in the PowerProtect Data Manager according to the instructions in the next "Enabling" chapter.

12 PowerProtect Data Manager for Microsoft Application Agent Overview

7. After the approval of agent requests, PowerProtect Data Manager must run a discovery operation to discover the assets.

The PowerProtect Data Manager Administration and User Guide provides information.

8. Add a protection policy for groups of assets that you want to back up.

You must add credentials to the database so that PowerProtect Data Manager can access the database and create backups. The PowerProtect Data Manager Administration and User Guide provides instructions.

NOTE: After you create a centralized protection job, the first backup is a full backup.

9. Add Service Level Objectives to the protection policy to verify that the protected assets meet the Service Level Agreements (SLAs).

The PowerProtect Data Manager Administration and User Guide provides instructions.

Configuration is complete.

10. Monitor protection compliance in the PowerProtect Data Manager dashboard.

PowerProtect Data Manager existing deployment overview Familiarize yourself with the high-level steps required to install PowerProtect Data Manager with the application agent in an existing environment.

Steps

1. Install DD Management Center (DDMC).

PowerProtect Data Manager uses DDMC to connect to the DD systems. The DD Management Center Installation and Administration Guide provides instructions.

NOTE: DDMC is required with a DD OS version earlier than 6.1.2. With DD OS version 6.1.2 or later, you can add and use

a DD system directly without DDMC.

2. Install PowerProtect Data Manager from the download file.

The PowerProtect Data Manager Deployment Guide provides instructions.

3. Add external DD systems or DDMC to PowerProtect Data Manager.

The PowerProtect Data Manager Administration and User Guide provides instructions on how to add protection storage.

4. Upgrade the application agent or uninstall and then reinstall the application agent on the hosts and connect them to PowerProtect Data Manager according to the instructions in the next "Enabling" chapter.

This operation should be performed by DBAs.

5. Add new or approve pending agent requests in the PowerProtect Data Manager according to the instructions in the next "Enabling" chapter.

6. After the approval of agent requests, PowerProtect Data Manager must run a discovery operation to discover the assets.

The PowerProtect Data Manager Administration and User Guide provides information.

7. Add a protection policy for groups of assets that you want to back up.

You must add credentials to the database so that PowerProtect Data Manager can access the database and create backups. The PowerProtect Data Manager Administration and User Guide provides instructions.

NOTE: After you create a centralized protection job, the first backup is a full backup.

8. Add Service Level Objectives to the protection policy to verify that the protected assets meet the Service Level Agreements (SLAs).

The PowerProtect Data Manager Administration and User Guide provides instructions.

Configuration is complete.

9. Monitor protection compliance in the PowerProtect Data Manager dashboard.

PowerProtect Data Manager for Microsoft Application Agent Overview 13

Enabling the Microsoft Application Agent for SQL

Topics:

Microsoft SQL Server data protection and replication requirements Protecting a stand-alone SQL Server Protecting SQL Server clustered environments Install and configure the application agent Manage the Microsoft application agent for SQL Support for existing Microsoft application agent backups with PowerProtect Data Manager Microsoft application agent for SQL Server application-aware protection Troubleshooting self-service T-SQL backups with an IO error message

Microsoft SQL Server data protection and replication requirements PowerProtect Data Manager can manage and monitor data protection and replication for Microsoft SQL Server assets through integration with the Microsoft application agent.

After installing the Microsoft application agent, review the following information for additional requirements before adding the Microsoft application agent as an asset source in PowerProtect Data Manager and discovering the SQL Server assets.

Verify that the environment meets the following requirements:

Ensure that you do not mix 32-bit and 64-bit instances on the same SQL Server host.

PowerProtect Data Manager operations do not support hosts with a mix of 32-bit and 64-bit SQL Server instances.

Ensure that all clocks on the SQL Server host, domain controller, and PowerProtect Data Manager are time-synced to the local NTP server to ensure discovery of the backups.

Ensure that the SQL Server and the PowerProtect Data Manager system network can see and resolve each other. Ensure that port 7000 is open on the SQL Server host. Ensure that port 8443 is open on the SQL Server host. This requirement applies only to VM Direct. Ensure that DNS is configured correctly on the application agent host for SQL Server. Ensure that DNS is configured correctly on the PowerProtect Data Manager host and the name resolution matches.

Protecting a stand-alone SQL Server Learn how to configure protection of a stand-alone SQL Server.

Steps

1. Add storage for Data Domain Management Console or the external Data Domain.

Add protection storage on page 27 provides information.

2. Install the Microsoft application agent on the SQL Server host.

Install the Microsoft application agent on page 16 provides information.

3. Add or approve the Microsoft application agent in PowerProtect Data Manager. Manage the Microsoft application agent for SQL on page 21 provides information.

4. Discover and add the credentials for the SQL application host.

2

14 Enabling the Microsoft Application Agent for SQL

Discover a SQL application host on page 30 provides information.

5. Create a protection policy to protect the SQL host.

Add a protection policy for SQL database protection on page 31 provides information.

NOTE: You cannot perform a backup to a secondary Data Domain device. You can only restore from a secondary Data

Domain device.

Protecting SQL Server clustered environments Learn how to configure protection of SQL Server clustered environments, including Always On availability groups and Failover Cluster Instances.

About this task

On each node in the cluster. repeat the steps to install the Microsoft application agent, and then add and discover the application host in PowerProtect Data Manager.

CAUTION: Protection of Failover Cluster Instances (FCI) requires that all nodes in the cluster be registered to

the PowerProtect Data Manager server. Prior to registration, the node must be the active node and own all

the disks in the cluster. The recommended method is to failover all nodes to the registering node. Repeat this

step for all nodes in the cluster and any nodes added to the cluster. Failure to perform this step results in

unpredictable results during protection policy.

Steps

1. Add a storage system.

Add protection storage on page 27 provides information.

2. Install the Microsoft application agent on each node in the cluster.

Install the Microsoft application agent on page 16 provides information.

3. Configure the required user privileges on each node in the cluster.

Required privileges for Application Direct backup and recovery on page 18 provides information.

4. Add or approve the Microsoft application agent on each node in the cluster. Manage the Microsoft application agent for SQL on page 21 provides information.

5. Discover and add the credentials for each SQL application host.

Discover a SQL application host on page 30 provides information.

6. Create a protection policy to protect the cluster.

Add a protection policy for SQL database protection on page 31 provides information.

NOTE: You cannot perform a backup to a secondary Data Domain device. You can only restore from a secondary Data

Domain device.

Install and configure the application agent

Prerequisites

Ensure that a SQL Server environment meets the following prerequisites before you install the Microsoft application agent:

Install the following applications on the Windows host: Microsoft SQL Server SQL Server Management Studio (SSMS) .NET Framework 4.0

If you are installing ItemPoint for table-level recovery, install .NET Framework 4.5.

Enabling the Microsoft Application Agent for SQL 15

In the PowerProtect Data Manager UI, select Agent Downloads from the System Settings menu, select the Microsoft application agent download package, msappagent196_win_x64.zip, and then download the package to the Windows SQL Server host.

Log in to the SQL Server host as an administrator to install the Microsoft application agent. To deploy the Common Language Runtime (CLR) assembly, ensure that you have administrator access to the SQL Server

host and the master database. If the SQL Server host is running in a domain, ensure that you have access as a Domain administrator.

Ensure that the required permissions are set up for the discovery of SQL instances and databases by the PowerProtect agent service. Required privileges for Application Direct backup and recovery on page 18 provide more information.

Install the Microsoft application agent

Learn how to install the Microsoft application agent.

About this task

NOTE: In Always On availability group or cluster environments, you must install the Microsoft application agent on each

node in the cluster.

Steps

1. In the PowerProtect Data Manager UI:

a. Select Agent Downloads from the System Settings menu. b. Select the Microsoft application agent download package, msappagent196_win_x64.zip.

c. Download the package to the host where you want to install the Microsoft application agent.

2. Open msappagent196_win_x64.zip with WinZip.

3. Use WinZip to unzip the msappagent196_win_x64.zip file.

4. In the unzipped folder, launch emcmsappagent-19.6.0.0.exe. The installation wizard appears.

5. On the Welcome Wizard page, select I agree to the license term and agreements, and then click Next.

6. On the Change Install Location page, perform one of the following tasks: To install the Microsoft application agent in the default folder, leave the installation location as is.

The default installation folder is C:\Program Files\DPSAPPS\MSAPPAGENT.

To specify a different installation location, perform the following steps:

a. Click Change. b. In the dialog box that appears, specify the installation location. c. Click OK.

NOTE: When the Microsoft application agent is integrated with PowerProtect Data Manager, the lockbox must be

located in the default directory C:\Program Files\DPSAPPS\common\lockbox.

7. Click Next.

8. On the Configure Installation Options page, specify any of the following installation options, as required: To integrate the Microsoft application agent with PowerProtect Data Manager for centralized or self-service protection

of SQL Server data, select the following options, as required: To install the Microsoft application agent software, select Application Direct. To install the SQL Server Management Studio plug-in user interface, select SSMS Plug-in.

You can use the SSMS plug-in to perform self-service SQL Server backup and restore operations.

To enable table-level restores, select ItemPoint.

This option installs ItemPoint for Microsoft SQL Server, which you can use to perform table-level restores.

You must specify the PowerProtect appliance details by performing the following steps: a. Select PowerProtect Data Manager Integration. b. In the Appliance Hostname or IP address field, type the hostname or IP address of the PowerProtect server.

16 Enabling the Microsoft Application Agent for SQL

NOTE: Installation of the Microsoft application agent requires port 7000 on SQL Server and port 8443 on

PowerProtect to be open bidirectionally. These ports enable communication between the Microsoft application

agent and PowerProtect.

To install the VM Direct Engine to recover application-aware SQL virtual machine backups, select the following options, as required:

NOTE: By default, when a SQL Server virtual machine is added to a virtual machine application-aware protection

policy in PowerProtect Data Manager, the Microsoft application agent and ItemPoint are silently installed on the

protected virtual machine. Before you restore a VM Direct Engine backup to an alternate virtual machine that is not

part of a protection policy, you must install the Microsoft application agent on the target virtual machine.

Select VM Direct Engine.

NOTE: The PowerProtect appliance details are disabled when you select the VM Direct Engine option.

To install the SQL Server Management Studio plug-in user interface, select SSMS Plug-in. To enable table-level restores, select ItemPoint.

This option installs ItemPoint for Microsoft SQL Server, which you can use to perform table-level restores.

9. Click Install >.

10. On the CLR assembly deployment wizard page, perform the following steps:

a. Select or clear the SQL Server instances on which you want to deploy the CLR Assembly. By default, all the SQL Server instances are selected.

b. To deploy CLR Assembly, select one of the following authentication options:

Current Windows User Use Windows Authentication Use Database Authentication

c. In the User name and Password fields respectively, type the username and the password of the user who has the privileges to deploy the CLR Assembly.

d. Click Deploy. e. Click Install. f. After the deployment completes, click Next.

11. On the Complete the Setup page, click Finish.

Upgrade the Microsoft application agent

The Microsoft application agent 19.6 supports a direct upgrade from an earlier version if you are using the latest version of PowerProtect Data Manager.

NOTE: If a table-level restore was performed since the last reboot of the host, Microsoft application agent requests a

reboot during an uninstall operation. The Microsoft application agent installs and uses the Eldos CBFS driver for table-level

restores. The Eldos CBFS driver is loaded during the table-level restore operation. Microsoft requires the driver to be

unloaded prior to an uninstall operation.

1. Launch emcmsappagent-19.6.0.0.exe. The installation wizard appears.

2. On the Welcome Wizard page, select I agree to the license term and agreements, and then click Next. 3. By default, the PowerProtect Data Manager option is selected and the IP address is displayed. SSMS Plug-in and

ItemPoint are also selected if these components were installed in the previous version. Click Upgrade. 4. On the CLR assembly deployment wizard page, perform the following steps:

a. Select or clear the SQL Server instances on which you want to deploy the CLR Assembly. By default, all the SQL Server instances are selected.

b. To deploy CLR Assembly, select one of the following authentication options:

Current Windows User Use Windows Authentication Use Database Authentication

c. In the User name and Password fields, respectively, type the username and password of the user who has the privileges to deploy the CLR Assembly.

d. Click Deploy.

Enabling the Microsoft Application Agent for SQL 17

e. Click Install. f. After the deployment completes, click Next.

5. On the Complete the Setup page, click Finish.

Uninstall the Microsoft application agent with the setup file

About this task

To uninstall the Microsoft application agent for SQL Server with the setup file, perform the following steps.

NOTE: If a table-level restore was performed since the last reboot of the host, Microsoft application agent requests a

reboot during an uninstall operation. The Microsoft application agent installs and uses the Eldos CBFS driver for table-level

restores. The Eldos CBFS driver is loaded during the table-level restore operation. Microsoft requires the driver to be

unloaded prior to an uninstall operation.

Steps

1. Launch emcmsappagent-19.6.0.0.exe.

2. On the Install Modification page, select Remove, and then click Next.

3. On the Configure Uninstallation Options page, click Remove.

4. On the Removing the CLR assembly page:

a. Select the required SQL Server instances to remove the CLR Assembly.

By default, all the SQL Server instances are selected.

b. Select one of the following options to remove the CLR assembly:

Use Windows Authentication Use Database Authentication

c. In the User name and Password fields, type the credentials for the user who has the privileges to remove CLR assembly.

d. Click Remove. e. After the removal completes, click Next.

5. On the Complete the Setup page, click Finish.

Required privileges for Application Direct backup and recovery

Learn about the user requirements for Application Direct backup and recovery.

Required SQL Server roles

Assign the user the following SQL Server roles:

sysadmin public

Required Windows user permissions

Create a local or domain Windows user account and assign the following roles:

The built-in Windows Administrator. A domain user added to the local Administrators user group. For a stand-alone server only:

For table-level backup and recovery, assign the administrative privileges. For database-level backup and recovery, assign the following permissions:

Add the user to the "Create global objects" Windows policy. For all SQL instances on the host that will be protected, assign the following permissions to the data and log folder of

the database: - Read

18 Enabling the Microsoft Application Agent for SQL

- Write - List folder contents

Assign the permissions for all paths where databases are stored, including the default data and log folder of the SQL Server installation. The default data and log folder may be under the SQL Server installation folder. For example, for SQL Server 2012, the default folder is C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\.

For an Always On availability group only:

A local user account added to the Administrators user group on each node in the cluster. The username and password must be the same on each node.

NOTE: For an Always On availability group, Failover Cluster Instance, or Always On Failover Cluster Instance, if you

use an account that you created (an account that is not the built-in Windows Administrator), you must launch the tool

where you will perform the backup or recovery with elevated permissions (run as administrator).

Setting the sysadmin privilege for the SQL Server host

To enable the integration with PowerProtect Data Manager on the SQL Server host, the NT AUTHORITY\SYSTEM account on the host requires the sysadmin privilege. Setting the sysadmin privilege for Microsoft SQL hosts on page 30 provides more information.

Stagger SQL discovery jobs in host scale-out environments

In a host scale-out environment with a large number of SQL hosts to register to PowerProtect Data Manager, consider the following method for staggering the SQL discovery jobs.

Kick off the installer in smaller group of hosts

When you install the Microsoft application agent by script, kick off the installer in smaller groups of hosts. The discovery jobs will kick off after the agent installation. Distributing the installer in smaller groups helps to stagger the incoming discovery results to PowerProtect Data Manager.

Configure the database backup stripe level

Starting with version 19.5, you can modify the stripe level of a backup at the individual database level by using the following procedures. Starting with version 19.6, you can set the stripe level through the following procedures only for self-service stand-alone SQL Server backups, not for centralized backups performed through PowerProtect Data Manager.

CAUTION: After you upgrade from version 19.5, centralized backups ignore any parallelism and backup stripe

settings that were previously configured on the Extended Properties page in SQL Server Management Studio

(SSMS). For centralized backups with version 19.6 or later, you must configure the parallelism settings through

the PowerProtect Data Manager UI, as described in the next topic.

The backup stripe level configuration includes the following features:

The backup stripe level setting for individual databases has a higher priority than the stripe level setting through the backup command with the -S option.

For any database, the minimum supported stripe level is 1 and the maximum supported stripe level is 32. In an FCI or AAG cluster, setting the backup stripe level of a database at any one node reflects across all the nodes in the

cluster.

To set the backup stripe level for any database, use either of the following procedures:

Perform the following steps in the SQL Server Management Studio (SSMS):

1. Right-click the database name, and select Properties. 2. In the properties window, select Extended Properties from the left side pane. 3. Add the required property name and the backup stripe level value:

For a full backup, add the property name ppdmFullStripes and a value between 1 and 32. For a differential backup, add the property name ppdmDiffStripes and a value between 1 and 32.

Enabling the Microsoft Application Agent for SQL 19

For a log backup, add the property name ppdmLogStripes and a value between 1 and 32.

Figure 1. Extended Properties page with backup stripe levels Run the following SQL commands to set the backup stripe level for any database:

USE ; GO EXEC sp_addextendedproperty @name = N'ppdmDiffStripes', @value = '4'; EXEC sp_addextendedproperty @name = N'ppdmFullStripes', @value = '8'; EXEC sp_addextendedproperty @name = N'ppdmLogStripes', @value = '2';

Enable multi-stream backups for SQL protection policy

To enable multi-stream SQL backups for a centralized protection policy, you can set the parallelism value in the PowerProtect Data Manager UI. The parallelism value controls the number of parallel streams that the Microsoft application agent uses to write the backup data to Data Domain.

The preceding topic describes how to set the stripe level at the individual database level for a self-service stand-alone SQL Server backup performed through PowerProtect Data Manager.

The following procedure enables the setting of separate stripe levels for the backup types supported for SQL. If you do not change the settings, the default settings are used for a centralized backup, with a default stripe value of 4 for full and differential backups and a default stripe value of 1 for log backups.

CAUTION: After you upgrade from version 19.5, centralized backups ignore any parallelism and backup stripe

settings that were previously configured on the Extended Properties page in SQL Server Management Studio

(SSMS). For centralized backups with version 19.6 or later, you must configure the parallelism settings through

the PowerProtect Data Manager UI.

In the PowerProtect Data Manager UI, perform the following steps to set the parallelism for multi-stream backups:

20 Enabling the Microsoft Application Agent for SQL

1. Select Infrastructure > Assets > SQL. 2. Select the required SQL asset. 3. Select More Actions > Set Parallelism. 4. Set the parallelism for each backup type as a value between 1 and 32 in the Full, Differential, and Log fields. The default

parallelism values are 4 for full and differential backups and 1 for log backups.

NOTE: A parallelism value of 1 is recommended for log backups.

5. Click Save.

Manage the Microsoft application agent for SQL You can use the PowerProtect Data Manager UI to add a Microsoft application agent for SQL data protection, approve and reject pending agent requests, and edit and delete existing agents.

Steps

1. Select Infrastructure > Application Agents.

The Application Agents window appears.

NOTE: If the PowerProtect agent service was able to register during the installation, the SQL host is already listed and

does not need to be added.

2. Click Add. The Add Application/FS Agent window appears.

3. Select one of the following options:

Add FQDN or CSV Filename.

This process is also called whitelisting.

If you select Add FQDN, perform the following steps: a. Type the fully qualified domain name (FQDN) for the application agent. b. Specify the date until which the application agent is preapproved. c. Click Save.

If you select CSV Filename, perform the following steps: a. Click the Choose File icon.

NOTE: The contents of the .csv file must be in the following format, for example:

"ppdm.dell.com" "ppdm2.emc.com" "ppdm.dellemc.com"

The Explorer window appears.

b. Select the .csv file, and then click Open.

The file appears in the Application/FS Agents window.

c. Select the date until which the application agent is preapproved. d. Click Save.

If you have disabled Auto whitelist, perform the following steps:

NOTE: The Auto whitelist option is disabled by default. When Auto whitelist is enabled, all preapproved

application agents are automatically approved.

a. Select the required application agent. b. Click one of the following options:

Approve Reject Edit, then make the required changes. Remove

Enabling the Microsoft Application Agent for SQL 21

c. Click Save.

Next steps

Discover a SQL application host on page 30 describes how to set the host credentials before you schedule a backup.

Support for existing Microsoft application agent backups with PowerProtect Data Manager The Microsoft application agent provides the capability to onboard existing stand-alone deployments, including their existing backups, to PowerProtect Data Manager. Existing backups are Microsoft application agent backups that you performed before integrating the Microsoft application agent with the PowerProtect Data Manager software and before adding an asset to a PowerProtect Data Manager protection policy.

NOTE:

Onboarding of SQL backup copies to PowerProtect Data Manager is supported only from backups performed with

Microsoft application agent 4.7 and later.

You can onboard up to three previous months of existing backups.

Retention lock is not supported for discovered existing backups in PowerProtect Data Manager.

Onboarding is not supported for DD Boost-over-FC backups and 32-bit FCI instance backups.

With the onboarding capability, PowerProtect provides the following centralized features:

Visibility of both existing backups and any new self-service or PowerProtect Data Manager policy-driven backups of onboarded assets.

Automatic configuration of target protection storage based on the PowerProtect Data Manager protection policies that are used for your database.

All the other functionality that is provided for PowerProtect Data Manager protection policies.

When you create a protection policy, the PowerProtect Data Manager software creates a storage unit on the specified DD system backup host that is managed by PowerProtect Data Manager. All subsequent backups of assets in that protection policy will go to this new storage unit. This implementation overrides the storage unit information that is provided in any running backup through scripts (T-SQL or CLI scripts) or the SSMS UI with the storage unit information that is provided by PowerProtect Data Manager.

Supporting existing Microsoft application agent backups with PowerProtect Data Manager

Learn how to support the existing Microsoft application agent backups.

Steps

1. Upgrade the Microsoft application agent on the SQL Server host.

Upgrade the Microsoft application agent on page 17 provides information.

2. Run the backup discovery tool, AgentBackupDiscovery.exe, to enable management of existing Microsoft application agent backups with PowerProtect Data Manager.

Using the backup discovery tool for PowerProtect Data Manager management of existing backups on page 23 provides information.

NOTE: This step enables the discovery of old backup copies that the Microsoft application agent created during

self-service backups with stand-alone deployments.

3. Register and approve the Microsoft application agent in PowerProtect Data Manager.

Manage the Microsoft application agent for SQL on page 21 provides information.

After a few minutes of approving the SQL host, older backup copies are discovered. Depending on the number of backups, the discovery and subsequent visibility of the backups in PowerProtect Data Manager can take some time. The retention

22 Enabling the Microsoft Application Agent for SQL

time of the discovered existing backup copies equals the retention time that was set in the protection policy plus 14 days, rounded off to the next day.

4. Discover and add the credentials for the SQL application host.

Discover a SQL application host on page 30 provides information.

5. Create a protection policy to protect the SQL host. For onboarding assets, only a subset of databases can be onboarded. It is not mandatory for all the databases on the host to be onboarded.

Add a protection policy for SQL database protection on page 31 provides information.

The first backup after onboarding must be a full backup:

The first centralized backup is automatically promoted to a full backup. The first self-service backup is automatically performed as a full backup.

NOTE: You cannot perform a backup to a secondary DD system device. You can restore only from a secondary DD

system device.

6. Perform a self-service backup of the Microsoft SQL databases. Onboarded assets can be part of either a centralized or self-service protection policy.

Performing self-service SQL database backups on page 43 provides information.

Using the backup discovery tool for PowerProtect Data Manager management of existing backups

To enable the PowerProtect Data Manager management of existing backups after you have upgraded from a previous version of Microsoft application agent or onboarded the current version, you must run the backup discovery tool, AgentBackupDiscovery.exe. Existing backups are Microsoft application agent backups that you performed before integrating the Microsoft application agent with the PowerProtect Data Manager software.

At the end of an upgrade of the Microsoft application agent with the installer, the wizard displays a message about running the AgentBackupDiscovery.exe tool to discover existing backups and manage them in PowerProtect Data Manager software.

CAUTION:

Onboarding of SQL backup copies to PowerProtect Data Manager is supported only from backups performed

with Microsoft application agent 4.7 and later.

Retention lock is not supported for discovered existing backups in PowerProtect Data Manager.

You cannot use the backup discovery tool to discover existing DD Boost-over-FC backups or 32-bit FCI instance

backups.

If you delete a SQL database before onboarding but the database backup copies exist on the Data Domain

system, then after onboarding, PowerProtect Data Manager will not manage those backup copies.

After you run the backup discovery tool, you can continue to use the existing backup scripts to perform the

Microsoft application agent backups. Ensure that all the databases backed up with a particular script are added

to a single protection policy. By default, the PowerProtect Data Manager overrides the Data Domain details by

using the storage unit from the protection policy. If you do not want the Data Domain details to be overridden,

use the -a "SKIP_DD_OVERRIDE=TRUE" option in the backup scripts.

To discover the existing backups by using the backup discovery tool, perform the following steps.

1. In the Microsoft application agent installation directory, C:\Program Files\DPSAPPS\MSAPPAGENT\bin, run AgentBackupDiscovery.exe as the administrator.

The Discovery of existing backups dialog box appears.

NOTE: If the program does not start but displays the following message, an ongoing backup discovery process is

running, as invoked by the PowerProtect Data Manager:

Backup discovery is in progress. Please wait for it to complete.

When the discovery process is complete, you can run the backup discovery tool.

Enabling the Microsoft Application Agent for SQL 23

2. In the Data Domain system list in the dialog box, select the appropriate Data Domain IP address or hostname, storage unit, and username for the existing backups that you want the PowerProtect Data Manager software to discover.

NOTE: Select only one storage unit at a time. After discovery is complete for the storage unit, you can run the backup

discovery tool again to discover the backups of another storage unit.

3. In the Client hostname field, you can change the client hostname from the default local hostname as needed.

To enable the backup discovery for an AAG or FCI, you must specify the appropriate client hostname:

If the host is part of an AAG, specify the Windows cluster name. If the host is part of a SQL virtual server or FCI, specify the virtual server name.

4. In the Backup discovery time period field, select the number of months for the time period, as the time in the past when the backups were performed. You can select 1 month, 2 months, or 3 months for the time period.

5. In the Application field, select SQL as the application. 6. After you have specified the required field values, click Generate.

When the PowerProtect Data Manager software completes the generation of the backup metadata or breadcrumbs, the following message appears in the dialog box. Depending on the number of old backups, the generation of breadcrumbs can take some time:

Breadcrumbs generated successfully.

The retention time for the discovered backup will be same as the expiration time set when the backup was taken.

Enabling the deletion of onboarded backup copies

PowerProtect Data Manager does not delete any SQL backup copies that are onboarded but not associated with a protection policy, even when the backup copies are expired. When you try to delete such copies by using the ddbmexptool or msagentadmin tool, you receive an error message, These copies are managed by PPDM.

About this task

Perform the following steps to enable the deletion of expired SQL backup copies that are onboarded to PowerProtect Data Manager but not associated with a protection policy.

Steps

1. Open the \Settings\.app.settings file for editing. The file content is in xml format, for example:

10.31.140.153 sql_rc-5b2bbffe443d-6451a sql_rc-5b2bbffe443d-6451a PROTECTION DDBOOST false false 86400 sql_rc INST1_SQL2012::dell1 INST1_SQL2012::dell2 MSSQLSERVER::dell_1 MSSQLSERVER::dell_2

2. In the file, locate the section between the and tags that contains the , , , and tags for the DD or MTree where the backup copies are generated and onboarded to PowerProtect Data Manager.

3. Delete the section in the \Settings\.app.settings file.

CAUTION: Delete only the , , , , ,

ddUser>, , and tags and the information between those tags. Do not delete any other tags or

information in the file, which contain the DD details that the PowerProtect Data Manager generates.

24 Enabling the Microsoft Application Agent for SQL

Microsoft application agent for SQL Server application-aware protection The Microsoft application agent is a component of the PowerProtect Data Manager data protection solution for VMware virtual machines.

A PowerProtect Data Manager application-aware VM protection policy uses the Microsoft application agent to provide advanced application-consistent protection for the following SQL workloads:

SQL Server full backup to Data DomainConfigure a PowerProtect Data Manager protection policy with the application- aware option to perform a SQL Server backup to a Data Domain device as part of a VMware image-level backup. A SQL Server full backup is performed during the in-guest quiesce by VMware Tools. When the backup is performed as part of the VMware image-level backup, the SQL data files are backed up as part of the VMDKs during the VM Direct backup. After completing the backup, the Microsoft application agent is automatically run on the virtual machine to catalog the SQL server backup on the Data Domain associated with the protection policy.

Transaction log backupWhen configuring a PowerProtect Data Manager protection policy with the Application Aware option, set an interval for Transaction log backup to enable transaction log backups for SQL instances running on the virtual machine, and specify the frequency of backups. The Microsoft application agent is run on the virtual machine to perform the transaction log backup. Backups are written directly to the Data Domain associated with the protection policy. A transaction log backup is only performed for databases in the proper state; otherwise, databases are skipped.

Database restore, flat file restore, table-level restore, or database Instant Access restore to the source virtual machine or an alternate virtual machine. To perform restores to an alternate virtual machine, that virtual machine must be an asset of PowerProtect Data Manager. However, instance-level restores can only be performed to the original source instance. Performing Self-Service Restores of SQL Virtual Machine Backups on page 97 provides details on how to use Microsoft application agent to restore SQL databases backed up with an application-aware VM protection policy.

The Microsoft application agent software package is bundled with the PowerProtect Data Manager appliance, and is automatically configured on a virtual machine when you add the virtual machine asset to a VM application-aware protection policy. As part of the VM protection policy configuration, both the VM Direct Agent and the Microsoft application agent are installed on the virtual machine. The Microsoft application agent installation includes the software components required for self- service restore, including the SQL Server Management Studio Microsoft App Agent plug-in and ItemPoint. After the agent installations, configuration information for the Data Domain is also sent to the virtual machine, calling the Microsoft application agent to perform the lockbox configuration. Subsequent protection policy backups and self-service restore operations jobs will also use this information without any further action required. During application-aware SQL Server full backups and transaction log backups, PowerProtect Data Manager upgrades the VM Direct Agent and Microsoft application agent software packages as required.

The virtual machine credentials provided in the protection policy or within the virtual machine asset are used during Microsoft application agent installation and during SQL Server full and transaction log backups. The Microsoft application agent is first called to validate the virtual machine SQL configuration. The agent verifies that the SQL Server is installed and running, and that the provided virtual machine credentials have the necessary permissions to perform an SQL Server backup.

In order to perform SQL Server application-consistent data protection for virtual machines, the Microsoft application agent requires the following:

The Microsoft application agent runs under the virtual machine credentials provided in the VM protection policy or virtual machine asset for installation and data protection operations. Configure all SQL Server instances on the virtual machine to grant account rights for this account to perform SQL database backup and recovery operations: Add the account to SQL logins. Grant the account the sysadmin role.

NOTE: The Microsoft application agent supports only a local Administrator or domain Administrator account for the

virtual machine credentials. The user must be Administrator. A local or domain account that has Administrator rights is

not supported.

Network connectivity, hostname resolution, and firewall ports between the Data Domain device and the virtual machines that are part of SQL Server application-consistent protection policies and restore to alternate operations. This connectivity is required to enable the Microsoft application agent to perform client direct operations to Data Domain.

VMware vCenter server version 6.5 or later. VMware ESXi server version 6.5 or later. VMware Tools version 10.1 or later. Enable the UUID attribute (disk.EnableUUID=TRUE) in the vSphere Client.

NOTE: After you set disk.EnableUUID to TRUE, ensure that you reboot the virtual machine.

Enabling the Microsoft Application Agent for SQL 25

The virtual machine must use SCSI disks only, and the number of available SCSI slots must at least match the number of disks. For example, a virtual machine with 7 disks will only require one SCSI controller, but a virtual machine with 8 disks will require 2 SCSI controllers.

The VM Direct Engine requires live network connectivity to the ESXi where the targeted SQL virtual machine resides.

Troubleshooting self-service T-SQL backups with an IO error message When the Microsoft application agent is integrated with the PowerProtect Data Manager, a successful self-service Transact- SQL (T-SQL) backup might display the following error message:

IO error: Permission denied

To prevent this error message during self-service T-SQL backups, ensure the that SQL instance service runs as the OS/SQL user, instead of NT SERVICE\ .

26 Enabling the Microsoft Application Agent for SQL

Managing Storage, Assets, and Protection

Topics:

Add protection storage Enable an asset source Setting the sysadmin privilege for Microsoft SQL hosts Discover a SQL application host Add a protection policy for SQL database protection Edit the retention period for backup copies Delete backup copies Manage the PowerProtect agent service Manage the cloud tier operations with PowerProtect Data Manager

Add protection storage

About this task

The PowerProtect Data Manager UI enables users with administrator credentials to add the following storage types:

DD Management Center (DDMC) External DD system

NOTE:

Adding the DDMC is not required for the Storage Direct agent.

The most up-to-date software compatibility information for PowerProtect Data Manager is provided in the eLab

Navigator.

PowerProtect Data Manager does not support DD systems with the High Availability (HA) feature enabled.

When a DD Management Center is added, PowerProtect Data Manager discovers all the supported DD systems that are managed by the DD Management Center. The PowerProtect Data Manager UI displays the discovered DD systems on the Protection Storage tab of the Infrastructure > Storage window. The DD systems that are managed by the DD Management Center are not displayed until discovery is complete. It might take a few minutes for the DD systems to appear in the Storage window.

For each DD system, the DD Management Center that manages the DD system is indicated in the Managed By column in the table.

If a DD system is added directly to PowerProtect Data Manager, the name that was provided for the DD system when it was added to the PowerProtect Data Manager system is displayed in the Managed By column.

NOTE: Data Domain is now PowerProtect DD. References to Data Domain or DD systems in this documentation, in the UI,

and elsewhere in the product include PowerProtect DD systems and older Data Domain systems. In many cases the UI has

not yet been updated to reflect this change.

Steps

1. Select Infrastructure > Storage.

The Storage window appears.

2. In the Protection Storage tab, click Add.

3. In the Add Storage dialog box, select a storage system (DD System, DD Management Center).

NOTE: If using the Storage Direct agent to move snapshot backups from a VMAX storage array to a DD system, you do

not need to add a DD Management Center.

4. Specify the storage system attributes:

3

Managing Storage, Assets, and Protection 27

a. In the Name field, specify a storage name. b. In the Address field, specify the hostname, fully qualified domain name (FQDN), or the IP address. c. In the Port field, specify the port for SSL communication. Default is 3009.

5. Under Host Credentials click Add, if you have already configured DD credentials that are common across DD systems, select an existing password. Alternatively, you can add new credentials, and then click Save .

6. If a trusted certificate does not exist on the storage system, a dialog box appears requesting certificate approval. Click Verify to review the certificate, and then click Accept.

7. Click Save to exit the Add Storage dialog and initiate the discovery of the storage system.

A dialog box appears to indicate that the request to add storage has been initiated.

8. In the Storage window, click Discover to refresh the window with any newly discovered storage systems. When a discovery completes successfully, the Status column updates to OK.

9. To modify a storage system location, complete the following steps:

A storage system location is a label that is applied to a storage system. If you want to store your copies in a specific location, the label helps you select the correct storage system during policy creation.

a. In the Storage window, select the storage system from the table. b. Click Set Location.

The Set Location window appears. c. Click Add in the Location list.

The Add Location window appears. d. In the Name field, type a location name for the asset, and click Save.

10. To manage MTrees in the Storage window, select the storage system from the table and click View storage units.

NOTE: For information about MTrees, see the DD Operating System Administration Guide.

Results

PowerProtect Data Manager displays External DD systems only in the Storage window Name column. PowerProtect Data Manager displays DD Management Center storage types in the Managed By column.

Troubleshooting protection policy for DD storage unit

When adding a protection policy in PowerProtect Data Manager, creation of a storage unit on the selected DD system fails if you reach the maximum MTree and Users count on the DD system. PowerProtect Data Manager enables you to finish adding the protection policy without the storage unit. However, if you subsequently run a backup of this protection policy, the backup process is suspended indefinitely with no error message.

To continue backup operations on this device, you must perform a cleanup on the DD system.

Viewing the DD Boost storage unit password

PowerProtect Data Manager provides a script to retrieve the password of a DD Boost unit that is configured as a backup target.

Prerequisites

This process requires the name of the DD MTree where the DD Boost storage unit resides.

Steps

1. SSH to the PowerProtect Data Manager appliance as the admin user.

2. Navigate to the /usr/local/brs/puppet/scripts directory.

3. Obtain the DD Boost storage unit password by typing the following command:

./get_dd_mtree_credential.py PLC-PROTECTION-1551667983302

28 Managing Storage, Assets, and Protection

Enable an asset source An asset source, such as a vCenter Server, must be enabled in PowerProtect Data Manager before you can add and register the asset source for the protection of assets.

About this task

There are some circumstances where enabling an asset source is not required, such as the following:

For application agents and other agents such as File System and Storage Direct, an asset source is enabled automatically when you register and approve the agent host. For example, if you have not enabled an Oracle asset source but have registered the application host though the API or the PowerProtect Data Manager UI, PowerProtect Data Manager automatically enables the Oracle asset source.

When you upgrade to PowerProtect Data Manager 19.6 from an earlier release, any asset sources that were previously enabled appear in the PowerProtect Data Manager UI. On a new installation, however, no asset sources are enabled by default.

Steps

1. In the PowerProtect Data Manager UI, select Infrastructure > Asset Sources, and then click + to reveal the New Asset Source tab.

2. In the pane for the asset source that you want to add, click Enable Source. The Asset Sources window updates to display a tab for the new asset source.

Results

You can now add or approve the asset source for use in PowerProtect Data Manager. For a vCenter Server, Kubernetes cluster, or SMIS Server, select the appropriate tab in this window and click Add. For an application agent, go to Infrastructure > Application Agents and click Add or Approve as required.

Disable an asset source

If you enabled an asset source that you no longer require, and the host has not been registered in PowerProtect Data Manager, perform the following steps to disable the asset source.

About this task

NOTE: An asset source cannot be disabled when one or more sources are still registered or there are backup copies of the

source assets. For example, if you registered a vCenter Server and created policy backups for the vCenter virtual machines,

then you cannot disable the vCenter asset source. But if you register a vCenter Server and then delete the vCenter without

creating any backups, you can disable the asset source.

Steps

1. In the PowerProtect Data Manager UI, select Infrastructure > Asset Sources, and then select the tab of the asset source that you want to disable. If no host registration is detected, a red Disable button appears.

2. Click Disable.

Results

PowerProtect Data Manager removes the tab for this asset source.

Managing Storage, Assets, and Protection 29

Setting the sysadmin privilege for Microsoft SQL hosts To enable the integration with PowerProtect Data Manager on each Microsoft SQL host, the NT AUTHORITY\SYSTEM account on each host requires the sysadmin privilege.

About this task

Before you register any SQL host with PowerProtect Data Manager, complete the following steps on each SQL host to set the required sysadmin privilege.

Steps

1. Log in to each SQL instance, open the SQL Server Management Studio (SSMS), and select View > Object Explorer.

2. In the Object Explorer, expand Security and then expand Logins.

3. Right-click NT AUTHORITY\SYSTEM, and then select Properties.

4. In the Login properties window, select Server Roles.

5. Select sysadmin under Server roles.

6. Click OK. The Login properties window closes.

Discover a SQL application host After you register an application host with PowerProtect Data Manager, the host appears in the Asset Sources window. Then you can select the host, perform discovery, and modify the application host credentials. For application hosts, discovery is required if you want to schedule a backup. Assets must also be discovered for the centralized copy management of self-service backups.

About this task

Perform the following steps to discover a SQL application host as an asset source in the PowerProtect Data Manager UI.

Steps

1. Select Infrastructure > Asset Sources.

The Asset Sources window appears.

2. Select the App/File System Host tab.

3. If you are adding a SQL database, select the host entry and click Edit Credentials.

The Edit Credentials dialog appears.

NOTE: The supported syntax for credentials is @ or @ . The syntax \ or

\ is not supported.

4. If you are adding credentials for a SQL database, ensure that you specify the OS credentials for the SQL host. Ensure that these credentials have the rights to perform the Microsoft SQL Server backup and restore operations.

5. Click Save.

6. On the App/File System Host tab, select the application host and click Discover to perform discovery.

The Discover dialog appears with an option to set the discovery schedule.

7. From the Discovery Schedule list, select the time of day to initiate the discovery, or select Manual to disable scheduled discovery. You can also select the Discover Now check box to perform the discovery upon completion of this procedure.

NOTE: On the App/File System Host tab, you can click Discover at any time if any additions or other changes to

your Asset Sources have taken place outside of the PowerProtect Data Manager environment. Asset discovery is also

initiated by default after registration of the host to PowerProtect Data Manager and at hourly intervals. Discovery time

is based on networking bandwidth. Each time you initiate a discovery process, the resources that are discovered and

those that are handling the discovery impact the system performance.

30 Managing Storage, Assets, and Protection

8. Click Save.

Results

If the application host is properly configured and discovery is successful, the database assets can now be added to a PowerProtect Data Manager protection policy.

Add a protection policy for SQL database protection Use the PowerProtect Data Manager UI to add a protection policy group for the purposes of SQL database protection.

Prerequisites

If applicable, complete all of the virtual network configuration tasks before you assign any virtual networks to the protection policy. The PowerProtect Data Manager Administration and User Guide provides more information.

About this task

NOTE: If a database is protected in an Always On availability group, you cannot configure stand-alone backups of that

database in a protection policy group.

Steps

1. Select Protection > Protection Policies.

The Protection Policies window appears.

2. Click Add.

The Add Policy wizard appears.

3. On the Type page, specify the new protection policies group fields. For example, if you are creating a protection policy for daily backups in the SQL production environment:

a. In the Name field, specify the name of the protection policy. For example, specify SQL Prod Databases.

NOTE: The name that you specify becomes part of the Data Domain MTree entry.

b. In the Description field, specify a short description of the protection policy. For example, specify SQL Prod Daily Backups.

c. In theType field, select Microsoft SQL. d. Click Next.

The Purpose page appears.

4. On the Purpose page, specify the following fields to indicate the purpose of the new protection policy group:

a. Select the type of protection policies group. For a SQL database, you can select from three types:

To use PowerProtect Data Manager to manage all protection centrally, click Centralized Protection.

Centralized protection means that PowerProtect Data Manager will schedule the backups and manage the life cycle of the copies.

To use SQL to create local backup protection, click Self-Service Protection. PowerProtect Data Manager creates a protection policy and manages extra stages.

Self-service protection means that DBAs will schedule that backups but PowerProtect Data Manager will discover and manage the life cycle of the copies.

If there are SQL assets within the protection policy that you plan to exclude from data protection operations, click Exclusion.

b. To specify the credentials, click Set Credentials. You can specify new credentials or select existing credentials from the list.

NOTE:

The supported syntax for credentials is @ or @ . The syntax \ or

\ is not supported.

The host-level credentials take precedence over protection policy-level credentials.

Managing Storage, Assets, and Protection 31

c. Click Next. The Assets page appears.

5. Select the unprotected assets that you want to add to the backup of this protection policy group. The window enables you to filter by asset name to locate the required assets.

NOTE: A SQL database asset can be protected by only one protection policy at a time.

6. Click Next.

If you selected Exclusion in the Purpose page, the Summary page appears. Proceed to the final two steps.

If you selected Centralized Protection or Self-Service Protection, the Schedule page appears.

7. Click + Backup. The Add Primary Backup dialog box appears.

8. Specify the backup schedule fields: For centralized protection:

a. In the Recurrence field, select the interval at which the backup job runs within the window that you specify.

Recurrence relates to Start Time and End Time fields.

When you select Hourly, Daily, Weekly, or Monthly recurrence, you are selecting the interval at which the backup job runs within the window that you specify.

b. In the Create Full field, specify the interval in hours to create a full backup.

The interval should be between 1 and 12 hours.

c. To create an incremental differential backup, click Differential, and then specify the interval in minutes. d. To create a log, click Log, and then specify the interval in minutes. e. In the Keep For field, specify the retention period for the backup.

You can extend the retention period for the latest primary backup copy by adding a promotion backup. For example, your regular schedule for daily backups can use a retention period of 30 days, but you can apply promotion backups to keep the full backups taken on Mondays for 10 weeks. Step 9 on page 32 provides instructions.

f. In the Start Time field, specify the time when new backups are initiated in this policy. g. In the End Time field, specify the time after which no new backups are initiated in this policy. It does not mean that

any policy that is running is stopped. h. Click OK.

NOTE:

When a new asset is added to a protection policy, the asset is not protected until the next full backup runs,

whether or not the asset is added within the backup schedule window. To immediately start protecting the asset,

run a manual full backup of the entire policy from the policy page or the newly added asset from the assets page.

The Schedule page updates with the newly added backup schedule.

For self-service protection: a. In the Keep For field, specify the retention time. b. Click OK.

After you complete a backup schedule, you can change any schedule details by selecting the check box next to the added schedule and clicking Edit.

9. To extend the retention period for the latest primary backup copy, add a promotion backup:

a. Select the checkbox next to the added schedule and click + Backup. b. In the Add Promotion Backup dialog box, specify a weekly or monthly recurrence for the promotion backup schedule, a

retention period for the backup, and then click OK.

10. To replicate these backups to a remote DD system:

a. Select the checkbox next to the primary backup schedule and click Replicate.

NOTE: You cannot replicate a promotion backup. When you select a promotion backup schedule, the Replicate

button is disabled.

b. Complete the schedule details in the Add Primary Replication dialog box, and then click OK.

NOTE: To enable replication, ensure that you add a remote DD system as the replication location.

32 Managing Storage, Assets, and Protection

11. Optionally, to add a cloud stage for the purpose of moving backups from DD storage to the cloud tier, select the check box next to the primary, replication, or promotion schedule, and then select Cloud Tier. Add a cloud tier schedule to a protection policy on page 40 provides more information.

NOTE: In order to move a backup or replica to the cloud tier, schedules must have a weekly or monthly recurrence and

a retention time of 14 days or more. Also, discovery of a DD system configured with a cloud unit is required.

12. Select the check box next to the added schedule. When you select the check box, the SLA, Storage Name, and Network interface lists are enabled for selection.

13. From the SLA list, select an existing service level agreement that you want to apply to this schedule, or select Add to create a SLA within the Add Backup Service Level Agreement window.

14. From the Storage Name list: Select the backup destination from the list of existing Data Domain systems. To add a system, select Add, and complete the details in the Storage Target window. When you select the destination storage, the Space field updates with the available capacity on the system.

15. Click Set Storage Quotas to set storage space restrictions for a Data Domain MTree or storage unit to prevent the consumption of excess space. There are two kinds of quota limits, hard limits and soft limits. You can set either a soft or hard limit or both a soft and hard limit. Both values must be integers, and the soft value must be less than the hard value.

NOTE: When you set a soft limit and the limit is reached, an alert is generated but data can still be written to the Data

Domain. When you set a hard limit and the limit is reached, data cannot be written to the MTree. Therefore, all data

protection operations fail until data is deleted from the MTree. The Data Domain Operating System Administration Guide

provides more information about MTree quota configuration.

a. Capacity QuotaSpecifies the total size of pre-compression data written to the Data Domain. b. Stream QuotaSpecifies the number of concurrent streams allowed on the system during data protection operations.

Setting a Stream Quota limit can help ensure that system performance is not impacted negatively if a data protection operation consumes too many system resources.

16. Select the Retention Lock check box to enable retention locking for these backups on the selected system.

17. From the Network interface list, select a network interface, if applicable.

18. Click Next. The Options page appears.

19. On the Options page, select the additional options that are required for the policy:

NOTE: If the PowerProtect Data Manager version is 19.6 but the application agent version is earlier than 19.6, then

the Exclude Simple Database and Backup Promotion options are not supported, although the options appear in the

PowerProtect Data Manager UI.

Exclude Simple DatabaseSelect this option to exclude the databases in simple recovery model from the transaction log backups.

Exclude System DatabasesSelect this option to exclude the system databases (including master, model, and msdb) from the differential and transaction log backups.

Backup PromotionSelect one of the following backup promotion options to use for differential and transaction log backups:

AllEnables backup promotion. This setting is the default backup promotion setting. NoneDisables backup promotion, without displaying a warning during backups. None With WarningsDisables backup promotion, but displays a warning during a backup when a backup promotion

would normally occur. TroubleshootingSelect this option to enable the debug logs for troubleshooting purposes.

20. Click Next. The Summary page appears.

21. Review the protection policy group configuration details. You can click Edit next to any completed window's details to change any information. When completed, click Finish. An informational message appears to confirm that PowerProtect Data Manager has saved the protection policy. When a new protection policy is created, PowerProtect Data Manager performs the first full backup and subsequent backups according to the specified schedule.

22. Click OK to exit the window, or click Go to Jobs to open the Jobs window to monitor the backup of the new protection policy group.

Managing Storage, Assets, and Protection 33

Edit the retention period for backup copies You can edit the retention period of one or more backup copies to extend or shorten the amount of time that backups are retained.

About this task

You can edit the retention period for all asset types and backup types.

Steps

1. Select Infrastructure > Assets.

2. From the Assets window, select the tab for the asset type for which you want to edit the retention period. If a policy has been assigned, the table lists the assets that have been discovered, along with the associated protection policy.

3. Select a protected asset from the table, and then click View Copies. The Copy Locations pane identifies where the backups are stored.

4. In the left pane, click the storage icon to the right of the icon for the asset, for example, DD. The table in the right pane lists the backup copies.

5. Select one or more backup copies from the table, and click Edit Retention.

6. Select one of the following options: To select a calendar date as the expiration date for backups, select Retention Date. To define a fixed retention period in days, weeks, months, or years after the backup is performed, select Retention

Value. For example, you can specify that backups expire after 6 months.

NOTE: When you edit the retention period for copies that are retention locked, you can only extend the retention

period.

7. When satisfied with the changes, click Save. The asset is displayed in the list with the changes. The Retention column displays both the original and new retention periods, and indicates whether the retention period has been extended or shortened.

Delete backup copies In addition to deleting backups upon expiration of the retention period, PowerProtect Data Manager enables you to manually delete backup copies from the DD system.

About this task

If you no longer require a backup copy and the retention lock is not enabled, you can delete backup copies prior to their expiration date.

Starting with PowerProtect Data Manager version 19.6, you can perform a backup copy deletion that deletes only a specified part of a backup copy chain, without impacting the ability to restore other backup copies in the chain. When you select a specific backup copy for deletion, only that backup copy and the backup copies that depend on the selected backup copy are deleted:

When you select to delete a full backup copy, any other backup copies in the chain that depend on the full backup copy are also deleted.

When you select to delete a differential backup copy, only the differential backup copy is deleted because there are no other dependent backup copies. The whole backup chain is not deleted.

When you select to delete a log backup copy, any other log backup copies that depend on the selected log backup copy are also deleted. The whole backup chain is not deleted.

When you select to delete all log backup copies, the full and differential backup copies are automatically excluded from deletion.

Regarding expired copy deletion for a backup chain, the full backup expires last. The differential and log backups that expire earlier than the full backup can be deleted when their retention time expires:

The full backup expires only when every other backup in the chain has expired. A differential backup expires when its retention time expires. A log backup expires only when all the log backups that depend on it have expired.

34 Managing Storage, Assets, and Protection

Steps

1. Select Infrastructure > Assets.

2. From the Assets window, select the tab for the asset type for which you want to delete copies. If a policy has been assigned, the table lists the assets that have been discovered, along with the associated protection policy.

3. Select a protected asset from the table, and then click View Copies. The Copy Locations pane identifies where the backups are stored.

4. In the left pane, click the storage icon to the right of the icon for the asset, for example, DD. The table in the right pane lists the backup copies.

5. Select one or more copies from the table that you want to delete from the DD system, and then click Delete.

A preview window opens and displays the selected backup copies and all the backup copies that depend on the selected backup copies.

NOTE: If you delete a backup copy, PowerProtect Data Manager deletes the specified backup copy and all backup

copies that depend on the specified backup copy.

6. For all asset types, you can choose to keep the latest backup copies or delete them. By default, PowerProtect Data Manager keeps the latest backup copies. To delete the latest backup copies, clear the checkbox next to Include latest copies.

7. To delete the backup copies, in the preview window, click Delete.

NOTE: The delete operation may take a few minutes and cannot be undone.

An informational dialog box opens to confirm the copies are being deleted. To monitor the progress of the operation, click Go to Jobs. To view the list of backup copies and their status, click OK.

When the job completes, the task summary provides details of each deleted backup copy, including the time that each copy was created, the backup level, and the retention time. The time of copy creation and the retention time is shown in UTC.

An audit log is also generated and provides details of each deleted backup copy, including the time that each copy was created, the backup level, and the retention time. The time of copy creation and the retention time is shown in UTC. Go to Alerts > Audit Logs to view the audit log.

8. Verify that the copies are deleted successfully from the DD system. If the deletion is successful, the deleted copies no longer appear in the table.

Retry a failed backup copy deletion

If a backup copy is not deleted successfully, you can manually retry the operation.

Steps

1. From the Assets window, select the tab for the asset type for which you want to delete copies. If a policy has been assigned, the table lists the assets that have been discovered, along with the associated protection policy.

2. Select a protected asset from the table, and then click View Copies. The Copy Locations pane identifies where the backups are stored.

3. In the left pane, click the storage icon to the right of the icon for the asset, for example, DD. The table in the right pane lists the backup copies.

4. Select one or more backup copies with the Deletion Failed status from the table, and then click Delete.

You can also filter and sort the list of backup copies by status in the Copy Status column.

The system displays a warning to confirm you want to delete the selected backup copies.

5. Click OK. An informational dialog box opens to confirm that the copies are being deleted. To monitor the progress of the operation, click Go to Jobs. To view the list of backup copies and their status, click OK.

6. Verify that the copies are successfully deleted from the DD system. If the deletion is successful, the deleted copies no longer appear in the table.

Managing Storage, Assets, and Protection 35

Export data for deleted backup copies

This option enables you to export results of deleted backup copies to a CSV file so that you can download an Excel file of the data.

Steps

1. From the Assets window, select the tab for the asset type for which you want to export results of deleted backup copies. If a policy has been assigned, the table lists the assets that have been discovered, along with the associated protection policy.

2. Select one or more protected assets from the table and click Export Deleted Copies.

If you do not select an asset, PowerProtect Data Manager exports the data for deleted backup copies for all assets for the specific asset type.

3. Specify the following fields for the export:

a. Time Range

The default is Last 24 Hours.

b. Copy Status

In order to export data for deleted backup copies, the backup copies must be in one of the following states:

Deleted Deleting Deletion Failed Deletion Failed (Agent Catalog)

NOTE: You cannot export data for backup copies that are in an Available state.

4. Click Download. If applicable, the navigation window appears for you to select the location to save the CSV file.

5. Save the CSV file in the desired location and click Save.

Remove backup copies from the PowerProtect Data Manager database

This option enables you to delete the backup copy records from the PowerProtect Data Manager database, but keep the backup copies in the DD system.

About this task

For backup copies that could not be deleted from the DD system, you can remove the backup copies from the PowerProtect Data Manager database. Removing the backup copies from PowerProtect Data Manager does not delete the copies in the DD system.

Steps

1. From the Assets window, select the tab for the asset type for which you want to delete copies. If a policy has been assigned, the table lists the assets that have been discovered, along with the associated protection policy.

2. Select a protected asset from the table, and then click View Copies. The Copy Locations pane identifies where the backups are stored.

3. In the left pane, click the storage icon to the right of the icon for the asset, for example, DD. The table in the right pane lists the backup copies.

4. Select one or more backup copies with the Deletion Failed or Deletion Failed (Agent Catalog) status from the table, and then click Remove from PowerProtect.

For backup copies with the Deletion Failed (Agent Catalog) status, click Remove from PowerProtect to remove the information from PowerProtect Data Manager for any backup copies that were successfully deleted from the DD system but for which the agent catalog was not deleted from the agent host.

The system displays a warning to confirm you want to delete the selected backup copies.

5. Click OK. An informational dialog box opens to confirm that the copies are being deleted. To monitor the progress of the operation, click Go to Jobs. To view the list of backup copies and their status, click OK.

36 Managing Storage, Assets, and Protection

6. Verify that the copies are deleted from the PowerProtect Data Manager database. If the deletion is successful, the deleted copies no longer appear in the table. The backup copies remain in the DD system.

Manage the PowerProtect agent service The PowerProtect agent service provides important functionality for the application agent operations with the PowerProtect Data Manager.

Review the following topics to ensure that you enable and manage the PowerProtect agent service functionality as required for application agent operations.

About the PowerProtect agent service

The PowerProtect agent service is a REST API based service that is installed by the application agent on the application host. The agent service provides services and APIs for discovery, protection, restore, instant access, and other related operations. The PowerProtect Data Manager uses the agent service to provide integrated data protection for the application assets.

This section uses to represent the PowerProtect agent service installation directory. By default, the agent service installation location is C:\Program Files\DPSAPPS\AgentService on Windows and /opt/dpsapps/agentsvc on Linux. All files that are referenced in this section are the relative paths to the agent service installation location.

The PowerProtect agent service performs the following operations:

Addon detectionAn addon integrates the application agent into the agent service. The agent service automatically detects the addons on the system for each application asset type and notifies the PowerProtect Data Manager. While multiple addons can operate with different asset types, only one agent service runs on the application host. Specific asset types can coexist on the same application host.

DiscoveryThe agent service discovers both stand-alone and clustered database servers (application systems), databases and file systems (assets), and their backup copies on the application agent host. After the initial discovery, when the agent service discovers any new application systems, assets, or copies, the agent service notifies the PowerProtect Data Manager.

Self-service configurationThe agent service can configure the application agent for self-service operations by using information that is provided by the PowerProtect Data Manager. When you add an asset to a protection policy for self-service or centralized protection, or modify the protection policy, including changing the DD Boost credentials, the PowerProtect Data Manager automatically pushes the protection configuration to the agents.

Centralized backupsThe agent service performs the centralized backups as requested by the PowerProtect Data Manager.

Centralized restoresThe agent service performs the centralized restores as requested by the PowerProtect Data Manager.

NOTE: In the current release, the centralized restores are only available for the File System agent, Microsoft SQL agent,

and Storage Direct agent.

Backup deletion and catalog cleanupThe PowerProtect Data Manager deletes the backup files directly from the protection storage when a backup expires or an explicit delete request is received and no dependent (incremental or log) backups exist. The PowerProtect Data Manager goes through the agent service to delete the catalog entries from the database vendor's catalog and the agent's local datastore.

NOTE: Deletion of any backup copies manually or through the command line is not recommended. PowerProtect Data

Manager deletes all the expired copies as needed.

The agent service is started during the agent installation by the installer. The agent service runs in the background as a service and you do not interact with it directly.

The config.yml file contains the configuration information for the agent service, including several parameter settings that you can change within the file. The config.yml file is located in the directory.

The agent service periodically starts subprocesses to perform the discovery jobs. You can see the type and frequency of these jobs in the jobs: section of the config.yml file. The job interval unit is minutes.

The agent service maintains a datastore in the /dbs/v1 directory, which contains information about the application system, assets, and backups discovered on the system. The size of the datastore files depends on the number of applications and copies on the host. The agent service periodically creates a backup of its datastore

Managing Storage, Assets, and Protection 37

in the /dbs/v1/backups directory, as used to recover the datastore if this datastore is lost.

NOTE: The size of each datastore backup is the same as the datastore itself. By default, a backup is created every hour.

To save space on the file system, you can reduce this datastore backup frequency for large datastores. By default, the

datastore backup is retained for one week. You can change the datastore backup frequency, retention period, and backup

location in the config.yml file.

Start, stop, or obtain the status of the PowerProtect agent service

The PowerProtect agent service is started during the agent installation by the installer. If needed, you can use the appropriate procedure to start, stop, or obtain the status of the agent service.

On Windows, you can start, stop, or obtain the status of the PowerProtect agent service from the Services Manager, similar to other Windows services. The name of the service in the Services Manager is PowerProtect Agent Service.

Troubleshoot the PowerProtect agent service installation

The PowerProtect agent service installation might fail with the following error message:

Service 'PowerProtect Agent Service' (AgentService) could not be installed. Verify that you have sufficient privileges to install system services.

Possible causes of the installation failure are as follows:

The installation was attempted on a passive node of a Failover Cluster Instance (FCI). The installation was canceled and a rollback left some stale entries of PowerProtect agent services.

As a workaround, clean up the PowerProtect agent service entries, and retry the installation.

Troubleshoot the PowerProtect agent service operations

To troubleshoot the agent service operations, you can check the agent service log file OpAgentSvc- .log, which is created in \logs on Windows and /logs on Linux. To modify the log level and retention of temporary files, you can modify specific parameter settings in the config.yml file.

About this task

To modify the log level and retention of temporary files, you can perform the following steps.

Steps

1. Stop the agent service by using the appropriate procedure from the preceding topic.

2. Open the config.yml file in an editor.

3. Modify the log-level settings in the following parameters, as required:

NOTE: These parameters are listed in order of decreasing number of messages in the debug information output. The

default log-level is INFO.

DEBUG INFO WARNING ERROR CRITICAL

4. To retain the temporary files, set the keepTempFiles parameter to True in the config.yml file.

NOTE: The agent service and application agent communicate through the temporary files, which are typically deleted

after use but can be useful for troubleshooting purposes. Do not leave the keepTempFiles parameter set to True

permanently, or the temporary files can use excessive space on the file system.

38 Managing Storage, Assets, and Protection

5. Start the agent service by using the appropriate procedure from the preceding topic.

Register the PowerProtect agent service to a different server address on Windows

The PowerProtect agent service is registered to a particular PowerProtect Data Manager server during the agent installation by the installer. If needed, you can register the agent service to a different PowerProtect Data Manager server address. If there are multiple agents installed on a host, all agents will be re-registered to the new PowerProtect server.

The agent service can only be registered to a single PowerProtect Data Manager server.

On Windows, perform the following steps to register the agent service to a different server address.

1. To unregister from the current PowerProtect Data Manager server, go to the C:\Program Files\DPSAPPS\AgentService directory and run unregister.bat.

2. In the dbs\v1 directory, rename the copies.db file to copies-backup.db.

3. To register the agent service to the different server address, run register.bat and provide the new PowerProtect Data Manager server IP address or hostname.

4. Verify the agent registration status:

a. In the PowerProtect Data Manager UI, select Infrastructure > Application Agents.

b. In the Application Agents window, select the entry that contains the agent hostname and ensure that the status is Registered.

5. To perform asset discovery for the agent:

a. In the PowerProtect Data Manager UI, select Infrastructure > Asset Sources.

The Asset Sources window appears.

b. Select the App/File System Host tab. c. Select the agent hostname and click Discover. At the confirmation prompt, click Yes.

When you select Infrastructure > Assets, the Assets window displays the discovered assets.

Recovering the PowerProtect agent service from a disaster

You can perform self-service restores of application assets by using a file system or application agent, regardless of the state of the agent service or PowerProtect Data Manager. The information in the this section describes how to bring the agent service to an operational state to continue if a disaster occurs and the agent service datastore is lost.

The agent service periodically creates a backup of its datastore in the /dbs/v1/backups repository. If all these backups are lost, the agent service can still start. The agent service discovers all the application systems, assets, and backup copies on the system again, and notifies PowerProtect Data Manager. Depending on when the failure occurred, the agent service might not be able to find older backup copies for some asset types. As a result, the centralized deletion operations might fail when cleaning up the database vendor catalog or removing older backups that are taken before the asset is added to PowerProtect Data Manager.

By default, the agent service backs up consistent copies of its datastore files to the local disk every hour and keeps the copies for 7 days. Each time the agent service backs up the contents of the datastore, it creates a subdirectory under the /dbs/v1/backups repository. The subdirectories are named after the time the operation occurred, in the format YYYY-MM-DD_HH-MM-SS_epochTime.

By default, the datastore repository is on the local disk. To ensure that the agent service datastore and its local backups are not lost, it is recommended that you back up the datastore through file system backups. You can also change the datastore backup location to a different location that is not local to the system. To change the datastore backup location, update the values in the config.yml file.

Restore the PowerProtect Data Manager agent service datastore

Prerequisites

NOTE: Ensure that the agent service is powered off. Do not start the agent service until disaster recovery is complete.

Managing Storage, Assets, and Protection 39

About this task

You can restore the datastore from the datastore backup repository. If the repository is no longer on the local disk, restore the datastore from file system backups first.

To restore the datastore from a backup in the datastore backup repository, complete the following steps:

Steps

1. Move the files in the /dbs/v1 directory to a location for safe keeping.

NOTE: Do not move or delete any /dbs/v1 subdirectories.

2. Select the most recent datastore backup.

The directories in the datastore backup repository are named after the time the backup was created.

3. Copy the contents of the datastore backup directory to the /dbs/v1 directory. After the copy operation is complete, the /dbs/v1 directory should contain the following files: copies.db objects.db resources.db sessions.db

4. Start the agent service.

Manage the cloud tier operations with PowerProtect Data Manager The PowerProtect Data Manager cloud tier feature works in tandem with the Data Domain Cloud Tier feature to move PowerProtect Data Manager backups from Data Domain systems to the cloud. This provides long-term storage of PowerProtect Data Manager backups by seamlessly and securely tiering data to the cloud.

From the PowerProtect Data Manager UI, you configure cloud tier to move PowerProtect Data Manager backups from Data Domain to the cloud, and you can perform seamless recovery of these backups.

Data Domain cloud storage units must be pre-configured on the Data Domain system before they are configured for cloud tier in the PowerProtect Data Manager UI. The Data Domain Operating System Administration Guide provides more information.

Add a cloud tier schedule to a protection policy

You can add a cloud tier schedule to a protection policy for SQL centralized and self-service backups to cloud tier.

Prerequisites

Ensure that a Data Domain system is set up for cloud tiering.

About this task

Both SQL centralized and self-service protection policies support cloud tiering. You can create the cloud tier schedule from both primary and replication stages. Schedules must have a minimum weekly recurrence and a retention time of 14 days or greater.

Steps

1. Log in to PowerProtect Data Manager with administrator credentials.

2. Select Protection > Protection Policies > Add.

3. On the Type page, enter a name and description, select SQL as the type of system to back up, and click Next.

4. On the Purpose page, select from the available options to indicate the purpose of the new protection policy, and then click Next.

5. On the Assets page, select the assets to be protected with this policy, and then click Next.

40 Managing Storage, Assets, and Protection

6. On the Schedule page, select + Backup.

7. On the Add Primary page, set the following parameters, and then click OK:

RecurrenceSelect Weekly or Monthly. Keep forCloud Tier backup requires a minimum of 2 weeks. Optionally, change the Start Time or End Time or both.

8. Select the Primary protection policy that you created, and then select Cloud Tier.

9. For Self -Service Protection Policy:

Select a primary backup to create a promotion backup. Keep forCloud Tier backup requires a minimum of 2 weeks.

10. In the Add Promotion Backup dialog box, set the following parameters, and then click OK:

RecurrenceSelect Weekly or Monthly. Create Full (Level 0)Add the option as per the recurrence. Keep forCloud Tier backup requires a minimum of 2 weeks.

11. Select the Promotion Backup policy, and then select Cloud Tier.

12. In the Add Cloud Tier dialog box, set the following parameters, and then click OK:

Select the appropriate unit from the Cloud Target list. For Tier After, set a time of at least 2 weeks.

The cloud tier protection policy is created.

13. Click Next, verify the information, and then click Finish. A new job is created, which you can view under the Jobs tab after the job completes.

Tier the PowerProtect Data Manager backups from Data Domain to the cloud

Once you add the SQL database assets to a protection policy that contains a cloud tier stage, you can perform tiering of these assets by using the PowerProtect Data Manager UI.

Steps

1. Log in to PowerProtect Data Manager with administrator credentials.

2. Select Infrastructure > Assets > SQL Databases.

3. On the Type Assets page, select the asset, and then click View Copies.

4. Select the Data Domain system where the PowerProtect Data Manager backups for SQL reside, and then select Full protection copy which is older than 2 weeks.

5. Click Tier to tier the backups.

A new job is created, which you can view under the Jobs tab after the job completes. When you monitor the cloud tier progress of backup copies for the asset job, the status remains in the running state until the data movement occurs from the Data Domain system.

6. Log in to the Data Domain system, and obtain the storage unit details by running the command data-movement policy show. For example:

# data-movement policy show

Mtree Target(Tier/Unit Name) Policy Value ----------------------------------------- ---------------------- ----------- ------- /data/col1/rman137-blrv136g140-840dd Cloud/ecs-unit app-managed enabled /data/col1/rman134-copy-blrv136g138-61900 Cloud/ecs-unit app-managed enabled /data/col1/rman-11-blrv136h010-7014f Cloud/ecs-unit app-managed enabled

7. Run the data-movement start mtrees command for the particular MTree. For example:

# data-movement start mtrees /data/col1/rman137-blrv136g140-840dd

Managing Storage, Assets, and Protection 41

After the successful data movement to the cloud, the cloud tier monitoring job completes. After some time, on the Assets > View Copies page, the Location field of the protection backups changes to Cloud.

The Data Domain Operating System Administration Guide provides more details about cloud tier data movement.

Restore the cloud tier backups to Data Domain

Before you run a self-service restore of a backup that was performed through a centralized policy and moved to the cloud tier, recall the backup to the active tier from PowerProtect Data Manager.

NOTE: Only Elastic Cloud Storage (ECS) supports a direct restore from the cloud tier.

Recall and re-tier the cloud tier backup

You can manually recall the backup from the cloud tier and restore the local copy:

NOTE: When a backup is recalled from the cloud tier to the active tier, the copy is removed from the cloud tier.

1. In the PowerProtect Data Manager UI, go to Infrastructure > Assets and select the SQL Databases tab. 2. Select the required asset, and then click View Copies. 3. Select the backup in the cloud, click Recall, and then specify how long to keep the copy on the active tier.

A job is created to recall the backup copy from the cloud tier for the selected asset. The copy moves from the cloud tier, and the status changes from Cloud to Local_Recalled. Then you can perform the restore from the SQL host.

4. To re-tier the recalled copy, select the recalled copy and click Re-tier. 5. To change the retention period for the recalled copy, select the recalled copy and click Edit Recall Retention.

42 Managing Storage, Assets, and Protection

Performing Self-Service Backups of Microsoft SQL Databases

Topics:

Performing self-service SQL database backups Overview of Application Direct with SQL Server backups Best practices to back up SQL Server with Application Direct Naming conventions for backups with Application Direct Circumstances that promote SQL Server backups to level full Scheduling backup jobs Performing manual backups

Performing self-service SQL database backups To enable self-service protection, when you create the SQL protection policy, select Self-Service Protection.

When performing a self-service stand-alone backup of an AAG asset, the backups appear under the AAG asset.

The following topics provide instructions on how to perform a self-service SQL Server backup.

Overview of Application Direct with SQL Server backups The Microsoft application agent for Application Direct with SQL Server module integrates with the SQL Virtual Device Interface (VDI).

The interface enables you to configure the module by using a SQL Server Management Studio plug-in.

The plug-in is similar to the SQL native backup and restore graphical user interface (GUI). Database administrators (DBAs) can use the Microsoft native tools to back up and restore the SQL data.

NOTE: Self-service backups and restores are backups and restores that you perform through the SQL Server Management

Studio (SSMS), Transact-SQL (T-SQL) scripting, or the Microsoft application agent command-line interface.

Application Direct backups to a Data Domain system use the following components:

The Application Direct library API enables the backup software to communicate with the Data Domain system.

The DDBEA section of the eLab Navigator at https://elabnavigator.emc.com/eln/modernHomeDataProtection provides information about the supported versions of the Application Direct library and the Data Domain operating system.

The distributed segment processing component reviews the data that is already stored on the Data Domain system, and sends only unique data for storage. The distributed segment processing component enables the backup data to be deduplicated on the database or application host to reduce the amount of data transferred over the network. Distributed segment processing on page 44 provides information.

When the Data Domain system restores data to a client, the system converts the stored data to its original non-deduplicated state before sending it over the network.

4

Performing Self-Service Backups of Microsoft SQL Databases 43

Federated backups of Always On availability groups

You can use the SQL Server's Always On availability groups feature to place databases in an Availability Group for high availability.

The database administrator can set backup preferences for the availability group and nominate a particular copy, which can be either the primary copy or one of the secondary copies, to use for the backup.

The Microsoft application agent supports federated backups for Application Direct backups. During federated backups, the Microsoft application agent detects the SQL Server's backup preferences setting for the availability group, and then performs the backup on the preferred node.

NOTE: On a secondary node, SQL Server supports only copy-only backups of databases. If the preferred node is a

secondary node, the Microsoft application agent performs a copy-only backup of databases. SQL Server does not support

differential backups on secondary SQL Server replicas. However, you can perform transaction log backups from either

copies.

The following figure illustrates an overview of the process interactions during federated backups. In the figure, the backup starts on Node A, but Node B is preferred.

Figure 2. Federated backup command and data flow

Distributed segment processing

Distributed segment processing uses the Data Domain Boost library on the database server and the Data Domain software on Data Domain Replicator. The Microsoft application agent loads the DD Boost library during backup and restore operations.

Distributed segment processing allows the Microsoft application agent to perform parts of the deduplication process, which avoids sending duplicate data to the Data Domain system that you configured as a storage server.

The distributed segment processing feature provides the following benefits:

Increases throughput because the DD Boost library sends only unique data to the Data Domain system. The throughput improvements depend on the redundant nature of the data that you back up, the overall workload on the database server, and the database server capability. In general, greater throughput is attained with higher redundancy, greater database server workload, and greater database server capability.

Decreases network bandwidth requirements by sending the unique data to the Data Domain system through the network.

Manage distributed segment processing by using the ddboost command options. Use distributed segment processing if the network connection is 1 Gb Ethernet. Configuring distributed segment processing on page 45 provides information on how to configure the distributed segment processing.

Distributed segment processing supports the following modes of operation for sending backup data to a Data Domain system:

Distributed segment processing enabled Distributed segment processing disabled

Set the operation mode on the Data Domain system. The Microsoft application agent negotiates with the Data Domain system for the current setting of the option and accordingly performs backups.

44 Performing Self-Service Backups of Microsoft SQL Databases

Distributed segment processing enabled mode

When you enable the distributed segment processing feature, the DD Boost library performs the following tasks: 1. Segments the data. 2. Computes IDs for the data segments. 3. Checks with the Data Domain system for duplicate segments. 4. Compresses unique segments that the Data Domain system does not contain. 5. Sends the compressed data to the Data Domain system, which writes the unique data to disk.

You must configure the local compression algorithm that the DD Boost library uses on the Data Domain system. The Data Domain Operating System Administration Guide provides more information about local compression and its configuration.

Distributed segment processing disabled mode

When you disable the distributed segment processing feature, the DD Boost library sends the data directly to the Data Domain system through the network. The Data Domain system then segments, deduplicates, and compresses the data before writing it to the disk.

NOTE: You cannot disable the distributed segment processing feature on an Extended Retention Data Domain system.

Configuring distributed segment processing

You must configure the distributed segment processing option on the Data Domain system. The option setting applies to all the database servers and all the software that uses DD Boost.

You can manage the distributed segment processing by using one of the following methods:

The ddboost command.

Data Domain System Manager on the Data Management > DD Boost page.

The Data Domain Operating System Administration Guide provides information.

To configure the distributed segment processing option, run the following command:

ddboost option set distributed-segment-processing {enabled | disabled} Enabling or disabling the distributed segment processing option does not require a restart of the Data Domain file system.

A host on which you have installed the Data Domain Operating System (DD OS) release 5.2 or later enables the distributed segment processing feature by default. If you upgrade a host from DD OS release 5.0.x or 5.1.x to DD OS release 5.2 or later, the distributed segment processing option remains in its previous state, that is, either enabled or disabled.

Best practices to back up SQL Server with Application Direct Consider the best practices to back up SQL Server using Application Direct.

Configure backups to use the same Data Domain path

To ensure the consistency of the backups on the Data Domain system, configure all the backups of a SQL Server instance to use the same Data Domain system and path.

Configure connection settings

Data Domain Boost devices do not distinguish among Transmission Control Protocol (TCP)/Internet Protocol (IP), Fibre Channel (FC), and LAN, WAN, and MAN network types. Data Domain Boost devices can successfully operate where packet loss is strictly 0% and latency is less than 20 ms.

Performing Self-Service Backups of Microsoft SQL Databases 45

Use supported characters

The Microsoft application supports locale-specific date and time processing and setting the date and time display language can be set to non-English characters. However, database and path names must be written in ASCII characters only. Naming conventions for backups with Application Direct on page 50 provides more information on supported characters for database and instance names.

Configure Data Domain quota limits

The Microsoft application agent does not have a parameter to control the total size that it consumes. The quota limits can only be set on the Data Domain system on a per-MTree (storage unit) basis.

An MTree's quota limits are calculated based on the logical size, which is the size before compression and de-duplication of the data.

The quota limits impact only backup operations.

Configuring usage limits of Data Domain resources on page 46 provides more information about quota limit, impact of exceeding the limits, and configuring the usage limits.

Configure usage limits for Data Domain streams

Configure a sufficient number of Data Domain streams for better performance of backups and restores. The streams control backup and restore parallelism for each database.

The Microsoft application agent requires one stream per save set that you back up or restore. When you perform striped backups, each stripe requires one stream. The stripes are concurrently executed for each database. Databases are sequentially backed up and restored. When you use stripes, the number of streams must be equal to or more than the number of stripes.

The minimum number of streams for a non-stripe environment is 1.

Configuring usage limits of Data Domain streams on page 48 provides more information about streams limit, impact of exceeding the limits, and configuring the usage limits.

Delete expired backups by using the ddbmexptool expiry tool

The Microsoft application agent does not delete the expired backup copies automatically. You must explicitly delete the expired backup copies by using the ddbmexptool expiry tool.

Configuring usage limits of Data Domain resources

Use either the Data Domain operating system commands or the Data Domain Administration GUI to set limits on usage of the following Data Domain resources:

Capacity: The amount of hard drive capacity that the application agent uses on a Data Domain host.

Capacity limits are based on the used logical space, which depends on the amount of data that is written to a storage unit before deduplication. Logical capacity is the size of the uncompressed data. For example, when a 1 GB file is written twice to the same empty storage unit, the storage unit has a logical size of 2 GB, but a physical size of 1 GB.

Streams: The number of Data Domain Boost streams that the application agent uses to read data from a storage unit or write data to a storage unit on a Data Domain host.

NOTE: The Microsoft application agent supports usage limits on Data Domain resources for Application Direct

operations only.

Data Domain uses the term quota to collectively describe the capacity soft and hard limits of a storage unit. Stream limits are called limits.

The Data Domain operating system supports soft and hard limits on capacity and streams usage:

When the Microsoft application agent exceeds a soft limit, the Data Domain host generates an alert. If the administrator has configured a tenant-unit notification list, the Data Domain host sends an email to each address in the list. The Microsoft application agent can continue to use more of the limited resource after a soft limit is exceeded.

46 Performing Self-Service Backups of Microsoft SQL Databases

When the Microsoft application agent exceeds a hard limit, it cannot use any more of the limited resource.

The Data Domain administrator must create a separate storage unit for each application agent host or set of hosts that are limited.

For example, if there are 10 application agent hosts, the Data Domain administrator must create at least 10 storage units to limit the storage unit capacity that each application agent host uses. To use fewer storage units, the administrator must group the application agent hosts and assign the group to a single storage unit. The application agent hosts in the group share this storage unit. However, you cannot limit the consumption of a storage unit by each host. One application agent host can consume 100% of the storage unit. The resources are consumed on the first-come, first-serve basis.

To determine the stream limits of a storage unit, run the following command:

msagentadmin.exe administration --listSU --config [-- debug 9] Example output of the command:

active write streams: 11 active read streams: 0 soft limit write streams: none soft limit read streams: none soft limit combined streams: 40 hard limit combined streams: 60

NOTE: Depending on the number and type of parallel operations that are performed at a given time, the stream usage

varies. To determine the exact usage of the streams, monitor the number of streams that the storage units use over a

period of time.

Impact of exceeding quota limits

At the start of a backup, the Microsoft application agent cannot determine how much capacity is required for the backup. The Microsoft application agent can perform a requested backup only when the destination host has sufficient space or storage capacity.

Exceeding the soft quota limit

When the Microsoft application agent exceeds the capacity soft limit: During a backup, if the storage unit is part of a tenant-unit with a notification list, the Data Domain host sends an email to

each address in the list. The list can include the Data Domain administrator and the application agent user. Alerts appear in the Current Alerts panel in the Data Domain Administration GUI regardless of whether the storage unit is

part of a tenant-unit. The backup or restore operation continues without any adverse impact. The application agent does not generate any warning

or error message in its log file or operational output.

Exceeding the hard quota limit

When the Microsoft application agent exceeds the capacity hard limit during a backup, the Microsoft application agent cancels the backup.

Check the client backup and restore logs for error messages related to insufficient space on the storage unit. The following message shows an example:

145732:(pid 4584):Max DD Stream Count: 60 153003:(pid 4584): Unable to write to a file due to a lack of space. The error message is: [5005] [ 4584] [984] Thu Apr 14 10:14:18 2016 ddp_write() failed Offset 163577856, BytesToWrite 524288, BytesWritten 0 Err: 5005-ddcl_pwrite failed (nfs: No space left on device) 86699:(pid 4584): Unable to write data into multiple buffers for save-set ID '1460654052': Invalid argument (errno=22)

Performing Self-Service Backups of Microsoft SQL Databases 47

Configuring usage limits of Data Domain quota

To configure capacity usage limits for the application agent, the Data Domain administrator must set the hard capacity limit for the storage unit that the application agent uses for backups:

Steps

1. Determine which application agent hosts use the storage unit.

2. Determine the amount of capacity to allow for the storage unit.

3. Create the storage unit, and then set the capacity quota by using either the GUI or the command prompt. The Data Domain documentation provides information.

4. Provide the Data Domain hostname, storage unit name, username, and password of the storage unit to the application agent users to use to perform backups.

The Data Domain administrator can also set the soft capacity quota for the storage unit, which sends alerts and notifications, but does not limit the capacity usage.

NOTE: When a storage unit is almost full and the capacity quota is decreased, the next backup can fail. Data Domain

administrators must notify the Microsoft application agent users when they decrease a capacity quota, so that the

application agent users can evaluate the potential impact on backups.

Impact of exceeding the soft stream limit

When the Microsoft application agent exceeds the stream soft stream limit: During a backup, if the storage unit is part of a tenant-unit with a notification list, the Data Domain host sends an email to

each address in the list. The list can include the Data Domain administrator and the application agent user. Alerts appear in the Current Alerts panel in the Data Domain Administration GUI regardless of whether the storage unit is

part of a tenant-unit. The backup or restore operation continues without any adverse impact. The application agent does not generate any warning

or error message in its log file or operational output.

Impact of exceeding the hard stream limit

When the Microsoft application agent exceeds the hard stream limit during an operation, the Microsoft application agent cancels the operation.

Check the client backup and restore logs for error messages related to an exceeded stream limit. The following message shows an example:

153004:(pid 4144): Unable to write to a file because the streams limit was exceeded.

Configuring usage limits of Data Domain streams

A storage unit can have soft and hard limits for streams. The Data Domain administrator can set individual soft limits for read, write, and replication streams. The administrator can set a hard limit only for the total number of streams.

About this task

To configure a streams usage limit for a storage unit, the Data Domain administrator must set the hard limit for the storage unit that the application agent uses for backups:

Steps

1. Determine which application agent hosts use the storage unit.

2. Determine the number of backup streams to allow for the storage unit.

3. Create the storage unit.

48 Performing Self-Service Backups of Microsoft SQL Databases

The Data Domain administrator can set the streams limit either as part of the ddboost storage-unit create command or after creating the storage unit by using the ddboost storage-unit modify command. The Data Domain documentation provides information.

NOTE: The Data Domain administrator cannot set a streams limit by using the Data Domain Administration GUI.

4. Provide the Data Domain hostname, storage unit name, username, and password of the storage unit to the application agent users to use to perform backups.

The Data Domain administrator can also set soft limits for the storage unit, which send alerts and notifications, but do not limit the number of streams used.

The Data Domain administrator can use the ddboost storage-unit modify command to modify the streams limits of storage units. The Data Domain documentation provides information.

CAUTION: The Data Domain administrator must use caution when setting a streams hard limit. Setting the

streams limit to a low value can impact the backup and restore performance. Decreasing a streams limit

can result in a restore failure. The Data Domain administrator must notify the application agent users when

decreasing a streams hard limit so that the application agent users can evaluate the potential impact on

backups and restores.

Configure the database backup stripe level

Starting with version 19.5, you can modify the stripe level of a backup at the individual database level by using the following procedures. Starting with version 19.6, you can set the stripe level through the following procedures only for self-service stand-alone SQL Server backups, not for centralized backups performed through PowerProtect Data Manager.

CAUTION: After you upgrade from version 19.5, centralized backups ignore any parallelism and backup stripe

settings that were previously configured on the Extended Properties page in SQL Server Management Studio

(SSMS). For centralized backups with version 19.6 or later, you must configure the parallelism settings through

the PowerProtect Data Manager UI, as described in the next topic.

The backup stripe level configuration includes the following features:

The backup stripe level setting for individual databases has a higher priority than the stripe level setting through the backup command with the -S option.

For any database, the minimum supported stripe level is 1 and the maximum supported stripe level is 32. In an FCI or AAG cluster, setting the backup stripe level of a database at any one node reflects across all the nodes in the

cluster.

To set the backup stripe level for any database, use either of the following procedures:

Perform the following steps in the SQL Server Management Studio (SSMS):

1. Right-click the database name, and select Properties. 2. In the properties window, select Extended Properties from the left side pane. 3. Add the required property name and the backup stripe level value:

For a full backup, add the property name ppdmFullStripes and a value between 1 and 32. For a differential backup, add the property name ppdmDiffStripes and a value between 1 and 32. For a log backup, add the property name ppdmLogStripes and a value between 1 and 32.

Performing Self-Service Backups of Microsoft SQL Databases 49

Figure 3. Extended Properties page with backup stripe levels Run the following SQL commands to set the backup stripe level for any database:

USE ; GO EXEC sp_addextendedproperty @name = N'ppdmDiffStripes', @value = '4'; EXEC sp_addextendedproperty @name = N'ppdmFullStripes', @value = '8'; EXEC sp_addextendedproperty @name = N'ppdmLogStripes', @value = '2';

Naming conventions for backups with Application Direct When naming SQL Server instance, database, and filegroups, consider that the Microsoft application agent does not distinguish the difference between upper and lowercase letters. The names are not case-sensitive.

Therefore, if there are two or more databases with the same name but with different capitalization, such as DB1 and db1, the Microsoft application agent views these databases as the same and by default backs up only one of the databases.

The following table described the special characters that are supported for naming database backups in SQL stand-alone, cluster, and Always On availability group configurations with Application Direct.

Table 4. Supported special characters

Special character Name

~ Tilde

` Accent grave

50 Performing Self-Service Backups of Microsoft SQL Databases

Table 4. Supported special characters (continued)

Special character Name

! Exclamation mark

@ At the rate

% Percentage

^ Caret

& Ampersand

( Open parenthesis

) Close parenthesis

- Hyphen

_ Underscore

{ Open curly bracket

} Close curly bracket

\ Backslash

. Period

' Apostrophe

NOTE: While SQL Server supports naming instances with the hash symbol (#), the Microsoft application agent does not. If

an instance includes a hash symbol, backups of that instance will fail.

Circumstances that promote SQL Server backups to level full Transaction log backups are promoted to full backups in certain situations.

By default, transaction log backups are promoted to level full in the following scenarios:

When there is not an existing level full backup. When a log gap is detected. When the recovery model is changed. When the backup includes simple model databases, either the simple model databases are promoted to a full backup or the

simple model databases are skipped, depending on the backup settings.

Options to tune the behavior of automatic promotion are available while configuring a backup. The ddbmsqlsv -a "BACKUP_PROMOTION" command flag and the Microsoft app agent for Application Direct SSMS plugin Backup Promotion option both control backup promotion.

Scheduling backup jobs You can schedule Microsoft SQL Server backup jobs by using either the SQL Server Agent or the Windows Task Scheduler.

Scheduling SQL Server backups by using SQL Server Agent

The SQL Server Agent is a job-scheduling agent which is contained in the Microsoft SQL Server package.

This section describes how to schedule SQL Server backups by using the SQL Server Agent job for the CmdExec and T-SQL subsystems. The SQL Server Agent, a job-scheduling agent within the SQL Server package, consists of a Windows service that runs jobs.

Performing Self-Service Backups of Microsoft SQL Databases 51

Each job can contain one or more job steps and each step can contain its own tasks. The Microsoft application agent uses SQL Server to store job information and can run jobs on a schedule in response to a specific event or in response to a specific demand.

Configuring the SQL Server Agent to schedule jobs

Configuring the SQL Server Agent comprises of the following tasks:

Enabling the SQL Server Agent Configuring security

Enabling the SQL Server Agent

The SQL Server Agent is in disabled state, by default. To enable the SQL Server Agent:

1. Open SSMS, and then select View > Object Explorer. 2. Right-click SQL Server Agent, and then select Start.

Configuring security

To run the Microsoft application agent command prompt commands in the CmdExec subsystem, you must have administrator privileges. You can either change the SQL Server Agent service login credentials to administrator or configure a proxy host to the CmdExec subsystem.

Changing the SQL Server Agent service login credentials

Steps

1. From the Windows desktop, click Start > Run.

2. In the Run dialog box, in the Open field, type services.msc, and then click OK.

3. In the Services window, right-click SQL Server Agent Service, and then select Properties.

4. In the SQL Server Agent Service Properties dialog box:

a. On the Log On tab, select This account. b. Type the administrator user credentials in the relevant fields.

You can also click Browse and select the user who has the administrator privileges.

c. Click OK.

Creating a proxy for the CmdExec subsystem

The SQL Server Agent uses proxies, which are objects that enable the SQL Server Agent to access stored credentials for Windows users, to define the security context for job steps.

About this task

When you run a job step that is configured to use a proxy, the SQL Server Agent uses the credentials that are defined in the proxy, and then uses the corresponding security context to run the job step.

Steps

1. Open SSMS, and then select View > Object Explorer.

2. Specify user credentials:

a. In the Object Explorer, expand Security, right-click Credentials, and then select Properties. b. On the Credential Properties - EMC page, specify the following fields, and then click OK:

Credential name: Type a name for the credential. Identity: Type the administrator username. Password: Type the password for the user that you specified in the Identity field.

52 Performing Self-Service Backups of Microsoft SQL Databases

Confirm password: Retype the password that you specified in the Password field.

3. Create a proxy:

a. In the Object Explorer, expand SQL Server Agent, and then expand Proxies. b. Right-click Proxies, and then select New proxy. c. On the EMC Proxy Account Properties page, specify the following fields, and then click OK:

Proxy name: Type a name for the proxy. Credential name: Type the credential name that you specified in step 2b. Active to the following subsystems: Under this field, select Operating system (CmdExec).

NOTE: When you create a job step, you must select this proxy from the Run as list on the Job Step Properties

page.

Scheduling a CmdExec job

You can schedule a CmdExec job on either a single SQL Server or multiple SQL Servers.

Scheduling a CmdExec job on a single SQL Server

Steps

1. Open SSMS, and then select View > Object Explorer.

2. In the Object Explorer, expand SQL Server Agent, right-click Jobs, and then select New job.

3. On the Job Properties window:

a. On the General page, type the appropriate information in the Name, Owner, and Description fields. b. On the Steps page, click New to create a step. c. In the Job Step Properties window, on the General page, specify the following fields:

Step name: Type a name for the job step. Type: Select Operating system (CmdExec). Run as: According to your configuration, select either proxy or SQL Server Agent Service Account. Process execute exit code of a successful command: Type the process success exit code. Command: Specify the required command.

Perform backups with the Microsoft app agent for Application Direct SSMS plug-in on page 57 provides information about how to generate the command.

The generated command does not have a full path to the binary. When you specify the generated command in this field, you must add the full path to the binary. If the binary path contains spaces, specify the path in the quotes.

For example:

"C:\Program Files\DPSAPPS\MSAPPAGENT\bin\ddbmsqlsv.exe" -D9 -c NMMDB154.nmmdev.com -l full -a "NSR_DFA_SI=TRUE" -a "NSR_DFA_SI_USE_DD=TRUE" -a "NSR_DFA_SI_DD_HOST=10.31.192.14" -a "NSR_DFA_SI_DD_USER=ddbma" -a "NSR_DFA_SI_DEVICE_PATH=/ddbma-sqlagent2" "MSSQL:master".

d. On the Advanced page, specify the following fields: On success action: Select whether to proceed to the next job step or quit the current job step and report success,

after the current job step succeeds. Specify the other fields according to your requirements. Click OK.

e. On the Schedules page, click New to schedule a job. f. In the Job Schedule Properties window, specify appropriate information in the corresponding fields, and then click OK. g. On the Alerts page, click Add to create an alert that will perform a job when a certain event occurs. h. In the New Alert window, on the General page, specify the following fields:

Name: Type a name for the alert. Type: Select the type of the event. Specify the appropriate information in the other fields.

i. On the Response page, specify the following fields: Notify operators: Select this option to send a message to the operators about the job step status. New Operator: Click this button to add an operator to the Operator list.

Performing Self-Service Backups of Microsoft SQL Databases 53

j. On the Options page, configure a method, such as E-mail, Pager, or Net Send, to notify operators about the status of the job step.

k. On the Notifications page, under Actions to perform when the job completes, select the appropriate notification methods to notify operators about the status of the job step.

l. On the Targets page, select Target local server. m. Click OK.

Scheduling a CmdExec job on multiple SQL Servers

Scheduling jobs from one central location to multiple target SQL Servers eases the database administrators job. To configure this setup, you must install the Microsoft application agent on all target hosts, make one SQL Server Agent a master, and make the rest of the hosts targets.

Configuring master and target SQL Server Agents

Steps

1. Open the SSMS, and then select View > Object Explorer.

2. Right-click SQL Server Agent, and then select Multi Server Administrator > Make this a Master.

The Master Server Wizard appears.

3. On the Welcome to the Master Server Wizard page, click Next.

4. On the Master Server Operator page, type the appropriate information in the E-mail address, Pager address, and Net send address fields to notify the operators about the status of the job, and then click Next.

5. On the Target Servers page:

a. Under the Registered servers panel, select the servers that you want to use as targets for the SQL Server Agent jobs, and then click the right arrow to move them to the Target servers panel.

b. If you want to add servers to the Registered servers panel, click Add Connection. c. In the Checking Server Compatibility dialog box, review the information, and then click Close.

The Master Server Login Credentials page appears.

6. Enable SQL Server remote connectivity:

a. In SSMS, right-click the SQL Server, and then click Properties. b. In the Server Properties window, under the Select a page group, select Connections. c. Select Allow remote connections to this server. d. Click OK.

7. Specify general firewall exceptions on the SQL Server.

8. If an SSL certificate does not exist, set the encryption level on the target hosts to 1 or 0 according to the security level you need.

To set the encryption level, change the MsxEncryptChannelOptions registry entry to 1 or 0. The registry key is located in \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\ \SQLServerAgent\.

9. On the Master Server Login Credentials page, click Next.

10. On the Complete the Wizard page, review the information, and then click Finish.

Running a job on the target servers

Steps

1. Open the SSMS, and then select View > Object Explorer.

2. In the Object Explorer, expand SQL Server Agent, right-click Jobs, and then select New job.

3. On the Job Properties window:

a. On the General page, type the appropriate information in the Name, Owner, and Description fields. b. On the Steps page, click New to create a step. c. In the Job Step Properties window, complete the following steps:

54 Performing Self-Service Backups of Microsoft SQL Databases

i. On the General page, specify the following fields: Step name: Type a name for the job step. Type: Select Operating system (CmdExec). Run as: According to your configuration, select either proxy or SQL Server Agent Service Account. Process execute exit code of a successful command: Type the process success exit code. Command: Specify the required command.

Perform backups with the Microsoft app agent for Application Direct SSMS plug-in on page 57 provides information about how to generate the command.

The generated command does not have a full path to the binary. When you specify the generated command in this field, you must add the full path to the binary. If the binary path contains spaces, specify the path in the quotes.

For example:

"C:\Program Files\DPSAPPS\MSAPPAGENT\bin\ddbmsqlsv.exe" -D9 -c NMMDB154.nmmdev.com -l full -a "NSR_DFA_SI=TRUE" -a "NSR_DFA_SI_USE_DD=TRUE" -a "NSR_DFA_SI_DD_HOST=10.31.192.14" -a "NSR_DFA_SI_DD_USER=ddbma" -a "NSR_DFA_SI_DEVICE_PATH=/ddbma-sqlagent2" "MSSQL:master"

ii. On the Advanced page, specify the following fields: On success action: Select whether to proceed to the next job step or quit the current job step and report

success, after the current job step succeeds. Specify the other fields according to your requirements. Click OK.

d. On the Schedules page, click New to schedule a job. e. In the Job Schedule Properties window, specify appropriate information in the corresponding fields, and then click OK. f. On the Alerts page, click Add to create an alert that will perform a job when a certain event occurs. g. In the New Alert window:

i. On the General page, specify the following fields. Name: Type a name for the alert. Type: Select the type of the event. Specify the appropriate information in the other fields.

ii. On the Response page, specify the following fields: Notify operators: Select this option to send a message to the operators about the job step status. New Operator: Click this button to add an operator to the Operator list.

iii. On the Options page, configure a method, such as E-mail, Pager, or Net Send, to notify operators about the status of the job step.

iv. On the Notifications page, under Actions to perform when the job completes, select the appropriate notification methods to notify operators about the status of the job step.

v. On the Targets page, from the Target multiple servers list, select the target servers.

h. Click OK.

Scheduling a T-SQL job

T-SQL subsystem does not work under proxies.

Steps

1. Open the SSMS, and then select View > Object Explorer.

2. In the Object Explorer, expand SQL Server Agent, right-click Jobs, and then select New job.

3. In the Job Properties window:

a. On the General page, specify the following fields:

Name: Type a name for the job. Owner: Click the button beside the text box, and then complete the following steps:

i. In the Select Login dialog box, click Browse. ii. In the Browse for Objects dialog box, under Matching objects, select NT SERVICE\SQLSERVERAGENT, and

then click OK. iii. In the Select Login dialog box, click OK.

Description: Type a description for the job.

Performing Self-Service Backups of Microsoft SQL Databases 55

b. On the Steps page, click New. c. In the Job Step Properties window, on the General page, specify the following fields:

Step name: Type a name for the job step. Type: Select Transact-SQL script (T-SQL). Run as: Select SQL Server Agent Service Account. Process execute exit code of a successful command: Type the process success exit code. Command: Specify the required T-SQL command.

Perform backups with the Microsoft app agent for Application Direct SSMS plug-in on page 57 provides information about how to generate the T-SQL command.

You can run the generated T-SQL command by using the New Query menu option to check whether the operation succeeds. If the command runs successfully, the scheduled backups will be successful.

If you want to use return codes in the generated T-SQL command, you must modify the command.

Consider the following example raw T-SQL command:

USE [master] GO DECLARE @returnCode int EXEC @returnCode = dbo.emc_run_backup ' -c NMMDB154.nmmdev.com -l full -a "NSR_DFA_SI=TRUE" -a "NSR_DFA_SI_USE_DD=TRUE" -a "NSR_DFA_SI_DD_HOST=10.31.192.14" -a "NSR_DFA_SI_DD_USER=ddbma" -a "NSR_DFA_SI_DEVICE_PATH=/ddbma-sqlagent2" "MSSQL:Fabrics"' PRINT @returnCode GO

Consider the following example T-SQL command with return codes:

DECLARE @returnCode int EXEC @returnCode = dbo.emc_run_backup ' -c NMMDA224.heroines.local -l full -a "NSR_DFA_SI=TRUE" -a "NSR_DFA_SI_USE_DD=TRUE" -a "NSR_DFA_SI_DD_HOST=10.31.77.27" -a "NSR_DFA_SI_DD_USER=ost112" -a "NSR_DFA_SI_DEVICE_PATH=/heroines" "MSSQL:CopyOftest_db_1" "MSSQL:db1"' IF @returnCode <>0 BEGIN RAISERROR ('Fail!', 16, 1) END

If the return code is error, the job step fails.

NOTE: The last two parameters, 16 and 1 are necessary to raise an error if a job step fails.

d. On the Advanced page, specify the following fields:

On success action: Select whether to proceed to the next job step or quit the current job step and report success, after the current job step succeeds.

Specify the other fields according to your requirements. Click OK. On the Schedules page, click New to schedule a job. In the Job Schedule Properties window, specify appropriate information in the corresponding fields, and then click

OK. On the Alerts page, click Add to create an alert that will perform a job when a certain event occurs. In the New Alert window:

On the General page, specify the following fields.

Name: Type a name for the alert. Type: Select the type of the event. Specify the appropriate information in the other fields.

e. On the Response page, specify the following fields:

Notify operators: Select this option to send a message to the operators about the job step status. New Operator: Click this button to add an operator to the Operator list.

f. On the Options page, configure a method, such as E-mail, Pager, or Net Send, to notify operators about the status of the job step.

56 Performing Self-Service Backups of Microsoft SQL Databases

g. On the Notifications page, under Actions to perform when the job completes, select the appropriate notification methods to notify operators about the status of the job step.

h. On the Targets page, select Target local server. i. Click OK.

Next steps

To check the status of a job, either right-click the job and select View History or review the log files in the nsr/applogs/ folder.

Scheduling SQL Server backups by using Windows Task Scheduler

To schedule SQL Server backups by using Windows Task Scheduler, perform the following steps:

Steps

1. On the Windows desktop, click Start > All Programs > Accessories > System Tools > Task Scheduler.

2. In the left pane of the Task Scheduler window, right-click Task Scheduler Library, and then select Create Basic Task.

The Create Basic Task Wizard wizard appears.

3. On the Create a Basic Task page, in the Name field, type a name for the task, and then click Next.

4. On the Task Trigger page, select the appropriate option to start the task, and then click Next.

The page that corresponds to the selected option appears.

5. Specify or select all the fields that you require to perform the task, and then click Next.

6. On the Action page, select Start a program, and then click Next.

7. On the Start a program page, browse for the .bat file and in the Program/script field, type the file path, and then click Next.

8. On the Summary page, review the details of the task, and then click Finish.

The process creates the task and adds it to your Windows schedule.

Performing manual backups The Microsoft application agent for Application Direct with SQL Server supports multiple tools to perform manual backups.

You can configure backups using the Microsoft application agent SQL Server Management Studio plug-in (GUI), Microsoft application agent for Application Direct commands, or T-SQL scripts.

Perform backups with the Microsoft app agent for Application Direct SSMS plug-in

The Microsoft application agent supports a user interface to perform backup operations through a SQL Server Management Studio (SSMS) plug-in.

About this task

If the Data Domain device is connected only on the backup LAN, and the SQL host is multi-homed and has an interface on the backup LAN, the backups to the Data Domain device proceed over the backup LAN by default.

If both the Data Domain device and the SQL host are multi-homed, and are connected to the backup LAN, ensure that the Data Domain server name that you specify in the Microsoft app agent for Application Direct SSMS plug-in is the same as the backup LAN IP address. The backups to the Data Domain device proceed over the backup LAN.

To back up SQL Server to a Data Domain server over fibre channel (FC), you must first configure FC on the Data Domain server.

Performing Self-Service Backups of Microsoft SQL Databases 57

The Script view is available in each page of the Backup tab, which generates a command prompt equivalent script. You can use the script to create a .bat file to perform scheduled backups, automation, and other tasks. The following script options are available:

CLI Scripts: To generate the CLI script, which you can use to run a backup from the CLI. T-SQL Scripts: To generate a backup script in the T-SQL format.

Configure general backup settings

To configure SQL backups with the Microsoft app agent for Application Direct SSMS plug-in, you must first specify general backup options on the Backup > General page.

Steps

1. Open the Microsoft app agent for Application Direct window to the Backup > General page.

The General page appears as shown in the following figure.

Figure 4. Application Direct - Backup-General page

2. Leave the SQL Server Host as-is. The instance is populated by default.

3. In the SQL Server Instance field, select the name of the SQL Server instance that contains the databases that you want to back up.

4. In the Database Filter list, select one of the following options:

All Databases: Displays the regular databases and the Always On availability group databases of the selected SQL Server instance in the database table, which is located below the Database Filter field.

This option is selected by default.

You can back up the Always On availability group databases as regular databases without considering the Always On availability group preferences.

Non AAG Databases: Displays only the regular databases of the selected SQL Server instance in the database table.

58 Performing Self-Service Backups of Microsoft SQL Databases

A list of Always On availability groups if available: Selecting one of the Always On availability groups displays the corresponding databases in the database table.

The Connections panel displays the name of the cluster that contains the Always On availability group and the backup preference that specifies the preferred replica to perform the backup. You can configure the replica preference of the Always On availability groups through the SSMS.

5. In the database table, select either all the databases by selecting the checkbox in the header row or only the individual databases that you want to back up.

6. In the Backup type list, select the type of the backup that you want to perform such as, Full, Transaction log, or Differential.

7. (Optional) To perform a copy-only backup, select Copy-only backup.

Copy-only backups do not disturb the SQL Server backup chain or affect backup-level promotion and log truncation. Copy-only backups are supported for level full or transaction log backups.

8. (Optional) To exclude databases from a SQL Server instance-level backup, perform the following steps:

a. Click the Excluded Databases button.

The Exclude Databases window appears.

b. Select each database that you want to exclude from the backup. c. Click OK.

The databases that are selected for exclusion appear grayed out in the list of databases and the number of excluded databases is displayed.

9. In the Name field, type a name for the backup that you want to perform.

After the backup completes, the save set names of the backed-up databases will be in the following format:

: For example, you select the databases db1 and db2, specify test as the backup or save set name, and then perform the backup. After the backup completes, the save set names of the backed-up databases are test: db1 and test:db2.

NOTE: The number sign (#) character is not supported for save set names. If you use this character, backups fail.

10. In the Description field, type a description for the backup that you want to perform.

11. In the Expires after (days) field, select the number of days after which the backup must expire. The default value is 30.

12. Under Destination, to select the target Data Domain server for the backup, perform the following steps:

a. Click the Data Domain Server browse button. The Data Domain Connection List & Lockbox Settings dialog box appears, as shown in the following figure.

Figure 5. Data Domain connection list and lockbox settings b. In the Lockbox Folder field, type the path to lockbox, and then click Refresh.

Performing Self-Service Backups of Microsoft SQL Databases 59

The default path to the lockbox is C:\Program Files\DPSAPPS\common\lockbox.

The DataDomain Connections list is refreshed. c. In the DataDomain Connections table, select the target Data Domain server for the backup. d. (Optional) To add or remove Data Domain servers from the DataDomain Connections table, perform one of the

following action sequences:

To add a server, perform the following steps: i. Click Add.

The Add Data Domain Server details dialog box appears as shown in the following figure.

Figure 6. Data Domain - Add Data Domain server details ii. In the DataDomain Server field, type the name of the server. iii. In the Communication Protocol list, select either Ethernet or Fibre channel, the medium through which you

want to back up the database to the server. Ethernet is selected by default. iv. If you have selected Fibre channel from the Communication Protocol list, type the name of the Data Domain

server as the FC service name in the FC Service Name field. v. In the User Name field, type the username of the DD Boost user. vi. In the Password field, type the password of the DD Boost user. vii. In the Storage Unit field, type the name of the target storage unit for the backup.

Data Domain Boost user credentials are verified before they are saved in the lockbox. Verification of the user credentials requires some time to complete.

To remove a Data Domain server, select the server, and then click Remove. To add a SQL virtual server to either back up databases to SQL clustered instances or restore databases from SQL

clustered instances, perform the following steps: Select Edit LockBox Settings. In the Enter Host Name field, type the FQDN of the SQL virtual server. Click OK.

To remove a SQL virtual server, select the FQDN of the SQL virtual server from the Select Host Name list, and then click Remove.

The PersistedSettings.xml file in the lockbox folder contains the information about Data Domain servers. Adding a server to a new lockbox creates the PersistedSettings.xml file. Adding a server to or removing a server from the lockbox updates the PersistedSettings.xml file.

13. To start the backup operation, click Run.

Configure optional backup settings

When you configure SQL backups with the Microsoft app agent for Application Direct SSMS plug-in, you can specify optional backup settings on the Backup > Options page.

About this task

All settings on the Options page are optional.

60 Performing Self-Service Backups of Microsoft SQL Databases

Steps

1. From the left panel, click Options to specify optional backup settings.

The Options page appears as shown in the following figure.

Figure 7. Application Direct - Backup-Options page

2. To perform a checksum operation with the backup and save the information to the backup media, select Perform checksum before writing to media.

The Microsoft application agent performs another checksum before a restore to ensure that the checksum matches the backup.

3. To use a checksum to detect a partial backup or restore state, select Continue on error.

The SQL Server verifies the checksum by calculating a local result and comparing the result with the stored value. If the values do not match and you encounter errors, you can select this option to continue the backup or restore operation.

4. To truncate the transaction logs before a backup, select Truncate the transaction log.

The Microsoft application agent enables this option if you select transaction log as the backup type.

5. To perform a tail-log backup of the database and leave the database in the restoring state, select Backup the tail of the log and leave database in restoring state.

6. Under Stripes, select Create a striped backup to create a striped backup. You can also specify the number of stripes. If you specify a value greater than the maximum limit of 32, the value defaults to 32.

7. To promote backups of SIMPLE recovery model databases to level full, select Promote to full backup.

SIMPLE recovery model databases do not support transaction log backups. The Microsoft application agent enables this option if you select the SIMPLE recovery model databases to back up, and transaction log as the backup type.

8. To omit SIMPLE recovery model databases from the backup, select Skip backup.

Microsoft application agent enables this option if you select the SIMPLE recovery model databases to back up, and transaction log as the backup type. SIMPLE recovery model databases do not support transaction log backups.

9. To check the status of the selected databases and ignore the databases that are unready or unavailable for the backup, select Skip databases that cannot be backed up at its current state.

Performing Self-Service Backups of Microsoft SQL Databases 61

If the status of the databases is ONLINE, the databases are ready or available for backups.

If the status of the databases is OFFLINE, EMERGENCY (SINGLE_USER Mode), SUSPECT, RESTORING, RECOVERING, or RECOVERY_PENDING, the databases are unready or unavailable for backups.

10. To generate detailed logs, which you can use to troubleshoot backup issues, specify a number between 1 and 9 in the Select a debug level field. The default value is 0 (zero).

11. To delete debug logs older than a certain number of days, in the Delete debug logs after field, specify the number of days with a number between 1 and 32767 days. The default value is 0 and does not delete any debug logs.

Regularly deleting debug logs prevents the log folder on the installation drive from becoming too large.

NOTE: This option only deletes debug logs named in the default format and located in the logs folder at

\MSAPPAGENT\logs.

12. To specify backup promotion options, select one of the following values from the Backup Promotion list: ALL: Enables backup promotion to occur in any applicable scenario. NONE: Disables all backup promotion. NONE_WITH_WARNINGS: Disables backup promotion, but logs a warning when backup promotion would normally

occur. SKIP_RECOVERY_MODEL: Disables database recovery model change detection. Backup promotion as a result of

recovery model change will not occur, but backup promotion in other scenarios will still occur. SKIP_RECOVERY_MODEL_WITH_WARNINGS: Enables database recovery model change detection, but if a recover

model change is discovered, logs a warning instead of promoting the backup. Backup promotion in other scenarios will still occur.

13. To specify advanced backup options, use the Advanced options field to select or type advanced options.

Separate multiple entries with a comma, for example:

BUFFERCOUNT=2, READ_WRITE_FILEGROUPS The following advanced backup options are supported:

BUFFERCOUNT=number_of_IO_buffers: Specifies the total number of IO buffers that can be used during the backup operation.

READ_WRITE_FILEGROUPS: Backs up only the read/write (active) filegroups within the database.

14. To start the backup operation, click Run.

Monitor the backup operation

After a backup operation is run from the Microsoft app agent for Application Direct SSMS plug-in, the Backup > Monitor page displays the backup script and status.

The following figure shows the backup information and status as it appears on the Monitor page.

62 Performing Self-Service Backups of Microsoft SQL Databases

Figure 8. Application Direct - Backup-Monitor page

NOTE: For information about the success or failure of the backup operation, review the log files that are located in the

installation folder. The typical location of the log files is C:\Program Files\DPSAPPS\MSAPPAGENT\logs.

Back up SQL Server with the Application Direct backup command

Use the ddbmsqlsv command to configure backups of Microsoft SQL Server data from a command prompt.

NOTE: In the syntaxes, the options that are enclosed in square brackets, that is, [ and ] are optional.

To perform specific backup-related and restore-related operations, the Microsoft application agent also supports the ddbmadmin.exe command besides the msagentadmin.exe command. However, the ddbmadmin.exe command is deprecated.

Syntax for backups of a stand-alone server

Run the ddbmsqlsv command with the following syntax to back up a stand-alone SQL Server:

ddbmsqlsv -c -l {full | incr | diff} -a "NSR_DFA_SI=TRUE" -a "NSR_DFA_SI_USE_DD=TRUE" -a "NSR_DFA_SI_DD_HOST= " -a "NSR_DFA_SI_DEVICE_PATH= " -a "NSR_DFA_SI_DD_USER= " [ ] " "

where:

-c

Specifies the SQL Server hostname that contains the SQL Server instance that you want to back up.

-l {full | incr | diff}

Specifies the type of the backup to perform such as full (full), transaction log (incr), or differential (diff). The default value is full.

Performing Self-Service Backups of Microsoft SQL Databases 63

-a "NSR_DFA_SI=TRUE"

Specifies that the backup is server-independent.

-a "NSR_DFA_SI_USE_DD=TRUE

Specifies that the backup destination is a Data Domain server.

-a "NSR_DFA_SI_DD_HOST= "

Specifies whether the backup destination is a Data Domain server.

-a "NSR_DFA_SI_DEVICE_PATH= "

Specifies the name and the path of the storage unit where you want to direct the backup.

-a "NSR_DFA_SI_DD_USER= "

Specifies the username of the DD Boost user.

You must register the hostname and the DD Boost username in the lockbox to enable Microsoft application agent to retrieve the password for the registered user.

Specifies the backup path.

Type the backup path in one of the following formats: For a default instance, type the path in one of the following formats:

To back up the entire instance, type MSSQL: To back up specific databases, type [MSSQL:] [[MSSQL:]

[...]]

For example: "MSSQL:database1" "MSSQL:database2" For a named instance, type the path in one of the following formats:

To back up the entire instance, type MSSQL$ : To back up specific databases, type MSSQL$ : [...] For example: "MSSQL$SqlInst1:database1" "MSSQL$SqlInst1:database2"

SQL stand-alone backup command

ddbmsqlsv.exe -c SQLX86.adesc.com -l full -a "NSR_DFA_SI=TRUE" -a "NSR_DFA_SI_USE_DD=TRUE" -a "NSR_DFA_SI_DD_HOST=10.31.196.90" -a "NSR_DFA_SI_DD_USER=ost" -a "NSR_DFA_SI_DEVICE_PATH=/sqlserver" "MSSQL$INST2008:d2"

Syntax for backups of an Always On availability group

Run the ddbmsqlsv command with the following syntax to back up a stand-alone SQL Server:

ddbmsqlsv -c -A -l {full | incr | diff} -a "NSR_DFA_SI=TRUE" -a "NSR_DFA_SI_USE_DD=TRUE" -a "NSR_DFA_SI_DD_HOST= " -a "NSR_DFA_SI_DEVICE_PATH= " -a "NSR_DFA_SI_DD_USER= " [ ] " "

Run the ddbmsqlsv command with the following syntax to back up a clusterless Always On availability group (AAG):

ddbmsqlsv -a "SKIP_CLIENT_RESOLUTION=TRUE" -c _ -l {full | incr | diff} -a "NSR_DFA_SI=TRUE" -a "NSR_DFA_SI_USE_DD=TRUE" -a "NSR_DFA_SI_DD_HOST= " -a "NSR_DFA_SI_DEVICE_PATH= " -a "NSR_DFA_SI_DD_USER= " [ ] " "

NOTE: For a clusterless AAG, you must add the option -a "SKIP_CLIENT_RESOLUTION=TRUE" and specify

_ with the -c option.

where:

-c

Specifies the Windows cluster name that you want to back up.

-c _

64 Performing Self-Service Backups of Microsoft SQL Databases

Specifies the clusterless AAG that you want to back up, for example, -c AAG1_ef770eaf-ebe3-f5be- bdff-3a7243ff1236.

-A

Specifies the fully qualified domain name (FQDN) of the SQL virtual server.

NOTE: The -A option does not apply to clusterless AAG backups.

-l {full | incr | diff}

Specifies the type of the backup to perform such as full (full), transaction log (incr), or differential (diff). The default value is full.

-a "NSR_DFA_SI=TRUE"

Specifies that the backup is server-independent.

-a "NSR_DFA_SI_USE_DD=TRUE

Specifies that the backup destination is a Data Domain server.

-a "NSR_DFA_SI_DD_HOST= "

Specifies the name of the Data Domain server that contains the storage unit where you want to back up the databases.

-a "NSR_DFA_SI_DEVICE_PATH= "

Specifies the name and the path of the storage unit where you want to direct the backup.

-a "NSR_DFA_SI_DD_USER= "

Specifies the username of the DD Boost user.

You must register the hostname and the DD Boost username in the lockbox to enable Microsoft application agent to retrieve the password for the registered user.

-a "SKIP_CLIENT_RESOLUTION=TRUE"

Specifies to skip the client resolution for _ , as specified with the -c option. Skipping the client resolution is required for a clusterless AAG backup.

" "

Specifies the path to the objects that you want to back up.

Type the backup path in one of the following formats: For a default instance, type the path in one of the following formats:

To back up the entire instance, type MSSQL# : To back up specific databases, type [MSSQL# :]

[[MSSQL# :] [...]] For example: For example: "MSSQL#aag1:database1" "MSSQL#aag1:database2"

For a named instance, type the path in one of the following formats: To back up the entire instance, type MSSQL$ # : To back up specific databases, type MSSQL$ # :

[...] For example: "MSSQL$SqlInst1#sql2012-aag3:database1" "MSSQL$SqlInst1#sql2012-aag3:database2"

NOTE: The Microsoft application agent does not support backing up multiple Always On availability groups in the same

operation.

SQL cluster backup command

The following command backs up databases in a SQL cluster environment:

ddbmsqlsv.exe -c SQLcluster1.adesc.com -A SQLcluster1.adesc.com -l full -a "NSR_DFA_SI=TRUE" -a "NSR_DFA_SI_USE_DD=TRUE" -a "NSR_DFA_SI_DD_HOST=10.31.196.90" -a "NSR_DFA_SI_DD_USER=ost" -a "NSR_DFA_SI_DEVICE_PATH=/sqlserver" "MSSQL$SQ12INST4#sql2012- aag3:"

Performing Self-Service Backups of Microsoft SQL Databases 65

Backing up an Always On availability group when all instance names are the same

When the server nodes contain only default instances, or when the SQL Server instance names are all the same in the availability group, type the backup command in the following syntax:

ddbmsqlsv.exe -c SQL2012clus3.brsvlab.local -S 4 -l full -y +30d -a "NSR_DFA_SI=TRUE" -a "NSR_DFA_SI_USE_DD=TRUE" -a "NSR_DFA_SI_DD_HOST=ddve-01" -a "NSR_DFA_SI_DD_USER=sqlboost" -a "NSR_DFA_SI_PATH=/ sqlboost" -a "NSR_DFA_SI_DD_LOCKBOX_PATH=C:\Program Files\DPSAPPS\common\lockbox" -a "NSR_SKIP_NON_BACKUPABLE_STATE_DB=TRUE" "MSSQL$SQ12INST4#sql2012-aag3:"

where:

-c SQL2012clus3.brsvlab.local specifies the cluster name.

"MSSQL$SQ12INST4#sql2012-aag3:" is the backup object name, where all the databases of the sql2012-aag3 Always On availability group are backed up. The backup object name is made of the following components: MSSQL is a mandatory term.

If you use named instances, $SQ12INST4 is the SQL Server instance name.

If you use a default instance, do not specify $ in the backup object.

# indicates a federated backup.

sql2012-aag3 is the AlwaysOn Availability Group name.

Backing up an Always On availability group when there are multiple instance names

When you back up an Always On availability group where the nodes contain SQL Servers with different instance names, the backup command on each instance should reflect the available instance.

For example, consider the following scenario:

There are two different instances: SQ12INST4 resides on Node1. SQ12INST5 resides on Node2

Node1 is the primary replica. Node2 is the secondary replica. The Always On availability group backup preference is set to secondary.

In this scenario, the backup command on SQL2INST4 should specify the backup object as "MSSQL$SQL2INST4#sql2012- aag3:", while backup command on SQL2INST5 should specify the backup object as "MSSQL$SQL2INST5#sql2012- aag3:". After failover of Node1 and Node2, Node1 becomes secondary, and Node2 becomes primary. The backup command is the same after failover.

NOTE: When browsing the backups for a restore operation, the save sets could be in either of the instances (SQL2INST4

or SQL2INST5). The save set depends on the instance that is used for the first backup.

Backing up multiple databases in an Always On availability group

To back up only certain databases in an Always On availability group, use " " " " " "... to specify the database names as the backup objects.

Type the backup command with the following syntax:

ddbmsqlsv.exe -c SQL2012clus3.brsvlab.local -S 4 -l full -y +30d -a "NSR_DFA_SI=TRUE" -a "NSR_DFA_SI_USE_DD=TRUE" -a "NSR_DFA_SI_DD_HOST=ddve-01" -a "NSR_DFA_SI_DD_USER=sqlboost" -a "NSR_DFA_SI_PATH=/ sqlboost" -a "NSR_DFA_SI_DD_LOCKBOX_PATH=C:\Program Files\DPSAPPS\common\lockbox" -a "NSR_SKIP_NON_BACKUPABLE_STATE_DB=TRUE" "MSSQL$SQ12INST4#sql2012- aag3:database1" "MSSQL$SQ12INST4#sql2012-aag3:database3" "MSSQL $SQ12INST4#sql2012-aag3:database8"

where:

MSSQL$SQ12INST4#sql2012-aag3:database1, MSSQL$SQ12INST4#sql2012-aag3:database3, and MSSQL$SQ12INST4#sql2012-aag3:database8 are the backup objects

Only database1, database3, and database8 of the sql2012-aag3 Always On availability group are backed up.

66 Performing Self-Service Backups of Microsoft SQL Databases

Optional parameters for the ddbmsqlsv command

The following list describes the optional parameters that you can use with the ddbmsqlsv command:

-N

Specifies a name for the backup.

-b

Specifies a description for the backup.

-S

Specifies to perform a striped backup using the number of stripes that you specify. If you specify a value greater than a maximum limit of 32, the value defaults to 32.

-a "SKIP_SIMPLE_DATABASE={TRUE | FALSE}"

Specifies whether to ignore backing up SIMPLE recovery model databases. Use this option if you select the SIMPLE recovery model databases for backup, and specify Transaction log as the backup type. The SIMPLE recovery model databases do not support transaction log backups.

The default value is FALSE.

-a "NSR_SKIP_NON_BACKUPABLE_STATE_DB={TRUE | FALSE}"

Specifies whether to check the status of the selected databases and ignore the databases that are unready or unavailable for the backup.

If the status of the databases is ONLINE, the databases are ready or available for backups.

If the status of the databases is OFFLINE, EMERGENCY (SINGLE_USER Mode), SUSPECT, RESTORING, RECOVERING, or RECOVERY_PENDING, the databases are not ready or available for backups.

The default value is TRUE.

-q

Displays ddbmsqlsv messages in the quiet mode, that is, the option displays summary information and error messages only.

-v

Displays ddbmsqlsv messages in the verbose mode, that is, the option provides detailed information about the progress of the backup operation.

-G

Specifies to perform a NO_LOG transaction log backup before backing up the database.

-R

Uses the NO_TRUNCATE option when backing up transaction logs.

-T

Performs a TRUNCATE_ONLY transaction log backup before backing up the database.

-k

Specifies to perform a checksum before backing up the data to the device.

-u

Specifies to perform a checksum before the backup but to proceed with the backup operation even in the case of errors.

-y + {d | w | m | y}

Specifies the period of time after which the backup must expire. For example:

-y +20d

You can specify any positive integer, followed by one of the following units of time: d for day

w for week

m for month

y for year

The maximum possible retention date is 2/7/2106.

Performing Self-Service Backups of Microsoft SQL Databases 67

If you omit this option or specify 0, the default value of 30 days is used.

-h " "

Specifies databases to omit from the backup. You can use this option to specify exact database names or use wildcard characters.

Two wildcard characters are supported: Question mark (?): Matches any single character Asterisk (*): Matches zero to unlimited characters

When you use wildcard characters you must enclose the database name in square brackets, for example, [DB?].

For example, consider the following scenarios:

To exclude only DB_1 and DB_2 from the backup, add -h DB_1 -h DB_2 to the backup command.

To exclude all databases named with the format of DB_x, such as DB_9 and DB_a, add -h [DB_?] to the backup command.

To exclude all databases with names ending in DB, add -h [*DB] to the backup command.

-O {BUFFERCOUNT | READ_WRITE_FILEGROUPS}

Specifies advanced backup options.

You can specify the following advanced backup options: BUFFERCOUNT= : Specifies the total number of IO buffers that can be used

during a backup operation. READ_WRITE_FILEGROUPS: Specifies to back up only the read/write (active) filegroups within the

database.

If you are specifying multiple options, separate each argument with a comma. The syntax is as follows:

-O "Option1, Option2, Option3"

-D

Generates detailed logs that you can use to troubleshoot backup issues. The default value is 0 (zero).

-a "DELETE_DEBUG_LOG_DAYS= "

Specifies to delete debug log files that are older than the specified number of days. The valid range is between 1 and 32767. By default, debug logs are not deleted. Regularly deleting debug logs prevents the log folder on the installation drive from becoming too large.

This parameter only deletes debug logs named in the default format and located in the logs folder at \MSAPPAGENT\logs.

-a "NSR_COPY_ONLY={TRUE | FALSE}"

Performs a copy-only backup from a SQL Server. The copy-only backups do not disturb the SQL Server backup chain and do not affect backup-level promotion and log truncation. The default value is FALSE.

This option applies only if you specify either full or incr as the backup type with the -l parameter.

Copy-only transaction log backups are generally required only to perform online restores.

-a "NSR_ENABLE_FC={TRUE | FALSE}"

Enables or disables backing up the databases to the Data Domain server through Fibre Channel. The default value is FALSE.

-a "NSR_FC_HOSTNAME=

Specifies the hostname of the Fibre Channel. Use this option with the -a "NSR_ENABLE_FC=TRUE" parameter.

-a "NSR_INCLUDE_AAG_DATABASE=None

Specifies to omit Always On availability group databases from an instance-level backup operation.

-a "NSR_DFA_SI_DD_LOCKBOX_PATH= "

Specifies the folder that contains the lockbox file, which contains encrypted information about the registered hosts and the corresponding usernames in pairs. Each pair is associated with a password that backups use.

If you do not specify a value, the path defaults to lockbox path in the default installation path, which is C:\Program Files\DPSAPPS\common\lockbox.

68 Performing Self-Service Backups of Microsoft SQL Databases

-a "SKIP_SYSTEM_DATABASE={TRUE | FALSE}"

Specifies whether to skip the system databases during instance-level transaction log backups. The default value is FALSE.

-a "BACKUP_PROMOTION={ALL | NONE | NONE_WITH_WARNINGS | SKIP_RECOVERY_MODEL | SKIP_RECOVERY_MODEL_WITH_WARNINGS}"

Specifies backup promotion options. The following values are valid: ALL (Default): Enables backup promotion to occur in any applicable scenario.

NONE: Disables all backup promotion.

NONE_WITH_WARNINGS: Disables backup promotion, but logs a warning when backup promotion would normally occur.

SKIP_RECOVERY_MODEL: Disables database recovery model change detection. Backup promotion as a result of recovery model change will not occur, but backup promotion in other scenarios will still occur.

SKIP_RECOVERY_MODEL_WITH_WARNINGS: Enables database recovery model change detection, but if a recover model change is discovered, logs a warning instead of promoting the backup. Backup promotion in other scenarios will still occur.

The setting specified with this option applies to every database in the SQL instance when this setting is specified along with an SQL instance level backup path.

Similarly, the setting specified with this option applies only to specified databases when this setting is specified with a database level backup path.

Perform backups with T-SQL scripts

The Microsoft application agent for Application Direct enables you to generate a SQL-CLR script that you can use to back up SQL Server.

The SQL-CLR backup command, emc_run_backup, uses the Microsoft application agent for Application Direct with SQL Server backup command prompt options. You must also provide VARCHAR parameters to this command.

To use this command, you must have a detailed knowledge of the Microsoft application agent for Application Direct with SQL Server backup command prompt options. Back up SQL Server with the Application Direct backup command on page 63 provides information about the Microsoft application agent for Application Direct with SQL Server backup command prompt options.

Sample T-SQL backup script

The following SQL-CLR script is an example of a backup script:

USE [master] GO DECLARE @returnCode int 8 -l full -N "Set1" -y +0d -a "NSR_DFA_SI=TRUE" -a "NSR_DFA_SI_USE_DD=TRUE" -a "NSR_DFA_SI_DD_HOST=10.31.192.10" -a "NSR_DFA_SI_DD_USER=ost" -a "NSR_DFA_SI_DEVICE_PATH=/ddsub7" -a "NSR_SKIP_NON_BACKUPABLE_STATE_DB=TRUE" "MSSQL:"' IF @returnCode <> 0 BEGIN RAISERROR ('Fail!', 16, 1) END

You can use any SQL Server standard interfaces, such as the SSMS Query window and OSQL command line tool, to run the SQL-CLR scripts.

You can use the SQL-CLR script to schedule SQL Server backups by using the SQL Server Agent. Scheduling SQL Server backups by using SQL Server Agent on page 51 provides information.

Best practices to back up SQL Server with Application Direct on page 45 provides guidelines for better performance of SQL Server backups by using the Microsoft application agent for Application Direct SQL-CLR scripts.

Performing Self-Service Backups of Microsoft SQL Databases 69

Performing SQL Server push backups by using the Microsoft application agent for Application Direct T-SQL scripts

About this task

To back up either all databases or only the specific databases of a SQL Server instance from a source host to a Data Domain device by using a different host, perform the following steps:

Steps

1. Start SSMS on a different host that you use to perform the backup.

2. Connect to the source SQL Server instance on the source host.

3. In the SSMS window on the different host, click New Query.

4. In the New Query window, run the T-SQL script to perform the backup.

Results

You can either generate the T-SQL script by using the Microsoft application agent GUI on the source host and copy it to the New Query window on the different host or write the T-SQL script in the New Query window. Perform backups with the Microsoft app agent for Application Direct SSMS plug-in on page 57 and Perform backups with T-SQL scripts on page 69 provide information.

T-SQL push backup script

USE [master] GO DECLARE @returnCode int EXEC @returnCode = dbo.emc_run_backup ' -c CLUST-SQL-02.contoso.com -A CLUST-SQL-02.contoso.com -l full -y +0d -a "NSR_DFA_SI=TRUE" -a "NSR_DFA_SI_USE_DD=TRUE" -a "NSR_DFA_SI_DD_HOST=nmmddtwo.sp2010.com" - a "NSR_DFA_SI_DD_USER=ost" -a "NSR_DFA_SI_DEVICE_PATH=/ddbmav2b75" -a "NSR_SKIP_NON_BACKUPABLE_STATE_DB=TRUE" "MSSQL$Inst1:Clus-SQL-01-DB01" "MSSQL$Inst1:CLUST-SQL-02-DB01" "MSSQL$Inst1:CLUST-SQL-02-DB02" "MSSQL $Inst1:CLUST-SQL-02-DB03"' IF @returnCode <> 0 BEGIN RAISERROR ('Fail!', 16, 1) END

where:

CLUST-SQL-02.contoso.com is the source host that has the source SQL Server instance that you want to back up.

Inst1 is the source SQL Server instance, to which you must connect from the different host to perform the backup.

Clus-SQL-01-DB01, CLUST-SQL-02-DB01, CLUST-SQL-02-DB02, and CLUST-SQL-02-DB03 are the databases that you have selected in the Inst1 instance to back up.

Performing federated backups of SQL Always On availability group databases by using T-SQL scripts

To perform federated backups, use the same backup command and options that Perform backups with T-SQL scripts on page 69 describes, but with the following modifications:

Specify the Windows cluster name for in -c .

Specify the backup object name for in -N .

Specify # in the backup object name.

The options in the following sample scripts indicate these modifications.

NOTE: The Microsoft application agent does not support backing up multiple Always On availability groups in the same

operation.

Sample T-SQL script to back up an entire Always On availability group

USE [master] GO

70 Performing Self-Service Backups of Microsoft SQL Databases

DECLARE @returnCode int EXEC @returnCode = dbo.emc_run_backup '-c SQL2012clus3.brsvlab.local - S 4 -l full -y +30d -a "NSR_DFA_SI=TRUE" -a "NSR_DFA_SI_USE_DD=TRUE" - a "NSR_DFA_SI_DD_HOST=ddve-01" -a "NSR_DFA_SI_DD_USER=sqlboost" -a "NSR_DFA_SI_PATH=/sqlboost" -a "NSR_DFA_SI_DD_LOCKBOX_PATH=C:\Program Files\DPSAPPS\common\lockbox" -a "NSR_SKIP_NON_BACKUPABLE_STATE_DB=TRUE" -N "MSSQL$SQ12INST4#sql2012- aag3:" "MSSQL$SQ12INST4#sql2012-aag3:"' IF @returnCode <> 0 BEGIN RAISERROR ('Fail!', 16, 1) END

Sample T-SQL script to back up multiple databases (a subset of databases) of an Always On availability group

USE [master] GO DECLARE @returnCode int EXEC @returnCode = dbo.emc_run_backup '-c SQL2012clus3.brsvlab.local - S 4 -l full -y +30d -a "NSR_DFA_SI=TRUE" -a "NSR_DFA_SI_USE_DD=TRUE" - a "NSR_DFA_SI_DD_HOST=ddve-01" -a "NSR_DFA_SI_DD_USER=sqlboost" -a "NSR_DFA_SI_PATH=/sqlboost" -a "NSR_DFA_SI_DD_LOCKBOX_PATH=C:\Program Files\DPSAPPS\common\lockbox" -a "NSR_SKIP_NON_BACKUPABLE_STATE_DB=TRUE" -N "MSSQL$SQ12INST4#sql2012- aag3:DB" "MSSQL$SQ12INST4#sql2012-aag3:database1" "MSSQL $SQ12INST4#sql2012-aag3:database3" "MSSQL$SQ12INST4#sql2012- aag3:database8"' IF @returnCode <> 0 BEGIN RAISERROR ('Fail!', 16, 1) END

Sample T-SQL script to back up a specific database of an Always On availability group

USE [master] GO DECLARE @returnCode int EXEC @returnCode = dbo.emc_run_backup '-c SQL2012clus3.brsvlab.local - S 4 -l full -y +30d -a "NSR_DFA_SI=TRUE" -a "NSR_DFA_SI_USE_DD=TRUE" - a "NSR_DFA_SI_DD_HOST=ddve-01" -a "NSR_DFA_SI_DD_USER=sqlboost" -a "NSR_DFA_SI_PATH=/sqlboost" -a "NSR_DFA_SI_DD_LOCKBOX_PATH=C:\Program Files\DPSAPPS\common\lockbox" -a "NSR_SKIP_NON_BACKUPABLE_STATE_DB=TRUE" -N "MSSQL$SQ12INST4#sql2012- aag3:database1" "MSSQL$SQ12INST4#sql2012-aag3:database1"' IF @returnCode <> 0 BEGIN RAISERROR ('Fail!', 16, 1) END

Performing Self-Service Backups of Microsoft SQL Databases 71

Performing Self-Service Restores of Microsoft SQL Databases

Topics:

Restoring a SQL application host Best practices to restore SQL Server with Application Direct Restoring SQL Server databases Performing table-level recovery Performing SQL Server disaster recovery

Restoring a SQL application host You can use the Microsoft application agent to restore the database or table-level backups directly to the SQL application host.

The following topics provide instructions on how to restore an application-aware SQL Server backup.

Best practices to restore SQL Server with Application Direct Consider the best practices to restore SQL Server using Application Direct.

Configure connection settings

Data Domain Boost devices do not distinguish among Transmission Control Protocol (TCP)/Internet Protocol (IP), Fibre Channel (FC), and LAN, WAN, and MAN network types. Data Domain Boost devices can successfully operate where packet loss is strictly 0% and latency is less than 20 ms.

Enable instant file initialization

For better restores, enable the instant file initialization feature on SQL Server. SQL Server's initialization procedure writes zeros to the portion of the disk that contains the data and the log files. Enabling the instant file initialization feature does not enable zeroing of the disk for the data files.

Configure usage limits for Data Domain streams

Configure a sufficient number of Data Domain streams for better performance of backups and restores. The streams control backup and restore parallelism for each database.

The Microsoft application agent requires one stream per save set that you back up or restore. When you perform striped backups, each stripe requires one stream. The stripes are concurrently executed for each database. Databases are sequentially backed up and restored. When you use stripes, the number of streams must be equal to or more than the number of stripes.

The minimum number of streams for a non-stripe environment is 1.

Configuring usage limits of Data Domain streams on page 48 provides more information about streams limit, impact of exceeding the limits, and configuring the usage limits.

5

72 Performing Self-Service Restores of Microsoft SQL Databases

Restoring SQL Server databases The Microsoft application agent for Application Direct with SQL Server supports multiple tools to recover databases.

You can recover databases using the Microsoft application agent SQL Server Management Studio plug-in (GUI), Microsoft application agent for Application Direct commands, or T-SQL scripts.

Prerequisites

Learn about prerequisites for database restores.

Prerequisites for restoring a database to a remote server

Learn how to restore a database to a remote SQL Server instance.

The Microsoft application agent supports browsing and restoring backups to a remote server.

Restore operations to a remote server are supported through the Application Direct SQL Server Management Studio (SSMS) plug-in only.

The Microsoft application agent and Application Direct SSMS plug-in must be installed on the SQL Server where you will configure the restore operation. Using the SSMS plug-in, you can browse SQL backups from any server on the Data Domain storage unit and set the target destination to the remote server where you want to restore the data.

Prerequisites

Ensure that your environment meets the following requirements:

The Microsoft application agent must be installed on the target remote instance. Configure the same lockbox on the target remote instance as the lockbox on the instance where you configure the restore

operation.

For example, if the instance where you are running the restore has a lockbox with DataDomain1 and StorageUnit1, you must create the same lockbox with DataDomain1 and StorageUnit1 on the target remote instance.

The SQL Server Agent must be running on the target remote instance.

If the remote instance is on the same domain as the server where you are configuring the restore operation, the Microsoft application agent starts the SQL Server Agent automatically during the restore operation. If the remote server is on a different domain, then you must launch the SQL Server Agent on the target server manually.

You should be able to connect with the remote instance using the Connect to Server option in the SQL Server Management Studio.

If you cannot connect to the remote instance, the restore will fail.

Prerequisite to restore a database in an Always On availability group

To restore a database that is part of an Always On availability group, before you start the restore operation, you must remove the database from the Always On availability group.

Re-add the database to the availability group after the restore operation is complete.

Performing Self-Service Restores of Microsoft SQL Databases 73

Restore a database with the Microsoft app agent for Application Direct plug-in

The Microsoft application agent supports a user interface to perform restore operations through a SQL Server Management Studio (SSMS) plug-in.

About this task

The Microsoft application agent caches SQL Server restore settings. The Microsoft application agent automatically loads the information from the last recovery operation and populates restore settings. Caching saves time by eliminating the need to reselect the settings each time you run a restore operation. To clear the cached settings, click Clear Cache.

The Script option is available in each page of the Database Restore tab, which generates a command prompt equivalent script. You can use the script to create a .bat file to perform automation and other tasks. The following script options are available:

CLI Scripts: To generate the command script, which you can use to run a restore from the command prompt. T-SQL Scripts: To generate a restore script in the T-SQL format.

Launching the Microsoft app agent for Application Direct SSMS plug-in

To launch the Microsoft app agent for Application Direct SSMS plug-in, perform the following steps: 1. From the Windows Start menu, select Microsoft SQL Server Management Studio. 2. In the Microsoft SQL Server Management Studio, in the Connect to Server window, specify the server information and

credentials, and then click Connect. 3. On the toolbar, click Microsoft App Agent (Application Direct).

NOTE: If the Microsoft App Agent (Application Direct) button is not on the toolbar, the plug-in may be unable to

register. In this scenario, you can launch the plug-in directly from the Start menu. From the Windows Start menu, select

DDBMA Plugin for SQL Server Management Studio.

Configure general restore settings

To configure SQL restores with the Microsoft app agent for Application Direct SSMS plug-in, you must first specify general restore options on the Database Restore > General page.

Steps

1. Open the Microsoft app agent for Application Direct window to the Database Restore > General page.

The General page appears as shown in the following figure.

74 Performing Self-Service Restores of Microsoft SQL Databases

Figure 9. Application Direct - Database Restore-General page

The Microsoft application agent automatically loads the information from the last recovery operation and populates all of the fields under Sources. To clear the cached settings, click Clear Cache.

2. In the Data Domain Server field, select the Data Domain server that contains the backup.

NOTE: When you want to restore a replicated backup from a secondary Data Domain server, select the secondary

server. The Data Domain user on the secondary Data Domain server must be in the same group as the primary server.

3. From the SQL Server host list, select the SQL Server host that is the source of the backup. Select the Windows cluster name in the case of federated backups.

4. From the SQL Server Instance list, select the SQL Server instance that contains the databases that you want to restore.

The databases located on the SQL Server instance appear in the database table below the SQL Server Instance field.

5. From the database table, select either the entire SQL Server instance by selecting the check box in the header row or select only the required databases to restore.

The save sets on the SQL Server instance that are available for recovery appear in the save sets table below the Browse time field.

By default, the restore process selects the most recent backup timestamp for each selected database. If you have selected a single database, you can perform a point-in-time (PIT) restore.

6. (Optional) To perform a point-in-time restore of a single database, perform the following steps:

a. From the Browse time list, select a date and time, and click Show Versions.

All of the backups that were performed within the specified timeline appear in the save sets table.

b. From the save sets table, select the save set with the timestamp that you want to restore.

Click < < Older or Newer > > to browse additional save sets.

Performing Self-Service Restores of Microsoft SQL Databases 75

NOTE: You cannot perform a PIT restore of multiple databases.

7. (Optional) To select the backup timestamp, perform the following steps

By default, the most recent timestamp is used.

a. Click Timeline. The Backup Timeline dialog box appears, as shown in the following figure.

Figure 10. Specifying the restore point b. Select the backup timestamp, and then click OK.

8. Choose the location where the backup is restored to: To perform a recovery directly to the database, perform the following steps:

a. Select Restore to SQL Server. b. From the Instance list, select the instance where you want to restore the database.

You can restore the database to the source instance or an alternate instance.

If you are restoring the database to an instance on a remote SQL Server that is not listed, perform the following steps:

i. From the Instance list, select Browse for more.

The Connect to Server window appears.

ii. In the Connect to Server window, beside Server Name, click to load the SQL Instances on the network. iii. In the Server Name field, type the instance name or select the name from the list. iv. From the Authentication list, select one of the following authentication modes to connect to the SQL Server:

Select Windows Authentication when you are restoring to a host in the same domain. Select SQL Server Authentication when you are restoring to a host in a different domain

v. Type the login credentials in the Login and Password fields. vi. To access the SQL Agent Service with a proxy user, under Proxy User Of SQL Agent, type the login credentials

in the User Name and Password fields. You must type the username in the format of Domain Name\User Name.

Specify the proxy user credentials if the SQL Agent service user does not have sufficient permissions to perform a restore operation or if you want to perform restore as a different user.

If the SQL Agent Service on the target server uses a Windows Domain account and you select Windows Authentication, the proxy user credentials are optional.

vii. Click OK. c. From the Database list, select the database where the backup will be recovered to.

To perform a flat-file recovery, perform the following steps: a. Select Restore backups as files. b. In the Folder field, specify the destination for the files. The Folder field is populated with the default destination

path.

9. To start the restore operation, click Run.

76 Performing Self-Service Restores of Microsoft SQL Databases

Configure files and filegroup restore settings

When you configure SQL restores with the Microsoft app agent for Application Direct SSMS plug-in, you change the default destination folders of the database files (.mdf and .ndf) and log files (.ldf) on the Database Restore > Files/Filegroups page.

About this task

All settings on the Files/Filegroups page are optional. NOTE: Settings on the Files/Filegroups page are disabled when you configure a restore to a remote server, including

changing the restore path.

Steps

1. From the left panel, click Files/Filegroups.

The Files/Filegroups page appears as shown in the following figure.

Figure 11. Application Direct - Database Restore-Files page

2. Under Filegroup Options, to display the corresponding database files of the databases that you have selected on the General page, from the Select Filegroup to restore list, select one of the following options: All Files: Displays the database files of all the selected databases.

PRIMARY: Displays the database files of the selected databases that belong to the PRIMARY filegroup only.

Custom filegroup name: Displays the database files of the selected databases that belong to the selected custom filegroup name only. In the figure, the custom filegroup names are sec and third.

3. Under Relocation Options, to change the destination restore paths, perform one of the following action sequences: To change the destination paths of all of the data and log files, perform the following steps:

a. Select Relocate all files to folder. b. Click the Data file folder browse button to specify a location for the data files, or click inside the field to type the

folder path.

Performing Self-Service Restores of Microsoft SQL Databases 77

c. Click the Log file folder browse button to specify a location for the log files, or click inside the field to type the folder path.

To change the destination path for each individual data or log file, perform one of the following actions in the files table: To browse for a destination path, click the browse button to the right of each data or log file. A window appears

where you can browse and select the file path. To type a new destination path, in the Restore As column, click the appropriate cell and type a destination path. If

the path does not exist, a dialog box appears asking if you want to create the folder.

NOTE: The settings under Relocation Options are disabled if you have selected the Restore backups as files option

on the General page.

4. To start the restore operation, click Run.

Configure optional database restore settings

When you configure SQL database restores with the Microsoft app agent for Application Direct SSMS plug-in, you can specify optional restore settings on the Database Restore > Options page.

About this task

All settings on the Options page are optional.

Steps

1. From the left panel, click Options to specify optional restore settings.

The Options page appears as shown in the following figure.

Figure 12. Application Direct - Database Restore-Options page

2. To overwrite the existing database with the restored data, select Overwrite the existing database (WITH REPLACE). The database is overwritten only if the database name is unchanged.

78 Performing Self-Service Restores of Microsoft SQL Databases

If you select the Restore backups as files option, this option is disabled.

3. To specify a recovery state, select one of the following options in the Recovery state field: RESTORE WITH RECOVERY: To leave the database in the ready-to-use state by rolling back uncommitted

transactions, and disable the ability to restore the most recent or additional transaction logs. RESTORE WITH NORECOVERY: To leave the database in the non-operational state by not rolling back uncommitted

transactions, and enable the ability to restore the most recent or additional transaction logs. RESTORE WITH STANDBY: To enable the ability to undo committed transactions, save the undo actions in a standby

file that enables you to reverse the restore effects, and put the database in the read-only mode. If you select this option, specify the Standby file field by clicking the button that is located on the right of the field, browsing for the file, and then selecting it.

4. To perform a verify only operation, select Verify only.

A verify only operation verifies that the restore process meets the following requirements without performing the restore operation: The backup set that you want to restore is complete and all volumes are readable Header fields, such as database page IDs are ready to write data Whether the checksum is proper if the backup was performed by selecting the Perform checksum before writing to

media option Whether the destination host has sufficient space to restore data

NOTE:

The Verify only option requires sufficient space on the client host to operate.

If you select the Restore backups as files option, this option is disabled.

5. To compress the restore contents and transport them from the Data Domain Replicator to the application host, select Data Domain Boost compressed restore.

This option reduces the impact on network bandwidth.

6. To generate detailed logs, which you can use to troubleshoot any restore issues, specify a number between 1 and 9 in the Select a debug level field. The default value is 0 (zero).

7. To delete debug logs older than a certain number of days, in the Delete debug logs after field, specify the number of days with a number between 1 and 32767 days. The default value is 0 and does not delete any debug logs.

Regularly deleting debug logs prevents the log folder on the installation drive from becoming too large.

NOTE: This option only deletes debug logs named in the default format and located in the logs folder at

\MSAPPAGENT\logs.

8. To restore all the backups that were performed during or after a specified start time and up until the time of the backup that is being restored, select Specify a start time for restore.

Specify the start date and time in the corresponding fields beside the Specify a start time for restore field. The start date and time must be before the backup time of the save set that is being restored.

9. To specify advanced recovery options, use the Advanced options field to select or type advanced recovery options.

Separate multiple entries with a comma, for example:

BUFFERCOUNT=2, KEEP_CDC, KEEP_REPLICATION The following advanced recovery options are supported:

BUFFERCOUNT=buffer_number: Specifies the total number of IO buffers that can be used during recovery.

KEEP_CDC: Enables change data capture (CDC) recovery.

When restoring a database with CDC enabled, the recover operation works differently depending on the recovery destination.

Use the KEEP_CDC option to:

Recover the CDC enabled database on the same SQL instance by overwriting an existing database.

In this scenario, KEEP_CDC is optional.

Recover the CDC enabled database with a different name on the same SQL instance.

In this scenario, KEEP_CDC is required.

Recover the CDC enabled database on a different SQL server instance.

Performing Self-Service Restores of Microsoft SQL Databases 79

In this scenario, KEEP_CDC is required.

KEEP_REPLICATION: Preserves the replication when recovering a published database. This option is required if a database was replicated when the backup was created.

10. To edit the number of save sets or versions that the Microsoft application agent cache retrieves, specify a number in the Number of entries to retrieve field.

You can specify a value of 1 through 10000. The default value is 50.

11. To perform a tail-log backup of the data before performing a restore operation, select Take tail-log backup before restore.

A tail-log backup ensures that the Microsoft application agent backs up the data that has changed since the previous backup.

NOTE:

The Microsoft application agent for Application Direct does not support tail-log backups of multiple databases.

This option is disabled if the Restore backups as files or Verify only options are selected.

12. To ensure exclusive access to the database during the restore operation if multiple connections exist, select Close existing connections to destination database.

13. To start the restore operation, click Run.

Monitor the restore operation

After a database restore operation is run from the Microsoft app agent for Application Direct SSMS plug-in, the Database Restore > Monitor page displays the restore script and status.

The following figure shows the restore information and status as it appears on the Monitor page.

Figure 13. Application Direct - Database Restore-Monitor page

NOTE: For information about the success or failure of the restore operation, review the log files that are located in the

installation folder. The typical location of the log files is C:\Program Files\DPSAPPS\MSAPPAGENT\logs.

80 Performing Self-Service Restores of Microsoft SQL Databases

Perform database restores with the Microsoft application agent for Application Direct recover command

Use the ddbmsqlrc command to configure a restore of Microsoft SQL Server databases from a command prompt.

NOTE: In the syntaxes, the options that are enclosed in square brackets, that is, [ and ] are optional.

To perform specific backup-related and restore-related operations, the Microsoft application agent also supports the ddbmadmin.exe command besides the msagentadmin.exe command. However, the ddbmadmin.exe command is deprecated.

Syntax to restore databases on a stand-alone server

Run the following command to restore databases in a stand-alone environment:

ddbmsqlrc.exe -c -S {normal | norecover | standby: \undo.ldf} -a "NSR_DFA_SI=TRUE" -a "NSR_DFA_SI_USE_DD=TRUE" -a "NSR_DFA_SI_DD_HOST= " -a "NSR_DFA_SI_DD_USER= " -a "NSR_DFA_SI_DEVICE_PATH= " [ ] " "

where:

-c

Specifies the SQL Server hostname, to which you want to restore the required databases. A SQL Server host contains the backed-up stand-alone or clustered SQL Server instances and the corresponding databases.

-S {normal | norecover | standby: \undo.ldf}

Performs one of the following tasks: normal: Enables you to roll back uncommitted transactions and use the database to restore the

most recent or additional transaction logs. norecover: Disables your ability to roll back uncommitted transactions and use the database to

restore the most recent or additional transaction logs. standby: \undo.ldf: Enables you to undo committed transactions, saves the undo actions

in a standby file that enables you to reverse the restore effects, and puts the database in the read-only mode.

-a "NSR_DFA_SI=TRUE"

Specifies that the restore is server-independent.

-a "NSR_DFA_SI_USE_DD=TRUE"

Specifies that the backup is located on a Data Domain server.

-a "NSR_DFA_SI_DD_HOST= "

Specifies the name of the Data Domain server that contains the backup.

When you have a remote (secondary) Data Domain server that has replicated databases to restore, type the name of the secondary server. A Data Domain user on the secondary Data Domain server must be in the same group as the primary server.

-a "NSR_DFA_SI_DD_USER= "

Specifies the username of the DD Boost user.

You must register the hostname and the DD Boost username in the lockbox to enable Microsoft application agent to retrieve the password for the registered user.

-a "NSR_DFA_SI_DEVICE_PATH= "

Specifies the name and the path of the storage unit that contains the backup.

" "

Specifies the path to the backup objects that you want to restore.

Type the restore path in one of the following formats: For a default instance, type the path in one of the following formats:

To restore backups of the entire instance, type MSSQL:

Performing Self-Service Restores of Microsoft SQL Databases 81

To restore backups of specific databases, type [MSSQL:] [[MSSQL:] [...]]

For example: "MSSQL:database1" "MSSQL:database2" For a named instance, type the path in one of the following formats:

To restore backups of the entire instance, type MSSQL$ : To restore backups of specific databases, type MSSQL$ :

[...] For example: "MSSQL$SqlInst1:database1" "MSSQL$SqlInst1:database2"

SQL stand-alone restore command

ddbmsqlrc.exe -c sqlx86.adesc.com -t "Monday, November 11, 2013 1:05:47 PM" -S normal -a "NSR_DFA_SI=TRUE" -a "NSR_DFA_SI_USE_DD=TRUE" -a "NSR_DFA_SI_DD_HOST=10.31.77.27" -a "NSR_DFA_SI_DD_USER=arti1" -a "NSR_DFA_SI_DEVICE_PATH=/artrep2" "MSSQL$SQL2K8:testddr1"

Syntax to restore databases in an Always On availability group

Run the following command to restore databases in an Always On availability group environment:

ddbmsqlrc.exe -c -A -S {normal | norecover | standby: \undo.ldf} -a "NSR_DFA_SI=TRUE" -a "NSR_DFA_SI_USE_DD=TRUE" -a "NSR_DFA_SI_DD_HOST= " -a "NSR_DFA_SI_DD_USER= " -a "NSR_DFA_SI_DEVICE_PATH= " [ ] " "

Run the following command to restore databases in a clusterless Always On availability group (AAG) environment:

ddbmsqlrc.exe -a "SKIP_CLIENT_RESOLUTION=TRUE" -c _ -S {normal | norecover | standby: \undo.ldf} -a "NSR_DFA_SI=TRUE" -a "NSR_DFA_SI_USE_DD=TRUE" -a "NSR_DFA_SI_DD_HOST= " -a "NSR_DFA_SI_DD_USER= " -a "NSR_DFA_SI_DEVICE_PATH= " [ ] " "

NOTE: For a clusterless AAG, you must add the option -a "SKIP_CLIENT_RESOLUTION=TRUE" and specify

_ with the -c option.

where:

-c

Specifies the Windows cluster name for the restore.

To perform a redirected restore, specify a SQL Server host that is not the current host.

-c _

Specifies the clusterless AAG for the restore, for example, -c AAG1_ef770eaf-ebe3-f5be- bdff-3a7243ff1236.

-A

Specifies the SQL virtual server FQDN to restore the databases from the SQL clustered instance.

NOTE: The -A option does not apply to clusterless AAG restores.

-S {normal | norecover | standby: \undo.ldf}

Performs one of the following tasks: normal: Enables you to roll back uncommitted transactions and use the database to restore the

most recent or additional transaction logs. norecover: Disables your ability to roll back uncommitted transactions and use the database to

restore the most recent or additional transaction logs. standby: \undo.ldf: Enables you to undo committed transactions, saves the undo actions

in a standby file that enables you to reverse the restore effects, and puts the database in the read-only mode.

82 Performing Self-Service Restores of Microsoft SQL Databases

-a "NSR_DFA_SI=TRUE"

Specifies that the restore is server-independent.

-a "NSR_DFA_SI_USE_DD=TRUE"

Specifies that the backup is located on a Data Domain server.

-a "NSR_DFA_SI_DD_HOST= "

Specifies the name of the Data Domain server that contains the backup.

When you have a remote (secondary) Data Domain server that has replicated databases to restore, type the name of the secondary server. A Data Domain user on the secondary Data Domain server must be in the same group as the primary server.

-a "NSR_DFA_SI_DD_USER= "

Specifies the username of the DD Boost user.

You must register the hostname and the DD Boost username in the lockbox to enable Microsoft application agent to retrieve the password for the registered user.

-a "NSR_DFA_SI_DEVICE_PATH= "

Specifies the name and the path of the storage unit that contains the backup.

-a "SKIP_CLIENT_RESOLUTION=TRUE"

Specifies to skip the client resolution for _ , as specified with the -c option. Skipping the client resolution is required for a clusterless AAG restore.

" "

Specifies the path to the backup objects that you want to restore.

Type the backup path in one of the following formats: For a default instance, type the path in one of the following formats:

To restore backups of the entire instance, type MSSQL# : To restore backups of specific databases, type [MSSQL# :]

[[MSSQL# :] [...]] For example: "MSSQL#aag1:database1" "MSSQL#aag1:database2"

For a named instance, type the path in one of the following formats: To restore backups of the entire instance, type MSSQL$ # : To restore backups of specific databases, type

MSSQL$ # : [...] For example: "MSSQL$SqlInst1#sql2012-aag3:database1" "MSSQL$SqlInst1#sql2012-aag3:database2"

SQL Always On availability group restore command

ddbmsqlrc.exe -c sqlcluster1.adesc.com -A sqlcluster1.adesc.com -t "Monday, November 11, 2013 1:05:47 PM" -S normal -a "NSR_DFA_SI=TRUE" -a "NSR_DFA_SI_USE_DD=TRUE" -a "NSR_DFA_SI_DD_HOST=10.31.77.27" -a "NSR_DFA_SI_DD_USER=arti1" -a "NSR_DFA_SI_DEVICE_PATH=/artrep2" "MSSQL$SqlInst1#sql2012-aag3:database2"

SQL clusterless Always On availability group restore command

ddbmsqlrc.exe -a "SKIP_CLIENT_RESOLUTION=TRUE" -c clusterlessa_6f27c29c-5d83-0062-8301-357210660ac6 -S normal -a "NSR_DFA_SI_DD_HOST=10.31.140.154" -a "NSR_DFA_SI_DD_USER=ost" -a "NSR_DFA_SI_DEVICE_PATH=/aru" -a "NSR_DFA_SI_DD_LOCKBOX_PATH=C: \Program Files\DPSAPPS\common\lockbox" -C "'ClusterlessADB01'='H: \MSSQL14.MSSQLSERVER\MSSQL\DATA\ClusterlessADB01.mdf', 'ClusterlessADB01_log'='H: \MSSQL14.MSSQLSERVER\MSSQL\DATA\ClusterlessADB01_log.ldf'" -f -d MSSQL:ClusterlessADB01 MSSQL:ClusterlessADB01

Optional parameters for the ddbmsqlrc command

You can use the following optional parameters with the ddbmsqlrc command.

-a "NSR_DFA_SI_DD_LOCKBOX_PATH= "

Performing Self-Service Restores of Microsoft SQL Databases 83

Specifies the folder that contains the lockbox file, which contains encrypted information about the registered hosts and the corresponding usernames in pairs. Each pair is associated with a password that backups use. If you do not specify a value, the path defaults to the installation path, which is typically C:\Program Files\DPSAPPS\common\lockbox.

-t " "

Specifies the backup time stamp that you want to restore.

-a "NSR_ENABLE_FC={TRUE | FALSE}"

Enables database restoration to the Data Domain server through Fibre Channel. The default value is FALSE.

-a "NSR_FC_HOSTNAME= "

Specifies the hostname of the Fibre Channel.

-a "FLAT_FILE_RECOVERY={TRUE | FALSE}"

Performs a flat file restore to files, that is, restores a save set that has a full backup and multiple transaction log and differential backups to files.

To know the order in which multiple transaction log backup files are restored, use the save times that are present in the filenames.

The default value is FALSE.

-a "FLAT_FILE_RECOVERY_DIR= "

Specifies the folder, in which the flat file restore files are generated.

You can see the generated files in the \ \ location.

-a "DDBOOST_COMPRESSED_RESTORE={TRUE | FALSE}"

Compresses the restore contents and transports them from the Data Domain Replicator to the application host. This option saves network bandwidth.

The default value is FALSE.

-a "RESTORE_START_TIME= "

Restores all the backups that were performed at or after a specified backup time (start date and time) and up to the backup time of the selected save set, that is, -t .

Specify the start date and time in the /

/
: : {AM | PM} format.

The start date and time must not be after the backup time of the selected save set, that is, -t .

-a "RESTORE_TO_SOURCE_PATH=TRUE"

Ensures that the backup is restored to the original source path by default. The -a "RESTORE_TO_SOURCE_PATH=TRUE" option is overridden if the -C or -H relocation options are used.

-a "CLOSE_SQL_CONNECTIONS={TRUE | FALSE}

Specifies whether to enable exclusive access to the target database by changing the database to single user mode during the restore operation to ensure a successful recovery.

-$

Specifies the target SQL Server instance that contains the target databases that you want to restore. The default value is the instance where the backup was taken.

Type the value in one of the following formats: For the default instance, type MSSQL For a named instance, type MSSQL$

-d

Specifies the target database, to which you want to restore the backup.

Type the value in one of the following formats: MSSQL: MSSQL$ :

-C file=path,file2=path2,...

Relocates the database files (.mdf, .ndf, and .ldf) to a different folder.

84 Performing Self-Service Restores of Microsoft SQL Databases

You cannot use the -C parameter and the -H parameter in the same operation.

-H " ' ';' '"

Relocates file paths and all files contained in a path to a different location during a restore operation.

The -H command option can be used to relocate multiple file paths in the same command line.

The -H option is supported on standard and redirected restores, which includes the following:

Normal restoreSame server and same instance. Different instance restoreSame server and different instance. Restore to different database file. Different server restore.

To relocate individual files, see the table entry for the -C option.

You cannot use the -C option and the -H option in the same operation.

-f

Overwrites the existing database with the current database that you restore if the names of both the databases are same.

-q

Displays ddbmsqlsv messages in the quiet mode, that is, the option displays summary information and error messages only.

-k

Specifies to perform a checksum before restoring the data.

-u

Specifies to perform a checksum before the restore operation but to proceed with the operation even in the case of errors.

-D

Generates detailed logs that you can use to troubleshoot backup issues. The default value is 0 (zero).

-a "DELETE_DEBUG_LOG_DAYS= "

Specifies to delete debug log files that are older than the specified number of days. The valid range is between 1 and 32767. By default, debug logs are not deleted. Regularly deleting debug logs prevents the log folder on the installation drive from becoming too large.

This parameter only deletes debug logs named in the default format and located in the logs folder at \MSAPPAGENT\logs.

-V

Verifies whether the restore process meets the following requirements: The backup set that you want to restore is complete and all volumes are readable Header fields, such as database page IDs, are ready to write data Whether the backup was performed by using the checksum option Whether the destination host has sufficient space to restore data

-l incr

Restores from the last transaction log backup.

When you use the -l incr option, you must restore the backup chain in order. For example, consider the following backup history:

A full backup taken at 1:31:49PM A logs only backup taken at 1:32:42PM A logs only backup taken at 1:34:03PM A logs only backup taken at 1:34:50PM

To restore this backup chain, perform the following steps:

1. Type the following command to restore up to the second last transaction log backup:

ddbmsqlrc.exe -a NSR_DFA_SI_DD_HOST=10.34.156.120 -a NSR_DFA_SI_DD_USER=ost -a NSR_DFA_SI_DEVICE_PATH=/msappstu4 -a NSR_DFA_SI_DD_LOCKBOX_PATH=C:\Program Files\DPSAPPS\common\lockbox -c mars-jupiter.planets.com -a SKIP_CLIENT_RESOLUTION=TRUE -f -t 09/06/2017 01:34:03 PM -S norecover MSSQL$TESTDB02:info3

Performing Self-Service Restores of Microsoft SQL Databases 85

2. Type the following command to restore the last transaction log backup with -l incr:

ddbmsqlrc.exe -a NSR_DFA_SI_DD_HOST=10.34.156.120 -a NSR_DFA_SI_DD_USER=ost -a NSR_DFA_SI_DEVICE_PATH=/msappstu4 -a NSR_DFA_SI_DD_LOCKBOX_PATH=C:\Program Files\DPSAPPS\common\lockbox -c mars-jupiter.planets.com -a SKIP_CLIENT_RESOLUTION=TRUE -f -l incr -S normal MSSQL$TESTDB02:info3

-O " , , ..."

Specifies advanced recovery options. Separate each option with a comma.

The following table describes the advanced recovery options that are available:

BUFFERCOUNT= : Specifies the total number of IO buffers that can be used during a recovery operation.

KEEP_CDC: Enables change data capture (CDC) recovery.

KEEP_REPLICATION: Preserves the replication when you recover a published database. This option is required if a database was replicated when the backup was created.

READ_WRITE_FILEGROUPS: Recovers only the read/write (active) filegroups from the backup. This option can only be used to recover backups performed with the READ_WRITE_FILEGROUPS option. You cannot specify filegroup or file level recover targets with the READ_WRITE_FILEGROUPS option.

If you are relocating multiple filegroups during the restore, you can use the -H option for global relocation.

After you restore a backup with the READ_WRITE_FILEGROUPS option, any read-only filegroups in the database will enter the recovery pending state and the rest of the filegroups will go online.

There are two different scenarios to recover cumulative incremental READ_WRITE_FILEGROUPS backups:

If you have taken a full READ_WRITE_FILEGROUPS backup prior to the cumulative incremental backup, the Microsoft application agent will chain together the two backups in the correct order and complete the restore.

If you have not taken a full READ_WRITE_FILEGROUPS backup but want to use a normal full backup as the differential base for the cumulative incremental READ_WRITE_FILEGROUPS backup, you must first restore the full backup normally and leave the database in no recovery mode, and then apply the READ_WRITE_FILEGROUPS differential backup with the -z option.

-z

Enables implementation of a recovery plan in independent command line operations. Normally the Microsoft application agent builds the recovery plan, ensuring that all the required backups are available and that they run in the proper order and with the proper options. The -z option removes safety checks.

This option is used in more complex recoveries. The following example commands use the following backup history:

savetime 1:00 - full backup savetime 2:00 - txnlog backup savetime 3:00 - txnlog backup

To recover multiple backups and restore a database in a single command, run the following command:

ddbmsqlrc ... -t "savetime 3:00" ...

This command recovers the entire recovery chain, from the first full backup to the last logs-only backup.

To recover this recovery chain and restore a database in a series of independent commands, run the following commands:

ddbmsqlrc ... -z -S norecover -t "savetime 1:00" ...

ddbmsqlrc ... -z -S norecover -t "savetime 2:00" ...

ddbmsqlrc ... -z -S normal -t "savetime 3:00" ...

These three commands recover each backup individually.

86 Performing Self-Service Restores of Microsoft SQL Databases

The -z option disables building the recovery plan and recovers only the specified backup. This command is required for all backups except for level full backups.

Use the -S norecover option with the -z option to prepare the database for more restores. This command is required for all except the last recovery command. The final command recovers the last logs-only backup and brings the database online.

NOTE: A point-in-time restore within the final txnlog can be specified by replacing the save time.

For instance, in the previous example, you can replace 3:00 with 2:45. The database is not available

for general use until after the final recovery completes. Any missing, incorrect, or out-of-order save

times result in SQL Server reporting errors.

Starting with Microsoft application agent 19.2, you can specify the ddmsqlrc command option -S norecover when you restore a read-write (active) filegroup, which leaves the database in the recovering mode. In this mode, you can then apply subsequent transaction logs to complete a point- in-time restore of the active filegroup, for example, when backups are configured as in the following example. The following example commands use the following backup history:

savetime 6/21/2019 12:10:35 PM - full read/write filegroup backup with -O "READ_WRITE_FILEGROUPS" option

savetime 6/21/2019 12:21:30 PM - txnlog backup savetime 6/21/2019 12:25:34 PM - txnlog backup

To perform a point-in-time restore of the active filegroup, run the following commands:

1. Restore the filegroup backup in norecover mode:

ddbmsqlrc ... -z -f -t "06/21/2019 12:10:35 PM" -S norecover -O "READ_WRITE_FILEGROUPS" ...

2. Restore the second last log backup in norecover mode:

ddbmsqlrc ... -z -f -t "06/21/2019 12:21:30 PM" -S norecover ... 3. Complete the point-in-time restore in normal mode to a time before the final log backup:

ddbmsqlrc ... -z -f -t "06/21/2019 12:25:34 PM" -S normal ...

Perform database restores with T-SQL scripts

The Microsoft application agent for Application Direct enables you to generate a SQL-CLR script that you can use to restore SQL Server databases.

The SQL-CLR restore command, emc_run_restore uses the Microsoft application agent for Application Direct with SQL Server restore CLI options. You must also provide VARCHAR parameters to this command. To use this command, you must have a detailed knowledge of the Microsoft application agent for Application Direct with SQL Server restore CLI options. Perform database restores with the Microsoft application agent for Application Direct recover command on page 81 provides information about the Microsoft application agent for Application Direct with SQL Server restore CLI options.

Sample T-SQL restore script

The following SQL-CLR script is an example of a restore script:

USE [master] GO DECLARE @returnCode int EXEC @returnCode = dbo.emc_run_restore ' -c win8sqlsp.sharepoint.com - f -t "02/03/2015 04:04:36 AM" -S normal -a "NSR_DFA_SI=TRUE" -a "NSR_DFA_SI_USE_DD=TRUE" -a "NSR_DFA_SI_DD_HOST=10.31.192.10" -a "NSR_DFA_SI_DD_USER=ost" -a "NSR_DFA_SI_DEVICE_PATH=/ddsub7" -d "MSSQL:dbtest" "MSSQL:db100"' IF @returnCode <> 0 BEGIN RAISERROR ('Fail!', 16, 1) END

Performing Self-Service Restores of Microsoft SQL Databases 87

You can use any SQL Server standard interfaces, such as the SSMS Query window and OSQL command prompt tool, to run the SQL-CLR scripts.

Best practices to back up SQL Server with Application Direct on page 45 provides guidelines for better performance of SQL Server restores by using the Microsoft application agent for Application Direct SQL-CLR scripts.

Performing SQL Server push restores by using the Microsoft application agent for Application Direct T-SQL scripts

About this task

To restore either all databases or only the specific databases of a SQL Server instance from a Data Domain device to a destination host by using a different host, perform the following steps.

NOTE: When the data to be restored exists in a Data Domain Cloud Tier, perform a recall operation prior to the push

restore operation.

Steps

1. Start SSMS on a different host that you use to perform the restore.

2. Connect to the destination SQL Server instance on the destination host.

3. In the SSMS window on the different host, click New Query.

4. In the New Query window, run the T-SQL script to perform the restore.

Results

You can either generate the T-SQL script by using the Microsoft application agent SSMS plug-in GUI on the destination host and copy it to the New Query window on the different host or write the T-SQL script in the New Query window. Restore a database with the Microsoft app agent for Application Direct plug-in on page 74 and Perform database restores with T-SQL scripts on page 87 provide information.

T-SQL push restore script

Consider the following example T-SQL push restore script:

USE [master] GO DECLARE @returnCode int EXEC @returnCode = dbo.emc_run_restore ' -c clust-sql-01.contoso.com - A clust-sql-02.contoso.com -f -t "06/22/2015 02:38:48 PM" -S normal -a "NSR_DFA_SI=TRUE" -a "NSR_DFA_SI_USE_DD=TRUE" -a "NSR_DFA_SI_DD_HOST=nmmddtwo.sp2010.com" -a "NSR_DFA_SI_DD_USER=ost" - a "NSR_DFA_SI_DEVICE_PATH=/ddbmav2b75" -d "MSSQL$Inst1:CLUST-SQL-02- DB02" "MSSQL$Inst1:CLUST-SQL-02-DB01"' IF @returnCode <> 0 BEGIN RAISERROR ('Fail!', 16, 1) END

where:

clust-sql-01.contoso.com is the source host from which the backup was performed.

Inst1 is the destination SQL Server instance, to which you must connect from the different host to perform the restore.

CLUST-SQL-02-DB02 is the destination database.

CLUST-SQL-02-DB01 is the database that was backed up from the source host.

Restoring SQL Always On availability groups by using T-SQL scripts

To restore databases from federated backups, use the same restore command and options that Perform database restores with T-SQL scripts on page 87 describes, but for in -c , specify the Windows cluster name.

The options in the following example scripts indicate the modification.

88 Performing Self-Service Restores of Microsoft SQL Databases

Sample T-SQL script to restore a federated backup to the source database

USE [master] GO DECLARE @returnCode int EXEC @returnCode = dbo.emc_run_restore '-a "NSR_DFA_SI_DD_HOST=ddve-01" -a "NSR_DFA_SI_DD_USER=sqlboost" -a "NSR_DFA_SI_DEVICE_PATH=/sqlboost" -a "NSR_DFA_SI_DD_LOCKBOX_PATH=C: \Program Files\DPSAPPS\common\lockbox" -c sql2012clus3.brsvlab.local - C" 'testdb1_Data'='E:\sql2012_data\ database1.mdf', 'testdb1_Log'='F: \sql2012_log\database1_log.LDF'" -f -S normal -$ "MSSQL$SQ12INST4:" -d "MSSQL$SQ12INST4:database1" "MSSQL$SQ12INST4:database1"' IF @returnCode <> 0 BEGIN RAISERROR ('Fail!', 16, 1) END

Sample T-SQL script to restore a federated backup to a different instance and database (redirected restore)

USE [master] GO DECLARE @returnCode int EXEC @returnCode = dbo.emc_run_restore '-a "NSR_DFA_SI_DD_HOST=ddve-01" -a "NSR_DFA_SI_DD_USER=sqlboost" -a "NSR_DFA_SI_DEVICE_PATH=/sqlboost" -a "NSR_DFA_SI_DD_LOCKBOX_PATH=C: \Program Files\DPSAPPS\common\lockbox" -c sql2012clus3.brsvlab.local - C" 'testdb1_Data'='E:\sql2012_data\database1.mdf', 'testdb1_Log'='F: \sql2012_log\database1_log.LDF'" -f -S normal -$ "MSSQL$SQ12INST4:" -d "MSSQL$SQ12INST5:copy-database1" "MSSQL$SQ12INST4:database1"' IF @returnCode <> 0 BEGIN RAISERROR ('Fail!', 16, 1) END

Sample T-SQL script to restore multiple databases of a federated backup

NOTE: When you restore multiple databases, do not use the -C and -d options with the T-SQL restore command. If you do,

the restore fails.

USE [master] GO DECLARE @returnCode int EXEC @returnCode = dbo.emc_run_restore '-a "NSR_DFA_SI_DD_HOST=ddve-01" -a "NSR_DFA_SI_DD_USER=sqlboost" -a "NSR_DFA_SI_DEVICE_PATH=/sqlboost" -a "NSR_DFA_SI_DD_LOCKBOX_PATH=C: \Program Files\DPSAPPS\common\lockbox" -c sql2012clus3.brsvlab.local - f -S normal -$ "MSSQL$SQ12INST4:database1" "MSSQL $SQ12INST4:database2"' IF @returnCode <> 0 BEGIN RAISERROR ('Fail!', 16, 1) END

Performing table-level recovery The Microsoft application agent supports table-level recovery of SQL Server data using the Microsoft app agent for Application Direct SSMS plug-in.

The Microsoft application agent does not support the Script option to perform table restores using a command prompt or T-SQL scripts.

Performing Self-Service Restores of Microsoft SQL Databases 89

Table-level restore workflow

Prerequisites

When you install the Microsoft application agent, you must install ItemPoint to perform table-level restores. Otherwise, the Table Restore tab does not appear in the Microsoft app agent for Application Direct SSMS plug-in.

About this task

To perform table-level restores, you must first mount the backup images and specify mount options by using the Microsoft app agent for Application Direct SSMS plug-in. Once the backup is mounted, use ItemPoint for Microsoft SQL Server to complete the table restore.

CAUTION: In the scenario where you have large databases where a large amount of data is recently committed

to the databases, a table restore may not be appropriate.

Committed data is represented as outstanding transactions when there are SQL Server full and incremental backups on the database. When you perform table-level recovery, ItemPoint must load all the outstanding transactions that are present in a backup.

To load large databases, ItemPoint requires a few hours to read the data because of poor Virtual File System performance. This problem occurs while ItemPoint loads the database and ItemPoint may appear to stop responding while reading the backup. If you observe this problem, an alternative to using SQL table-level recovery is to restore the backup as flat files to a server where you have enough space, and then use ItemPoint to restore the files on the local disk.

Launching the Microsoft app agent for Application Direct SSMS plug-in

To launch the Microsoft app agent for Application Direct SSMS plug-in, perform the following steps: 1. From the Windows Start menu, select Microsoft SQL Server Management Studio. 2. In the Microsoft SQL Server Management Studio, in the Connect to Server window, specify the server information and

credentials, and then click Connect. 3. On the toolbar, click Microsoft App Agent (Application Direct).

NOTE: If the Microsoft App Agent (Application Direct) button is not on the toolbar, the plug-in may be unable to

register. In this scenario, you can launch the plug-in directly from the Start menu. From the Windows Start menu, select

DDBMA Plugin for SQL Server Management Studio.

Configure general table restore settings

To configure SQL table-level restores with the Microsoft app agent for Application Direct SSMS plug-in, you must first specify general table restore options on the Database Restore > General page.

Steps

1. Open the Microsoft app agent for Application Direct window to the Table Restore > General page.

The General page appears as shown in the following figure.

90 Performing Self-Service Restores of Microsoft SQL Databases

Figure 14. Application Direct - Table Restore-General page

The Microsoft application agent caches SQL Server restore settings. The Microsoft application agent automatically loads the information from the last recovery operation and populates restore settings. Caching saves time by eliminating the need to reselect the settings each time you run a restore operation. To clear the cached settings, click Clear Cache.

2. Use the DataDomain Server field to select the Data Domain server and the storage unit to restore the data. A storage unit contains the backed-up SQL Server hosts.

NOTE: When you want to restore a replicated backup from a secondary Data Domain server, select the relevant server.

The Data Domain user on the secondary Data Domain server must be in the same group as the primary Data Domain

server.

3. From the SQL Server host list, select the SQL Server host that contains the backup. Select the Windows cluster name in the case of federated backups.

To perform a redirected restore, select the SQL Server host where you want to restore the backup to.

4. From the SQL Server Instance list, select the SQL Server instance that contains the backup.

5. From the Database list, select the database that you want to restore. The corresponding save sets appear in the save sets table that is located below the Browse time field.

6. To select the backup that you want to restore, perform either of the following actions: Select a save set in the save sets table that is located below the Browse time field. Use the backup timestamp to

choose the backup. To browse for a backup that is not listed, perform the following steps:

From the Browse time list, select a date and time, and then click Show Versions. From the save sets table, select the save set with the timestamp that you want to restore.

Click < < Older or Newer > > to browse additional save sets.

7. To mount the backup and proceed with the table restore, click Run.

Performing Self-Service Restores of Microsoft SQL Databases 91

Results

The backup is mounted and the ItemPoint for Microsoft SQL Server GUI appears.

Configure optional table restore settings

When you configure SQL backups with the Microsoft app agent for Application Direct SSMS plug-in, you can specify optional backup settings on the Table Restore > Options page.

About this task

All settings on the Options page are optional.

Steps

1. From the left panel, click Options.

The Options page appears as shown in the following figure.

Figure 15. Application Direct - Table Restore-Options page

All settings on the Options page are optional.

2. In the Mount Folder field, specify the location to mount the backup images.

NOTE: The location (folder or drive) to mount the backup images must be empty. Otherwise, the mount operation fails.

To mount the backup as a drive, specify an unused drive letter. The restore operation, after completion, deletes the

mount location.

3. In the Mount Expire list, specify the number of hours, after which the mounted backup image must be dismounted. The default value is 8. The valid range is between 1 and 24 hours.

4. To select the level of information recorded in the logs which can be used in troubleshooting recovery issues, select Select a debug level.

92 Performing Self-Service Restores of Microsoft SQL Databases

Levels range 0-9, with 0 representing no information and 9 representing the most amount of information. The default value is 0 (zero).

5. To change the restore parallelism setting, type or select a value in the Parallelism field.

Parallelism controls the number of recovery sessions the NWFS process uses while mounting a backup. The default and maximum value is 31.

6. To mount the backup and proceed with the table restore, click Run.

Results

The backup is mounted and the ItemPoint for Microsoft SQL Server GUI appears.

Monitor the table restore mount operation

After a table-level restore mount operation is run from the Microsoft app agent for Application Direct SSMS plug-in, the Table Restore > Monitor page displays the mount script and status.

The following figure shows the mount information and status as it appears on the Monitor page.

Figure 16. Application Direct - Table Restore-Monitor page

NOTE: For information about the success or failure of the restore operation, review the log files that are located in the

installation folder. The typical location of the log files is C:\Program Files\DPSAPPS\MSAPPAGENT\logs.

Performing Self-Service Restores of Microsoft SQL Databases 93

Restore table-level data using ItemPoint

After the backup images are mounted using the Microsoft application agent SSMS plug-in, complete the table-level restore using ItemPoint for Microsoft SQL Server.

Prerequisites

Configure and run the table level restore operation with the Table Restore tab in the Microsoft application agent SSMS plug-in.

About this task

Once the table level restore is configured and run through the Microsoft application agent SSMS plug-in, ItemPoint launches.

If you select Do not run ItemPoint after mounting the backup images when you configure the table level restore, you must manually launch ItemPoint, and then launch the Data Wizard.

Steps

1. On the Select Source page, select the source backup files from the mounted volume that contains the SQL backup data as shown in the following figure, and then click Next.

Figure 17. ItemPoint Data Wizard: select the source files

2. On the Select Target Server page, specify the details and login credentials for the target SQL Server and database.

The following figure shows the ItemPoint Data Wizard Select Target Server page:

94 Performing Self-Service Restores of Microsoft SQL Databases

Figure 18. ItemPoint Data Wizard: select the target server

3. Click Finish. The Data Wizard closes and ItemPoint loads the tables contained in the source backup files.

4. Use ItemPoint to browse and restore the individual tables.

NOTE: The PowerProtect ItemPoint for Microsoft SQL Server User Guide provides more information on using ItemPoint

for Microsoft SQL Server to restore table-level data.

5. To dismount the mounted backup images, exit ItemPoint for Microsoft SQL Server.

NOTE: If you select Leave backup images mounted after ItemPoint exits or Do not run ItemPoint after

mounting the backup images when you configure the mount operation, the backup image remains mounted for 4

hours. To dismount the backup manually, use the msagentadmin command.

Performing SQL Server disaster recovery When a disaster scenario occurs, the Microsoft application agent supports disaster recovery of data located on both a Data Domain server and Data Domain Cloud Tier.

Perform SQL Server disaster recovery

The Microsoft application agent for Application Direct supports disaster recovery.

Steps

1. Create a target Windows host with the same name as the source hostname.

2. Install a SQL Server instance with the same name as the source instance name.

3. Install the Microsoft application agent on the target Windows host.

4. Browse the backups of the source instance by selecting the appropriate storage unit.

5. Restore the system databases, such as master, model, msdb, and so on, to the target instance.

6. Restore all the user databases to the target instance.

Performing Self-Service Restores of Microsoft SQL Databases 95

Perform disaster recovery from the Data Domain Cloud Tier

The Microsoft application agent provides a command line tool to complete disaster recovery of save sets that are located in a Data Domain Cloud Tier.

After an MTree is recovered according to the disaster recovery procedure described in Perform SQL Server disaster recovery on page 95, you must restore the backup indexes from the Data Domain Cloud Tier.

When the Microsoft application agent moves a backup to the cloud, the index files are maintained on the active tier. A copy of the index files is created and moved to the cloud tier for long-term retention.

After an MTree is restored during a disaster recovery, all the files that resided only on the active tier are lost and unavailable. Only the files that were moved to the cloud are available.

In this case, you must run msagentadmin administration with the --dr-recall or -M flag to restore the indexes.

After the indexes are recalled to the active tier, the data save sets for the same time range are also recalled unless you type n when prompted with Continue with the recall of the found save sets [y/n]. If you choose to not recall the save sets, you can manually recall the save sets later.

Type the msagentadmin administration command with the following syntax to recall the indexes to the active tier:

msagentadmin administration --dr-recall --ddhost "<Data_Domain_server_name>" --ddpath "<storage_unit_name_and_path>" --dduser "<DD_Boost_username>" --appID "mssql"

where:

--dr-recall

Specifies an operation to recall save sets for disaster recovery.

You can use the -M alias for the --dr-recall parameter.

--ddhost " "

Specifies the name of the Data Domain server that contains the storage unit, to which you backed up the databases.

--ddpath "<storage_unit_name_and_path>"

Specifies the name and the path of the storage unit, to which you backed up the databases.

--dduser "<DD_Boost_username>"

Specifies the username of the DD Boost user.

--appID "mssql"

Specifies the application ID (namespace) to locate backups.

You can use the -n alias for the --appID parameter.

Consider the following example commands to perform disaster recovery of SQL Server with data located on a Data Domain Cloud Tier device:

Cloud tier disaster recovery recall command without a configuration file

msagentadmin administration --dr-recall --tier --after 1481104962 --before 1481105533 -- appID mssql --ddhost "10.70.102.111" --ddpath "/mt1" --dduser "ost" --confirm --client SQLX86.adesc.com --debug 9

Cloud tier disaster recovery recall command with a configuration file

msagentadmin administration --dr-recall --tier --after 1481104962 --before 1481105533 -- appID mssql --confirm --config c:\temp\config_pp.txt --debug 9

96 Performing Self-Service Restores of Microsoft SQL Databases

Performing Self-Service Restores of SQL Virtual Machine Backups

Topics:

Restoring a SQL Server virtual machine backup Overview of SQL Server virtual machine restore operations Prerequisites Restoring SQL Server databases to a virtual machine Performing SQL Server table-level recovery to a virtual machine Performing an instant access recovery

Restoring a SQL Server virtual machine backup You can use the Microsoft application agent to restore the SQL databases that are backed up with an application-aware VM protection policy.

The following topics provide instructions on how to restore a SQL Server virtual machine backup.

Overview of SQL Server virtual machine restore operations Use the Microsoft application agent tools to restore full and transaction log backups created by a PowerProtect virtual machine application-aware protection policy. The backups are restored to a SQL Server hosted on a VMware virtual machine.

When you add a SQL Server virtual machine asset to a PowerProtect virtual machine application-aware protection policy, the Microsoft application agent and ItemPoint are silently installed on the protected SQL Server.

The Microsoft application agent automatically stores the Data Domain host and login information from the protection settings that are configured in the PowerProtect protection policy. This automatic configuration occurs when the SQL Server virtual machine asset is added to the PowerProtect protection policy.

You can use the Microsoft app agent for VM Direct SQL Server Management Studio (SSMS) plug-in or the command prompt to perform the restore operations. T-SQL scripts are not supported with VM Direct.

The Microsoft application agent can perform a database restore, table-level restore, or database instant access restore to the source virtual machine or an alternate virtual machine. To perform restores to an alternate virtual machine, that virtual machine must be an asset of PowerProtect. However, instance-level restores can only be performed to the original source instance.

The Microsoft application agent supports both full backups and transaction log backups for a Microsoft Always On availability group (AAG). The AAG databases are indexed against the AAG cluster name. Full backups index the AAG database for all the AAG cluster nodes for one cycle of backup. Transaction log backups occur only on the preferred node. You may restore the AAG database to any copy. When you restore an AAG database for the purpose of adding back into the AAG, you may restore to any AAG database copy but first remove the database from the AAG, as required by Microsoft. Once the restore is complete, you must add the database back into the AAG and re-seed the replicas.

Prerequisites An environment must meet the following requirements for application-aware virtual machine restore operations:

vCenter 6.5 and VMware ESXi 6.5 or later must be installed. VMware tools version 10.1 or later must be installed and running on the SQL host (virtual machine).

6

Performing Self-Service Restores of SQL Virtual Machine Backups 97

The UUID attribute must be enabled in the vSphere Client (disk.EnableUUID=TRUE).

The following VMware Knowledge Base article provides instructions:

https://kb.vmware.com/s/article/52815

NOTE: After you set disk.EnableUUID to TRUE, ensure that you reboot the virtual machine.

The virtual machine must use SCSI disks only and the number of available SCSI slots must match the number of disks at a minimum.

For example, a virtual machine with 7 disks requires one SCSI controller but a virtual machine with 8 disks requires 2 SCSI controllers.

The SQL Server instance must be up and running on the virtual machine. The SQL Server must be a stand-alone instance or part of a clustered or clusterless Always On availability group that is

configured with file share witness. The SQL writer service must be running. The full computer name and FQDN for the SQL virtual machine that are added in the application-aware protection policy

must be identical and have the DNS resolve. The user account that is configured in the PowerProtect protection policy must have access to perform backup and

recovery operations.

To configure the required permissions, perform the following steps on each protected SQL Server instance:

1. Create a SQL Login with the user account configured in the PowerProtect protection policy. 2. Add the user the sysadmin SQL Server role.

The SQL Server to which the data will be restored must be hosted on a virtual machine that is a discovered asset of PowerProtect.

NOTE: If you are restoring data to an alternate location to a SQL Server virtual machine that is not protected in

PowerProtect, you must manually install the Microsoft application agent on the target virtual machine before you can

restore the data. The PowerProtect Microsoft Application Agent Installation Guide provides instructions to install the

Microsoft application agent for VM Direct. When you configure the restore operation, you must manually register the

Data Domain server, PowerProtect appliance, and lockbox details.

Restoring SQL Server databases to a virtual machine Learn how to restore Microsoft SQL Server database backups taken in a PowerProtect virtual machine application-aware protection policy.

You can restore database full and transaction log backups from the primary Data Domain system or from the secondary Data Domain system if replication is enabled for the PowerProtect protection policy. However, when you are restoring from the secondary Data Domain system, the tail-log backup option is not supported.

Restoring a backup that was moved to the cloud using Data Domain Cloud Tier

An application-aware workflow supports application-aware full backups that are tiered to the cloud using Data Domain Cloud Tier. This support is available for Amazon Web Services (AWS) and Elastic Cloud Storage (ECS) profiles.

For a SQL database recovery from backups that have been tiered using an application-aware workflow, the backups must be recalled to the active tier before you perform the recovery.

When you try to restore a backup after an associated full backup has been moved to the cloud using Data Domain Cloud Tier, the restore operation fails with the following error message:

"reason" : "Missing or invalid value specified for property 'copyId'"

This error occurs when you try to restore either a full backup that has been tiered to the cloud or a tail-log backup when the associated full backup has been tiered to the cloud. For example, the following full and tail-log backups are performed at the specified times:

FULL1: 10:00 a.m. (in cloud tier) TLOG1: 10:30 a.m.

98 Performing Self-Service Restores of SQL Virtual Machine Backups

TLOG2: 11:00 a.m. FULL2: 11:15 a.m. (in active tier) TLOG4: 11:30 a.m. TLOG5: 12:00 p.m.

In this case, the restore of the FULL1, TLOG1, or TLOG2 backup fails with the error message because the FULL1 backup has been tiered to the cloud. The restore of the FULL2, TLOG4, or TLOG5 backup succeeds because the FULL2 backup is in the active tier.

To resolve this issue, contact the PowerProtect administrator to recall the associated full backup (FULL1 in this example) from the cloud tier to the active tier, and then perform the restore.

Restore SQL Server databases with the VM Direct SSMS plug-in

Learn how to perform a VM Direct SQL Server database restore operation using the Microsoft app agent for VM Direct SSMS plug-in.

Launch the Microsoft app agent for VM Direct SSMS plug-in

About this task

Steps

1. From the Windows Start menu, select Microsoft SQL Server Management Studio.

2. In the Microsoft SQL Server Management Studio, in the Connect to Server window, specify the server information and credentials, and then click Connect.

3. On the toolbar, click Microsoft App Agent (VM Direct). The Microsoft app agent for VM Direct window appears.

NOTE: If the Microsoft App Agent (VM Direct) button is not on the toolbar, the plug-in may be unable to register. In

this scenario, you can launch the plug-in directly from the start menu. From the Windows Start menu, select DDBMA

Plugin for SQL Server Management Studio.

Configure general database restore settings

To configure SQL restores with the Microsoft app agent for VM Direct SSMS plug-in, you must first specify general restore options on the Database Restore > General page.

Steps

1. Open the Microsoft app agent for VM Direct window to the Database Restore > General page.

The General page appears as shown in the following figure.

Performing Self-Service Restores of SQL Virtual Machine Backups 99

Figure 19. VM Direct: Database Restore General page

2. Beside Data Domain Server, click ... The Data Domain Server List & Lockbox Settings window appears and lists the Data Domain server and lockbox that is configured in the PowerProtect protection policy. If the protection policy is configured for replication, the secondary Data Domain server and storage unit also appear.

3. In the Data Domain Server List & Lockbox Settings window, select the Data Domain server that contains the backup, and then click OK.

NOTE: If you are restoring the backup to an alternate virtual machine that is not protected in a PowerProtect

protection policy, contact the PowerProtect administrator to retrieve the Data Domain host details for the protection

policy associated with the original virtual machine. You must also request the PowerProtect administrator to perform

either of the following actions:

Approve (whitelist) the Microsoft application agent on the target virtual machine.

Provide the Data Domain storage unit login credentials.

The SQL Server host field is updated with the client backups that are available on the selected Data Domain server storage unit.

4. From the SQL Server host list, select the SQL Server host that is the source of the backup.

If you are restoring an AAG database, you must select the Windows cluster name, which enables you to browse the list of AAGs hosted on this cluster.

If you are restoring a clusterless AAG database, you must select the AAG group name, which enables you to browse the list of AAGs hosted on the clusterless AAG group. In the case of a clusterless AAG, the AAG name plus GUID is displayed.

5. Use the Start browsing from list to select a backup save time, and then click Show Versions.

The SQL Server Instance field and save sets table are updated with the backups that are within the specified browse time. If no level full backups are found in the specified time range, you are prompted for confirmation to load the latest full backup.

100 Performing Self-Service Restores of SQL Virtual Machine Backups

NOTE: If you selected a cluster name or AAG group name with GUID in the preceding step, then the list of AAGs in the

cluster or the list of AAGs in the AAG group named with GUID is displayed instead of the list of SQL Server instances.

6. From the SQL Server Instance list, select the SQL Server instance that contains the databases that you want to restore.

The databases located on the SQL Server instance appear in the database table below the SQL Server Instance field.

7. From the database table, select either the entire SQL Server instance by selecting the check box in the header row or select only the required databases to restore. The corresponding save sets appear in the save sets table that is below the databases table.

8. For single database restores, from the save sets table, select the database backup and timestamp that you want to restore. By default, the most recent backup is selected.

NOTE: This step only applies to single database restores. For multiple database and instance-level restores, the most

recent backup is restored.

9. (Optional) To restore the database to a specific point-in-time, perform the following steps:

a. Under Destination, click Timeline. The Backup Timeline dialog box appears as shown in the following figure.

Figure 20. Specifying the restore point b. Specify the backup date and time, and then click OK.

10. Specify the restore destination by performing one of the following action sequences: To perform a recovery directly to a database, perform the following steps:

a. Select Restore to SQL Server. b. From the Instance list, select the SQL Server instance where you want to restore the backup.

NOTE:

If you are restoring the backup to an alternate instance, the data and log files are automatically relocated to the

target SQL instance default data and log path. You can change the file destination settings on the Files page.

If you are restoring an AAG database, select the target instance that is part of the AAG for the restore. You must

remove the database from the AAG first. You can add the database back to the AAG after the restore completes.

c. From the Database list, select or type the name of the database where the backup will be restored to.

If the database exists, the operation prompts you for confirmation to overwrite the database.

To recover the database as flat-files, perform the following steps: a. Select Restore backups as files. b. In the Folder field, specify the destination for the files. The Folder field is populated with the default destination

path.

To perform a redirected restore, change the destination path by clicking ... beside the Folder field, and specifying the required destination.

NOTE:

Ensure that the destination path is not a root drive, for example, E:\.

Full backups are restored as .mdf, .ndf, and .ldf files. Transaction log backups are restored as .bak files.

Performing Self-Service Restores of SQL Virtual Machine Backups 101

Next steps

Specify the PowerProtect server details and additional restore options on the Options page. Also, to change the file destination settings, use the Files page.

Configure file destination settings

View and change the default destination folders of the database files that are data files (.mdf and .ndf) and log files (.ldf) from the Database Restore > Files page.

About this task

All settings on the Files page are optional.

File information on the Files page is shown only when a single database and a backup version is selected on the General page. If you select multiple databases or do not select a backup version, file information does not appear and the databases are restored to the latest backup.

NOTE:

If you are restoring the backup to an alternate instance, the data and log files are automatically relocated to the target SQL

instance default data and log path. You can change the file destination settings on the Files page.

Ensure that the SQL Server administrative account for the target SQL Server instance has access to the selected path so

that it can access the database files once they are mounted.

Steps

1. From the left panel, click Files.

The Files page appears as shown in the following figure.

102 Performing Self-Service Restores of SQL Virtual Machine Backups

Figure 21. VM Direct: Database Restore Files page

The table on the page displays the source locations of the database files.

2. To change the destination paths of all of the data and log files, perform the following steps:

a. Select Relocate all files to folder. b. Click the Data file folder browse button to specify a location for the data files, or click inside the field to type the folder

path. c. Click the Log file folder browse button to specify a location for the log files, or click inside the field to type the folder

path.

3. To change the destination path at the file group level, perform one of the following actions in the table: To browse for a destination path, click the browse button to the right of each file group.

A window appears where you can browse and select the file path.

To type a new destination path, in the Restore To column, click the appropriate cell and type a destination path.

If the path does not exist, a dialog box appears asking if you want to create the folder.

NOTE: Ensure that the destination paths are not root drives, for example, E:\.

Next steps

Specify the PowerProtect server details and additional restore options on the Options page.

Performing Self-Service Restores of SQL Virtual Machine Backups 103

Configure database restore options

When you configure SQL database restores with the Microsoft app agent for VM Direct SSMS plug-in, you can specify additional restore options on the Database Restore > Options page.

About this task

NOTE: If you want to restore an AAG database, you must remove the database from the AAG first. You can add the

database back to the AAG after the restore completes. If you have not removed the database from the AAG before you

start the restore, the restore UI displays a prompt about the requirement.

Steps

1. From the left panel, click Options. The Options page appears as shown in the following figure.

Figure 22. VM Direct: Database Restore Options page

2. In the Recovery state list, select one of the following options, as required:

Option Description

RESTORE WITH RECOVERY

(Default) To leave the database in the ready-to-use state by rolling back uncommitted transactions, and disable the ability to restore the latest or additional transaction logs.

RESTORE WITH NORECOVERY

To leave the database in the non-operational state by not rolling back uncommitted transactions, and enable the ability to restore the latest or additional transaction logs.

3. To compress the restore contents and transport them from the Data Domain Replicator to the application host, select Data Domain Boost compressed restore.

This option reduces network bandwidth.

104 Performing Self-Service Restores of SQL Virtual Machine Backups

4. To generate detailed logs, which you can use to troubleshoot the restore issues, select Select a debug level, and then specify a value of 1 through 9. The default value is 0 (zero).

5. To delete debug logs older than a certain number of days, select Delete debug logs after, and then specify the number of days with a number between 1 and 32767 days. The default value is 0 and does not delete any debug logs.

NOTE: This option only deletes debug logs named in the default format and located in the logs folder at

\MSAPPAGENT\logs.

Regularly deleting debug logs prevents the log folder on the installation drive from becoming too large.

6. To enable advanced restore options, type or select one of the following options in the Advanced options list:

Option Description

KEEP_REPLICATION Preserves the replication when recovering a published database. This option is required if a database was replicated when the backup was created.

KEEP_CDC Enables change data capture (CDC) recovery. When restoring a database with CDC enabled, the recover operation works differently depending on the recovery destination. Use the to KEEP_CDC option to: Recover the CDC enabled database with a different name on the same SQL instance.

In this scenario, KEEP_CDC is required.

Recover the CDC enabled database on a different SQL server instance.

In this scenario, KEEP_CDC is required.

Recover the CDC enabled database on the same SQL instance by overwriting an existing database.

In this scenario, KEEP_CDC is optional.

Separate multiple advanced options with a comma.

7. To limit the backup history of the databases on the General page to a set number of days, type or select a number in the Days of backup history field.

The default value is 7 days.

If no level full backups are found in the specified time range, you are prompted for confirmation to load the latest full backup.

8. Exclude client name resolution is selected by default, as required to use the client name that is provided as is without converting it to the fully qualified domain name (FQDN).

Use this option when you restore to a virtual machine in a different domain that does not have name resolution for the source client, or when the source host is no longer available.

Unselect the option to resolve the source FQDN during restore.

9. To perform a transaction log backup of the data before performing restore, select Take tail-log backup before restore.

The tail-log backup ensures that the Microsoft application agent backs up the transaction logs that has changed from the previous backup. The restore operation does not restore the logs of the previous tail-log backup.

NOTE:

The tail-log backup feature through the SSMS plug-in is not supported for AAG. Only scheduled transaction log backups

through a PowerProtect protection policy may be performed.

Tail-log backup is not supported in the following scenarios:

When you select Restore backups as files on the General page.

When the PowerProtect protection policy has Data Domain MTree replication enabled and you select the secondary

Data Domain system as the backup source.

The Microsoft application agent does not support tail-log backups of multiple databases.

10. To specify the PowerProtect appliance, perform the following steps:

a. Beside Appliance Host, click ...

Performing Self-Service Restores of SQL Virtual Machine Backups 105

The PowerProtect Data Manager List & Lockbox Settings window appears.

When you restore a backup to the original virtual machine, the details for the PowerProtect appliance that protects the SQL Server in a protection policy automatically appear.

b. If you do not see the PowerProtect appliance that you want to use for the restore operation, click Add, and then in the Add PowerProtect Data Manager details dialog box, perform one of the following actions:

To add a PowerProtect with credential-based authentication, specify the appliance hostname and the login credentials.

Contact the PowerProtect administrator to retrieve the PowerProtect appliance host and login details.

To add a PowerProtect with certificate-based authentication, specify only the appliance hostname. Do not specify the username and password fields.

To use certificate-based authentication, the PowerProtect administrator must whitelist the target Microsoft application agent.

When you add a virtual machine to a protection policy, that virtual machine is whitelisted with a certificate that expires after one day. If you are restoring to an alternate virtual machine that is an asset of PowerProtect but not a member of a protection policy, the PowerProtect must whitelist the target virtual machine.

The PowerProtect administrator can approve (whitelist) the target Microsoft application agent on the PowerProtect Infrastructure > Application Agents page.

c. Select the PowerProtect appliance that will mount the backup, and then click OK.

11. To start the restore operation, click Run.

Monitor the database restore operation

After a database restore operation is run from the Microsoft app agent for VM Direct SSMS plug-in, the Database Restore > Monitor page displays the restore script and status.

The following figure shows the restore information and status as it appears on the Monitor page.

106 Performing Self-Service Restores of SQL Virtual Machine Backups

Figure 23. VM Direct: Database Restore Monitor page

NOTE: For information about the success or failure of the restore operation, review the log files that are located in the

installation folder. The location of the log files when the Microsoft application agent is installed in the default path is

C:\Program Files\DPSAPPS\MSAPPAGENT\logs.

Restore a SQL Server database with the VM Direct recover command

Learn how to perform a VM Direct SQL Server database restore operation using the command line.

Syntax to restore a database with a level full backup

Run the following command to restore databases with VM Direct:

msagentrc.exe -A VM_DIRECT=TRUE -A "NSR_RESTORE_TYPE=regular" -c -A "NSR_DFA_SI_DD_HOST= " -A "NSR_DFA_SI_DD_USER= " -A "NSR_DFA_SI_DEVICE_PATH= " -A "DM_HOST= " [ ] -I - -t

where:

-A VM_DIRECT=TRUE

Specifies to use the VM Direct workflow for the recover operation.

-A "NSR_RESTORE_TYPE=regular"

Specifies to perform a database level restore.

Performing Self-Service Restores of SQL Virtual Machine Backups 107

-c

Specifies the client name that was used for the backup.

-A "NSR_DFA_SI_DD_HOST= "

Specifies the name of the Data Domain server that contains the backup.

When you have a remote (secondary) Data Domain server that has replicated databases to restore, type the name of the secondary server. A Data Domain user on the secondary Data Domain server must be in the same group as the primary server.

-A "NSR_DFA_SI_DD_USER= "

Specifies the username of the DD Boost user.

You must register the hostname and the DD Boost username in the lockbox to enable Microsoft application agent to retrieve the password for the registered user.

-A "NSR_DFA_SI_DEVICE_PATH= "

Specifies the name and the path of the storage unit that contains the backup.

-A "DM_HOST= "

Specifies the PowerProtect appliance hostname or IP address.

-t

Specifies the save time of the backup. Specify this parameter along with the restore path for each database in the restore operation. The application pauses for you to input this option line by line. Press Enter to move to the next line. A blank line indicates completion of the list.

Specifies the path to the backup objects that you want to restore.

Type the backup path in one of the following formats: For a default instance, type the path in one of the following formats:

To restore a backup of the entire instance, type APPLICATIONS: \SqlServerWriter\

To restore a backup of specific databases, type APPLICATIONS: \SqlServerWriter\ \ \ [...]\

For example: "APPLICATIONS:\SqlServerWriter\LEDMF158\dbtest3" For a named instance, type the path in one of the following formats:

To restore a backup of the entire instance, type APPLICATIONS:\SqlServerWriter\ encoded_SQL_instance_name>

To restore a backup of specific databases, type APPLICATIONS:\SqlServerWriter\ encoded_SQL_instance_name>\ \ [...]\ The is typically %5C

For example: "APPLICATIONS:\SqlServerWriter\LEDMF158%5Cinst1\dbtest3"

Example VM Direct level full database restore commands

msagentrc.exe -A VM_DIRECT=TRUE -A DM_MOUNT_TIMEOUT=240 -A DM_HOST=12.34.155.77 -A DM_PORT=8443 -c appvm-sql.appvmtest.com -A NSR_DFA_SI_DD_HOST=mars.jupiter.emc.com -A NSR_DFA_SI_DD_USER=admin_user -A NSR_DFA_SI_DEVICE_PATH=/PLC-PROTECTION-11234 -A "NSR_DFA_SI_DD_LOCKBOX_PATH=C:\Program Files\DPSAPPS\common\lockbox" -A RESTORE_FROM_DD_ONLY=YES -A NSR_RESTORE_TYPE=regular -I - -t 1541991384 APPLICATIONS:\SqlServerWriter\APPVM-SQL\DB1\

msagentrc.exe -A VM_DIRECT=TRUE -A DM_MOUNT_TIMEOUT=240 -A DM_HOST=12.34.222.184 -A DM_PORT=8443 -c win-appvm-000.appsvm.com -A NSR_DFA_SI_DD_HOST=host.lss.emc.com -A NSR_DFA_SI_DD_USER=PLC-PROTECTION-USER -A NSR_DFA_SI_DEVICE_PATH=/PLC-PROTECTION-154 -A "NSR_DFA_SI_DD_LOCKBOX_PATH=C:\Program Files\DPSAPPS\common\lockbox" -A RESTORE_FROM_DD_ONLY=YES -A NSR_RESTORE_TYPE=regular -I - -t 1543513971 APPLICATIONS:\SqlServerWriter\WIN-APPVM-191\ReportServerTempDB\ -t 1543513971 APPLICATIONS:\SqlServerWriter\WIN-APPVM-191\master\ -t 1543513971 APPLICATIONS:\SqlServerWriter\WIN-APPVM-191\model\ -t 1543513971 APPLICATIONS:\SqlServerWriter\WIN-APPVM-191\msdb\ -t 1543513971 -A ADDITIONAL_RESTORES=yes APPLICATIONS:\SqlServerWriter\WIN- APPVM-191\ReportServer\

108 Performing Self-Service Restores of SQL Virtual Machine Backups

-t 1543513971 -A ADDITIONAL_RESTORES=yes APPLICATIONS:\SqlServerWriter\WIN- APPVM-191\data1\ -t 1543513971 -A ADDITIONAL_RESTORES=yes APPLICATIONS:\SqlServerWriter\WIN- APPVM-191\data10\ -t 1543513971 -A ADDITIONAL_RESTORES=yes APPLICATIONS:\SqlServerWriter\WIN- APPVM-191\data11\

Syntax to restore a database with a transaction log backup

Use the ddbmsqlrc command to restore a transaction log from the command prompt for VM Direct workflow backups. This command may be used if the database full backup was restored in the NORECOVERY state.

Run the following command to restore database transaction logs in a stand-alone environment with VM Direct:

ddbmsqlrc.exe -a VM_DIRECT=TRUE -a "NSR_DFA_SI_DD_HOST= " -a "NSR_DFA_SI_DD_USER= " -a "NSR_DFA_SI_DEVICE_PATH= " -c [-a "SKIP_CLIENT_RESOLUTION=TRUE"] -t -S normal -a "RESTORE_START_TIME= "

where:

-A VM_DIRECT=TRUE

Specifies to use the VM Direct workflow for the recover operation.

-a "NSR_DFA_SI_DD_HOST= "

Specifies the name of the Data Domain server that contains the backup.

When you have a remote (secondary) Data Domain server that has replicated databases to restore, type the name of the secondary server. A Data Domain user on the secondary Data Domain server must be in the same group as the primary server.

-a "NSR_DFA_SI_DD_USER= "

Specifies the username of the DD Boost user.

You must register the hostname and the DD Boost username in the lockbox to enable Microsoft application agent to retrieve the password for the registered user.

-a "NSR_DFA_SI_DEVICE_PATH= "

Specifies the name and the path of the storage unit that contains the backup.

-c

Specifies the client name that was used for the backup.

-a "SKIP_CLIENT_RESOLUTION=TRUE"

(Optional) Specifies to use the client name as it is exactly entered with the -c option and to skip the FQDN lookup to automatically resolve the client name.

-t " "

Specifies the backup time stamp that you want to restore.

-S normal

Enables you to roll back uncommitted transactions and use the database to restore the most recent or additional transaction logs.

-a "RESTORE_START_TIME= "

Restores all the backups that were performed at or after a specified backup time (start date and time) and up to the backup time of the selected save set, that is, -t .

Specify the start date and time in the /

/
: : {AM | PM} format.

The start date and time must not be after the backup time of the selected save set, that is, -t .

" "

Specifies the path to the backup objects that you want to restore.

Type the restore path in one of the following formats: For a default instance, type the path in one of the following formats:

To restore backups of the entire instance, type MSSQL:

Performing Self-Service Restores of SQL Virtual Machine Backups 109

To restore backups of specific databases, type [MSSQL:] [[MSSQL:] [...]]

For example: "MSSQL:database1" "MSSQL:database2" For a named instance, type the path in one of the following formats:

To restore backups of the entire instance, type MSSQL$ : To restore backups of specific databases, type MSSQL$ :

[...] For example: "MSSQL$SqlInst1:database1" "MSSQL$SqlInst1:database2"

Example VM Direct transaction logs database restore commands

ddbmsqlrc.exe -a NSR_DFA_SI_DD_HOST=mars.jupiter.emc.com -a NSR_DFA_SI_DD_USER=PLC- PROTECTION-USER -a NSR_DFA_SI_DEVICE_PATH=/PLC-PROTECTION-123456 -a "NSR_DFA_SI_DD_LOCKBOX_PATH=C:\Program Files\DPSAPPS\common\lockbox" -c appvm- sql.appvmdc.com -a "SKIP_CLIENT_RESOLUTION=TRUE" -C "'DB1'='C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\DB1.mdf', 'DB1_log'='C: \Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\DB1_log.ldf'" -t "11/12/2018 08:54:53 AM" -S normal -a "RESTORE_START_TIME=11/12/2018 08:26:25 AM" -a VM_DIRECT=TRUE MSSQL:DB1

ddbmsqlrc.exe -a NSR_DFA_SI_DD_HOST=host.lss.emc.com -a NSR_DFA_SI_DD_USER=PLC-PROTECTION-USER -a NSR_DFA_SI_DEVICE_PATH=/PLC-PROTECTION-154 -a "NSR_DFA_SI_DD_LOCKBOX_PATH=C:\Program Files\DPSAPPS\common\lockbox" -c win-appvm-191.appsvm.com -a "SKIP_CLIENT_RESOLUTION=TRUE" -C "'ReportServer'='C:\Program Files (x86)\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\ReportServer.mdf', 'ReportServer_log'='C:\Program Files (x86)\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\ReportServer_log.ldf'" -S normal -a "RESTORE_START_TIME=11/29/2018 09:52:52 AM" -a VM_DIRECT=TRUE MSSQL:ReportServer

Optional parameters for VM Direct database-level restores

You can use the following optional parameters with the msagentrc.exe command.

-A "NSR_DFA_SI_DD_LOCKBOX_PATH= "

Specifies the folder that contains the lockbox file, which contains encrypted information about the registered hosts and the corresponding usernames in pairs. Each pair is associated with a password that backups use. If you do not specify a value, the path defaults to the installation path, which is typically C:\Program Files\DPSAPPS\common\lockbox.

-A "DM_USER= "

Specifies the username to use for the PowerProtect connection.

This parameter is mandatory only for credential-based access to the PowerProtect appliance. You are not required to specify this parameter when the Microsoft application agent is whitelisted by PowerProtect.

When you add a virtual machine to a protection policy, that virtual machine is whitelisted with a certificate that expires after one day. The PowerProtect administrator can approve (whitelist) the target Microsoft application agent on the PowerProtect Infrastructure > Agents page.

-A "DM_PORT= "

Specifies the port number to communicate with the PowerProtect server. The default value is 8443.

-A "DM_LOG_LEVEL={WARNINGS | INFO | TRACE | DEBUG}"

Specifies the PowerProtect log level, with WARNINGS providing the least amount of detail, and DEBUG providing the most amount of detail.

-A "DM_LOG_TAG= "

Specifies the PowerProtect log tag.

-A "ADDITIONAL_RESTORES={yes | no}"

Specifying yes disables the ability to roll back uncommitted transactions and uses the database to restore the latest or additional transaction logs. The default value is no.

110 Performing Self-Service Restores of SQL Virtual Machine Backups

-A "RENAME_TO= "

Specifies the new name for the destination database. The selected database will be restored to the new database. The new name of the database must be a valid SQL database name. If the destination contains a database with the same name as the database that you have specified, the restore operation overwrites the existing database.

The database files that are data files (.mdf and .ndf) and log file (.ldf) will be restored to their source locations only. If the source database exists, its files can be in use. So, to avoid conflicts for using a new database name, use the -A RELOCATE_TO option along with the -A RENAME_TO option.

-A "INSTANCE_TO= \ "

Performs a redirected restore operation to a SQL Server instance that is different from the source instance where the backup was taken.

NOTE: The target instance must reside on the same system where the restore operation is

configured. You cannot restore the backup to an instance on a remote host.

-A "RELOCATE_TO=' '='destination_folder-1', ' '='destination_folder-2',[...]"

Performs redirected restores of the database files that are data files (.mdf and .ndf) and log file (.ldf). You can rename or change only the destination folders, but not the filenames.

Ensure that the destination paths are not root drives, for example, E:\.

-A "NSR_SQL_RECOVER_MODE= "

Performs a redirected restore to the specified path.

-A "NSR_SQL_TARGET_DIR= "

Specifies the destination path, to which you want to copy the restored files. You can then manually either move the files to the required folder or attach the files to a SQL Server instance.

Ensure that the destination path is not a root drive, for example, E:\.

-D

Generates detailed logs that you can use to troubleshoot the restore issues. The supported debug levels are 1 through 9.

-A "DELETE_DEBUG_LOG_DAYS= "

Deletes debug log files that are older than the specified number of days. The valid range is between 1 and 32767. By default, debug logs are not deleted. Regularly deleting debug logs prevents the log folder on the installation drive from becoming too large.

NOTE: This parameter only deletes debug logs named in the default format and located in the logs

folder at \MSAPPAGENT\logs.

Performing SQL Server table-level recovery to a virtual machine To recover table-level data, you can use the SSMS plug-in to first mount the backup, and then use ItemPoint for Microsoft SQL Server to browse and restore the tables.

NOTE: You can only mount and restore tables from the level full backups.

Table-level restore operations mount the application-aware virtual machine image backup on the target virtual machine.

The Microsoft application agent sends a request to the PowerProtect appliance to mount the selected backup.

When the backup is mounted, the VMDK images from the virtual machine image backup are added to the target-virtual machine and assigned mount points.

The backup is mounted with the PowerProtect appliance for 4 hours by default. The valid range for the mount timeout is between 1 and 24 hours, after which the mounted backup must be dismounted.

NOTE: When backup images are mounted, you cannot start a new session of backup, database restore, table-level restore,

or instant access.

Performing Self-Service Restores of SQL Virtual Machine Backups 111

Restore SQL Server tables with the VM Direct SSMS plug-in

Learn how to perform a VM Direct SQL Server table-level restore operation using the Microsoft app agent for VM Direct SSMS plug-in.

Launch the Microsoft app agent for VM Direct SSMS plug-in

About this task

Steps

1. From the Windows Start menu, select Microsoft SQL Server Management Studio.

2. In the Microsoft SQL Server Management Studio, in the Connect to Server window, specify the server information and credentials, and then click Connect.

3. On the toolbar, click Microsoft App Agent (VM Direct). The Microsoft app agent for VM Direct window appears.

NOTE: If the Microsoft App Agent (VM Direct) button is not on the toolbar, the plug-in may be unable to register. In

this scenario, you can launch the plug-in directly from the start menu. From the Windows Start menu, select DDBMA

Plugin for SQL Server Management Studio.

Configure general table restore settings

To configure SQL table-level restores with the Microsoft app agent for VM Direct SSMS plug-in, you must first specify general table restore options on the Table Restore > General page.

Steps

1. Open the Microsoft app agent for VM Direct window to the Table Restore > General page.

The General page appears as shown in the following figure.

112 Performing Self-Service Restores of SQL Virtual Machine Backups

Figure 24. VM Direct: Table Restore General page

2. Beside Data Domain Server, click . The Data Domain Server List & Lockbox Settings window appears and lists the Data Domain server and lockbox that is configured in the PowerProtect protection policy. If the protection policy is configured for replication, the secondary Data Domain server and storage unit also appear.

3. In the Data Domain Server List & Lockbox Settings window, select the Data Domain server that contains the backup, and then click OK. The SQL Server host field is updated with the client backups that are available on the selected Data Domain server.

4. From the SQL Server host list, select the SQL Server host that is the source of the backup.

If you are restoring an AAG database, you must select the Windows cluster name, which enables you to browse the list of AAGs hosted on this cluster.

If you are restoring a clusterless AAG database, you must select the AAG group name, which enables you to browse the list of AAGs hosted on the clusterless AAG group. In the case of a clusterless AAG, the AAG name plus GUID is displayed.

5. Use the Start browsing from list to select a backup save time, and then click Show Versions.

The SQL Server Instance field and save sets table are updated with the backups that are within the specified browse time. If no level full backups are found in the specified time range, you are prompted for confirmation to load the latest full backup.

NOTE: If you selected a cluster name or AAG group name with GUID in the preceding step, then the list of AAGs in the

cluster or the list of AAGs in the AAG group named with GUID is displayed instead of the list of SQL Server instances.

6. From the SQL Server Instance list, select the SQL Server instance that contains the databases that you want to restore.

The databases located on the SQL Server instance appear in the database table below the SQL Server Instance field.

7. From the databases table, select the database that contains the tables that you want to restore. The corresponding save sets appear in the save sets table.

8. In the save sets table, select the save set that contains the tables that you want to restore.

Performing Self-Service Restores of SQL Virtual Machine Backups 113

Next steps

Specify the PowerProtect server details and additional restore options on the Options page.

Configure table restore options

When you configure SQL table-level restores with the Microsoft app agent for VM Direct SSMS plug-in, you can specify additional restore options on the Table Restore > Options page.

Steps

1. From the left panel, click Options.

The Options page appears as shown in the following figure.

Figure 25. VM Direct: Table Restore Options page

2. Under Mount, specify the following fields as required:

The Mount Folder field specifies the location where backup images are mounted. You cannot edit the value in this field. In the Mount Timeout list, specify the number of hours, after which the mounted backup image must be dismounted.

The default value is 4, and the value cannot be more than 24 hours. To prevent ItemPoint from launching after you run the mount operation, select Do not run ItemPoint after mounting

the backup images.

Selecting this option disables the Leave backup images mounted after ItemPoint exits option, and enables you to perform manual table-level restores.

To leave the backup images in the mounted state after you close ItemPoint, select Leave backup images mounted after ItemPoint exits.

114 Performing Self-Service Restores of SQL Virtual Machine Backups

This option is enabled only if you have not selected the Do not run ItemPoint after mounting the backup images option.

3. To generate detailed logs, which you can use to troubleshoot the restore issues, select Select a debug level, and then specify a value of 1 through 9. The default value is 0 (zero).

4. To delete debug logs older than a certain number of days, select Delete debug logs after, and then specify the number of days with a number between 1 and 32767 days. The default value is 0 and does not delete any debug logs.

NOTE: This option only deletes debug logs named in the default format and located in the logs folder at

\MSAPPAGENT\logs.

Regularly deleting debug logs prevents the log folder on the installation drive from becoming too large.

5. To limit the backup history of the databases on the General page to a set number of days, type or select a number in the Days of backup history field.

The default value is 7 days.

If no level full backups are found in the specified time range, you are prompted for confirmation to load the latest full backup.

6. Exclude client name resolution is selected by default, as required to use the client name that is provided as is without converting it to the fully qualified domain name (FQDN).

Use this option when you restore to a virtual machine in a different domain that does not have name resolution for the source client.

Unselect the option to resolve the source FQDN during restore.

7. To specify the PowerProtect appliance, perform the following steps:

a. Beside Appliance Host, click ... The PowerProtect Data Manager List & Lockbox Settings window appears.

When you restore a backup to the original virtual machine, the details for the PowerProtect appliance that protects the SQL Server in a protection policy automatically appear.

b. If you do not see the PowerProtect appliance that you want to use for the restore operation, click Add, and then in the Add PowerProtect Data Manager details dialog box, perform one of the following actions:

To add a PowerProtect with credential-based authentication, specify the appliance hostname and the login credentials.

Contact the PowerProtect administrator to retrieve the PowerProtect appliance host and login details.

To add a PowerProtect with certificate-based authentication, specify only the appliance hostname. Do not specify the username and password fields.

To use certificate-based authentication, the PowerProtect administrator must whitelist the target Microsoft application agent.

When you add a virtual machine to a protection policy, that virtual machine is whitelisted with a certificate that expires after one day. If you are restoring to an alternate virtual machine that is an asset of PowerProtect but not a member of a protection policy, the PowerProtect must whitelist the target virtual machine.

The PowerProtect administrator can approve (whitelist) the target Microsoft application agent on the PowerProtect Infrastructure > Application Agents page.

c. Select the PowerProtect appliance that will mount the backup, and then click OK.

8. To mount the backup and proceed with the table restore, click Run.

Monitor the table restore mount operation

After a table-level restore mount operation is run from the Microsoft app agent VM Direct SSMS plug-in, the Table Restore > Monitor page displays the mount script and status.

The following figure shows the mount information and status as it appears on the Monitor page.

Performing Self-Service Restores of SQL Virtual Machine Backups 115

Figure 26. VM Direct: Table Restore Monitor page

NOTE: For information about the success or failure of the restore operation, review the log files that are located in the

installation folder. The typical location of the log files is C:\Program Files\DPSAPPS\MSAPPAGENT\logs.

Restore table-level data using ItemPoint

After the backup images are mounted using the Microsoft application agent SSMS plug-in, complete the table-level restore using ItemPoint for Microsoft SQL Server.

Prerequisites

Configure and run the table level restore operation with the Table Restore tab in the Microsoft application agent SSMS plug-in.

About this task

Once the table level restore is configured and run through the Microsoft application agent SSMS plug-in, ItemPoint launches.

If you select Do not run ItemPoint after mounting the backup images when you configure the table level restore, you must manually launch ItemPoint, and then launch the Data Wizard.

Steps

1. On the Select Source page, select the source backup files from the mounted volume that contains the SQL backup data as shown in the following figure, and then click Next.

116 Performing Self-Service Restores of SQL Virtual Machine Backups

Figure 27. ItemPoint Data Wizard: select the source files

2. On the Select Target Server page, specify the details and login credentials for the target SQL Server and database.

The following figure shows the ItemPoint Data Wizard Select Target Server page:

Figure 28. ItemPoint Data Wizard: select the target server

3. Click Finish. The Data Wizard closes and ItemPoint loads the tables contained in the source backup files.

4. Use ItemPoint to browse and restore the individual tables.

NOTE: The PowerProtect ItemPoint for Microsoft SQL Server User Guide provides more information on using ItemPoint

for Microsoft SQL Server to restore table-level data.

5. To dismount the mounted backup images, exit ItemPoint for Microsoft SQL Server.

NOTE: If you select Leave backup images mounted after ItemPoint exits or Do not run ItemPoint after

mounting the backup images when you configure the mount operation, the backup image remains mounted for 4

hours. To dismount the backup manually, use the msagentadmin command.

Performing Self-Service Restores of SQL Virtual Machine Backups 117

Restore SQL Server tables with the VM Direct recover command

Learn how to perform a VM Direct SQL Server table-level restore operation using a command prompt.

Mount VM Direct backups for table-level recovery using the command prompt

Use the msagentrc.exe command with the -A NSR_RESTORE_TYPE=mount parameter to mount backups for table-level recovery.

Type the recover command with the following syntax to mount VM Direct backup images:

msagentrc.exe -A VM_DIRECT=TRUE -c [- A "SKIP_CLIENT_RESOLUTION=TRUE"] -A NSR_RESTORE_TYPE=mount -A "NSR_DFA_SI_DD_HOST= " -A "NSR_DFA_SI_DD_USER= " -A "NSR_DFA_SI_DEVICE_PATH= " -A "DM_HOST= " [- A "DM_USER= "] [- A "DM_PORT= "] [- A "DM_MOUNT_TIMEOUT= "] [-A "NSR_DFA_SI_DD_LOCKBOX_PATH= "] -I - -t

where:

-A VM_DIRECT=TRUE

Specifies to use the VM Direct workflow for the recover operation.

-c

Specifies the client name that was used for the backup.

-A "SKIP_CLIENT_RESOLUTION=TRUE"

(Optional) Specifies to use the client name as it is exactly entered with the -c option and to skip the FQDN lookup to automatically resolve the client name.

-A NSR_RESTORE_TYPE=mount

Specifies a mount operation

-A "NSR_DFA_SI_DD_HOST= "

Specifies the name of the Data Domain server that contains the backup.

When you have a remote (secondary) Data Domain server that has replicated databases to restore, type the name of the secondary server. A Data Domain user on the secondary Data Domain server must be in the same group as the primary server.

-A "NSR_DFA_SI_DD_USER= "

Specifies the username of the DD Boost user.

You must register the hostname and the DD Boost username in the lockbox to enable Microsoft application agent to retrieve the password for the registered user.

-A "NSR_DFA_SI_DEVICE_PATH= "

Specifies the name and the path of the storage unit that contains the backup.

-A "DM_HOST= "

Specifies the PowerProtect appliance hostname or IP address.

-A "DM_USER= "

(Optional) Specifies the username to use for the PowerProtect connection.

This parameter is mandatory only for credential-based access to the PowerProtect appliance. You are not required to specify this parameter when the Microsoft application agent is whitelisted by PowerProtect.

When you add a virtual machine to a Lifecycle group, that virtual machine is whitelisted with a certificate that expires after one day. The PowerProtect administrator can approve (whitelist) the target Microsoft application agent on the PowerProtect Infrastructure > Agents page.

-A "DM_PORT= "

118 Performing Self-Service Restores of SQL Virtual Machine Backups

(Optional) Specifies the port number to communicate with the PowerProtect server. The default value is 8443.

-A "DM_MOUNT_TIMEOUT= "

(Optional) Specifies the amount of time in minutes until the mount times out. The default value is 4 hours (240 minutes).

-A "NSR_DFA_SI_DD_LOCKBOX_PATH= "

(Optional) Specifies the folder that contains the lockbox file, which contains encrypted information about the registered hosts and the corresponding usernames in pairs. Each pair is associated with a password that backups use. If you do not specify a value, the path defaults to the installation path, which is typically C:\Program Files\DPSAPPS\common\lockbox.

-t

Specifies the save time of the backup. Specify this parameter along with the restore path for each database in the restore operation. The application pauses for you to input this option line by line. Press Enter to move to the next line. A blank line indicates completion of the list.

Specifies the path to the objects that you want to restore.

Type the backup path in one of the following formats: For a default instance, type the path in one of the following formats:

To restore a backup of the entire instance, type APPLICATIONS: \SqlServerWriter\

To restore a backup of specific databases, type APPLICATIONS: \SqlServerWriter\ \ \ [...]\

For example: "APPLICATIONS:\SqlServerWriter\LEDMF158\dbtest3" For a named instance, type the path in one of the following formats:

To restore a backup of the entire instance, type APPLICATIONS:\SqlServerWriter\ encoded_SQL_instance_name>

To restore a backup of specific databases, type APPLICATIONS:\SqlServerWriter\ encoded_SQL_instance_name>\ \ [...]\ The is typically %5C

For example: "APPLICATIONS:\SqlServerWriter\LEDMF158%5Cinst1\dbtest3"

Restore table-level data using ItemPoint

Steps

1. Launch the command prompt.

2. Run the following command:

C:\Program Files (x86)\DPSAPPS\MSAPPAGENT\ItemPoint\Sql\EMCIPSQL.exe

The ItemPoint for Microsoft SQL Server appears.

3. Launch the Data Wizard. The Data Wizard appears to the Select Source page.

4. On the Select Source page, select the source backup files from the mounted volume that contains the SQL backup data as shown in the following figure, and then click Next.

Performing Self-Service Restores of SQL Virtual Machine Backups 119

Figure 29. ItemPoint Data Wizard: select the source files

5. On the Select Target Server page, specify the details and login credentials for the target SQL Server and database.

The following figure shows the ItemPoint Data Wizard Select Target Server page:

Figure 30. ItemPoint Data Wizard: select the target server

6. Click Finish. The Data Wizard closes and ItemPoint loads the tables contained in the source backup files.

7. Use ItemPoint to browse and restore the individual tables.

NOTE: The PowerProtect ItemPoint for Microsoft SQL Server User Guide provides more information on using ItemPoint

for Microsoft SQL Server to restore table-level data.

Next steps

Dismount the backups using the msagentadmin.exe command.

120 Performing Self-Service Restores of SQL Virtual Machine Backups

Dismount the backup images

You must dismount the backup images after the table-level restore operation is complete.

To dismount the backup images, run the following command:

msagentadmin dismount --ddhost " " --ddpath " " --dduser " " --dmhost " " -dmport [--lockbox ]

where: dismount

Specifies an operation to dismount backups.

--ddhost " "

Specifies the name of the Data Domain server that contains the storage unit, to which you backed up the databases.

--ddpath " "

Specifies the name and the path of the storage unit, to which you backed up the databases.

--dduser " "

Specifies the username of the DD Boost user.

---dmhost " "

Specifies the PowerProtect appliance hostname or IP address.

--dmport

Specifies the port number to communicate with the PowerProtect server. The default value is 8443.

--lockbox

(Optional) Specifies the folder that contains the lockbox file, which contains encrypted information about the registered hosts and the corresponding usernames in pairs. Each pair is associated with a password that backups use. If you do not specify a value, the path defaults to the installation path, which is typically C:\Program Files\DPSAPPS\common\lockbox.

Example VM Direct dismount command

msagentadmin.exe dismount --ddhost mars.jupiter.emc.com --ddpath /PLC-PROTECTION-123456 --dduser PLC-PROTECTION-USER --dmhost 11.22.333.44 --dmport 8443 --lockbox "C:\Program Files\DPSAPPS\common\lockbox"

Performing an instant access recovery An instant access recovery enables you to quickly bring a database online from a point-in-time by running the database directly on protection storage, which is added to the host through a backup image mount.

Instant access recovery overview

Instant access recovery enables you to access a live-mounted SQL database from protection storage without restoring the virtual machine or SQL database.

This type of operation is useful for database administrators who must use or query a SQL database before restoring the database due to time and resource constraints.

This feature also supports the migration of mounted disks containing live mounted databases from protection storage to production datastore.

When you initiate instant access recovery, the operation locates the corresponding backup virtual disks and mounts them from the Data Domain protection storage. The operation locates the SQL Server database selected by the database administrator from mounted disks and connects the database to the SQL Server instance. If transaction logs were selected, the transaction logs are replayed against the instant access database.

Performing Self-Service Restores of SQL Virtual Machine Backups 121

Instant access recovery use cases

Instant access recovery to a SQL Server database is useful in a number of cases, including ad hoc queries, health checks, disaster recovery, and development tests.

Ad hoc queries

When you need a history of changes, perform an instant access restore to mount SQL databases for browsing and finding historical data.

With SQL instant access recovery, you can browse the history of changes in specific rows or tables quickly by running SQL queries on the mounted database. You can also execute SQL queries to move data from a mounted database to a live SQL database.

Health checks

You can validate the SQL database health by running health check queries on the live mounted database.

By performing health checks on the mounted database instead of the live database in the production environment, you can reduce the impact on production resources while checking the health status.

Disaster recovery

During a disaster recovery, SQL instant access recovery provides temporary access to SQL data while the database is down.

You can connect the live mounted database to a SQL Server instance for urgent queries.

Development tests

Merging changes to the production database is an operation with a risk of breaking applications.

With SQL instant access recovery, you can use a replica of the database to test changes before you upload them to the live production database.

Perform an instant access restore with the VM Direct SSMS plug- in

Learn how to perform a VM Direct SQL Server instant access restore operation using the Microsoft app agent for VM Direct SSMS plug-in.

Launching the Microsoft app agent for VM Direct SSMS plug-in

To launch the Microsoft app agent for VM Direct SSMS plug-in, perform the following steps: 1. From the Windows Start menu, select Microsoft SQL Server Management Studio. 2. In the Microsoft SQL Server Management Studio, in the Connect to Server window, specify the server information and

credentials, and then click Connect. 3. On the toolbar, click Microsoft app agent for VM Direct.

NOTE: If the Microsoft App Agent (VM Direct) button is not on the toolbar, the plug-in may be unable to register. In this

scenario, you can launch the plug-in directly from the Start menu. From the Windows Start menu, select DDBMA Plugin

for SQL Server Management Studio.

122 Performing Self-Service Restores of SQL Virtual Machine Backups

Configure general instant access settings

To configure SQL restores with the Microsoft app agent for VM Direct SSMS plug-in, you must first specify general restore options on the Instant Access > General page.

Steps

1. Open the Microsoft app agent for VM Direct window to the Instant Access > General page.

The General page appears as shown in the following figure.

Figure 31. VM Direct: Instant Access General page

2. Beside Data Domain Server, click ... The Data Domain Server List & Lockbox Settings window appears and lists the Data Domain server and lockbox that is configured in the PowerProtect protection policy. If the protection policy is configured for replication, the secondary Data Domain server and storage unit also appear.

3. In the Data Domain Server List & Lockbox Settings window, select the Data Domain server that contains the backup, and then click OK. The SQL Server host field is updated with the client backups that are available on the selected Data Domain server.

4. From the SQL Server host list, select the SQL Server host that is the source of the backup.

If you are restoring an AAG database, you must select the Windows cluster name, which enables you to browse the list of AAGs hosted on this cluster.

If you are restoring a clusterless AAG database, you must select the AAG group name, which enables you to browse the list of AAGs hosted on the clusterless AAG group. In the case of a clusterless AAG, the AAG name plus GUID is displayed.

5. Use the Start browsing from list to select a backup save time, and then click Show Versions.

The SQL Server Instance field and save sets table are updated with the backups that are within the specified browse time. If no level full backups are found in the specified time range, you are prompted for confirmation to load the latest full backup.

Performing Self-Service Restores of SQL Virtual Machine Backups 123

NOTE: If you selected a cluster name or AAG group name with GUID in the preceding step, then the list of AAGs in the

cluster or the list of AAGs in the AAG group named with GUID is displayed instead of the list of SQL Server instances.

6. From the SQL Server Instance list, select the SQL Server instance that contains the databases that you want to mount for instant access.

The databases located on the SQL Server instance appear in the database table below the SQL Server Instance field.

7. From the database table, select either the entire SQL Server instance by selecting the check box in the header row or select only the required databases to mount for instant access.

The corresponding save sets appear in the save sets table that is below the databases table.

NOTE: The Microsoft application agent does not support instant access for system databases. The system databases

do not appear in the database table.

8. For single database instant access, from the save sets table, select the database backup and timestamp that you want to mount for instant access. By default, the most recent backup is selected.

NOTE: This step only applies to single database instant access. For multiple database and instance-level operations, the

most recent backup is mounted and the most recent transaction logs for the browse time are restored.

9. (Optional) To access the database from a specific point-in-time, perform the following steps:

a. Under Access by time, click Timeline. The Backup Timeline dialog box appears as shown in the following figure.

Figure 32. Specifying the access point b. Specify the backup date and time, and then click OK.

10. Under Destination, specify the details for the mount operation:

a. From the Instance list, select the target SQL Server instance to mount the backup. b. From the Database list, select the name of the SQL Server database for instant access.

When you select a single database, you can specify a new name for the database. For instant access, a default name is generated, by appending the text "InstantAccess" and a date/time stamp to the original database name. For a single database restore, you may change this name. For a multiple database restore, you may not change the database name and the default database name is used.

Configure optional instant access settings

When you configure SQL database instant access restores with the Microsoft app agent for VM Direct SSMS plug-in, you can specify optional settings on the Instant Access > Options page.

About this task

Steps

1. From the left panel, click Options to specify optional instant access settings.

The Options page appears as shown in the following figure.

124 Performing Self-Service Restores of SQL Virtual Machine Backups

Figure 33. VM Direct: Instant Access Options page

2. Under Mount:

The Mount Folder field specifies the location where backup images are mounted.

NOTE:

The value in the Mount Folder field is the default mount location under the config directory of the Microsoft

application agent installation path, for example, C:\Program Files\DPSAPPS\MSAPPAGENT\config\mount.

Ensure that the SQL Server administrative account for the target SQL Server instance has access to this path so

that it can access the database files once they are mounted.

In Mount Timeout, specify the number of days after which the mounted backup image must be dismounted. The default value is 7 days.

NOTE: The maximum mount timeout setting is 7 days but can be extended up to 7 more days. After the mount

starts, you can extend the mount timeout on the Instant Access > Active Mounts page by specifying the number

of days in the Change mount timeout field and clicking Change. The specified number of days are added to the

initial timeout value that was set during the mount. Dismount the mounted database on page 128 provides more

details. To preserve any changes that you make to the database during the instant access active time, you can

vMotion the database.

3. To generate detailed logs, which you can use to troubleshoot the mount issues, select Select a debug level, and then specify a value of 1 through 9. The default value is 0 (zero).

4. To delete debug logs older than a certain number of days, select Delete debug logs after, and then specify the number of days with a number between 1 and 32767 days. The default value is 0 and does not delete any debug logs.

Performing Self-Service Restores of SQL Virtual Machine Backups 125

NOTE: This option only deletes debug logs named in the default format and located in the logs folder at

\MSAPPAGENT\logs.

Regularly deleting debug logs prevents the log folder on the installation drive from becoming too large.

5. To limit the backup history of the databases on the General page to a set number of days, type or select a number in the Days of backup history field.

The default value is 7 days.

If no level full backups are found in the specified time range, you are prompted for confirmation to load the latest full backup.

6. Exclude client name resolution is selected by default, as required to use the client name that is provided as is without converting it to the fully qualified domain name (FQDN).

Use this option when you restore to a virtual machine in a different domain that does not have name resolution for the source client.

Unselect the option to resolve the source FQDN during restore.

7. To specify the PowerProtect appliance, under Mount Method, perform the following steps:

a. Beside Appliance Host, click ...

The PowerProtect Data Manager List & Lockbox Settings window appears.

The details for the PowerProtect appliance that protects the SQL Server in a protection policy automatically appear. b. If you do not see the PowerProtect appliance that you want to use for the mount operation, click Add, and then in the

Add PowerProtect Data Manager details dialog box, perform one of the following actions:

To add an PowerProtect with credential-based authentication, specify the appliance hostname and the login credentials.

Contact the PowerProtect administrator to retrieve the PowerProtect appliance host and login details.

To add an PowerProtect with certificate-based authentication, specify only the appliance hostname. Do not specify the username and password fields.

To use certificate-based authentication, the PowerProtect administrator must whitelist the target Microsoft application agent.

The PowerProtect administrator can approve (whitelist) the target Microsoft application agent on the PowerProtect Infrastructure > Application Agents page.

c. Select the PowerProtect appliance that will mount the backup, and then click OK.

8. To mount the database for instant access, click Mount.

Monitor the instant access mount operation

After an instant access mount operation is run from the Microsoft app agent for VM Direct SSMS plug-in, the Instant Access > Monitor page displays the restore script and status.

NOTE: For information about the success or failure of the mount operation, review the log files that are located in the

installation folder. The typical location of the log files is C:\Program Files\DPSAPPS\MSAPPAGENT\logs.

Change the mount timeout

After the mount starts, you can change the mount timeout on the Instant Access > Active Mounts page by specifying the number of days in the Change mount timeout field and clicking Change. The mount timeout is changed by the specified number of days.

About this task

Starting with the Microsoft application agent 19.2, a msagentadmin process runs in the background with a keep_mount_alive option to keep the mount session active for the number of days specified by the user. When the user-specified mount period expires, the msagentadmin process removes the SQL databases and dismounts the instant

126 Performing Self-Service Restores of SQL Virtual Machine Backups

access session. After a machine reboot, the Microsoft application agent restarts the mount monitoring process to ensure that the user-specified mount period is maintained. For example, the following monitoring process runs in the background:

msagentadmin.exe keep_mount_alive --mountperiod --dmhost --dmport --lockbox "C:\Program Files\DPSAPPS\common\lockbox"

Steps

1. From the left panel, click Active Mounts.

The Active Mounts page appears as shown in the following figure.

Figure 34. VM Direct: Instant Access Active Mounts page

2. Use the Change mount timeout control to select the number of days by which you want to change the mount time.

3. Click Change.

A confirmation dialog box appears.

4. Click OK.

Results

The mount timeout is modified as shown in the following figure.

Performing Self-Service Restores of SQL Virtual Machine Backups 127

Figure 35. VM Direct: Instant Access Active Mounts page

Dismount the mounted database

After you mount a database for instant access, you can use the Instant Access > Active Mounts page to dismount the mounted database.

About this task

All settings on the Instant Access > Active Mounts page are optional. If you do not manually dismount the database, the database will be dismounted when the mount timeout period passes, as specified on the Instant Access > Options page.

Steps

1. From the left panel, click Active Mounts.

The Active Mounts page appears as shown in the following figure.

128 Performing Self-Service Restores of SQL Virtual Machine Backups

Figure 36. VM Direct: Instant Access Active Mounts page

The Active Mounts table displays the details for each active mount session.

2. From the Active Mounts table, select the backup that you want to dismount.

NOTE: All changes to the database will be lost. To preserve the changes, select to vMotion the database instead or

extend the mount timeout.

3. Click Dismount. The backup is dismounted.

Configure vMotion data movement

After you mount a database for instant access, use the Instant Access > Active Mounts page configure vMotion data movement.

About this task

NOTE: Once vMotion is started, you cannot cancel the vMotion data movement.

Steps

1. From the left panel, click Active Mounts.

The Active Mounts page appears as shown in the following figure.

Performing Self-Service Restores of SQL Virtual Machine Backups 129

Figure 37. VM Direct: Instant Access Active Mounts page

2. To update the table and list available datastores, click Show datastores.

3. Select the datastore to which you want the virtual disk to be migrated.

The vdisks will be allocated from the selected datastore. Ensure that the database administrator checks with the data center administrator or backup administrator about the appropriate datastore to select.

4. In the Disk Provisioning field, select the vdisk provisioning mode to be used for the vMotion operation.

5. Click Migrate.

Results

The virtual disk is migrated to the selected datastore by using storage vMotion. You can observe the progress in the Monitor window. Once the vMotion is complete, the mount is removed from the Active Mounts page as the databases are then on permanent VMDKs on the vCenter datastore.

130 Performing Self-Service Restores of SQL Virtual Machine Backups

Performing Centralized Restores of SQL Virtual Machine Backups

Topics:

Centralized restores of SQL Server virtual machine backups Considerations for SQL centralized restores Centralized restore of SQL system databases Centralized restore of a SQL stand-alone database Centralized restore of a SQL AAG database Centralized restore of multiple SQL databases Troubleshooting of SQL centralized restore operations

Centralized restores of SQL Server virtual machine backups When SQL Server data is backed up as part of a SQL virtual machine application-aware protection policy in PowerProtect Data Manager, you can recover the SQL Server virtual machine backups by using the centralized restore functionality in the PowerProtect Data Manager UI.

You can perform the following types of centralized restores of SQL virtual machine backups, depending on the type of database assets:

Centralized restore of a system database Centralized restore of a stand-alone database Centralized restore of an Always On availability group (AAG) database

You can restore single or multiple databases from the same SQL host and instance. You can restore the databases either to the original SQL host or to an alternate SQL host with the following requirements:

The alternate host must be a SQL virtual machine. The Microsoft application agent software must be installed and configured on the alternate host, as described in Application

agent manual installation and configuration on page 132. You cannot restore a system database to an alternate host or SQL instance.

You must perform all centralized restores from the Recovery > Assets > SQL window in the PowerProtect Data Manager UI. You can perform a centralized restore of a full or transaction log backup to a specified SQL host and instance. Select one of the following restore options to specify the file system location where the databases are restored:

Restore database files to the original file location (location at backup time)Restores the backup data to the file directory that was used during the backup and overwrites the existing contents.

NOTE: If the directory path cannot be created during the centralized restore, the restore fails.

Restore database files to the default file location as set by SQLRestores the backup data to the default file directory as used by the SQL Server.

Restore database files to a user-specified file locationRestores the backup data to file directories that you specify for the database files and log files.

The following topics describe the considerations, prerequisites, and procedures for the supported types of centralized restores of SQL virtual machine backups.

7

Performing Centralized Restores of SQL Virtual Machine Backups 131

Considerations for SQL centralized restores Ensure that you review the following information before you perform the centralized restores of SQL application-aware virtual machine backups.

The centralized restore of multiple SQL databases supports the following use cases:

Performing disaster recovery of the original SQL instance. Performing a restore rehearsal by restoring a SQL instance database to an alternate host to validate the backups.

For disaster recovery to the original host, you can select all the databases for the SQL instance. Once the restore job starts, the application agent automates the disaster recovery procedures as outlined by Microsoft. The disaster recovery includes a restart of the SQL Server instance in single user mode to restore the master database.

For disaster recovery to an alternate host, ensure that the alternate host is a SQL virtual machine that is a discovered asset of PowerProtect Data Manager.

NOTE: During the centralized restore to an alternate host, if the alternate host is not included in the list of available hosts,

follow the instructions in Application agent manual installation and configuration on page 132 to ensure that the application

agent is properly installed and configured.

PowerProtect Data Manager applies the following concurrency rules for jobs associated with either a single-database or multi-database centralized restore, including any backup, restore, and manual agent installation jobs that you run on the same virtual machine, VM1:

If an agent installation for VM1 is in progress, the VM1 backup is queued and the SQL restore against target VM1 is queued. If a VM1 backup is in progress, the agent installation for VM1 is queued and the SQL restore against target VM1 is queued. If the SQL restore against target VM1 is in progress, the agent installation for VM1 is queued and the VM1 backup is queued.

Application agent manual installation and configuration

In either of the following cases, you must manually install and configure the Microsoft application agent on the target virtual machine host of the restore:

The PowerProtect Data Manager has been upgraded. You want to restore to a virtual machine that is not part of a protection policy.

Ensure that the target virtual machine meets the following prerequisites:

A Windows OS is running. The VMware Tools are installed at the same version as required for backup. The SQL Server is installed and running. The SYSTEM account has the same security configuration as required for backup. The network ports are configured as required for backup.

Perform the following steps to manually install and configure the application agent:

1. Ensure that the Windows account credentials for the virtual machine are set at the virtual machine asset level, not the protection policy level.

To set the credentials at the asset level, select Infrastructure > Assets > Virtual Machine, select the virtual machine, and select More Actions > Set Credential.

2. To install the application agent, select Infrastructure > Assets > Virtual Machine, select the virtual machine, and select More Actions > Install Agent.

After you confirm to start the installation, you can monitor the installation job status on the Jobs page.

Centralized restore of SQL system databases You can perform a centralized restore of a full backup of a SQL system database in the PowerProtect Data Manager UI.

Steps

1. In the PowerProtect Data Manager UI, select Recovery > Assets and select the SQL tab.

132 Performing Centralized Restores of SQL Virtual Machine Backups

The Recovery window displays all the databases that are available for recovery.

To filter the displayed list of assets if needed, you can click the Host/Cluster/Group and Application Name column headings:

The Host/Cluster/Group column lists the hostnames. The Application Name column lists the SQL instance names.

NOTE: Only the assets that were created by a virtual machine application-aware protection policy and have at least one

copy are displayed. You can select assets only from the same SQL host and instance.

2. Select the checkbox next to the master, model, or msdb database, and click Restore.

The Recovery wizard opens on the Select Copy page.

3. On the Select Copy page, click the DD icon in the left pane. You can select the primary or secondary DD.

The right pane displays the available backup copies.

4. In the right pane, select the checkbox next to the full backup copy that you want to restore, and then click Next.

5. On the Select Destination page, the Host, SQL Instance, and Database fields are prepopulated for the system database. You cannot edit the field values on the page.

NOTE: You can only restore a system database to the original host and instance, overwriting the source database.

Click Next to continue.

6. On the Select File Location page, select one of the following options, and then click Next:

Restore database files to the original file location (location at backup time)

NOTE: If the directory path cannot be created during the centralized restore, the restore fails.

Restore database files to the default file location as set by SQL Restore database files to a user-specified file location

NOTE: When you select this option, you must specify the restore file directories for the database files and log files.

7. On the Select Options page, select any the following options, and then click Next:

NOTE: The tail-log backup option is not supported for a system database.

Overwrite DatabaseEnables the overwrite of the existing database.

NOTE: When the database exists but the Overwrite Database option is not selected, the restore fails.

TroubleshootingEnables the debug log, setting the debug level to a value of 9. Recovery StateSelect one of the following options:

RESTORE WITH RECOVERYLeaves the database ready to use by rolling back the uncommitted transactions. Additional transaction logs cannot be restored.

RESTORE WITH NORECOVERYLeaves the database nonoperational and does not roll back the uncommitted transactions. Additional transaction logs can be restored.

8. On the Summary page:

a. Review the Source, Destination, File Location, and Options information to ensure that the restore details are correct.

NOTE: When the specified database name matches the name of an existing database, the restore overwrites the

existing database.

b. Click Restore.

The restore operation starts. Then the Go to Jobs informational dialog box appears with a link to the Jobs page where you can monitor the restore job.

Performing Centralized Restores of SQL Virtual Machine Backups 133

Centralized restore of a SQL stand-alone database You can perform a centralized restore of a full or transaction log backup of a SQL stand-alone database in the PowerProtect Data Manager UI. The following procedure restores a single database.

Steps

1. In the PowerProtect Data Manager UI, select Recovery > Assets and select the SQL tab.

The Recovery window displays all the databases that are available for recovery.

To filter the displayed list of assets if needed, you can click the Host/Cluster/Group and Application Name column headings:

The Host/Cluster/Group column lists the hostnames. The Application Name column lists the SQL instance names.

NOTE: Only the assets that were created by a virtual machine application-aware protection policy and have at least one

copy are displayed. You can select assets only from the same SQL host and instance.

2. Select the checkbox next to the stand-alone database, and click Restore.

The Recovery wizard opens on the Select Copy page.

3. On the Select Copy page, click the DD icon in the left pane. You can select the primary or secondary DD.

The right pane displays the available backup copies.

4. In the right pane, select the checkbox next to the backup copy that you want to restore.

NOTE: You can select only one backup copy at a time, either Log or Full. The Selected Copy Time appears at the top

of the right pane.

To see the log copies, click the > symbol beside a full copy, which displays a list of the available log copies. When you select a log copy, you can optionally click Select Point in Time and follow the prompts to specify a point-in-time restore. When you select a point-in-time, the Selected Point in Time also appears at the top of the right pane.

Click Next to continue.

5. On the Select Destination page, the Host, SQL Instance, and Database fields are prepopulated, but you can click Select for each field to specify the host, instance, and database values as needed. You may choose to restore to an existing database name or type a new database name.

NOTE: The database name must have 128 or fewer characters. Do not specify a system database name such as master,

model, msdb, or tempdb.

Click Next to continue.

6. On the Select File Location page, select one of the following options, and then click Next:

Restore database files to the original file location (location at backup time)

NOTE: If the directory path cannot be created during the centralized restore, the restore fails.

Restore database files to the default file location as set by SQL Restore database files to a user-specified file location

NOTE: When you select this option, you must specify the restore file directories for the database files and log files.

7. On the Select Options page, select any the following options, and then click Next:

Overwrite DatabaseEnables the overwrite of the existing database.

NOTE: When the database exists but the Overwrite Database option is not selected, the restore fails.

Tail LogPerforms a tail-log backup.

NOTE: The tail-log backup option is displayed when the destination is the same as the host. This option is selected

by default.

TroubleshootingEnables the debug log, setting the debug level to a value of 9. Recovery StateSelect one of the following options:

RESTORE WITH RECOVERYLeaves the database ready to use by rolling back the uncommitted transactions. Additional transaction logs cannot be restored.

134 Performing Centralized Restores of SQL Virtual Machine Backups

RESTORE WITH NORECOVERYLeaves the database nonoperational and does not roll back the uncommitted transactions. Additional transaction logs can be restored.

8. On the Summary page:

a. Review the Source, Destination, File Location, and Options information to ensure that the restore details are correct.

NOTE: When the specified database name matches the name of an existing database, the restore overwrites the

existing database.

b. Click Restore.

The restore operation starts. Then the Go to Jobs informational dialog box appears with a link to the Jobs page where you can monitor the restore job.

Centralized restore of a SQL AAG database You can perform a centralized restore of a full or transaction log backup of a SQL Always On availability group (AAG) database in the PowerProtect Data Manager UI. The following procedure restores a single database.

Steps

1. In the PowerProtect Data Manager UI, select Recovery > Assets and select the SQL tab.

The Recovery window displays all the databases that are available for recovery.

To filter the displayed list of assets if needed, you can click the Host/Cluster/Group and Application Name column headings:

The Host/Cluster/Group column lists the hostnames. The Application Name column lists the SQL instance names.

NOTE: Only the assets that were created by a virtual machine application-aware protection policy and have at least one

copy are displayed. You can select assets only from the same SQL host and instance.

2. Select the checkbox next to the AAG database, and click Restore.

NOTE: You cannot restore a database that is currently part of an AAG. To enable the database restore, you must

remove the database from the AAG on the SQL server.

The Recovery wizard opens on the Select Copy page.

3. On the Select Copy page, click the DD icon in the left pane. You can select the primary or secondary DD.

The right pane displays the available backup copies.

4. In the right pane, select the checkbox next to the backup copy that you want to restore.

NOTE: You can select only one backup copy at a time, either Log or Full. The Selected Copy Time appears at the top

of the right pane.

To see the log copies, click the > symbol beside a full copy, which displays a list of the available log copies. When you select a log copy, you can optionally click Select Point in Time and follow the prompts to specify a point-in-time restore. When you select a point-in-time, the Selected Point in Time also appears at the top of the right pane.

Click Next to continue.

5. On the Select Destination page, click Select for each of the Host, SQL Instance, and Database fields to specify the required host, instance, and database values. You may choose to restore to an existing database name or type a new database name.

NOTE: The database name must have 128 or fewer characters. Do not specify a system database name such as master,

model, msdb, or tempdb.

Click Next to continue.

6. On the Select File Location page, select one of the following options, and then click Next:

Restore database files to the original file location (location at backup time)

NOTE: If the directory path cannot be created during the centralized restore, the restore fails.

Restore database files to the default file location as set by SQL

Performing Centralized Restores of SQL Virtual Machine Backups 135

Restore database files to a user-specified file location

NOTE: When you select this option, you must specify the restore file directories for the database files and log files.

7. On the Select Options page, select any the following options, and then click Next:

NOTE: The tail-log backup option is not supported for an AAG configuration.

Overwrite DatabaseEnables the overwrite of the existing database.

NOTE: When the database exists but the Overwrite Database option is not selected, the restore fails.

TroubleshootingEnables the debug log, setting the debug level to a value of 9. Recovery StateSelect one of the following options:

RESTORE WITH RECOVERYLeaves the database ready to use by rolling back the uncommitted transactions. Additional transaction logs cannot be restored.

RESTORE WITH NORECOVERYLeaves the database nonoperational and does not roll back the uncommitted transactions. Additional transaction logs can be restored.

8. On the Summary page:

a. Review the Source, Destination, File Location, and Options information to ensure that the restore details are correct.

NOTE: When the specified database name matches the name of an existing database, the restore overwrites the

existing database.

b. Click Restore.

The restore operation starts. Then the Go to Jobs informational dialog box appears with a link to the Jobs page where you can monitor the restore job.

Centralized restore of multiple SQL databases You can perform a centralized restore of the full or transaction log backups of multiple SQL databases in the PowerProtect Data Manager UI.

Prerequisites

Before you perform a multi-database restore, review the Considerations for SQL centralized restores on page 132.

About this task

The centralized restore of multiple databases includes the following restrictions:

A multi-database restore can restore only the most recent database backups.

If you need to restore older backup copies of multiple databases, then perform a single database restore of each older backup copy, one at a time.

A multi-database restore can restore only to the original database names.

If you need to restore multiple databases and rename the databases, then perform a single database restore of each backup copy, one at a time.

A multi-database restore does not support the tail-log backup option.

Multiple databases are restored serially (one at time).

A multi-database restore also supports partial success:

If a given database exists on the target host and the overwrite option is not specified, the database restore is not attempted and the next database restore is attempted.

If the restore fails for a given database, the next database restore is attempted.

Steps

1. In the PowerProtect Data Manager UI, select Recovery > Assets and select the SQL tab.

The Recovery window displays all the databases that are available for recovery.

136 Performing Centralized Restores of SQL Virtual Machine Backups

To filter the displayed list of assets if needed, you can click the Host/Cluster/Group and Application Name column headings:

The Host/Cluster/Group column lists the hostnames. The Application Name column lists the SQL instance names.

NOTE: Only the assets that were created by a virtual machine application-aware protection policy and have at least one

copy are displayed. You can select assets only from the same SQL host and instance.

2. Select the checkbox next to each database that you want to restore, or in a filtered view, select multiple databases through a checkbox in a column heading, and then click Restore.

The Recovery wizard opens on the Select Copy page.

3. On the Select Copy page, click the DD icon in the left pane for the primary or secondary source Data Domain.

The right pane displays a message that the restore operation uses the latest copy on the storage target, when available. The most recent full or transaction log backup copy is restored.

NOTE: If a transaction log backup is the most recent copy, the entire backup chain from the base full backup is

restored. If you need to restore older backup copies, use a single database restore to restore the backup copies one at a

time.

Click Next to continue.

4. On the Select Destination page, the Host and SQL Instance fields are prepopulated, but you can click Select for each field to specify the host and instance as needed.

NOTE: System databases (master, model, msdb) can only be restored to the original host and SQL instance. If the

required host is not listed, follow the instructions in Application agent manual installation and configuration on page 132.

Click Next to continue.

5. On the Select File Location page, select one of the following options, and then click Next:

Restore database files to the original file location (location at backup time)

NOTE: If the directory path cannot be created during the centralized restore, the restore fails.

Restore database files to the default file location as set by SQL Restore database files to a user-specified file location

NOTE: When you select this option, you must specify the restore file directories for the database files and log files.

6. On the Select Options page, select any the following options, and then click Next:

Overwrite DatabaseEnables the overwrite of the existing database.

NOTE: When the database exists but the Overwrite Database option is not selected, the restore fails.

TroubleshootingEnables the debug log, setting the debug level to a value of 9. Recovery StateSelect one of the following options:

RESTORE WITH RECOVERYLeaves the database ready to use by rolling back the uncommitted transactions. Additional transaction logs cannot be restored.

RESTORE WITH NORECOVERYLeaves the database nonoperational and does not roll back the uncommitted transactions. Additional transaction logs can be restored.

7. On the Summary page:

a. Review the Source, Destination, File Location, and Options information to ensure that the restore details are correct.

NOTE: When the specified database name matches the name of an existing database, the restore overwrites the

existing database.

b. Click Restore.

The restore operation starts. Then the Go to Jobs informational dialog box appears with a link to the Jobs page where you can monitor the restore job.

Performing Centralized Restores of SQL Virtual Machine Backups 137

Troubleshooting of SQL centralized restore operations You can use the information in the following topics to troubleshoot issues with the SQL centralized restore operations.

Troubleshooting the PowerProtect agent service registration

With a virtual machine that is part of a protection policy, the failure of the PowerProtect agent service registration causes the virtual machine asset configuration to also fail. Despite these failures:

The protection policy backups continue to run. The virtual machine configuration job continues to run if the virtual machine asset configuration did not fail for other

reasons.

You can perform the following steps to troubleshoot these failures:

1. Review the job details for the virtual machine configuration in PowerProtect Data Manager. 2. Enable the Microsoft application agent debugging on the client. Run the following command on the command line, where

is the debug level from 1 to 9. The default debug level is 0:

msagentcon --administration --debug=

When you set the debug level to a value from 1 to 9, the Microsoft application agent generates the debug logs on the virtual machine when the PowerProtect protection group runs. To deactivate the debug logs, set the debug level to 0.

3. After the next failed job, inspect the following logs:

Microsoft application agent diagnostic logs in C:\Program Files\DPSAPPS\MSAPPAGENT\bin\msagentcon.XXXXX.log

Agent service log in agentsvc.log in the agent service home directory on the SQL host

ADM log from PowerProtect in C:\Program Files\DPSAPPS\AgentService\logs\OPAgentSVC.xxx.log

Troubleshooting the PowerProtect Data Manager restores

You can perform the following tasks to troubleshoot issues with the PowerProtect Data Manager restores:

Inspect the ADM logs in /var/log/brs/adm/adm.log in PowerProtect Data Manager.

Inspect the Microsoft application agent logs in C:\Program Files\DPSAPPS\MSAPPAGENT\logs.

Enable advanced debugging output for the PowerProtect agent service by setting the DEBUG

Manualsnet FAQs

If you want to find out how the 19.6 Dell works, you can view and download the Dell PowerProtect 19.6 Data Manager SQL Server User Guide on the Manualsnet website.

Yes, we have the SQL Server User Guide for Dell 19.6 as well as other Dell manuals. All you need to do is to use our search bar and find the user manual that you are looking for.

The SQL Server User Guide should include all the details that are needed to use a Dell 19.6. Full manuals and user guide PDFs can be downloaded from Manualsnet.com.

The best way to navigate the Dell PowerProtect 19.6 Data Manager SQL Server User Guide is by checking the Table of Contents at the top of the page where available. This allows you to navigate a manual by jumping to the section you are looking for.

This Dell PowerProtect 19.6 Data Manager SQL Server User Guide consists of sections like Table of Contents, to name a few. For easier navigation, use the Table of Contents in the upper left corner.

You can download Dell PowerProtect 19.6 Data Manager SQL Server User Guide free of charge simply by clicking the “download” button in the upper right corner of any manuals page. This feature allows you to download any manual in a couple of seconds and is generally in PDF format. You can also save a manual for later by adding it to your saved documents in the user profile.

To be able to print Dell PowerProtect 19.6 Data Manager SQL Server User Guide, simply download the document to your computer. Once downloaded, open the PDF file and print the Dell PowerProtect 19.6 Data Manager SQL Server User Guide as you would any other document. This can usually be achieved by clicking on “File” and then “Print” from the menu bar.