How to restore a Sybase IQ database

Here are my notes on restoring a Sybase IQ 12.7 database from disk files to a new server using raw devices.

Preparing the Raw Devices

The first thing you have to do is to ensure that the raw devices on the new server are at least the same size as the original raw devices (actually they might have to be just a little bigger because of some extra metadata or something I can't remember). Also, when you build the new raw devices, ensure they have the same sector size and block size as the source's devices.  The first time I tried the restore with different sector sizes, I got the following error:

Invalid Block I/O argument, maybe <path to raw device> is a directory, or it exceeds maximum file size limit for the platform, or trying to use Direct IO on unsupported OS
-- (hos_bio.cxx 770)
SQLCODE=-1006089, ODBC 3 State="HY000"

The Sybase documentation does not tell you the solution, but rebuilding the raw devices with matching sector and block sizes solved the problem.

The RESTORE Statement

RESTORE DATABASE 'db_file'

FROM 'archive_device' [ FROM 'archive_device' ]...

... [ RENAME dbspace_name TO 'new_dbspace_path' ]...

My restore statement looked like this:

restore database '/localhome/sybase/storage_file/restored_database.db'

from '/backup/full/obtober_backup.full'

rename iq_main_dbspace01 to '/localhome/sybase/storage_raw/iq_mtest_dbspace01'

rename iq_main_dbspace02 to '/localhome/sybase/storage_raw/iq_mtest_dbspace02'

rename iq_main_dbspace03 to '/localhome/sybase/storage_raw/iq_mtest_dbspace03'

rename iq_main_dbspace04 to '/localhome/sybase/storage_raw/iq_mtest_dbspace04'

rename iq_temp_dbspace11 to '/localhome/sybase/storage_raw/iq_tmptest_dbspace11'

rename IQ_SYSTEM_MSG to '/localhome/sybase/storage_file/restored_database.iqmsg'

The rename clause specifies the new raw path for each dbspace. It does not change the dbspace name. If the raw target path does not exist (e.g. if you misspelled the path), the restore command will attempt to create it as a regular file, which is not what you want.  The following is the error I received when I did not specify the full path of the target.

Disk is or will be full on I/O to or allocation of file <target file path>

-- (hos_bion.cxx 182)

SQLCODE=-1006094, ODBC 3 State="HY000"

Also, you have to rename all dbspaces including the temp ones.  How do you find the original dbspace names to rename? One way to do it is to go to the original database and run the stored procedure sp_iqdbspace. It will show you the dbspaces and their original path. If the database is dead, go to ~/storage_raw and see if the links are still there. If not, run the command without the rename clauses and see what files are created.

Running the RESTORE command

On the target server, stop all instances of Sybase IQ:

stop_asiq

Start IQ using the Utility DB and restrict any other users from connection to the database:

start_asiq -gd DBA -gm 1 -n util_db -iqmc 10000 -iqtc 10000 -ch 1024M -cl 512M -iqmpx_sn 1 -iqmpx_ov 1

Assuming that the restore command is in file restore.sql, run the following command:

dbisql.sh -nogui -c "uid=DBA;pwd=your_dba_pwd;eng=util_db;dbn=utility_db;links=tcpip;" restore.sql

This could take several hours to complete. To follow the progress, check the log files in ~/asiq12/logfiles

Connecting to the New Database

To start the new database, you will need a new params.cfg file.  The most important change in the new params file is the database name. I changed my to "-n restored_database".

Now you can start the new database using one of the following commands:

start_asiq @params.cfg restored_database

or, if the original database was running in multiplex mode:

start_asiq -iqmpx_ov @params.cfg restored_database

The -iqmpx_ov 1 is the multiplex override switch and should be used if you restored to a new machine.