Administration: Backup, Restore, and Data Recovery

1 Administration: backup , Restore, and DataRecoverySAP Sybase IQ ID: DC01759-01-1600-01 LAST REVISED: February 2013 Copyright 2013 by Sybase, Inc. All rights publication pertains to Sybase software and to any subsequent release until otherwise indicated in new editions ortechnical notes. Information in this document is subject to change without notice. The software described herein is furnishedunder a license agreement, and it may be used or copied only in accordance with the terms of that are provided only at regularly scheduled software release dates. No part of this publication may be reproduced,transmitted, or translated in any form or by any means, electronic, mechanical, manual, optical, or otherwise, without the priorwritten permission of Sybase, trademarks can be viewed at the Sybase trademarks page at Sybase andthe marks listed are trademarks of Sybase, Inc.

2 Indicates registration in the United States of and other SAP products and services mentioned herein as well as their respective logos are trademarks or registeredtrademarks of SAP AG in Germany and in several other countries all over the and all Java-based marks are trademarks or registered trademarks of Oracle and/or its affiliates in the and and the Unicode Logo are registered trademarks of Unicode, other company and product names mentioned may be trademarks of the respective companies with which they , duplication, or disclosure by the government is subject to the restrictions set forth in subparagraph (c)(1)(ii) of for the DOD and as set forth in FAR (a)-(d) for civilian , Inc.

3 , One Sybase Drive, Dublin, CA backup , Recovery , and to Back Up of Data of Archive for Operator Presence ..7 Specify the Type of Virtual Archive Devices ..9 Other backup for Tape and Restore Using from Errors During You Complete a Backups with Non-Sybase of Virtual backup with SAN Snapshot or Down the Up the Right from a System-Level backup ..20 Validating Your Your : backup , Restore, and Data RecoveryiiiBefore You RESTORE in the Correct Order ..28 Reconnecting After You the Transaction Log after you the Database After You Requires Exclusive Write Access.

4 31 Displaying Header from Errors During a Database Information about Backups and the backup of the backup the backup Dbspace Your Data backup and Routine backup and Performance for backup and Data with Read-Only Read-Only Recovery and Database and Repair sp_iqcheckdb Stored Issues Running of Index Error Sybase IQAnalysis of Allocation Allocation Problems using Recovery Forced a Server in Forced Recovery Mode ..58 Recovering Leaked Multiplex Databases .. 60 Problems Reported by Problems that DBCC Cannot Repair ..61 Dropping Inconsistent Indexes, Tables, Error of Online Support.

5 67 Solutions for Specific Flow for Server Recovery andDatabase Operational Connection Issues ..85 Troubleshooting Network Compatible Current Down Your Computer the Protocol Stack Layer by a TCP/IP Protocol Wiring Problems .. 92 Checking Common Network CommunicationsProblems .. 92 Diagnostic Tools ..93 Restoring to a New Temporary File sp_iqstatus Stored Notification : backup , Restore, and Data RecoveryvThe sp_iqcheckdb Stored Database and Server Startup the Currently Executing Server for Collecting Communications Problems to Technical Diagnostic Information Collected by Connection Information Betweenthe.

6 Srvlog and .iqmsg Web : Information for Technical DATABASE Sybase IQData backup , Recovery , and ArchivingTo protect your data, schedule and perform regular backups. You can also use read-onlyhardware to archive non-modifiable data for easy ProtectionSAP Sybase IQ provides a full set of features that protect you from two types of computerfailure, and from database inconsistency. A system failure occurs when the computer or operating system goes down while there arepartially completed transactions. This could occur when the computer is inappropriatelyturned off or rebooted, when another application causes the operating system to crash, orbecause of a power failure.

7 A media failure occurs when the database file, the file system, or the device storing thedatabase file, becomes a system failure, SAP Sybase IQ can usually recover automatically, so that you may notneed to restore your media failure, or if for any reason the data in your database is inconsistent, you mustrestore your database. To protect your data in all of these situations, make regular backups ofyour databases. In particular, you should back up your database each time you finish insertingany large quantities of new data into the failures occur, the Recovery mechanism treats transactions properly, as atomic units ofwork: any incomplete transaction is rolled back and any committed transaction is ensures that even in the event of failure, the data in your database remains in a to Back Up DatabasesUse the backup command to back up your SAP Sybase IQ includes both the SAP Sybase IQ data (the IQ store) and the underlying SAP SybaseSQL Anywhere database (the catalog store).

8 backup runs concurrently with read and write operations in the database. By contrast, during arestore no other operations are allowed on that must be connected to a database to back it up. The backup command has no way tospecify another backup , Recovery , and ArchivingAdministration: backup , Restore, and Data Recovery1 Types of Data StoresSAP Sybase IQ data stores consist of one or more can contain both user data and internal database structures used for startup, Recovery , backup , and transaction management. Typically, an SAP Sybase IQ database has thefollowing stores: is the catalog dbspace containing the system tables and stored proceduresdescribing the database and any standard SQL Anywhere database objects you add.

9 It isknown as the catalog store, and has the dbspace-name SYSTEM. You can create additionaldbspaces in the catalog store. is the main data dbspace containing the SAP Sybase IQ table data and is known as the IQ store, and has the dbspace-name IQ_SYSTEM_MAIN. The dbfilename matches dbspace-name, IQ_SYSTEM_MAIN. You can create multiple dbspaces inthe IQ store, and each dbspace can hold multiple dbfiles, includingIQ_SYSTEM_MAIN. is the initial temporary dbspace containing the temporary tables generatedby certain queries. It is known as the IQ temporary store and has the dbspace-nameIQ_SYSTEM_TEMP. You can add dbfiles to the IQ temporary of these stores, and the log files, are possible areas of of BackupsThere are four ways to back up SAP Sybase IQ data.

10 Database backup Operating system-level backup Virtual backup Archive backup (for log files)Types of Database BackupsSAP Sybase IQ provides four types of database backups: Full backup makes a complete copy of the database. Virtual backup copies all of the database except the table data from the IQ store. Incremental backup copies all transactions since the last backup of any type. Incremental-since-full backup copies all transactions since the last full these backup types fully back up the catalog store. In most cases, the catalog store is muchsmaller than the IQ store. If the catalog store is larger than (or nearly as large as) the IQ store,however, incremental backups of IQ are bigger than you may want or virtual backup is supported using the backup store data is not backed up.