Redirected restore of a V7 DB to a V8 Instance

jrichardson · #1 (**permalink**) 04-09-09, 08:54

currently running a local instance of DB2 on Windows - Version 7

This box has to follow a migration plan to V9 that has been set up to follow the migration plan on the Mainframe to DB2 V9. (V7 to V8 compatablity mode to Version 9)

I have about 6 smallish Databases on this local DB2 on Windows Instance.

a new Server was built to eventually replace the old server running version 7 and I installed DB2 V8 on the new server.

The trouble I am having now is figuring out how to do a restore of a V7 Database into my new V8 Instance.

I've tried a number of different ways including - creating the DB via the GUI and doing a restore from file (V7 Backup) . I get this error: SQL2542N No match for a database image file was found based on the source

I also tried a re-directed restore like this:

Code:

CONNECT TO DBINGWP1 user kcapdba1 using kcapdba1;
QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS;
CONNECT RESET;

RESTORE DATABASE DBINGWP1 FROM "C:\V7Backups\DBINGWP1" TO "E:" INTO DBINGWP1 WITH 2 BUFFERS BUFFER 1024 REDIRECT PARALLELISM 1 WITHOUT PROMPTING;
SET TABLESPACE CONTAINERS FOR 0 IGNORE ROLLFORWARD CONTAINER OPERATIONS USING (FILE "E:\DBINGWP1\SYSCATSPACE\DB2CAT" 640000 );

SET TABLESPACE CONTAINERS FOR 1 IGNORE ROLLFORWARD CONTAINER OPERATIONS USING (FILE "E:\DBINGWP1\TEMPSPACE1\TMPSP" 640000 );

SET TABLESPACE CONTAINERS FOR 2 IGNORE ROLLFORWARD CONTAINER OPERATIONS USING (FILE "E:\DBINGWP1\DFLT" 640000 );


SET TABLESPACE CONTAINERS FOR 3 IGNORE ROLLFORWARD CONTAINER OPERATIONS USING (PATH "E:\DBINGWP1\TBLSP1" 64000 );

RESTORE DATABASE DBINGWP1 CONTINUE;
ROLLFORWARD DATABASE DUTDM TO END OF LOGS AND COMPLETE NORETRIEVE;

redirected restore did not work... same error as above.

can anyone help get me started here???

n_i · #2 (**permalink**) 04-09-09, 09:19

What is the output of "dir /s C:\V7Backups\DBINGWP1"?

rdutton · #3 (**permalink**) 04-09-09, 09:58

try this syntax:
db2 restore database BACKEDUPDBNAME from "c:\db2\data\backup" taken at 20090304123329 into NEWDBNAME newlogpath "c:\db2\data\yourdb\logs" redirect

The newlogpath parameter is only if you have a specific location you want logs to go. This also assumes that NEWDBNAME already has been created.

jrichardson · #4 (**permalink**) 04-09-09, 13:33

The output of this dir /s C:\V7Backups\DBINGWP1

is jsut my lonely little backup file called
224504.001

n_i · #5 (**permalink**) 04-09-09, 14:16

Quote:

Originally Posted by jrichardson

The output of this dir /s C:\V7Backups\DBINGWP1

is jsut my lonely little backup file called
224504.001

And this is your problem. The v7 backup (v8 too) creates a directory structure similar to <database name>\<node name>\N0000...\C00....\<date>\<time>.xxx, and the restore commands requires the entire path to be present, otherwise it cannot properly attribute the backup image. Copy the entire path from the source system. If you invoke backup like this:

backup db whatever to c:\v7backups ...

the entire contents of c:\v7backups must be copied, subdirectories and all, and the restore should be invoked like this:

restore db whatever from c:\v7backups

jrichardson · #6 (**permalink**) 04-13-09, 09:44

Thank you! I duplicated the V7 machine backup directory structure on my new V8 box and then copied my backup over.

The path to the backup file and the backup file name is:

Code:

E:\BATCH_BACKUP\DBINGWP1.0\DB2\NODE0000\CATN0000\20090409\224503.001

This is my restore command:

Code:

RESTORE DATABASE DBINGWP1 FROM "E:\BATCH_BACKUP\DBINGWP1.0\DB2\NODE0000\CATN0000\20090409\" TAKEN AT 20090409224503 WITH 1 BUFFERS BUFFER 1024 PARALLELISM 1 WITHOUT PROMPTING

This is my error:

Code:

SQL2542N  No match for a database image file was found based on the source database alias "DBINGWP1" and timestamp "20090409224503" provided.

what did I do wrong?

n_i · #7 (**permalink**) 04-13-09, 10:30

May be I could not explain it clearly; the backup image is the entire directory structure, starting with the database name, and the RESTORE command should specify the location of the image, in your case:

RESTORE DATABASE DBINGWP1 FROM "E:\BATCH_BACKUP" TAKEN AT 20090409224503 ...

jrichardson · #8 (**permalink**) 04-13-09, 13:48

OK.

I created a new DB with this script:

Code:

CREATE DATABASE DBINGWP1 ON E: ALIAS DBINGWP1 USING CODESET IBM-1252 TERRITORY US COLLATE USING SYSTEM USER TABLESPACE MANAGED BY DATABASE USING (FILE 'E:\DBINGWP1\TBLSP' 3187200) EXTENTSIZE 16 PREFETCHSIZE 16 OVERHEAD 10.50 TRANSFERRATE 0.33 CATALOG TABLESPACE MANAGED BY SYSTEM USING ('E:\DBINGWP1\SYSCATSPACE') EXTENTSIZE 8 PREFETCHSIZE 8 OVERHEAD 10.50 TRANSFERRATE 0.33 TEMPORARY TABLESPACE MANAGED BY SYSTEM USING ('E:\DBINGWP1\TEMPSPACE1') EXTENTSIZE 32 PREFETCHSIZE 32 OVERHEAD 10.50 TRANSFERRATE 0.33;


CREATE  REGULAR  TABLESPACE TBLSP PAGESIZE 4 K  MANAGED BY DATABASE  USING ( FILE 'E:\DBINGWP1\TBLSP' 5120 ) EXTENTSIZE 16 OVERHEAD 10.5 PREFETCHSIZE 16 TRANSFERRATE 0.33 BUFFERPOOL IBMDEFAULTBP DROPPED TABLE RECOVERY OFF;
COMMENT ON TABLESPACE TBLSP IS '';

I then copied my Version 7 backup file called

Code:

224503.001

from the version 7 box:

Code:

E:\BATCH_BACKUP\DBINGWP1.0\DB2\NODE0000\CATN0000\20090409

to the version 8 box:

Code:

E:\BATCH_BACKUP\DBINGWP1.0\DB2\NODE0000\CATN0000\20090409

Now, assuming that the actual filename is the timestamp for the backup, Here is my restore script:

Code:

CONNECT TO DBINGWP1;
QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS;
CONNECT RESET;
RESTORE DATABASE DBINGWP1 FROM "E:\BATCH_BACKUP\DBINGWP1.0\DB2\NODE0000\CATN0000\20090409\" TAKEN AT 20090409224503 WITH 1 BUFFERS BUFFER 1024 PARALLELISM 1 WITHOUT PROMPTING;
CONNECT TO DBINGWP1;
UNQUIESCE DATABASE;
CONNECT RESET;

but I get this error:

Code:

SQL2542N  No match for a database image file was found based on the source 
database alias "DBINGWP1" and timestamp "20090409224503" provided.

n_i · #9 (**permalink**) 04-13-09, 13:59

Do you bother to read answers to your questions?

jrichardson · #10 (**permalink**) 04-13-09, 14:03

yes. of course I do. you said that the entire directory structure has to be the same. which I did. I duplicated the directory structure on the V8 box and then copied by backup file over. Then I used the entire path to the backup file and used the name of the backup file as the time stamp.. seems like I'm doing it correct.