Moving database files

If you need to move database files to a new location—for example, if one of your disk drives fails—you use one of the following methods:

Restoring to a raw device

When restoring to a raw device, make sure that the device is large enough to hold the dbspace being restored. IQ RESTORE checks the raw device size and returns an error, if the raw device is not large enough to restore the dbspace.

The operating system takes a small amount of space on the raw device and the IQ dbspace occupies the rest. When you restore the dbspace, your raw partition must hold both the IQ dbspace and the space reserved for the operating system.

To restore an IQ main or temporary dbspace to a raw partition, find the raw device size needed for each IQ dbspace from system tables as follows:

SELECT segment_type, file_name, block_count,
data_offset, block_size, 
(block_count * block_size) + data_offset AS raw_size
FROM SYS.SYSIQFILE, SYS.SYSIQINFO 
where segment_type !='Msg' ORDER BY 1,2 

The segment_type and file_name are informational. Segments of type ‘Main’ or ‘Temp’ may be stored on a raw partition, but message files (type ‘Msg’) may not. The file_name is the name of the dbspace.

The block_count column is an integer, the number of blocks used by IQ.

The data_offset column is an integer, the number of bytes reserved for the operating system.

The block_size column is an integer, the number of bytes per IQ block.

The raw_size column is an integer, the minimum size in bytes of a raw device needed to restore this dbspace. Sybase recommends restoring to a raw device that is at least 10MB larger than the original raw device.

Example 2 — Moving the catalog store

This example restores the same database as Example 1. In Example 2, however, you move the catalog store file and any database files that were created relative to it. To do so, you replace the original file name with its new location, c:\newdir, as follows:

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

Sybase IQ moves database files other than the catalog store as follows:

In other words, if you want to move an entire database, you should specify in a RENAME clause the new location for every IQ dbspace in the database—required, temporary, and user-defined. The SYSTEM dbspace is the only one you do not include in a RENAME clause.

If you only want to move some of the files, and overwriting the original files is not a problem, then you only need to rename the files you actually want to move.

You specify each dbfile name as it appears in the SYSIQDBFILE table. You specify new_dbspace_path as the new raw partition, or the new full or relative path name, for that dbspace.

You cannot use the RENAME option to specify a partial restore.

Relative path names in the RENAME clause work as they do when you create a database or dbspace: the main IQ store dbspace, temporary store dbspaces, and message log are restored relative to the location of db_file (the catalog store); user-created IQ store dbspaces are restored relative to the directory that holds the catalog store.

If you are renaming files while restoring both full and incremental backups, be sure you use the dbspace names and paths consistently throughout the set of restores. It is the safest way to ensure that files are renamed correctly.

If a dbspace was added between the full backup and an incremental backup, and you are renaming database files, you need one more RENAME clause for the incremental restore than for the full restore. Similarly, if a dbspace was deleted between backups, you need one fewer RENAME clause for the restores from any backups that occurred after the dbspace was deleted.

See “Recording dbspace names” for information on how to obtain a list of the dbspace names in your database, so that you know the correct names to include in RENAME clauses.

Example 3 — Moving a user dbspace

This example shows how you restore the full and incremental backups in the example shown earlier in this chapter. In this case, media failure has made a UNIX raw partition unusable. The user-defined dbfile on that raw partition, IQ_USER, must be moved to a new raw partition, /dev/rdsk/c1t5d2s1. No other database files are affected.

First, you connect to the utility_db database. Then you restore the full backup from two tape devices. In this case they are the same two tape devices used to make the backup, but the devices could differ as long as you use the same number of archive devices, the same media type (tape or disk), and the same tape sets in the correct order, as described in “Restoring in the correct order”.

The first RESTORE command is:

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 RESTORE command, to restore the incremental backup, is:

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'

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