SAP Sybase IQ: How to Restore Your Backups to Another system

[Former Member]

SAP Sybase IQ: How to Restore Your Backups to Another systemJason L. Froebe - Tech tips and How Tos ...

SAP/Sybase’s documentation isn’t very clear for new IQ dbas and developers. Once such item is simply restoring an IQ database on to another system. Unlike ASE, you need to specify the new file locations if they are different than the source server.

Assumptions:

1. IQ software has been installed

2. The new dbfile locations are symbolic links to raw partitions OR the path exists but not the files

3. You have a valid SYSAM license for the new IQ instance.

4. The new IQ instance name is set (via -n instance)

5. The old directory for the .db, .log and .mir exists (use a symbolic link if you wish)

Obtain dbspace file names with sp_iqfile:

select DBFileName, Path, DBFileSize from sp_iqfile();

DBFileName Path DBFileSize

'IQ_SYSTEM_MAIN' '/dba/syb/old_iq/sybdev/IQ_MAIN/old_iqmain001.iq' '32G'

'IQ_USER_MAIN_FILE_01' '/dba/syb/old_iq/sybdev/IQ_USER_MAIN/old_iqusermain001.iq' '1024G'

'IQ_SYSTEM_TEMP' '/dba/syb/old_iq/sybdev/IQ_TEMP/old_iqtemp001.iqtmp' '32G'

'IQ_SYSTEM_TEMP_002' '/dba/syb/old_iq/sybdev/IQ_TEMP/old_iqtemp002.iqtmp' '32G'

Create a restore.sql file renaming the DBFileName to the new locations:

restore database 'new_iq'

FROM '/dba/backup/sybbackup/old_iq.20140423100111.17760.IQfullbkp'

RENAME IQ_SYSTEM_MAIN TO '/dba/syb/new_iq/sybdev/IQ_MAIN/new_iqmain001.iq'

RENAME IQ_SYSTEM_TEMP TO '/dba/syb/new_iq/sybdev/IQ_TEMP/new_iqtemp001.iq'

RENAME IQ_SYSTEM_TEMP_002 TO '/dba/backup/sybbackup/new_iqtemp002.iq'

RENAME IQ_SYSTEM_MSG TO '/dba/syb/new_iq/instlog/new_iq.iqmsg'

RENAME IQ_USER_MAIN_FILE_01 TO '/dba/syb/new_iq/sybdev/IQ_USER_MAIN/new_iqusermain001.iq';

Stop the destination IQ instance if it is running and start the utility database:

$ stop_iq

Checking system ...

The following 1 server(s) are owned by 'sybdba'

## Owner          PID   Started  CPU Time  Additional Information

-- ---------  -------  --------  --------  ------------------------------------

1: sybdba       13909     Apr24  00:43:46  SVR:new_iq DB:new_iq PORT:58116

              /dba/syb/new_iq/sybase/IQ-16_0/bin64/iqsrv16 @/dba/syb/new_iq/sybdb/new_iq.cfg /dba/syb/new_iq/sybdb/new_iq.db -gn 65 -o /dba/syb/new_iq/sybase/IQ-16_0/logfiles/

${SYBASE}/IQ-16_0/bin64/start_iq -n utility_db -gu dba -c 48m -gc 20 -iqgovern 30 \

        -gd all -gl all -gm 10 -gp 4096 -ti 4400 -z -zr all -zo $SYBASE/IQ-16_0/logfiles/utility_db.out \

        -o $SYBASE/IQ-16_0/logfiles/utility_db.srvlog -iqmc 100 -iqtc 100 -x "tcpip{port=9000}"

Starting server utility_db on localhost at port 9000 (04/30 09:37:16)

Run Directory       : /dba/syb/new_iq/sybdb

Server Executable   : /dba/syb/new_iq/sybase/IQ-16_0/bin64/iqsrv16

Server Output Log   : /dba/syb/new_iq/instlog/utility_db.srvlog

Server Version      : 16.0.0.653/sp03 16.0.0/Linux 2.6.18-194.el5

Open Client Version : N/A

User Parameters     : '-n' 'utility_db' '-gu' 'dba' '-c' '48m' '-gc' '20' '-iqgovern' '30' '-gd' 'all' '-gl' 'all' '-gm' '10' '-gp' '4096' '-ti' '4400' '-z' '-zr' 'all' '-zo' '/dba/syb/new_iq/instlog/utility_db.out' '-o' '/dba/syb/new_iq/instlog/utility_db.srvlog' '-iqmc' '100' '-iqtc' '100' '-x' 'tcpip{port=9000}'

Default Parameters  : -gn 25

….

Remove the db, log and mir files:

$ rm instance.db instance.log instance.mir

Restore the full backup:

dbisql -c "uid=dba;pwd=sql;eng=utility_db;dbn=utility_db" -port 9000 -host $( hostname ) -nogui "restore.sql"

Restore the incremental backup(s):

dbisql -c "uid=dba;pwd=sql;eng=utility_db;dbn=utility_db" -port 9000 -host $( hostname ) -nogui "restore_incrementals.sql"

Stop the utility database:

$ stop_iq

Start the IQ server to ensure it comes up then shut it back down.

If the name of the server has changed (e.g. old_iq -> new_iq), then we need to update the log and mir files. First let’s find out where the log and mir files are currently set to in the db file:

$ dblog new_iq.db

SQL Anywhere Transaction Log Utility Version 16.0.0.653

"new_iq.db" is using log file "/dba/syb/old_iq/sybdb/old_iq.log"

"new_iq.db" is using log mirror file "/dba/syb/old_iq/sybdb/old_iq.mir"

Transaction log starting offset is 0702994164

Transaction log current relative offset is 0000397583

Set the log file to “new_iq.log”:

$ dblog -t new_iq.log new_iq.db

SQL Anywhere Transaction Log Utility Version 16.0.0.653

"new_iq.db" was using log file "/dba/syb/old_iq/sybdb/old_iq.log"

"new_iq.db" is using log mirror file "/dba/syb/old_iq/sybdb/old_iq.mir"

"new_iq.db" is now using log file "new_iq.log"

Transaction log starting offset is 0702994164

Transaction log current relative offset is 0000397625

We need to clear the mir file(s) before we can assign a new one:

$ dblog -r new_iq.db

SQL Anywhere Transaction Log Utility Version 16.0.0.653

"new_iq.db" is using log file "new_iq.log"

"new_iq.db" was using log mirror file "/dba/syb/old_iq/sybdb/im00.mir"

"new_iq.db" is now using no log mirror file

Transaction log starting offset is 0702994164

Transaction log current relative offset is 0000397625

Set the mir file:

$ dblog -m new_iq.mir new_iq.db

SQL Anywhere Transaction Log Utility Version 16.0.0.653

"new_iq.db" is using log file "new_iq.log"

"new_iq.db" was using no log mirror file

"new_iq.db" is now using log mirror file "new_iq.mir"

Transaction log starting offset is 0702994164

Transaction log current relative offset is 0000397625

Start your IQ instance.