Computer Associates ARCserve Backup for Windows Manual Download

Page: 1 / 137

Agent for Microsoft SQL Server Guide

r15

CA ARCserve

Backup for

Windows

Summary of Contents for ARCserve Backup for Windows

Page 1: ...Agent for Microsoft SQL Server Guide r15 CAARCserve Backupfor Windows...

Page 2: ...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 DOC...

Page 3: ...serve 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 Serv...

Page 4: ...serve 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 Libr...

Page 5: ...sistance 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...

Page 6: ...al 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 inclu...

Page 7: ...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 M...

Page 8: ...ore 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...

Page 9: ...uthentication 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...

Page 10: ......

Page 11: ...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 Ba...

Page 12: ...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...

Page 13: ...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 Bac...

Page 14: ...he 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 t...

Page 15: ...nd 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...

Page 16: ...e 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 Overr...

Page 17: ...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...

Page 18: ...gentRmtInst 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 Eleme...

Page 19: ...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 D...

Page 20: ......

Page 21: ...re is certain prerequisite information you must satisfy before installing the Agent for Microsoft SQL Server in a standard Microsoft SQL environment or a Microsoft SQL Server 2000 or later cluster env...

Page 22: ...stallation Prerequisites 22 Agent for Microsoft SQL Server Guide Install the Agent for Microsoft SQL Server on the local drives of all nodes in the MSCS cluster as part of the initial agent installati...

Page 23: ...rror AE50015 Backup Failed or AE51004 Restore Failed To address this error perform the following procedure 1 Locate the version of sqlvdi dll that belongs to the newest version of Microsoft SQL Server...

Page 24: ...n 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...

Page 25: ...rosoft 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...

Page 26: ...t 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 Sett...

Page 27: ...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...

Page 28: ...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 t...

Page 29: ...cking 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 Transact...

Page 30: ...he 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 J...

Page 31: ...gent 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...

Page 32: ...up 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 ARCser...

Page 33: ...ll 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 change...

Page 34: ...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 selec...

Page 35: ...efault 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 m...

Page 36: ...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 backu...

Page 37: ...CC 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 D...

Page 38: ...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...

Page 39: ...e 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 instan...

Page 40: ...f 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...

Page 41: ...ect 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...

Page 42: ...ndow 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...

Page 43: ...roupA 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 thi...

Page 44: ......

Page 45: ...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...

Page 46: ...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 Compl...

Page 47: ...Restore to Alternative Disk Locations Using Automatic Selection Restore Types The Agent for Microsoft SQL Server supports the following types of restores Database restore Restores the entire database...

Page 48: ...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 t...

Page 49: ...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 damage...

Page 50: ...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 specif...

Page 51: ...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 l...

Page 52: ...ly 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 loca...

Page 53: ...ctioning properly This option is available for Microsoft SQL Server 7 0 and Microsoft SQL Server 2000 Continue Restore After Checksum Failure This option allows Microsoft SQL Server 2005 to continue p...

Page 54: ...nal 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 thi...

Page 55: ...ile 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 wan...

Page 56: ...s 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 beg...

Page 57: ...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 Restor...

Page 58: ...cations 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...

Page 59: ...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 to all rows option Click OK 12 Click OK on the S...

Page 60: ...lar 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...

Page 61: ...ent 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...

Page 62: ...e 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 E...

Page 63: ...veral databases with the same matching character set in the name they are all displayed 4 Click OK Note Once you apply the filter settings expand the media item to view the results If the media item w...

Page 64: ...er in CA ARCserve Backup to restore databases by session using a single restore job To restore databases by session using a single restore job 1 On the Restore Manager Source tab select Restore by Ses...

Page 65: ...expand the session containing that backup and select the most recent full database backup of the database you want to restore This is the full backup on which the more recent backup sessions depend 3...

Page 66: ...In the navigation tree expand the computer from which the database you want to restore was backed up Click the yellow database icon to expand the database instance containing the database you want to...

Page 67: ...ession to be restored confirm that the Leave database operational No additional transaction logs can be restored option is selected under Recovery Completion State 11 Click OK to close the Agent Resto...

Page 68: ...ase is using the Simple Recovery Model 2 Optional Perform a Database Consistency Check DBCC CheckDB on the database to locate any additional damaged pages beyond the one already reported This can be d...

Page 69: ...store Using Microsoft SQL Server 2005 or 2008 Chapter 4 Restoring Microsoft SQL Server Databases 69 j Submit the Restore job 6 Change to Simple Recovery Model if you changed the Recovery Model in step...

Page 70: ...soft SQL Server 2005 or 2008 Enterprise Data Center or Developer Editions 1 Change to Full Recovery Model if the database is using the Simple Recovery Model 2 Optional Perform a Database Consistency C...

Page 71: ...empt to query the table that contained the damaged page 5 Perform a Transaction Log backup with the default options 6 Perform a restore of this final Transaction Log backup without Automatic Selection...

Page 72: ......

Page 73: ...see page 77 Restore by Session in Microsoft SQL Server 2000 2005 or 2008 Environments see page 80 Perform Disaster Recovery in Microsoft SQL Server 2000 2005 or 2008 Cluster Environments see page 83 B...

Page 74: ...agent installation Microsoft SQL Server 2000 2005 or 2008 Cluster Environment Backups The following sections provide the procedures to back up data in Microsoft SQL Server 2000 2005 or 2008 cluster e...

Page 75: ...Edition that is used as the ARCserve Database of a clustered CA ARCserve Backup installation should be browsed through the virtual server name associated with the clustered CA ARCserve Backup primary...

Page 76: ...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...

Page 77: ...tore Manager Source tab select Restore by Tree from the drop down list 2 In the navigation tree expand the Microsoft SQL Server virtual server name from which the database you want to restore was back...

Page 78: ...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 mu...

Page 79: ...n which the Microsoft SQL Server instance is still running Specify a domain user with the format DomainName UserName 13 Verify or change the user name or password for the database servers to which you...

Page 80: ...base and 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 If you do...

Page 81: ...ver 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 dest...

Page 82: ...crosoft SQL Server instance is still running Specify a domain user with the format DomainName UserName 11 Verify or change the user name or password for the database servers to which you are restoring...

Page 83: ...in a Microsoft SQL Server 2000 2005 or 2008 cluster environment 1 Reinstall Microsoft SQL Server and re create the Microsoft SQL Server virtual server 2 Reinstall the Agent for Microsoft SQL Server if...

Page 84: ......

Page 85: ...his section contains the following topics General Considerations for CA ARCserve Backup and the Agent see page 86 Upgrade Considerations for the Agent for Microsoft SQL see page 88 Skip or Include Dat...

Page 86: ...ws authentication to perform Virtual Devices based backup and restore operations you must have system administrator rights for Microsoft SQL Server This is a Microsoft requirement The CA ARCserve Back...

Page 87: ...e out value After the restore is done you should reset the value to 1200 The SendTimeOut and ReceiveTimeOut keys can be found on the CA ARCserve Backup Server machine under HKEY_LOCAL_MACHINE SOFTWARE...

Page 88: ...crosoft SQL Server will be unaware of the new or changed instances and you may not have access to additional instances or the additional features of the updated instance You should also perform your f...

Page 89: ...certain database files during backup jobs Use of these keys is determined by the type of database agent you are using Agents that use the SkipDSAFiles registry key Agent for Oracle Agent for SAP R 3...

Page 90: ...ft SQL Server at the start of a file backup This list typically includes but not exclusively ldf mdf ndf Except distmdl mdf distmdl ldf mssqlsystemresource mdf mssqlsystemresource ldf which cannot be...

Page 91: ...osoft SQL Server manual for information on backup and restore operations No Icon in Browse Tree No Microsoft SQL Server icon in the the backup source or restore destination tree Reason This can occur...

Page 92: ...code may be embedded in the message to indicate the reason for failure Action Check the network connections and verify that the Agent for Microsoft SQL Server services are running Increase the Receiv...

Page 93: ...be appended to the message Action To address this error 1 Verify that the CA Backup Agent Universal Agent service is running on the target server 2 Verify that the target database server instance is...

Page 94: ...and retry the operation 3101 Exclusive access could not be obtained because the database is in use Reason An attempt was made to restore a database while another program was accessing that database A...

Page 95: ...cial character for example or by default Microsoft SQL Server names the file FileGroup or database with the same characters To perform a backup or restore job rename the file FileGroup or database so...

Page 96: ...d For each database status the default behavior is listed for each selection type as well as the setting name that changes it The procedure for creating and setting DWORDS follows the table Database S...

Page 97: ...some other reason Message Type Error Job Result Failure Setting Name Inaccessible Explicit Message Type Error Job Result Failure Setting Name Inaccessible Implicit Note SQL 2005 Point In Time Snapsho...

Page 98: ...SQL Server has no knowledge of databases that have been removed from the SQL Server instance Microsoft SQL Server Disaster Recovery The Agent for Microsoft SQL Server uses the Microsoft SQL Server Bac...

Page 99: ...ation create or drop a database or add a device you should perform an offline backup Performing a Full Database backup of the master model and msdb databases in the same job generates a Microsoft SQL...

Page 100: ...have an offline backup or a Disaster Recovery Elements session and you have the Microsoft SQL 7 0 or 2000 rebuildm exe utility use the utility to recreate the master and model database For SQL 2005 an...

Page 101: ...ication For example you must use Microsoft SQL Server authentication for Microsoft SQL Server 7 0 or Microsoft SQL Server 2000 when the database is running in a cluster Authentication Requirements For...

Page 102: ...and change the ODBC open database connectivity settings 4 Update existing backup jobs in CA ARCserve Backup More information regarding the steps in this process are contained in the following section...

Page 103: ...n 1 From the Start menu start the Microsoft SQL Agent Account Configuration utility The Account Configuration dialog opens 2 Locate the Microsoft SQL Server instance you modified in the first part of...

Page 104: ...re prompted to indicate whether Microsoft SQL Server should use Windows authentication or Microsoft SQL Server authentication to verify the authenticity of the login ID If you are using Windows Only f...

Page 105: ...pending backup jobs already scheduled for this Microsoft SQL Server select the Job Queue folder tab in the Job Status Manager 3 Select the first job you need to update right click and select Modify f...

Page 106: ......

Page 107: ...nce as you design your strategy The decisions you make during this critical analysis phase govern the backup and restore options available in CA ARCserve Backup The following information is provided t...

Page 108: ...store database information across multiple disks and to manage database growth Data Files can include full text search catalogs which allow you to search on the full text catalogs stored with a datab...

Page 109: ...Secondary Data Files ndf grouped in two user defined FileGroups and stored on three separate disks You can create a table on each FileGroup so that any queries against the data in a table can then be...

Page 110: ...the database are therefore included in the database file set you can back up You can perform a complete full or a differential backup and restore of a full text catalog An individual full text index c...

Page 111: ...log file on a disk that does not also contain data files If you are using Microsoft SQL Server 2005 or later you can partition tables across multiple FileGroups to speed query access times queries sc...

Page 112: ...roup contains fewer columns but the same number of rows There are two types of vertical partitioning Normalization This moves redundant columns from a table and stores them in smaller tables linked to...

Page 113: ...ts and is limited to all changes made since the last backup Full Allows your database to be recovered to the point of failure or any point in time Including transaction log backups is required so you...

Page 114: ...SQL Server 2005 or later The Resource Database is read only It contains system objects required by SQL Server It is not mounted as a live database so the files are included in a file system backup ra...

Page 115: ...nges or differences that have been made within a database and can be combined with an earlier backup to create a newer image Use CA ARCserve Backup and the Agent for Microsoft SQL Server to generate b...

Page 116: ...h SQL database files For more information refer to the topic Skip or Include Database Files in Backups see page 89 Important Performing a backup can slow the system down For better backup performance...

Page 117: ...tabase When you enable the Database Consistency Check option for a backup the DBCC performs the following tests DBCC CHECKDB Checks the allocation and structural integrity of all objects in the specif...

Page 118: ...re the entire Log file and truncate the log so it does not grow too large A Full backup can be ordered from the Backup Method selections of the Agent Options from the Global Agent Options or from the...

Page 119: ...ll database backups They are also typically more efficient because they require less space on media and have a briefer impact on database performance than frequent full backups Additionally you can us...

Page 120: ...orm a Transaction Log backup of a damaged database If the database is in the Suspect or Damaged state and its transaction log files are intact you will be able to perform a Transaction Log backup with...

Page 121: ...later allow both Full and Differential File and FileGroup backups If a File and FileGroup Differential backup is performed restore of this session is dependent on the last Full backup of each file be...

Page 122: ...n the backup job is run in one or more error messages which will be written to the Activity Log by the Agent for Microsoft SQL Server Partial Backups Microsoft SQL Server 2005 introduces a special typ...

Page 123: ...ore sequences manually Restore Overview To restore is to load a database from a backup of that database and if applicable one or more backups of its Transaction log If a database is lost or damaged yo...

Page 124: ...tate and will be unusable until the restore sequence is completed If the session that was being restored when the job was cancelled is not the first session in the restore sequence you may have to sta...

Page 125: ...tore by Session for Microsoft SQL Server databases Checklists for Dependencies by Type Using the Restore by Tree or Restore by Session method you can perform various types of restores Each type has sp...

Page 126: ...k is the Microsoft SQL Log Backup transaction which is completed as part of the restore process instead Transaction Log Restores can only be performed from Transaction Log Backups Selected Session Pre...

Page 127: ...ith Truncation and Transaction Log Tail backups Partial Full Backup None For more information see Note 2 see page 130 Use one of the following All successive Transaction log backups All successive Tra...

Page 128: ...t is a subset of the database FileGroups that are not restored are marked as offline and are not accessible Due to some FileGroups being offline a database that was created from a Partial Restore may...

Page 129: ...All successive Transaction log backups All successive Transaction Log backups with Truncation and Transaction Log Tail backups Partial Full Backup None For more information see Note 2 see page 130 Us...

Page 130: ...s vary between File and FileGroup Full and File and FileGroup Differential backups CA ARCserve Backup does not detect these conditions in this release Alternatively restoring Transaction Log backups t...

Page 131: ...very Elements session to its original location will restore the files to the location where the master and model database files existed Restoring them to an Alternate Location and selecting a disk or...

Page 132: ...includes some secondary services which are part of Microsoft SQL Server such as the SQL Server Automation Agent SQL Agent service The only exception to this is CA ARCserve Backup itself the Agent for...

Page 133: ...Microsoft SQL Server Databases 15 29 76 backup actions requiring a full backup after 116 Backup Agent Options dialog 42 Backup Manager 42 configuring parameters 26 data flow steps with agent 14 datab...

Page 134: ...122 D Database consistency 117 locking 123 restoring lost or damaged 123 database consistency check defined 117 options 117 Database Consistency Check DBCC Options 31 36 46 52 Database Consistency Che...

Page 135: ...ing 38 K Keep Replication Settings option 54 L Limitations of Microsoft SQL Server Database 95 load 123 loading transaction logs sequentially 47 Log Point in Time option explained 50 limitations 50 op...

Page 136: ...47 automatic selection option 47 automatic selection automatic selection 47 avoiding problems 99 by session using separate restore job for each session 65 by session using single restore job for each...

Page 137: ...ct Server Protocol Security and Backup Type 74 Skip or Include Database Files in Backups 89 116 SQL Agent Filter option 63 SQL Agent Filter Options 63 SQL Server DBAgent Configuration dialog 26 standa...

Search results

Summary of Contents for ARCserve Backup for Windows

Reviews:

Related manuals for ARCserve Backup for Windows

Brands by name

Popular brands

Computer Associates ARCserve Backup for Windows, Manual

Search results

Summary of Contents for ARCserve Backup for Windows

Reviews:

Related manuals for ARCserve Backup for Windows

Brands by name

Popular brands