Hi folks,

Is it possible to clone (BACKUP / RESTORE) a database with a different name?

Here is what I did :
1. Backup database dod99 from server A
CONNECT TO dod099
QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS;
CONNECT RESET;
BACKUP DATABASE dod099 TO "E:\BACKUP" WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 UTIL_IMPACT_PRIORITY 50 WITHOUT PROMPTING;
CONNECT TO dod099;
UNQUIESCE DATABASE;
CONNECT RESET;

2. Copied the backup file to server B (E:\BACKUP)

3. Restore database dod99 on server B
RESTORE DATABASE DOD99 FROM "E:\BACKUP" TAKEN AT 20100628140601

It works, but I would like to restore it as dos099 in order to be compliant with our naming standards.

dod099 : database object dev databaseNumber)
dos099 : database object staging databaseNumber)

The only workaround I have found is to create an empty database as dos99 and then use the following command :
RESTORE DATABASE DOD099 FROM "E:\BACKUP" TAKEN AT 20100628140601 TO "E:" INTO DOS099 WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 WITHOUT PROMPTING;

Thank you very much

Charles

You should not have to create an empty database first. The restore command you are using should do the trick.

Andy

Well, if I use that restore command (#1 in my previous post), the database is still named dod099. I would like to be able to restore it AS dos099. Is it possible?

Otherwise, is it possible to alter a table so I could rename the database after the restore command?

Thanks!

Charles

In your original post, you listed 2 RESTORE commands. The first one will keep the database name the same, the second one will rename the database. Use that one. As I said, you do not need to create an empty DB first.

Andy

When doing a backup and restore you should omit using the the options below and let DB2 choose the appropriate buffers and parallelism:

WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1

Thank you Marcus for the hint!

It worked!

Here is the command I used :
RESTORE DATABASE DOD099 FROM "E:\BACKUP" TAKEN AT 20100628140601 INTO DOS099

Thank you guys for help! Much appreciated

C

I just discovered a problem with the database restore. The tablespaces seems to keep their native path (the path on the DEV server).

Here is how the restore is done right now :
E:\DB2S001\NODE0000\DOS015\
.................\T0000005\
.................\T0000006\
..................SQLCRT.FLG

E:\DB2D001\DOD015\
.................\SMS_ST01\
.................\SMS_UR01\
.................\SMS_UR01\
.................\SYSCATSPACE\
..................SQLCRT.FLG

I would like to have all files located in the same directory instead of keeping the DEV instance name :
E:\DB2S001\NODE0000\DOS015\

I tried using the REDIRECT RESTORE functionnality but I'm not sure about this step :
Taken from : IBM DB2 9.5 Information Center for Linux, UNIX, and Windows

Here is the command I used :
Anyone could assist please?

Thank you very much

C

After you issue the restore command with the REDIRECT clause, you need to issue a SET TABLESPACE CONTAINERS statement for each tablespace you want to change the path/container for. When you have completed that you issue you issue a RESTORE .. CONTINUE command.

Andy

If the orginal database used automatic storage this problem could have been averted without a redirected restore.

The original database uses automatic storage. What would you recommend?

Cheers

Here is what I just tried :
RESTORE DATABASE DOD015 FROM "E:\BACKUP" TAKEN AT 20100628140601 TO "E:" INTO DOS015 WITHOUT PROMPTING; ..... WORKED.

SET TABLESPACE CONTAINERS FOR 2 USING (PATH "E:\DB2S001\DOS015\SMS_UR01");
SQL1024N - A database connection does not exist.

So.. I tried connecting...
db2 CONNECT TO DOS015... WORKED.

then I retried setting the containers as follow :
SET TABLESPACE CONTAINERS FOR 2 USING (PATH "E:\DB2S001\DOS015\SMS_UR01");
SQL0290N Table space access is not allowed.

SET TABLESPACE CONTAINERS FOR 3 USING (PATH "E:\DB2S001\DOS015\SMS_UR02");
SQL0290N Table space access is not allowed.

RESTORE DATABASE DOD015 CONTINUE
DB21000E No previous RESTORE DATABASE command with REDIRECT option was issued for this database alias, or the information about that command is lost.

I also tried with the Control Center by Creating a database from a backup. The code generated by the tool is basically the same as above, but the tablespaces remains at the wrong place. I can't find a way to put them all in the right folder (E:\DB2S001\DOS015)

So, I'm quite confused... We are moving from MSSQL to DB2.. I can't believe how hard it is to get a simple backup/restore task working in DB2...

Please help...!

Thanks for the support

C

In order to use the SET TABLESPACE CONTAINERS statement, they must immediately follow a RESTORE command that has the REDIRECT clause. The REDIRECT clause basically puts the RESTORE on hold until the "RESTORE .. CONTINUE" statement is issued. During this "hold" period is when you specify where to put the tablespace containers.

You could also use the db2relocatedb command to "move" the tablespaces. This is a lot more work than using REDIRECT.

Andy

I would recommend that you consult the manul (Command Reference). In the manual you will see these options you can use to redirect the path all at once:

DBPATH ON target-directory

This parameter states the target database directory. This parameter is ignored if the utility is restoring to an existing database. The drive and directory that you specify must be local. If the backup image contains a database that is enabled for automatic storage and the ON parameter is not specified then this parameter is synonymous with the TO parameter and only the database directory changes, the storage paths associated with the database do not change.

ON path-list

This parameter redefines the storage paths associated with an automatic storage database. Using this parameter with a database that is not enabled for automatic storage results in an error (SQL20321N). The existing storage paths as defined within the backup image are no longer used and automatic storage table spaces are automatically redirected to the new paths. If this parameter is not specified for an automatic storage database then the storage paths remain as they are defined within the backup image. One or more paths can be specified, each separated by a comma. Each path must have an absolute path name and it must exist locally. If the database does not already exist on disk and the DBPATH ON parameter is not specified then the first path is used as the target database directory.

deleted dup post.