Computer Associates SQLSTQ99000600 - Unicenter SQL-Station 6.0.0, ARCserve Backup User Manual

CA ARCserve® Backup for Windows

Agent for Microsoft SQL Server Guide

r15

This documentation and any related computer software help programs (hereinafter referred to as the "Documentation") are for your informational purposes only and are subject to change or withdrawal by CA at any time.

This Documentation may not be copied, transferred, reproduced, disclosed, modified or duplicated, in whole or in part, without the prior written consent of CA. This Documentation is confidential and proprietary information of CA and may not be used or disclosed by you except as may be permitted in a separate confidentiality agreement between you and CA.

Notwithstanding the foregoing, if you are a licensed user of the software product(s) addressed in the Documentation, you may print a reasonable number of copies of the Documentation for internal use by you and your employees in connection with that software, provided that all CA copyright notices and legends are affixed to each reproduced copy.

The right to print copies of the Documentation is limited to the period during which the applicable license for such software remains in full force and effect. Should the license terminate for any reason, it is your responsibility to certify in writing to CA that all copies and partial copies of the Documentation have been returned to CA or destroyed.

TO THE EXTENT PERMITTED BY APPLICABLE LAW, CA PROVIDES THIS DOCUMENTATION "AS IS" WITHOUT WARRANTY OF ANY KIND, INCLUDING WITHOUT LIMITATION, ANY IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, OR NONINFRINGEMENT. IN NO EVENT WILL CA BE LIABLE TO THE END USER OR ANY THIRD PARTY FOR ANY LOSS OR DAMAGE, DIRECT OR INDIRECT, FROM THE USE OF THIS DOCUMENTATION, INCLUDING WITHOUT LIMITATION, LOST PROFITS, LOST INVESTMENT, BUSINESS INTERRUPTION, GOODWILL, OR LOST DATA, EVEN IF CA IS EXPRESSLY ADVISED IN ADVANCE OF THE POSSIBILITY OF SUCH LOSS OR DAMAGE.

The use of any software product referenced in the Documentation is governed by the applicable license agreement and is not modified in any way by the terms of this notice.

The manufacturer of this Documentation is CA.

Provided with "Restricted Rights." Use, duplication or disclosure by the United States Government is subject to the restrictions set forth in FAR Sections 12.212, 52.227-14, and 52.227-19(c)(1) - (2) and DFARS Section

252.227-7014(b)(3), as applicable, or their successors.

CA Product References

This documentation set references the following CA products:

■

BrightStor® Enterprise Backup

■

CA Antivirus

■

CA ARCserve® Assured Recovery™

■

CA ARCserve® Backup Agent for Advantage™ Ingres®

■

CA ARCserve® Backup Agent for Novell Open Enterprise Server for Linux

■

CA ARCserve® Backup Agent for Open Files on NetWare

■

CA ARCserve® Backup Agent for Open Files on Windows

■

CA ARCserve® Backup Client Agent for FreeBSD

■

CA ARCserve® Backup Client Agent for Linux

■

CA ARCserve® Backup Client Agent for Mainframe Linux

■

CA ARCserve® Backup Client Agent for NetWare

■

CA ARCserve® Backup Client Agent for UNIX

■

CA ARCserve® Backup Client Agent for Windows

■

CA ARCserve® Backup Enterprise Option for AS/400

■

CA ARCserve® Backup Enterprise Option for Open VMS

■

CA ARCserve® Backup for Microsoft Windows Essential Business Server

■

CA ARCserve® Backup for Windows

■

CA ARCserve® Backup for Windows Agent for IBM Informix

■

CA ARCserve® Backup for Windows Agent for Lotus Domino

■

CA ARCserve® Backup for Windows Agent for Microsoft Exchange Server

■

CA ARCserve® Backup for Windows Agent for Microsoft SharePoint Server

■

CA ARCserve® Backup for Windows Agent for Microsoft SQL Server

■

CA ARCserve® Backup for Windows Agent for Oracle

■

CA ARCserve® Backup for Windows Agent for Sybase

■

CA ARCserve® Backup for Windows Agent for Virtual Machines

■

CA ARCserve® Backup for Windows Disaster Recovery Option

■

CA ARCserve® Backup for Windows Enterprise Module

■

CA ARCserve® Backup for Windows Enterprise Option for IBM 3494

■

CA ARCserve® Backup for Windows Enterprise Option for SAP R/3 for Oracle

■

CA ARCserve® Backup for Windows Enterprise Option for StorageTek ACSLS

■

CA ARCserve® Backup for Windows Image Option

■

CA ARCserve® Backup for Windows Microsoft Volume Shadow Copy Service

■

CA ARCserve® Backup for Windows NDMP NAS Option

■

CA ARCserve® Backup for Windows Serverless Backup Option

■

CA ARCserve® Backup for Windows Storage Area Network (SAN) Option

■

CA ARCserve® Backup for Windows Tape Library Option

■

CA ARCserve® Backup Patch Manager

■

CA ARCserve® Backup UNIX and Linux Data Mover

■

CA ARCserve® D2D

■

CA ARCserve® High Availability

■

CA ARCserve® Replication

■

CA VM:Tape for z/VM

■

CA 1® Tape Management

■

Common Services™

■

eTrust® Firewall

■

Unicenter® Network and Systems Management

■

Unicenter® Software Delivery

■

Unicenter® VM:Operator®

Contact CA

Contact Technical Support

For your convenience, CA provides one site where you can access the information you need for your Home Office, Small Business, and Enterprise CA products. At CA ARCserve Backup Support, you can access the following:

■

Online and telephone contact information for technical assistance and customer services

■

Information about user communities and forums

■

Product and documentation downloads

■

CA Support policies and guidelines

■

Other helpful resources appropriate for your product

Provide Feedback

If you have comments or questions about CA product documentation, you can send a message to techpubs@ca.com.

If you would like to provide feedback about CA product documentation, complete our short customer survey, which is also available on the CA Support website, found at http://ca.com/docs.

Documentation Changes

The following documentation updates have been made since the last release of this documentation:

■

Compatibility Matrix (see page 16) --Shows the features of Microsoft SQL

Server supported in CA ARCserve Backup, by version.

■

Planning a Backup and Recovery Strategy (see page 107)--Presents

essential Microsoft SQL Server basics you should know before you use CA ARCserve Backup, describes new SQL Server 2008 features, and provides a series of checklists to facilitate the restore process. Also included in this section is a revised Backup Manager Agent Options topic.

■

Restoring Microsoft SQL Server Databases (see page 45)--This section no

longer contains topics on Microsoft SQL Server 7.0 Cluster Environments. SQL 7.0 and NT 4 Clusters are no longer supported by CA ARCserve Backup Agents effective with r12.

Contents

Chapter 1: Introducing the Agent 11

Introduction .................................................................................. 11

Architectural Overview ........................................................................ 12

How the Agent Works ......................................................................... 13

How a Backup Request Works ............................................................. 13

How a Restore Request Works ............................................................. 14

How Data Flows During Backup ............................................................ 14

Agent Services ........................................................................... 14

Access Requirements ..................................................................... 15

Backup and Restore Options ............................................................... 15

Microsoft SQL - CA ARCserve Backup Compatibility Matrix ....................................... 16

Agent Activity Log ............................................................................ 17

Online Backup of SAP R/3 Databases .......................................................... 17

Agent for ARCserve Database ................................................................. 18

Chapter 2: Installing the Agent 21

Installation Prerequisites ...................................................................... 21

Basic Prerequisites ........................................................................ 21

Microsoft SQL Server 2000, 2005 and 2008 Cluster Environment Prerequisites ................ 21

Installation Considerations .................................................................... 23

Install the Agent .............................................................................. 24

Install the Agent in a Standard Microsoft SQL Server Environment ........................... 24

Install the Agent in a Microsoft SQL Server 2000 or Later Cluster Environment ................ 25

Post-Installation Procedures ................................................................... 25

Configure Backup and Restore Parameters for the Agent for Microsoft SQL Server ............. 26

How to Uninstall the CA ARCserve Backup Agent for Microsoft SQL ............................... 28

Chapter 3: Backing Up Microsoft SQL Server Databases 29

Backup Overview ............................................................................. 29

Backup Manager Agent Options ............................................................ 30

Dynamic and Explicit Job Packaging ............................................................ 38

Dynamic Job Packaging ................................................................... 39

Mark Objects for Dynamic Job Packaging ................................................... 40

Explicit Job Packaging ..................................................................... 40

Mark Objects for Explicit Job Packaging..................................................... 41

Back Up a Database .......................................................................... 42

Contents 7

Chapter 4: Restoring Microsoft SQL Server Databases 45

Restore Options .............................................................................. 46

Automatic Selection Option ................................................................ 47

Restore Types ............................................................................ 47

Log Point-in-Time Restore Options ......................................................... 50

Recovery Completion State Options ........................................................ 51

Database Consistency Check (DBCC) Options ............................................... 52

Force Restore to use Named Pipes ......................................................... 53

Continue Restore After Checksum Failure ................................................... 53

Miscellaneous ............................................................................. 53

Database File Options ......................................................................... 54

Restore Databases Using Restore by Tree Method .............................................. 57

Restore Databases Using Restore by Session Method ........................................... 60

SQL Agent Filter Options ...................................................................... 63

Restore to Alternate Disk Locations Using Automatic Selection ................................... 63

Restore to Alternate Disk Locations by Individual Session ....................................... 64

Restore by Session Using a Single Restore Job .............................................. 64

Restore by Session Using a Separate Job for Each Session ................................... 65

Restore by Tree Using a Separate Job for Each Session ...................................... 66

Perform an Offline Torn Page Restore Using Microsoft SQL Server 2005 or 2008 .................. 68

Perform an Online Torn Page Restore Using Microsoft SQL Server 2005 or 2008 Enterprise, Data

Center, or Developer Editions ................................................................. 70

Chapter 5: Backing Up and Restoring in Cluster Environments 73

Backup and Restore Considerations in Microsoft SQL Server Cluster Environments ................ 73

Microsoft SQL Server 2000, 2005 and 2008 Cluster Environment Prerequisites.................... 74

Microsoft SQL Server 2000, 2005, or 2008 Cluster Environment Backups ......................... 74

Select Server, Protocol, Security, and Backup Type ................................ ......... 74

Select Backup Destination, Schedule, and Submit the Job ................................... 76

Restore by Tree in Microsoft SQL Server 2000, 2005 or 2008 Cluster Environments ............... 77

Restore by Session in Microsoft SQL Server 2000, 2005 or 2008 Environments ................... 80

Perform Disaster Recovery in Microsoft SQL Server 2000, 2005, or 2008 Cluster Environments .... 83

Appendix A: Troubleshooting and Disaster Recovery 85

General Considerations for CA ARCserve Backup and the Agent .................................. 86

Upgrade Considerations for the Agent for Microsoft SQL ......................................... 88

Skip or Include Database Files in Backups ...................................................... 89

Agent and CA ARCserve Backup Error Messages ................................................ 91

Microsoft SQL Server Error Messages .......................................................... 93

Limitations of Microsoft SQL Server Database ............................................... 95

Replication of Microsoft SQL Server ............................................................ 95

8 Agent for Microsoft SQL Server Guide

Configure Agent Behavior for Databases Ineligible for Backup ................................... 96

Additional Database Status Considerations ................................................. 98

Microsoft SQL Server Disaster Recovery ........................................................ 98

The Master Database ...................................................................... 98

Disaster Recovery Scenario ............................................................... 100

Appendix B: Configuring Microsoft SQL Server Security Settings 101

Types of Microsoft SQL Server Authentication ................................................. 101

Authentication Requirements ................................................................. 101

How User Authentication Is Changed .......................................................... 102

Check or Change the Microsoft SQL Server Authentication Method .......................... 102

Update the Agent Account Configuration .................................................. 103

Check and Change the ODBC Settings ..................................................... 104

Update the Backup Manager .............................................................. 105

Appendix C: Backup and Recovery Best Practices 107

Microsoft SQL Server Database Basics ........................................................ 108

About Files and FileGroups ............................................................... 108

Performance Improvement Tips ........................................................... 111

Recovery Models ......................................................................... 113

System Databases ....................................................................... 114

Backup and Restore Considerations in Microsoft SQL Server Cluster Environments ........... 115

Backup Concepts ............................................................................ 115

Backup Considerations ................................................................... 116

Restore Overview ............................................................................ 123

Restore Types and Methods .............................................................. 124

Checklists for Dependencies, by Type ..................................................... 125

Microsoft SQL Server Disaster Recovery Elements .......................................... 131

Restore Master Databases ................................................................ 132

Index 133

Contents 9

Chapter 1: Introducing the Agent

This section contains the following topics:

Introduction (see page 11) Architectural Overview (see page 12) How the Agent Works (see page 13) Microsoft SQL - CA ARCserve Backup Compatibility Matrix (see page 16) Agent Activity Log (see page 17) Online Backup of SAP R/3 Databases (see page 17)

Introduction

Agent for ARCserve Database (see page 18)

CA ARCserve Backup is a comprehensive, distributed storage solution for applications, databases, distributed servers, and file systems. It provides backup and restore capabilities for databases, business-critical applications, and network clients.

Among the agents CA ARCserve Backup offers is the CA ARCserve Backup Agent for Microsoft SQL Server. This agent enables you to perform the following actions:

■

Back up your Microsoft SQL Server databases using CA ARCserve Backup without taking your database off-line or preventing users from adding new data

■

Manage backups of Microsoft SQL Server databases remotely

■

Schedule backups

■

Back up to a wide array of media storage devices

■

Restore Microsoft SQL Server databases using CA ARCserve Backup

The agent handles all communications between CA ARCserve Backup and Microsoft SQL Server during backup and restore jobs, including preparing, retrieving, and processing the data packets that are sent back and forth between Microsoft SQL Server and CA ARCserve Backup.

Chapter 1: Introducing the Agent 11

Architectural Overview

You can install CA ARCserve Backup on the same host as the Agent for Microsoft SQL Server for local operation, or on separate systems. A single installation of CA ARCserve Backup can work with agents on many systems, allowing multiple machines to be backed up by a single backup server. CA ARCserve Backup and the agent work together to back up and restore Microsoft SQL Server database objects.

The agent provides services that allow CA ARCserve Backup to back up and restore Microsoft SQL Server databases. The agent must reside on the same server as Microsoft SQL Server, or on a local drive of each node in a Microsoft Cluster Services environment that contains Microsoft SQL Server. In the Cluster, the agent dynamically handles the association between Microsoft SQL Server instances and Virtual Server names, and also recognizes which instances are running on which nodes.

Note: If you are using a clustered instance of Microsoft SQL Server to house your ARCserve Database, you will need to use the stand-alone installer to install the Agent for ARCserve Database on the nodes of the cluster where the instance is not active when CA ARCserve Backup is installed.

However, if you are installing a clustered CA ARCserve Database Primary Server using the Microsoft SQL Server 2008 Express Edition option for your ARCserve Database, this will not be necessary. The agent will be automatically installed on each node along with CA ARCserve Backup before the ARCserve Primary Server is configured for Clustered operation.

Architecturally, the agent is positioned between CA ARCserve Backup and Microsoft SQL Server, on the machine hosting SQL Server.

12 Agent for Microsoft SQL Server Guide

How the Agent Works

CA ARCserve Backup and the agent work together to back up and restore SQL Server databases. When CA ARCserve Backup backs up a database, it sends a request to the agent. The agent retrieves a point-in-time-consistent image of the database or its transaction log from Microsoft SQL Server in the form of a logical data stream, and sends it to CA ARCserve Backup, where the complete database image is backed up to media. During a restore, the agent functions in a similar fashion, and transfers the backed up database from CA ARCserve Backup to Microsoft SQL Server.

The agent takes advantage of the Microsoft SQL Server dump database and transaction log backup method. A dump backs up the database or transaction log in a single step. This ensures that a consistent image of the database is backed up.

For each database or transaction log submitted for backup, the agent initiates a dump in Microsoft SQL Server. Microsoft SQL Server sends the database to the agent in a series of data chunks. The agent receives the data, one chunk at a time, and passes it directly to CA ARCserve Backup, where it is recorded to backup media.

How the Agent Works

In a restore operation, for each database or transaction log backup being restored, the agent initiates a load operation in Microsoft SQL Server, and then returns the backed-up data to Microsoft SQL Server in the same manner that the data was provided during backup. If a single database requires more than one backup to restore it, CA ARCserve Backup assists in assembling the correct sequence of restore operations to be able to fully restore the database.

How a Backup Request Works

Backup jobs use the following process:

1. You start a backup job in CA ARCserve Backup.

2. CA ARCserve Backup sends the request to the agent for a database.

3. The agent retrieves a particular database or transaction log from Microsoft

SQL Server, which sends multiple data chunks to the agent.

4. The agent retrieves the data chunks and transfers them to CA ARCserve

Backup, which backs up the data to the specified storage media.

Chapter 1: Introducing the Agent 13

How the Agent Works

How a Restore Request Works

Restore jobs use the following process:

1. You issue a restore command from CA ARCserve Backup.

2. CA ARCserve Backup informs the agent of the restore job.

3. The agent instructs Microsoft SQL Server to prepare to receive the data.

4. CA ARCserve Backup accesses storage media and begins restoring data.

5. CA ARCserve Backup transfers data to the agent.

6. The agent transfers data to Microsoft SQL Server.

7. Microsoft SQL Server recovers the database.

How Data Flows During Backup

The following steps describe the data flow when CA ARCserve Backup uses the Agent for Microsoft SQL Server to back up a Microsoft SQL Server instance:

1. CA ARCserve Backup sends a request to the agent for a database.

2. The agent instructs Microsoft SQL Server to perform a backup of a particular

database or log.

3. Microsoft SQL Server returns the data from the database in multiple chunks

to the agent, one chunk at a time.

4. The agent receives the data chunks from Microsoft SQL Server and transfers

them to CA ARCserve Backup.

5. CA ARCserve Backup writes the data chunks to media.

These steps are repeated until there is no more data to be backed up. The agent and the Microsoft SQL Server backup function guarantee the consistency and accuracy of the data being backed up.

Agent Services

The Agent for Microsoft SQL Server runs within the context of the CA ARCserve Universal Agent Service. This service is shared with other agents, providing a single point of access for backup and restore operations. The service starts automatically after the installation is completed, and dynamically recognizes the

14 Agent for Microsoft SQL Server Guide

addition of new agents as they are installed.

Access Requirements

When you submit a job that includes Windows database servers, CA ARCserve Backup prompts you for a system user name and password for the system on which the database resides. CA ARCserve Backup accesses the remote servers using this user name and password.

A Microsoft SQL Server native user name and password are also required to access some database instances. When prompted by the system, enter the Microsoft SQL Server user ID and the password of the system administrator (sa), or enter a user ID and password with equivalent privileges. For more information about agent security configuration, see Configuring Microsoft SQL Server Security Settings.

Note: There are two different data transfer mechanisms available to the agent, and they have different permission requirements. A backup using Named Pipes only requires the Backup Operator permission for the specific database being backed up, and the Database Creator role to perform the restore. A backup using Virtual Devices requires the System Administrator role. However, the Named Pipes mechanism is available only for Microsoft SQL Server 7.0 and 2000. The CA ARCserve Backup Agent for Microsoft SQL Server requires Local Administrator permission in Windows Vista, Windows Server 2008 and later.

How the Agent Works

Backup and Restore Options

Backup options enable you to do the following:

■

Perform a Full or Differential backup of a database

■

Back up the entire database, a selected set of files and FileGroups within a database, or an automatically-selected set of FileGroups that contain changeable data

■

Back up the transaction log of a database, with or without truncating the log

■

Take the database offline automatically after a transaction log backup, leaving it in a restoring state

■

Check the consistency of databases before and after backup

■

Perform both a Data and Transaction Log backup of a database in a single backup job

■

Include SQL Server’s native error checking information as part of the backed-up data

For more information about backup options, see the section, Backing Up

Microsoft SQL Server Databases (see page 29).

Chapter 1: Introducing the Agent 15

Microsoft SQL - CA ARCserve Backup Compatibility Matrix

Feature/Function

Microsoft SQL Server Version

SQL 7.0

SQL 2000

SQL 2005

SQL 2008

Named Pipes

Yes

Multiple Instance Support

Yes

Partitioning

Yes

Database Mirroring

Yes

FILESTREAM

Yes

File and FileGroup backups (see page

121)

Yes

Cluster Environments (see page 73)

Yes

Restore options enable you to do the following:

■

Restore data and transaction logs

■

Automatically determine a sequence of backups to restore, to produce a live, consistent database with a single restore job

■

Use transaction log restores to restore the data to a specific point in time, or to the start or end of a named transaction

■

Restore the entire database or a selected subset of the database files and FileGroups

■

Restore a selected subset of the database as a new database

■

Perform a Torn Page Repair of a database, even while it is online

■

Leave the database in a restricted access mode

■

Preserve or clear the replication settings of the restored database

■

Change the physical location on disk of data and transaction log files

■

Check only the physical consistency of a database after restore is complete

■

Override any inconsistencies found with SQL Server’s native error checking

information

For more information about restore options, see the section, Restoring Microsoft

SQL Server Databases (see page 45).

Microsoft SQL - CA ARCserve Backup Compatibility Matrix

CA ARCserve Backup supports features and functions introduced in various Microsoft SQL Server versions, as follows:

16 Agent for Microsoft SQL Server Guide

Agent Activity Log

Feature/Function

Microsoft SQL Server Version

SQL 7.0

SQL 2000

SQL 2005

SQL 2008

Full-text search catalogs (see page

110)

Yes

Yes Resource database

Yes

Full database backups (see page

118)

Yes

Differential database backups (see

page 119)

Yes

Partial database backups (see page

122)

Yes

Yes Torn Page Repair

Yes

Include checksum generated by SQL Server

Yes

Continue restore after checksum failure

Yes

Agent Activity Log

The Agent for Microsoft SQL Server generates a log with information about backup or restore jobs and their status. This Activity log is called sqlpagw.log and is located in the directory in which the agent has been installed. If errors appear in the CA ARCserve Backup job logs, check the Activity log for more information about why the errors occurred.

Online Backup of SAP R/3 Databases

When you use Microsoft SQL Server 7.0 or later as the database server for SAP R/3, you can perform an online backup of SAP R/3 databases using the Agent for Microsoft SQL Server. A separate agent for SAP R/3 is not required. The online backup procedure is the same as it is with any other database in the Microsoft SQL Server.

Note: You cannot perform offline backups of SAP R/3 databases in Microsoft SQL Server using the Agent for Microsoft SQL Server.

Chapter 1: Introducing the Agent 17

Agent for ARCserve Database

The CA ARCserve Backup Agent for ARCserve Database is a form of the CA ARCserve Backup Agent for Microsoft SQL Server. The agent is either installed automatically when you install CA ARCserve Backup, or manually using a special utility, either after the location of the CA ARCserve Backup database is changed, or on multiple nodes of a cluster.

This utility, named SQLAgentRmtInst.exe, is placed in the Packages sub-folder of the CA ARCserve Backup home directory, in a folder named ASDBSQLAgent, when you install CA ARCserve Backup. If you need to install the agent on a computer that is not a CA ARCserve Backup server, you must copy the ASDBSQLAgent folder to the system where you are installing the agent, and run the SQLAgentRmtInst.exe utility on that machine.

By itself, the Agent for ARCserve Database allows you to back up and restore the CA ARCserve Backup database, and the system databases and Disaster Recovery Elements from the Microsoft SQL Server instance that contains the CA ARCserve Backup database. When installed with the Agent for Microsoft SQL Server, it allows the Agent for Microsoft SQL Server to recognize the presence of a CA ARCserve Backup database, and to work with CA ARCserve Backup to provide the special recovery mechanisms that are available for the CA ARCserve Backup database.

When upgrading from a previous release of CA ARCserve Backup, you must upgrade the Agent for ARCserve Database. This behavior is designed to help ensure that the current version of the CA ARCserve Backup database is protected by the current version of the agent. As a result, you cannot clear the check box next to Agent for Microsoft SQL Server in the product selection tree on the Components dialog as illustrated by the following:

18 Agent for Microsoft SQL Server Guide

Agent for ARCserve Database

Because the Agent for ARCserve Database is a form of the Agent for Microsoft SQL Server, it will appear as the CA ARCserve Backup Agent for Microsoft SQL

Server in the system’s installed programs list. If both are present, only a single

entry will appear. If you need to uninstall one or the other, the installation sequence will prompt you to select which variant to remove.

You can use the stand-alone utility that installs the Agent for ARCserve Database in any of the following situations:

■

When the CA ARCserve Backup database is moved

■

To re-install the agent if it is accidentally uninstalled

■

To install the agent to additional nodes of a cluster

■

To install the agent on a remote computer, if the CA ARCserve Backup installer is unable to do so directly

Chapter 1: Introducing the Agent 19

Chapter 2: Installing the Agent

The Agent for Microsoft SQL Server is a client program you can install in two configurations:

■

On the same machine as Microsoft SQL Server

■

On a local drive of each node in a Microsoft Cluster Services cluster that contains Microsoft SQL Server

This section explains how to install the Agent for Microsoft SQL Server in both configurations.

This section contains the following topics:

Installation Prerequisites (see page 21) Installation Considerations (see page 23) Install the Agent (see page 24) Post-Installation Procedures (see page 25) How to Uninstall the CA ARCserve Backup Agent for Microsoft SQL (see page 28)

Installation Prerequisites

There is certain prerequisite information you must satisfy before installing the Agent for Microsoft SQL Server in a standard Microsoft SQL environment or a

Basic Prerequisites

Microsoft SQL Server 2000, 2005 and 2008 Cluster Environment Prerequisites

Microsoft SQL Server 2000 or later cluster environment.

Before you install the Agent for Microsoft SQL Server in a standard Microsoft SQL Server environment, review the information in the Readme to ensure you have met the basic configuration requirements.

Before you install the Agent for Microsoft SQL Server in a Microsoft SQL Server 2000, 2005, or 2008 cluster environment, in addition to the basic prerequisites, perform the following tasks:

■

Make a note of the user name and password of an MSCS domain user with system administrator privileges.

■

Make a note of the Microsoft SQL Server virtual server name, cluster server user name, and cluster server password.

Chapter 2: Installing the Agent 21

Installation Prerequisites

■

Install the Agent for Microsoft SQL Server on the local drives of all nodes in the MSCS cluster as part of the initial agent installation.

22 Agent for Microsoft SQL Server Guide

Installation Considerations

The following should be considered before installing the CA ARCserve Backup Agent for Microsoft SQL Server:

■

Installing 32-bit components on a 64-bit machine generates two copies of the file PortsConfig.cfg on the machine. The PortsConfig.cfg file for the 64-bit components is in c:\Program Files\CA\SharedComponents\CA ARCserve Backup, and the PortsConfig.cfg file for the 32-bit components is in c:\Program Files (x86)\CA\SharedComponents\CA ARCserve Backup. If you make changes to one, you must make the same changes to the other, or the components may not be able to contact one another.

■

The Agent is always installed in the version native to your computer's hardware.

■

The Agent requires Named Pipes and TCP/IP protocols enabled on the SQL server to communicate.

■

If you uninstall a Microsoft SQL Server instance, some components used by other Microsoft SQL Server instances on the machine may be deregistered, including the Virtual Devices mechanism used to perform backups. If this occurs, the agent generates Backup Agent Error 511 ("Error creating Virtual Device Interface: COM class not found") within the error AE50015 ("Backup Failed"), or AE51004 ("Restore Failed"). To address this error, perform the following procedure:

Installation Considerations

1. Locate the version of sqlvdi.dll that belongs to the newest version of

Microsoft SQL Server on the machine.

2. From a command prompt, change to the directory containing the

selected sqlvdi.dll, and enter the following command to re-register the Virtual Devices component and re-enable backup operations:

regsvr32 sqlvdi.dll

■

Installing two or more versions of SQL Server concurrently on the same machine may create multiple versions of the SQLVDI.DLL file, which causes conflicts resulting in database backup job failures. To resolve any SQLVDI.DLL conflicts, ensure all instances of SQL Server are at the latest Service Pack, security patch or Hotfix level. You may also restart the failed SQL Server instance to reload a new SQLVDI.DLL file, or reboot the machine.

■

If you are installing the Agent with Microsoft SQL Server 2000, the Agent for Microsoft SQL Server requires Microsoft SQL Server 2000 with Service Pack 3, Service Pack 3a, or Service Pack 4.

■

Windows Small Business Server 2003 Standard Edition does not include Microsoft SQL Server 2000. However, you can install Microsoft SQL Server 2000 separately, but you must install Service Pack 3, Service Pack 3a, or Service Pack 4.

Note: Windows Small Business Server 2003 Premium Edition includes Microsoft SQL Server 2000, Service Pack 3.

Chapter 2: Installing the Agent 23

Install the Agent

■

Install the Agent

In cluster environments, you may receive a Lost Connection error while installing the Agent for Microsoft SQL Server. This occurs in primary and stand-alone installations when you select the clustered SQL server instance as the CA ARCserve Backup database. To avoid this error, copy the Agent for CA ARCserve Backup database package to each cluster node and run setup manually. For more information, see the CA ARCserve Backup Administration Guide.

To protect SQL Server instances in a cluster-aware environment, you must manually install the Agent for Microsoft SQL Server on all of the nodes in the cluster-aware environment.

When installing the Agent for the CA ARCserve Backup database on other nodes in a cluster-aware environment, you must run SQLAgentRmtInst.exe, install and then run the Account Configuration Utility, DBAConfig.exe, after installation to input the correct cluster node name and password so that the Setup process can create ODBC communication.

Ensure that you have confirmed the installation prerequisites and performed the required pre-installation tasks. When you have completed these tasks and gathered the required information, you are ready to begin the installation process.

Note: If you have multiple versions of Microsoft SQL Server installed on the same machine, the version of SQLVDI.dll registered with the machine must be from the latest version of Microsoft SQL Server. If it is not, backup operations will fail for instances of the later versions.

Install the Agent in a Standard Microsoft SQL Server Environment

To install the Agent for Microsoft SQL Server in a standard Microsoft SQL Server environment, follow the standard installation procedure for the system components, agents, and options of CA ARCserve Backup. For the detailed steps in this procedure, see the Implementation Guide.

During the installation procedure, after you select the Agent for Microsoft SQL Server for installation, the Account Configuration dialog appears.

Enter the appropriate information for each instance of your standard Microsoft Server:

■

Select either SQL Server or Windows Authentication.

■

Enter the user name and password of a Microsoft SQL Server user with system administrator privileges for each Microsoft SQL Server instance for which you have specified SQL Server authentication.

24 Agent for Microsoft SQL Server Guide

Post-Installation Procedures

Install the Agent in a Microsoft SQL Server 2000 or Later Cluster Environment

To install the Agent for Microsoft SQL Server in a Microsoft SQL Server 2000, 2005, or 2008 cluster environment, follow the standard installation procedure for the system components, agents, and options of CA ARCserve Backup. For the detailed steps in this procedure, see the Implementation Guide.

During the installation procedure, after you select the Agent for Microsoft SQL Server for installation, the Account Configuration dialog appears.

Enter the appropriate cluster information for each instance of your Microsoft SQL Server 2000 or later virtual server:

■

Click the cell containing the instruction in the Instance column to add virtual Microsoft SQL Server instances to the configuration window.

■

Specify either Windows or SQL Server authentication in the Authentication column. If you specify SQL Server authentication, enter the user name and password of a Microsoft SQL Server user with system administrator (sa) rights for that instance. Confirm the password.

■

Enter the name of the Microsoft SQL Server 2000, 2005, or 2008 virtual server associated with each instance.

■

Enter the login ID of an MSCS domain user with system administrator privileges and the password for that user. Confirm the password.

Post-Installation Procedures

After you have installed the agent, you may need to customize the Data Transfer settings, including Virtual Device Striping parameters.

Chapter 2: Installing the Agent 25

Post-Installation Procedures

Configure Backup and Restore Parameters for the Agent for Microsoft SQL Server

Use the Central Agent Admin utility to configure the Agent for Microsoft SQL Server backup and restore parameters for supported versions of Microsoft SQL Server. The parameters include settings for Microsoft Virtual Device Interface (VDI) objects and remote communication.

To configure backup and restore parameters for the Agent for Microsoft SQL Server

1. From the CA ARCserve Backup Quick Start menu, choose Administration,

Central Agent Admin.

Central Agent Admin opens.

2. In the Windows Systems tree, expand the server on which the Agent is

installed and then select the Agent for Microsoft SQL Server.

3. Click Configuration on the toolbar.

The Options Configuration dialog opens.

4. Click Agent for Microsoft SQL Server from the list on the left.

The Options Configuration shows the corresponding SQL Server settings.

5. Specify the level of detail and synchronized recording under Agent Log

Settings as follows:

■ Level of Detail--Controls the settings for level of detail of the agent’s

Activity Log and Debugging Log. For the Activity Log settings, a Level of Detail setting of Normal (0) includes basic information about agent activity. A setting of Detail (1) includes more detailed information about agent activity. A setting of Debug (2) enables the Debugging Log at a moderate level of detail. A setting of Trace (3) enables the Debugging Log at a very high level of detail. The Activity Log is localized for your reference. The Debugging Log is for CA Support use, and is not available in multiple languages.

■ Synchronized Recording--Forces the log messages to be written to

the Activity Log as they are posted. You can disable this option to improve the performance on high-load systems by caching several messages and writing them as a group.

26 Agent for Microsoft SQL Server Guide

Post-Installation Procedures

6. Select the Instance (ARCSERVE_DB) or the name of the instance for which

you wish to change configuration for the Agent for Microsoft SQL Server.

7. Set the parameters under Virtual Device Configuration as follows:

■ Number of Stripes--Determines the number of CPUs used to perform

backups. Set this value to match the number of CPUs in the database server for the fastest backup performance. The default setting is 1 and the maximum value is 32.

■ Number of Buffers--The total number of VDI buffers (of maximum

transfer size) used to back up and restore. The default setting is 1. This number cannot be less than the number of stripes.

■ Data Block Size (in bytes)--All data transfer sizes are multiples of this

value. Values must be a power of 2 between 512 bytes and 64 KB inclusive. The default is 65536 or 64 KB.

■ Maximum transfer size--The maximum input or output request issued

by Microsoft SQL Server to the device. This is the data portion of the buffer. This parameter value must be a multiple of 64 KB. The range is from 64 KB to 4 MB. The default setting is 2097152 or 2 MB.

■ Maximum VDI Wait Time - Backup (ms)--The time, in milliseconds,

a Virtual Device object waits for a response from Microsoft SQL Server during a backup operation. This setting is also used by the agent when waiting for parallel operations to synchronize or background operations to complete, including during some parts of restore operations. The default setting is 60000 ms (ten minutes).

■ Maximum VDI Wait Time - Restore (ms)--The time, in milliseconds,

a Virtual Device object waits for a response from Microsoft SQL Server during a restore. Increase this time if the database to be restored contains very large data files. The default setting is 9000000 ms (2.5 hours).

Chapter 2: Installing the Agent 27

How to Uninstall the CA ARCserve Backup Agent for Microsoft SQL

8. Under Named Pipes Configuration, specify the Maximum Connection Wait

Time (ms) time, in milliseconds, the Agent for Microsoft SQL Server should wait to close a named pipe if a remote connection fails. The default setting is 400 ms.

9. Set the parameters under Restore Post-Processing Wait as follows:

■ Polling Period (seconds)--The amount of time to wait between

checks of the database status. The default setting is 60 seconds (one minute).

■ Maximum Wait Timeout (minutes)--The total amount of time to wait

before abandoning the waiting process. If this timeout elapses and the job contains additional Transaction Log sessions to be restored, then those additional sessions may fail to restore because SQL Server is not yet ready. The default setting is 180 minutes (three hours).

Click Apply to Multiple to display a dialog from which you can select additional SQL Servers. Click OK when to finished to apply settings and return to Configuration.

10. Click OK to end configuration.

How to Uninstall the CA ARCserve Backup Agent for Microsoft SQL

Uninstalling the Agent for Microsoft SQL Server is controlled through a single point from the Windows Add/Remove Programs dialog. Select CA ARCserve Backup and click the Remove button. The Uninstallation Application displays a list of the CA ARCserve Backup components installed on your system. Select the Agent you wish to uninstall and click Uninstall to automatically break the dependencies among components and uninstall them in the proper sequence.

28 Agent for Microsoft SQL Server Guide

Chapter 3: Backing Up Microsoft SQL Server Databases

This section contains information about backing up databases and Transaction logs using CA ARCserve Backup, the Agent for Microsoft SQL Server, and Microsoft SQL Server 7.0, Microsoft SQL Server 2000, Microsoft SQL Server 2005 and Microsoft SQL Server 2008.

This section contains the following topics:

Backup Overview (see page 29) Dynamic and Explicit Job Packaging (see page 38)

Backup Overview

Back Up a Database (see page 42)

To back up is to create a copy or image of a database, Transaction Log, Database Differential, a list of changes that have been made since a previous backup, or a collection of files or FileGroups on another device (typically a media drive). Use CA ARCserve Backup and the Agent for Microsoft SQL Server to perform backups using the SQL Server Backup statement.

Backing up a database creates a copy of its tables, data, system objects, and user-defined objects. In the event of media failure, if you have been making regular backups of your databases and their Transaction Logs, you can recover your databases.

Important! Transaction Logs are not backed up or truncated during full or differential database backups. To back up and truncate Transaction Logs, perform a separate Transaction Log backup. When you perform the Transaction Log backup, select the Remove inactive entries from Transaction Log option to truncate the log files. For more information about Transaction Log backups see Transaction Log Backups in this chapter.

When a Microsoft SQL Server database backup is started in CA ARCserve Backup, the Agent for Microsoft SQL Server initiates an online backup of the database. This backup takes place while the database is active. The backup captures the state of the data at the moment the statement is executed. No partial transactions are captured. Any data changes made after the backup begins are not captured in the backed up copy of the database.

Chapter 3: Backing Up Microsoft SQL Server Databases 29

Backup Overview

Backup Manager Agent Options

You may select backup options on a per-database basis or define a set of default options for all databases in a backup job. The Agent then applies the options to each database as appropriate.

■

Agent Options/Database Level Options--These are agent backup options and apply to only the selected database. They can either extend or override the Global Agent options. Access Database Level Options by right-clicking the database object and selecting Agent Option from the shortcut menu.

■

Global Options/Agent Options--These options let you specify default job options for all selected objects in the Agent type. Global Agent Options are not supported by releases of the Agent prior to r12.5. Access Global Agent Options from the Agent Options tab of the Global Options dialog.

Global Agent Options applied at a global level let you specify default job options for all databases for the Agent selected. Options selected for a specific object at the database level can either extend or override the options specified as a global option. As a general rule, options applied at the global level will extend or override options that you specify on the Job Schedule tab.

Certain options are available from only one Agent Option dialog; they are noted.

Note: The agent combines options that you specify at the database level for a specific database with the appropriate global agent options.

You can specify Global Agent Options for the CA ARCserve Backup components that follow:

■

Agent for Microsoft SQL Server--Includes the Agent for CA ARCserve Backup Database, which is supported by CA ARCserve Backup Agent for Microsoft SQL Server, r12.5 and later.

■

Agent for Virtual Machines--Supported by the CA ARCserve Backup Agent for Virtual Machines, r12.5 and later.

■

Agent for Microsoft Exchange Server - Includes Microsoft Exchange Server Database Level and Document Level options.

Consider the behavior that follows when packaging jobs using the above-described agents:

■

Global agent options are not supported by releases of the above-described agents prior to CA ARCserve Backup r12.5, nor are they applied if you use any other agent to back up Microsoft SQL or Exchange Server databases.

30 Agent for Microsoft SQL Server Guide

■

When you upgrade an older agent to CA ARCserve Backup r12.5 or later, the agent applies both any pre-existing local options and all global options that apply and do not conflict with the database level (local) options.

■

For jobs packaged using older agents, local options are carried over as local options.

Agent Options (Database Level)

The agent options you apply to only the selected database can either extend or override Global Option/Agent Options. These options include:

■

Backup Method -- see the topic, Backup Method Options (see page 32).

– Use Global or Rotation Options – Full – Differential

■

Database Consistency Check -- see the topic, Database Consistency Check

Options (see page 36).

– Before Backup

Backup Overview

– After Backup – Continue with backup if DBCC fails – Do not check indexes – Check the physical consistency of the database

■

Other options -- see the topic, Other Options (see page 38).

– Override Global Options

Chapter 3: Backing Up Microsoft SQL Server Databases 31

Backup Overview

Agent Options (Global Options)

Global Options/Agent Options (supported by releases of the Agent in r12.5 and later) allow you to set default job options for all SQL Server Agent objects and include essentially the same options accessible from the local options.

Backup Methods

The following backup methods are provided on both the Agent Options (database level) and Agent Options (Global Options) dialogs:

■

Use Global or Rotation Options--Use Global or Rotation Options is the default setting.

CA ARCserve Backup can apply Incremental and Differential global backup methods from the Job Scheduler when backing up Microsoft SQL Server databases. This lets you use rotation schemes to perform differential and Transaction Log backups of Microsoft SQL Server databases, which are

32 Agent for Microsoft SQL Server Guide

dynamically adjusted based on the limitations of each individual database.

Backup Overview

In releases of the agent that pre-date CA ARCserve Backup r12, the Global Backup Method or Rotation Scheme option from the Job Scheduler overrides local database options. In this release, the Global Backup Method or Rotation Scheme is applied only if you selected Use Global or Rotation Options in the database level options for the database and in the Global Agent Options for SQL Server.

This option backs up the selected database using the Backup Method from the Job Schedule. The Job Methods are applied using the logic that follows:

– The Full job method will result in a Full backup of the database. – The Differential job method will result in a Differential backup of the

database, unless this database has not yet had a Full backup.

– The Incremental job method will result in a Transaction Log backup With

Truncation for databases using the Full and Bulk-Logged Recovery Models, and a Differential backup of databases using the Simple Recovery Model, unless this database has not yet had a Full backup.

– The three main System databases are exempt from the Job Method and

from the Backup Method in the Global Agent Options; selecting this option for databases master, model, or msdb will always result in a Full

■

backup.

Full--A Full backup is performed. All files included in the Database Subset selected will be backed up in their entirety.

Differential--Backs up only data that has changed since the last Full backup. For example, if you ran a complete backup of your database on Sunday night, you can run a differential backup on Monday night to back up only the data that changed on Monday.

Note: When selected in the Global Agent Options, this option is ignored by system databases. Databases that have not received a Database Full Backup will revert to a Full Backup.

■

Back up Transaction Log After Database--Backs up only the Transaction log. This option is only available for databases using the Full and Bulk-Logged Recovery Models. For databases using the Simple Recovery Model, CA ARCserve Backup performs a Differential backup when you select Transaction Log Only from the Global Agent Options tab.

Note: When selected in the Global Agents Options, this option is ignored by system databases. Databases that have not received a Database Full Backup will revert to a Full Backup.

The backup method selected on the Global Agent Options tab overrides the selection made in a job's global backup method or rotation phase in the Job Scheduler. If you select the backup method using the Global Agent Options tab, note the following:

■

The three system databases (master, model, and msdb) are exempt from the Backup Method in the Global Agent Options tab.

Chapter 3: Backing Up Microsoft SQL Server Databases 33

Backup Overview

Database Subset

■

For databases that have not yet received a Database Full backup, CA ARCserve Backup ignores the backup method set in the Global Agent Options tab and performs a full backup by default.

■

For databases using the Simple Recovery Model, CA ARCserve Backup performs a Differential backup when you choose Transaction Log Only on the Global Agent Options tab.

Because any selection other than Use Global or Rotation Method for a database overrides the selection in the Global Agent Options dialog, the Backup Method is not affected by the Override Global Options setting on the database's Agent Options (database level) dialog.

Database Subset options let you define the types of database components that you want to back up. You can use this option to choose between the entire database, or a selection of files and FileGroups contained within the database, when the size of the database and performance requirements do not allow you to back up the entire database.

Database Subset options are disabled if the selected Backup Method for a database is Transaction Log Only.

Important! Of the following Database Subset options, only the Back up Transaction Log After Database option is available on the Global Options/Agent Options dialog.

■

Entire Database--Backs up the entire database.

■

Files and FileGroups--Backs up selected files in a database. Use this option to back up a file or FileGroup when the database size and performance requirements make it impractical to perform a full database backup. This option is only available for databases using the Full and Bulk-Logged Recovery Models.

Note: For Microsoft SQL Server 7.0 databases, CA ARCserve Backup performs a Files and FileGroups Full backup if you set the database subset to Files and FileGroups for the database level and backup method to Differential on the Global Agent Options tab.

■

Partial Database--Backs up the Primary FileGroup, and any other Read-Write FileGroups. For a Read-Only database, only the Primary FileGroup will be backed up. This option requires SQL Server 2005 or later.

■

Back up Transaction Log After Database--Backs up the Transaction Log after the database, partial database, or selected set of data files is backed up. This allows you to perform both a Full or Differential backup and a Transaction Log backup in the same job. This option is only available for databases using the Full and Bulk-Logged Recovery Models and is ignored for databases using the Simple Recovery Model if set in the Global Agent Options dialog.

34 Agent for Microsoft SQL Server Guide

Backup Overview

Be aware of the following behavior:

■ If you select this option on the Global Options/Agent Options tab and

specify Incremental backup method using the Job Scheduler, CA ARCserve Backup performs only one transaction log backup on the database and uses the Transaction Log Truncation Options from the Global Agent Options tab instead of the Incremental backup's default behavior (truncate the transaction log).

■ If you select this option using the Database Level Agent options, set the

backup method at the database level to Use Global or Rotation, and set the backup method in the Global Agent Options to Transaction Log Only, CA ARCserve Backup performs only one transaction log backup on the database and uses the Transaction Log Truncation Options set from the database level.

■ If you select this option in the Global Agent Options dialog and specify

Transaction Log Only in Database Level Options, this option and the accompanying global Log Truncation Options setting is ignored for that database.

Chapter 3: Backing Up Microsoft SQL Server Databases 35

Backup Overview

Log Truncation Options

Log Truncation Options are accessible from the Database Level/Agent Options and Global Options/Agent Options dialogs:

■

Remove inactive entries from transaction log, after backup--(Truncation) Truncates the Transaction Log files, removing entries

included in the backup so that the space in the files can be reused. This is the default option.

■

Do not remove inactive entries from transaction log, after backup--(No truncation) Retains backed up log entries after backup. These

entries will be included in the next Transaction log backup.

■

Back up only the log tail and leave the database in unrecovered mode--(No recovery) Backs up the log and leaves the database in a

restoring state. This option is available for Microsoft SQL Server 2000 or later. Use this option to capture activity since the last backup and take the database offline prior to restoring or repairing it.

The Log Truncation Options are available only when the selected Backup Method is Transaction Log, or when the Backup Transaction Log After Database option is checked.

Important! Do not use the "Backup only the log tail and leave the database in unrecovered mode" log truncation option to back up the ARCserve Database. Performing a backup with this option causes the database to be placed in an offline status, and you can lose the ability to find the backups of the ARCserve Database in order to perform a restore and bring the database online. If you perform a backup of the ARCserve Database using this option, you can use ARCserve Database Recovery Wizard to recover the CA ARCserve Backup database and bring it back online.

Log Truncation Options are not affected by the Override Global Options setting on the Database Level Agent Options dialog. If Transaction Log or Backup Transaction Log After Database is selected for the database, the database Log Truncation Options are used.

Database Consistency Check (DBCC) Options

A database consistency check (DBCC) tests the physical and logical consistency of a database. DBCC provides the following options:

■

Before Backup--Checks consistency before the backup of the database.

■

After Backup--Checks consistency after the backup of the database.

■

Continue with backup, if DBCC fails--Performs a database backup even if a consistency check before backup reports errors.

36 Agent for Microsoft SQL Server Guide

Backup Overview

■

Do not check indexes--Checks the database for consistency without checking indexes for user-defined tables.

Note: The system table indexes are checked regardless of whether you select this option.

■

Check only the physical consistency of the database--Detects torn pages and common hardware failures, but does not check the data against the rules of the database schema. It still checks the integrity of the physical structure of the page and record headers, and the consistency between the page’s object ID and index ID. This option is available for Microsoft SQL Server 2000 or later. If this option is selected from the Global Agent Options tab, it is ignored for SQL Server 7.0 databases.

All error messages that are generated during the DBCC are recorded in the Agent for Microsoft SQL Server log file called sqlpagw.log. The log is located in the Backup Agent directory.

Example: How DBCC Options Work

The following example illustrates how DBCC options work in conjunction with Override Global Options on the Agent Backup Options dialog.

■

With Override Global Options specified, the DBCC options selected at the database level will be the only DBCC options specified.

■

With Override Global Options not specified, all of the DBCC options specified for the database and all of the DBCC options selected in the Global options will be applied together.

On the Global Options/Agent Options tab, the Database Consistency Check options that follow are specified:

■

After backup

■

Do not check indexes

On the Agent Backup Options dialog, Override Global Options is not selected and the Database Consistency Check options that follow are specified:

■

Before backup

■

Continue with backup, if DBCC fails

Note: To open the Agent Backup Options dialog, open the Backup Manager, click the Source tab, browse to and expand the CA ARCserve Backup server, right-click the CA ARCserve Backup database, and then select Agent Option from the pop-up menu.

When you submit the backup job, CA ARCserve Backup applies the DBCC options specified in logical order: Perform the DBCC before the backup starts. If the DBCC fails, perform the backup. After the backup is complete, do not check the indexes.

Chapter 3: Backing Up Microsoft SQL Server Databases 37

Dynamic and Explicit Job Packaging

Other Options

From the Global Agent Options tab, you can specify the following additional options:

■

Include Checksum Generated by SQL Server--Includes error checking information from Microsoft SQL Server, which can be used to validate the integrity of the backed-up data during restore. This option requires SQL Server 2005 or later and is ignored when set in the Global Agent Options dialog for SQL Server 7.0 or 2000 databases.

■

SQL Native Backup Compression--This option applies to only SQL Server 2008 (Enterprise) and later versions. If enabled, this option directs CA ARCserve Backup to use SQL Server database backup compression settings, resulting in faster backup times and smaller sessions.

From the local Agent Option dialog, you can choose to Override Global Options. This setting lets you choose a backup method and database consistency check that applies to only the database selected for this job.

Dynamic and Explicit Job Packaging

CA ARCserve Backup provides the following two ways to package or set up your backup jobs:

■

Dynamic job packaging.

■

Explicit job packaging.

38 Agent for Microsoft SQL Server Guide

Dynamic Job Packaging

If you mark a database instance for dynamic job packaging when defining a backup job, CA ARCserve Backup automatically selects, at the time the backup job runs, all the components of the object (for example, child volumes and files) for backup.

For example, if you choose to back up an entire server and mark the server for dynamic job packaging and you change the volumes on the server, when you run the next backup job, the volumes on the server at the time of the backup are the volumes that are backed up. Any change that you make on the server marked for dynamic job packaging is included in the next backup.

Note: If you select the whole SQL Server instance for backup and then select backup options for individual databases, the individual database backup options are ignored. Only when you select the individual databases, selected per-database options are packaged and retained when you want to modify the job.

Important! When you mark a parent object for dynamic job packaging, all of its associated (or child) objects are also marked for dynamic job packaging and are selected for backup. Child objects of an object marked for dynamic packaging lose any separate options you have assigned them when the job is submitted.

Dynamic and Explicit Job Packaging

Chapter 3: Backing Up Microsoft SQL Server Databases 39

Dynamic and Explicit Job Packaging

Mark Objects for Dynamic Job Packaging

When you mark an object for dynamic job packaging when defining a backup job, CA ARCserve Backup automatically selects all the components of that object (child volumes, files, and so on) for backup.

To mark an object for dynamic job packaging

1. On the Source tab in the Backup Manager, expand the directory tree until the

object you want to mark for dynamic job packaging is displayed.

2. Click the square next to the object. The square next to the object, and the

squares next to all the children of the object, become completely green. In the following example, Microsoft SQL Server has been marked for dynamic job packaging. All of its children are also marked for dynamic job packaging.

Explicit Job Packaging

To select a database object for explicit job packaging when defining your backup job, select some or all of its child objects for dynamic job packaging, but do not

40 Agent for Microsoft SQL Server Guide

select the parent.

Explicit job packaging gives you the ability to customize local backup options. For example, if you run a backup job for which you have dynamically packaged the C and E drives on your server (which is explicitly packaged), you can select one set of options for drive C and another set of options for drive E.

Note: To customize volume or database options, you must package the volume or database parent items explicitly.

Mark Objects for Explicit Job Packaging

Instead of an entire database, you may wish to select a specific child object for backup, but not its parent. To customize volume and database options, mark objects for explicit job packaging.

To mark an object for explicit job packaging

1. On the Source tab in the Backup Manager, expand the directory tree until the

object you want to mark for explicit job packaging is displayed.

2. Click the squares next to the children of the object. The squares next to the

child objects become completely green and the square next to the parent object becomes half green and half white. In the following example, the C and D drives have been marked for dynamic job packaging. The computer on which they exist, WIN, has been marked for explicit job packaging.

Dynamic and Explicit Job Packaging

Chapter 3: Backing Up Microsoft SQL Server Databases 41

Back Up a Database

Use the Backup Manager in CA ARCserve Backup to back up databases.

To back up a database

1. Ensure that Microsoft SQL Server is running on your server. The Microsoft

SQL Server service must be started.

2. Start the CA Backup Universal Agent, if necessary.

Note: This service is started automatically when the agent is installed and is set to start automatically if the machine is restarted.

3. Open the Backup Manager and locate the Microsoft SQL Server instance,

listed under the name of the computer on which it is running. Expand the Microsoft SQL Server instance to display a list of databases.

4. Click Options to access Global Options and then click the Agent Options tab

to access the Global Agent Options, if desired. For more information about setting Global Agent Options, see Backup Manager Agent Options. (see page

30)

5. If you are backing up from a Microsoft SQL Server 7.0 or 2000 instance,

right-click the Microsoft SQL Server instance and select Transfer Method from the pop-up window. The Remote Protocol dialog opens. Select a data transfer mechanism and click OK.

If you are backing up from a Microsoft SQL Server 2005 or 2008 instance, go to the next step.

The default mechanism is Virtual Devices.

Note: Select Named Pipes only if you have backup operator rights, but do not have system administrator rights, or if you cannot use Virtual Devices. Named Pipes is not available for Microsoft SQL Server 2005 and later.

In prior releases of this agent, Virtual Devices were always used for local backups and TCP/IP backups.

6. Choose a database under the Microsoft SQL Server instance. Information

about the selected database appears in the right pane of the Backup Manager.

Note: For information about how you can select a database to apply options properly for backup, see Dynamic and Explicit Job Packaging. (see page 38)

7. Right-click the database object and select Agent Options from the pop-up

window. The Agent Options dialog for Microsoft SQL Server opens. The options available vary based on the version of the Microsoft SQL Server instance and the Recovery Model of the database.

8. Select the backup method you want to perform, and the subset type you

want to perform it on. For more information about backup methods and subsets, see Backup Methods.

42 Agent for Microsoft SQL Server Guide

Back Up a Database

9. If you chose the Files-and-FileGroups subset, click the Browse button. The

Specify FileGroups and Files dialog opens.

Select the specific files and FileGroups you want to back up and click OK.

10. (Optional) Enable a Database Consistency Check and select the Database

Consistency Check options. For more information about Database Consistency Checks, see Database Consistency Checks and the Microsoft SQL Server documentation.

11. If you selected the Transaction Log backup type, or the Backup Transaction

Log After Database option, select a Transaction Log Truncation Option.

12. Click OK.

13. Repeat the preceding steps for each database you are backing up in this job.

14. On the Destination tab in the Backup Manager, select a backup destination.

Note: You can use the * symbol in the Group or Media fields to create partial wildcards when you select a backup destination. For example, if you have two sets of device groups, one with all members beginning GroupA and the other with all members beginning GroupB, you can select all the GroupA members by entering GroupA* in the Group field. For more information about selecting devices and media, see the Administration Guide.

Click the Schedule tab and select the scheduling options for this backup. For information about scheduling backups, see the Administration Guide.

15. Click Submit. The Security and Agent Information dialog opens.

Note: In this dialog, the column and button labeled Agent refer to the Client Agent for Windows, not the Agent for Microsoft SQL Server. You can edit client agent information at this time. For more information about client agents, see the Administration Guide.

Verify the user name and password for the target machine and for Microsoft SQL Server. To change the security information for Microsoft SQL Server, click Security and change the information in the dialog.

16. After verifying or changing the security information, click OK. The Submit

Job dialog opens.

17. (Optional) Use the Submit Job dialog to select the job execution time, submit

the job on hold, add an optional job name for the backup job, or select source priority.

18. Click OK.

The job is submitted.

If you selected the Run Now option, the Job Status window opens. Use this window to monitor the current status of your job. For more information about the Job Status window, see the Administration Guide.

Chapter 3: Backing Up Microsoft SQL Server Databases 43

Chapter 4: Restoring Microsoft SQL Server Databases

This section contains information about restoring databases and Transaction logs using CA ARCserve Backup, the Agent for Microsoft SQL Server, and Microsoft SQL Server 7.0, Microsoft SQL Server 2000, Microsoft SQL Server 2005 and Microsoft SQL Server 2008.

This section contains the following topics:

Restore Options (see page 46) Database File Options (see page 54) Restore Databases Using Restore by Tree Method (see page 57) Restore Databases Using Restore by Session Method (see page 60) SQL Agent Filter Options (see page 63) Restore to Alternate Disk Locations Using Automatic Selection (see page 63) Restore to Alternate Disk Locations by Individual Session (see page 64) Perform an Offline Torn Page Restore Using Microsoft SQL Server 2005 or 2008

(see page 68)

Perform an Online Torn Page Restore Using Microsoft SQL Server 2005 or 2008 Enterprise, Data Center, or Developer Editions (see page 70)

Chapter 4: Restoring Microsoft SQL Server Databases 45

Restore Options

The Agent for Microsoft SQL Server provides restore options as shown in the following dialog:

■

Automatic Selection (see page 47)

■

Restore Type (see page 47): Database, Files or FileGroups, Partial (Microsoft

SQL Server 2000 and later), or Torn Page Repair (Microsoft SQL Server

2005)

■

Log Point in Time Restore (see page 50)

■

Recovery Completion State (see page 51)

■

Database Consistency Check (see page 36)

■

Force Restore to use Named Pipes (see page 53)

■

Continue Restore After Checksum Failure (see page 53)

■

Miscellaneous Options (see page 53)

46 Agent for Microsoft SQL Server Guide

Automatic Selection Option

The Automatic Selection option automatically:

■

Selects other sessions that must be restored with the session you are restoring for the restore job to be successful

■

Applies selected options appropriately to the automatically selected sessions

The Automatic Selection option is enabled by default for every restore job. Using Automatic Selection saves you time and prevents errors in packaging restore jobs.

Important! Using Automatic Selection, you may be unable to restore a database to a different location on a disk (for example, to a different drive letter or directory path, or with a different file name) using backups from previous versions of CA ARCserve Backup or BrightStor® Enterprise Backup. For more information about restoring to a different location, see Restore to Alternative Disk Locations Using Automatic Selection.

Restore Options

Restore Types

The Agent for Microsoft SQL Server supports the following types of restores:

Database restore

Restores the entire database. If the selected session is a Database Differential backup or a Partial Full backup, the last prior Database Full backup is required as a pre-requisite. If the selected session is a Partial Differential backup, the last Database Full or Partial Full backup is required as a pre-requisite.

Transaction Log restore

Restores the Transaction log. Restoring a Transaction log is also referred to as "applying" a Transaction log. When you restore a Transaction log, Microsoft SQL Server re-executes the changes contained in the log and rolls back any transactions that were uncommitted when you backed up the Transaction log.

After restoring a database from a Full Backup, you can load the differential backup (if any) and the Transaction log backups you created after backing up that database. Loading Transaction logs lets you to recover as much of a database as possible.

Chapter 4: Restoring Microsoft SQL Server Databases 47

Restore Options

You must load backups of the Transaction log in the sequence in which they were created. Microsoft SQL Server checks the timestamps on each backed up database and each backed up Transaction log to verify that the sequence is correct.

After Microsoft SQL Server has loaded the entire sequence of Transaction log backups, the database is restored to its state at the time of the last Transaction log backup, excluding any uncommitted transactions. The only uncommitted transaction that Microsoft SQL Server does not roll back is the Microsoft SQL Backup Log transaction, which is completed as part of the restore process instead.

Note: A Transaction Log restore must be applied to the original database or to a restored copy of the original database. Otherwise, the logs will not be applicable.

48 Agent for Microsoft SQL Server Guide

Restore Options

Files and FileGroups restore

Restores selected files and FileGroups. You can restore files and FileGroups from either a Files-and-FileGroups backup, Partial backup or Database backup. When restoring files or FileGroups, you must first restore from a Full backup session, then optionally from a differential session, followed by all of the Transaction log backup sessions performed after the Full or Differential backup. When you restore a file or FileGroup, you must apply the Transaction log to the database files immediately after the last file or FileGroup operation.

Note: A Files and FileGroups restore must be applied to the original database or to a restored copy of the original database. Otherwise, the logs will not be applicable.

Partial restore

A partial restore always restores the primary FileGroup and any other FileGroups you specify as a new database. The result is a subset of the database. FileGroups that are not restored are marked as offline and are not accessible.

Note: A database that is created using a Partial Restore may not be eligible for a Database backup, due to the unrestored FileGroups being offline. To resolve this condition, either restore the remaining FileGroups to the partially-restored database, or remove them from the database structure using the Microsoft SQL Server Enterprise Manager or Management Studio.

Torn Page Repair

Restores only the pages of data on disk which have been marked as damaged. SQL Server 2005 has the ability to isolate the damaged parts of a database while leaving the rest intact. When this happens with a Full Recovery Model database, the database may be eligible for a Torn Page Repair, which is much faster than a Database restore. The dependencies for this restore are similar to a Files and FileGroups restore.

There are both Online and Offline versions of this operation. In an Online Torn Page Repair, the database remains online the entire time, and undamaged tables are still accessible. In an Offline Torn Page Repair, the database should be taken offline using a Log Tail backup before the restore is performed. Online Repair requires the Enterprise Edition of SQL Server.

Note: Torn Page Repair restore must be applied to the original database, or to a restored copy of the original database. Otherwise, the data and logs will not be applicable.

Important! When using Automatic Selection, you may be unable to restore a database to a different location on a disk (for example, to a different drive letter or directory path, or with a different file name) using backups from previous versions of CA ARCserve Backup or BrightStor® Enterprise Backup. For more information about restoring to a different location, see Restore to Alternative Disk Locations Using Automatic Selection.

Chapter 4: Restoring Microsoft SQL Server Databases 49

Restore Options

Log Point-in-Time Restore Options

The Log Point-in-Time Restore option restores a database to the state it was in at a date and time, or as of a named transaction, that you have specified. You should use Automatic Selection with this option. This option is only available when the selected session is a Transaction Log backup.

Important! You cannot use the Log Point in Time Restore option if the database you are recovering uses the Bulk-Logged recovery model.

To find the correct log when you have selected the Log Point in Time Restore option, Microsoft SQL Server restores the record in each Transaction log backup containing the start and finish time of the backup. Microsoft SQL Server then searches this record for the time you have specified.

■

If Microsoft SQL Server finds the specified time, it restores the log to the point in the record that contains the time you submitted. The agent then signals CA ARCserve Backup to stop restoring and the database is fully recovered. If there are other logs with the same time, those logs are ignored and the subsequent sessions are skipped.

■

If the specified time occurs after those contained in the log, Microsoft SQL Server restores the log and leaves the database in a restoring state, waiting for the next log restore operation.

■

If the specified time occurs before those contained in the log, Microsoft SQL Server cannot restore the log.

The Log Point in Time Restore option has limitations. For example, if you do not select Automatic Selection and choose the Force Restore Over Existing Files option and you restore one or more logs belonging to the same database, but you do not select the appropriate database, differential backup, and FileGroup sessions to be restored first, the job will be incomplete and the subsequent sessions for that database will be ignored.

The options available for Log Point in Time Restore are:

Stop at time

Includes date and time fields in which you can enter a specific date and time. The option recovers the database to the specified date and time. This is the default option.

50 Agent for Microsoft SQL Server Guide

Restore Options

Stop at log mark

The option recovers the database activity through the transaction which is marked with the specified name, including the transaction that contains the mark. If you do not select the After Date and Time option, recovery stops at the first marked transaction with the specified name. If you select the After Date and Time option, recovery stops at the first marked transaction with the specified name exactly at or after the specified date and time.

Note: This option is available in Microsoft SQL Server 2000 and Microsoft SQL Server 2005 or later.

Stop before log mark

The option recovers the database activity prior to the transaction which is marked with the specified name. The transaction which contains the mark is not re-executed. If you do not select the After Date and Time option, recovery stops at the first marked transaction with the specified name. If you select the After Date and Time option, recovery stops at the first marked transaction with the specified name exactly at or after the specified date and time.

Note: This option is available in Microsoft SQL Server 2000 and Microsoft SQL Server 2005 or later.

After Date and Time

Allows you to specify a point in time after which Microsoft SQL Server will look for the specified Log Mark. The recovery stops at the specified mark only when the timestamp for the log mark is later than the specified time. The same Date and Time entry fields are used as for the Stop at Time option. Use this option with the Stop at Log Mark or Stop Before Log Mark options.

Recovery Completion State Options

The Recovery Completion State options let you specify the final state of a database after restore. The following options are available:

Leave database operational. No additional transaction logs can be restored

Instructs the restore operation to roll back any uncommitted transactions. After the recovery process, the database is ready for use.

Note: If you use Automatic Selection, you do not have to choose any of the Recovery Completion State selections manually for each session. CA ARCserve Backup performs the selection of sessions and applies the appropriate options automatically to each session. If you do not choose Automatic Selection, you must follow Microsoft SQL Server rules regarding the restore flow.

For more information, see Microsoft SQL Server documentation.

Chapter 4: Restoring Microsoft SQL Server Databases 51

Restore Options

Leave database nonoperational, but able to restore additional transaction logs

Instructs the restore operation not to roll back any uncommitted transactions and to leave the database in a state where it can accept additional Files-and-FileGroups, Differential, or Transaction Log restores. You must choose either this option or the Leave database read-only option to apply another differential backup or Transaction log after this restore job is complete. This option is generally used when restoring a database from multiple sessions without using Automatic Selection.

Leave database read-only and able to restore additional transaction logs

Prepares a standby (warm backup) database. A standby database is a second database on a different server which you can bring online if the primary production server fails. It contains a copy of the database on the primary server. The database is brought online in a special form of Read-Only state which can be revered to a Restoring state if another restore occurs. An Undo File is created during the restore which contains the information SQL Server needs to make this transition. The location and filename of the Undo File must be specified in the restore options.

Note: Standby databases are not eligible for backup. If a standby database is explicitly selected for backup, the backup will fail. If a SQL Server instance which contains a standby database is selected for backup, the standby database will be excluded by the agent.

For more information about standby servers, see Microsoft SQL Server documentation.

Database Consistency Check (DBCC) Options

A database consistency check (DBCC) tests the physical and logical consistency of a database. DBCC provides the following options:

After restore

Performs DBCC after the restore of the database.

Before restore

Performs DBCC before an Online Torn Page Repair restore of the database. (SQL Server 2005 or later, Enterprise Edition only.)

Do not check indexes

Checks the database for consistency without checking indexes for user-defined tables.

Note: The system table indexes are checked regardless of whether you select this option.

52 Agent for Microsoft SQL Server Guide

Check only the physical consistency of the database

Detects torn pages and common hardware failures, but does not check the data against the rules of the database schema. It still checks the integrity of the physical structure of the page and record headers, and the consistency

between the page’s object ID and index ID. This option is available for

Microsoft SQL Server 2000 or later. If this option is selected from the Global Agent Options tab, it is ignored for SQL Server 7.0 databases.

All error messages that are generated during the DBCC are recorded in the Agent for Microsoft SQL Server log file called sqlpagw.log. The log is located in the Backup Agent directory.

Force Restore to use Named Pipes

This option forces the agent to use Named Pipes to return the data to the Microsoft SQL Server. This provides an alternate mechanism for restoring a session if the Virtual Device Interface is not functioning properly. This option is available for Microsoft SQL Server 7.0 and Microsoft SQL Server 2000.

Restore Options

Continue Restore After Checksum Failure

This option allows Microsoft SQL Server 2005 to continue processing a restore if it detects an inconsistency between the data and checksums included in the backup.

Miscellaneous

The following are the miscellaneous options you can select from:

Force Restore Over Existing Files and Databases

Lets Microsoft SQL Server overwrite files it does not recognize as part of the database it is restoring. Use this option only if you receive a message from Microsoft SQL Server prompting you to use the With Replace option.

Microsoft SQL Server supports this option for database restore and file or FileGroup restore operations.

Important! Microsoft SQL Server 2005 will refuse by default to overwrite an online database using the Full or Bulk-Logged Recovery Model. Instead, it produces an error message saying to either take the database offline by

performing a Log Tail backup, or restore with the “WITH REPLACE” option. Selecting this option applies the “WITH REPLACE” option to the restore, and

forces SQL Server to overwrite the existing database.

Chapter 4: Restoring Microsoft SQL Server Databases 53

Database File Options

Restricted User Access After Restore Option

Restricts access to a newly restored database to members of the db_owner, dbcreator, or sysadmin roles. In Microsoft SQL Server 2000 and Microsoft SQL Server 2005, Restricted_User replaces the DBO_Only option from Microsoft SQL Server 7.0. This option requires the Leave database operational, no additional transaction logs can be restored option.

Keep Replication Settings

Instructs the restore operation to preserve replication settings when restoring a published database to a server other than the one on which it was created. This prevents Microsoft SQL Server from resetting the replication settings when it restores a database or log backup on a warm standby server and recovers the database. Use the Keep Replication Settings option when setting up replication to work with log shipping.

You cannot select this option when restoring a backup with the Leave database non-operational, but able to restore additional transaction logs option. Use this option only with the Leave database operational, no additional transaction logs can be restored option.

Use Current ARCserve Database as Original Location

Overwrites the current ARCserve Database instead of the database which was backed up to this session when a restore to Original Location is selected. You can use this option to migrate the session and log information from one ARCserve Domain to another.

Preserve Current ARCserve Domain Memberships

Retrieves the current information about ARCserve Domains, such as the ARCserve Domain name, Primary Server identity, and Member Server identities from the destination database before the restore begins. This information is written back after the restore is completed so that the information is preserved even after the restore. This option is enabled when the Automatic Selection, Leave Database Operational and Use Current ARCserve Database as Original Location options are selected.

Database File Options

Using the Database File options, you can:

■

View the list of files for the database and related information.

■

Select the files to be restored in a Files and FileGroup restore, or the FileGroups to be restored in a Partial Restore.

■

Change the location or file names of files during a restore. Use the location change function only when restoring from Full backups, or when using Automatic Selection.

54 Agent for Microsoft SQL Server Guide

Database File Options

You can move the files to a different location on disk either individually or using Move Rules. Move Rules can be applied to an entire database, individual FileGroup or Transaction Log, or to an individual file. You can specify rules to move files to a particular drive, directory path, or rename the file, all independently of each other. If you want to rename files at the Database or FileGroup level, you can specify a filename change using a wildcard pattern.

To change database file locations

1. On the Agent Restore Options dialog, select the Database File Options tab.

2. In the navigation tree, select one of the following options to apply the

change:

■ Select the database, if you want to apply the rule to all of the files in the

database.

■ Select a FileGroup or the Transaction Log, if you want to apply the rule to

the files in a particular FileGroup or the Transaction Log.

■ Select the file, if you want to change just a single file.

3. Under Restore Database Files As, select one of the following options, as

appropriate:

Restore to Original Location

Available at the Database level. Clears any changes to the drive letters, paths, and file names. You must still click the Apply button after selecting this option for the change to take effect.

Chapter 4: Restoring Microsoft SQL Server Databases 55

Database File Options

Restore to Original Location, Except

Available at the Database, FileGroup and Transaction Log, and File levels. Applies the requested changes to the drive letter, paths and file names based on the location of the file when the backup was performed.

Inherit Move Rules, Except

Available at the FileGroup and Transaction Log, and File levels. Applies the requested changes to the drive letter, paths and file names based on the changes which have already been made.

4. In the box labeled Database Move Rules, FileGroup Move Rules, or File Move

Rules, select one or more of the following options:

■ Select the Move To Drive check box and enter a different drive letter in

the field beside it.

■ Select the Move To Directory check box and enter a different directory

path in the field beside it.

■ Select the Filename Pattern Change check box, to change the filenames

for the entire database, FileGroup, or Transaction Log. Enter a wildcard pattern that matches the names of the files you want to rename in the field below and enter the wildcard pattern that you want it to be renamed to in the to field.

For example, if you want to rename all the files that begin with Group as Members, enter Group* in the field and Member* in the to field.

■ Select the Rename File check box and enter a different file name, to

rename a single file.

5. Click the Apply button.

The changes are applied to the tree.

Note: If you make a different selection or close the Agent Restore Options dialog without applying the changes, the selections you made will be lost.

If the rules you specify result in two files being given the same physical location on disk, a red indicator appears at the bottom of the dialog, and in the tree next to both the affected files and the object where the rule was applied.

6. Repeat steps 2 through 5 for each change you need to make.

Note: If you are using a wildcard pattern to rename files, and the pattern for the original filenames does not match with one or more of the files to which it would be applied, a yellow indicator will appear at the bottom of the dialog, and in the tree next to both the affected files and the object where the rule was applied.

56 Agent for Microsoft SQL Server Guide

Restore Databases Using Restore by Tree Method

To restore using the Restore by Tree method

1. On the Restore Manager Source tab, select Restore by Tree from the

drop-down list.

2. In the navigation tree, expand the computer from which the database was

backed up to view the database instances. Click the yellow database icon to expand the database instance containing the database you want to restore and click the database name to select it.

3. To use the most recent backup, go to the following step.

To use a backup other than the most recent one, click Version History. The Version History dialog opens.

Note: Version History provides information on the media name attached to each backup session, its backup session number, backup method, and the date and time the backup was made. You can select the session to restore from the backup media using Version History.

4. Right-click the database name you selected and choose Agent Options from

the pop-up window. The Agent Restore Options dialog appears. The dialog varies depending on the backup method of the selected session and the version of SQL Server from which the database was backed up.

5. Perform one of the following to select restore options:

■ Click OK to accept the default options and allow the Automatic Selection

option select the appropriate restore sequence and options for the restore job. The Automatic Selection option is the default option for every restore job.

■ Manually select the options you want to use for this restore sequence

and click OK. For more information about options, see Restore Options.

Important! The Automatic Selection option on the Agent Restore Options dialog automatically selects the sessions which must be restored

6. On the Source tab, ensure that the session you want to restore is selected.

and applies the selected options appropriately to each session.

Chapter 4: Restoring Microsoft SQL Server Databases 57

Restore Databases Using Restore by Tree Method

7. In the Restore Manager, click the Destination tab and select a destination

using one of the following procedures:

■ To restore to the original instance on the original server using the

original database name, select the Restore files to their original locations option if not selected already.

■ To restore to a different server but to an instance of Microsoft SQL

Server with the same version and instance name, clear the Original Location check box and select the destination machine. The destination machine must have an instance with the same name as the original and the same version of Microsoft SQL Server or higher.

■ To restore to a different server or a different instance on the original

server using the original database name, clear the Restore files to their original locations option and select the Microsoft SQL Server instance for the destination server.

■ To restore using a different database name, clear the Restore files to

their original locations option, select the destination server, and select the Microsoft SQL Server instance on the destination server. Enter a backslash and the new name of the database at the end of the displayed path, as in the following examples:

\\SERVER1\MSSQLSERVER\Lightning \\SERVER2\WEATHER\Thunder

Note: If you are restoring a database backed up from a Microsoft SQL Server 7.0 instance to a Microsoft SQL Server 2000, 2005 or 2008 instance, you must explicitly select the target instance even if it is a default instance.

8. Click Submit.

The Restore Media dialog appears.

9. Select the backup server where the restore job is going to run and click OK.

The Session User Name and Password dialog opens.

10. Verify or change the user name or password for the Windows computer on

which Microsoft SQL Server is loaded. To verify or change the user name or password, follow these steps:

a. Select a session on the Machine tab and click Edit. The Enter User Name

and Password dialog opens.

b. Enter or modify the user name and password. c. If a session password was assigned to this session, enter the session

password.

d. To apply the user name and password you entered to all the sessions you

are restoring, select the Apply [User Name and Password] to all rows

Click OK.

option.

58 Agent for Microsoft SQL Server Guide

Restore Databases Using Restore by Tree Method

11. Verify or change the user name or password for the database servers to

which you are restoring. To verify or change the user name or password for the database servers, follow these steps:

a. Select the DBAgent tab. b. Select a session and click Edit. The Enter User Name and Password

dialog opens.

c. Enter or modify the user name and password. d. If you want the user name and password you entered applied to all the

sessions you are restoring, select the Apply [User Name and Password]

Click OK.

to all rows option.

12. Click OK on the Session User Name and Password dialog. The Submit Job

dialog opens.

13. (Optional) Use the Submit Job dialog to select the job execution time, submit

the job on hold, add an optional job name for the backup job, or select source priority.

14. Click OK to submit this job. If you selected Run Now, the Job Status window

opens. Use this window to monitor your job. For more information about the Job Status window, see the Administration Guide.

Chapter 4: Restoring Microsoft SQL Server Databases 59

Restore Databases Using Restore by Session Method

To perform a restore operation using Restore by Session method

1. On the Restore Manager Source tab, select Restore by Session from the

drop-down list. A list of the media you have used when backing up with CA ARCserve Backup opens.

2. To create a filter to view only the sessions from a specific server or the

sessions from a specific database on a specific server, perform the following procedure:

a. Select the Filter tab. The Filter dialog opens. b. Click the SQL Agent Filter tab. The SQL Agent Filter dialog opens. c. Enter a machine name to restore the sessions from a particular

server, or a machine name and database name to restore the sessions of a particular database.

If you are using Microsoft SQL Server 2000 or Microsoft SQL Server

2005, you can also enter a machine name, instance name, and database name to restore a database from a specific instance of Microsoft SQL Server.

d. Click OK.

Note: Once you apply the filter settings, expand the media item to view the results. If the media item was already expanded, collapse it and expand it again to see the results.

3. Expand the media containing the backup you want to restore and select the

session containing the specific database or log you want to restore.

Note: Microsoft SQL Server backups have one Database, Partial, Files-and-FileGroups, or Transaction log backup for each session on the media.

4. Right-click the session containing the backup session you want to restore

and choose Agent Options from the pop-up window.

The Agent Restore Options dialog appears. This dialog varies depending on the backup method of the session selected, and the version of SQL Server from which the database was backed up.

60 Agent for Microsoft SQL Server Guide