Recording Dbspace Names

In the event that you ever need to use the RENAME option of RESTORE to move a database or one of its dbspaces, you need to know the name of every dbspace in the database.

The dbspace names are in the SYSFILE table of every database, but you do not have this table available when you are restoring. Run db_backupheader on the first backup archive file path to view this information. Alternatively, you may execute the sp_iqdbspace and sp_iqfile stored procedures or issue the following statement any time you back up your database:

SELECT dbf.dbfile_name, f.*
FROM SYSFILE f, SYSDBFILE dbf
WHERE f.file_id=dbf.dbfile_id

Keep the results of this query some place other than the disk where the database resides, so that you have a complete list of dbspace names if you need them.

You can also run the following script in Interactive SQL. This script produces an output file that contains the set of rename clauses you use, if you do not actually change the location of any files. You can substitute any new file locations, and use the resulting file in your RESTORE statement.

Note: Because the database may not exist when you need to restore, you may want to run this script after you back up your database.

-- Get dbspace and IQ file names and add 
-- rename syntax including quotation marks

select 'rename' as 'restore ... rename' ,
dbf.dbfile_name as 'IQ file' , 'to' as 'to' ,
'''' + f.file_name + '''' as 'file_path'
from SYSFILE f, SYSDBFILE dbf
where f.store_type=2 and f.file_id=dbf.dbfile_id

 -- Send output to a file in proper format
 -- without delimiters or extra quotation marks

output to restore.tst delimited by ' ' quote ''; 

 -- This produces a restore.tst file like the following:
 -- rename IQ_SYSTEM_MAIN to '/dev/rdsk/c2t0d1s7'
 -- rename IQ_SYSTEM_TEMP to '/dev/rdsk/c2t1d1s7'
 -- rename IQ_SYSTEM_MSG to 'all_types.iqmsg'