Types of backups

There are four ways to back up Sybase IQ data:

• Database backup

• Operating system-level backup

• Virtual backup

• Archive backup (for log files)

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 backup.

All these backup types fully back up the Catalog Store. In most cases, the Catalog Store is much smaller 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 expect.

Incremental virtual backup is supported using the BACKUP statement.

Temporary Store data is not backed up. However, the metadata and any other information needed to recreate the Temporary Store structure is backed up.

This procedure summarizes backup steps. Read the rest of this chapter for complete details before you perform a backup.

1. Connect to the server using an account with DBA privileges. For a multiplex database, you must connect to the write server.

2. Run the BACKUP command. For complete syntax, see the Sybase IQ Reference Manual.

   It backs up the following files:

   • Catalog Store (SYSTEM dbspace file), typically named dbname.db

   • The transaction log file, which is required for system recovery, typically named dbname.log

   • All dbspace files of the IQ Store

3. Make a copy of the params.cfg file for each server. BACKUP does not back it up.

4. Save the lengths of the following files:

   • All dbspace files on the write server

   • IQ Temporary Store

Enter commands in the following procedure without line breaks.

1. Connect to the query server using dbisql or dbisqlc. Enter the following command:

   BACKUP DATABASE FULL to 'full_path_to_backup_file'
   

2. Stop the query server.

3. In the write server’s directory, enter the following from a Command Prompt:

   sync_qnode SQL query_server_name
   

4. In the query server’s directory, enter the following command:

   dbbackup -y -d <full_path_of_the_query_server_dir> -c "uid=DBA;pwd=SQL;eng=<writer_name>;links=tcpip{host=<writer_host>;port=<writer_port>}"
   

5. Delete the log file. For example:

   rm <database>.log
   

   If the log file or database name on the query server is different from the write server’s files, use the dblog utility to set the transaction log file name. You may use the relative path of the query server’s database file. For more about dblog, see Chapter 3 in the Sybase IQ Utility Guide. For example:

   dblog -r -t all_types.log all_types.db 
   

6. Start the query server without the database as:

   start_asiq @params.cfg -n query_server_name -x 'tcpip{port=query_server_port}'
   

7. Connect to the query server using dbislqc or dbisql and specify the utility database in the connect string. Enter the following command:

   RESTORE DATABASE '<database_name>' FROM '<full_path_to_backup_file>'
   

8. Stop the query server that was started without the database.

9. Restart the query server via Sybase Central.