Skip to Content
0

backup and restore readonly user dbspace

Jan 06, 2017 at 10:46 AM

140

avatar image

Hello all,

we have an SAP IQ 16 SP 10 at an Linux System with VLDB licenses.

  • We create for each user a separate user dbspace incl. tables.

CREATE DBSPACE USERNAME_1 USING FILE USERNAME_1 '/PATH/USERNAME_1.iq' SIZE 500MB RESERVE 100MB IQ STORE;

CREATE DBSPACE USERNAME_2 USING FILE USERNAME_2 '/PATH/USERNAME_2.iq' SIZE 500MB RESERVE 100MB IQ STORE;

Then create the user her tables. (1 to 100).

CREATE TABLE USERNAME_1.SAMPLE_1 (id integer) IN USERNAME_1;

  • Then we backup the DB.

ALTER DBSPACE USERNAME_1 READONLY;

BACKUP DATABASE READONLY DBSPACES USERNAME_1 to '/PATH/USERNAME_1.dmp';

ALTER DBSPACE USERNAME_1 READWRITE;

  • The user create more tables.(1 to 100)

CREATE TABLE USERNAME_1.SAMPLE_2 (id integer) IN USERNAME_1;

  • After this, we would like to restore only one user dbspace USERNAME_1

ALTER DBSPACE USERNAME_1 READONLY;

ALTER DBSPACE USERNAME_1 OFFLINE;


Connect to the DB as user DBA or

dbisql -c "uid=dba;pwd=PASS;eng=our_engine" -nogui

dbisql -c "uid=dba;pwd=PASS;eng=our_utility;dbn=utility_db" -nogui

RESTORE DATABASE '/PATH_TO/OUR_DB.db' READONLY DBSPACES

USERNAME_1 FROM '/PATH/USERNAME_1.dmp';


The error message ...

Could not execute statement.
The backup no longer matches the version of the dbspace/dbfile in the
database and, therefore, cannot be restored.
--
(dblib/db_backupCmds.cxx 2219)
SQLCODE=-1012041, ODBC 3 State="HY000"


Best regards






10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

4 Answers

Best Answer
Volker Stöffler Jan 19, 2017 at 07:01 PM
0

Hi Jörn,

I'm not quite sure what you want to achieve. In your above sequence, you're trying to get rid of a set of tables created after a certain point in time. That obviously is quite easy to achieve by dropping those tables. But I guess what you want to achieve is less specific. It sounds like you want to back up and restore a single db space (like you would with an ASE database). As I outlined before, this is not part of the concept of IQ db spaces and databases. Backup and restore always (at least logically) are related to the whole database. The only way I'm aware of to use the granularity of users (which I think was your primary goal) or db spaces is to selectively export the table set and reload it as the equivalent of "restore". That would also include a method to handle all the metadata related to these objects (i.e. drop and recreate them, grant rights, ...). The iqunload utility then would be the closest system tool for that purpose.

For a more detailed discussion of your requirements, we might try to go to a direct communication channel...

HTH

Volker

Share
10 |10000 characters needed characters left characters exceeded
Volker Stöffler Jan 09, 2017 at 08:43 PM
0

Hi Jörn,

I doubt that what you're trying to achieve is possible. The essential idea of restoration is to restore the whole database including all containers to a backed up state. The distinction between read only and read / write db spaces is only intended to reduce the size of the backup archive / the amount of data being handled in a backup operation. It is not intended (as the error message indicates) to restore individual db spaces to a different point in time than others.

If I'm missing something, somebody else who knows better may be able to jump in.

HTH regardless

Volker Stöffler
DB-TecKnowledgy

Share
10 |10000 characters needed characters left characters exceeded
Jörn Grollmisch Jan 17, 2017 at 12:42 PM
0

Hi Volker,

thanks for your answer!

We search for our environment a "best practice solution".

Which is the optimal backup and restore way for our environment?

Can you give me a hint?

Share
10 |10000 characters needed characters left characters exceeded
Jörn Grollmisch Jan 27, 2017 at 11:09 AM
0

Hi Volker,

please klick on the "Follow" button so I can send you a direct message.

Share
10 |10000 characters needed characters left characters exceeded