Moving Database Files

Redirect the Catalog Store

To move the catalog store (db_file) to a new location on the file system, redirect the catalog to the target directory with a new db_file file name:

RESTORE DATABASE 'new-file-path''new-db_file-name'
  FROM  'archive_device '
  ...

RESTORE copies the dbfiles to the target location, and renames the catalog (db_file) to new-db_file-name.db. For example, to redirect a backup of iqdemo.db to a new location on the file system, use:

RESTORE DATABASE 'c:\\newdir\\iqnew.db'
 FROM 'c:\\iq\\backup1'
 FROM 'c:\\iq\\backup2'

Contents of the newdir directory:

iqdemo.iq
iqdemo.iqmsg
iqdemo.iqtmp
iqdemo.log
iqdemo_main.iq
iqnew.db

RESTORE renamed the backed up catalog (iqdemo.db) to iqnew.db. All other dbfile names remain unchanged.

RENAME Clause

Use the RENAME clause to move one or more database files to a new location:

RESTORE DATABASE 'new-database-name'
 'FROM  'archive_device '
 RENAME file-name TO new-file-path
 ...

Specify each dbfile file_name as it appears in the SYSIQDBFILE table. Specify the new-dbspace-path as the new raw partition, full, or relative path for that dbspace.

Do not use the RENAME clause to move the SYSTEM dbspace, which holds the catalog store. To move the catalog store, and any relative files not specified in a RENAME clause, specify a new location as part of the new-database-name parameter.

This example moves a user-defined dbfile (iquser) on a raw partition to a new raw partition (/dev/rdsk/c1t5d2s1). No other database files are affected. The first code block restores the full backup:

RESTORE DATABASE 'iquser' 
 FROM '/dev/rmt/0n'
 FROM '/dev/rmt/1n'
 RENAME IQ_SYSTEM_MAIN TO '/dev/rdsk/c2t0d1s1'
 RENAME IQ_SYSTEM_TEMP TO '/dev/rdsk/c2t1d1s1'
 RENAME IQ_SYSTEM_MSG TO 'iquser.iqmsg'
 RENAME IQ_USER TO '/dev/rdsk/c1t5d2s1'

The second code block restores the incremental backup:

RESTORE DATABASE 'iquser' 
 FROM '/dev/rmt/0n'
 RENAME IQ_SYSTEM_MAIN TO '/dev/rdsk/c2t0d1s1'
 RENAME IQ_SYSTEM_TEMP TO '/dev/rdsk/c2t1d1s1'
 RENAME IQ_SYSTEM_MSG TO 'iquser.iqmsg'
 RENAME IQ_USER TO '/dev/rdsk/c1t5d2s1'

In this example, you can also issue these commands using only the last RENAME clause, since only one dbspace is being restored to a new location. Listing all files or raw partitions, as shown here, ensures that you know exactly where each will be restored.

Note: When you move a database, you may need to modify your data sources, configuration files, and integrated logins to reflect the new location.

Transaction Log

When you move a database, you can rename all files except the transaction log. SAP Sybase IQ continues to write to the old log file name, in the location where the catalog store file (the .db file) is after the database is restored.

Use dblog to move or rename the .log file:

dblog[options] database-file

Before issuing the command, stop the server. After you rename the log, retain the old log until the next backup, in case it is needed for recovery from a media failure.

See Utility Guide > dblog Database Administration Utility.