Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Jun 2010
    Posts
    36

    Unanswered: Clone database with a different name

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You should not have to create an empty database first. The restore command you are using should do the trick.

    Andy

  3. #3
    Join Date
    Jun 2010
    Posts
    36
    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

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Jun 2010
    Posts
    36
    Quote Originally Posted by Marcus_A View Post
    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!

  7. #7
    Join Date
    Jun 2010
    Posts
    36
    Quote Originally Posted by ARWinner View Post
    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
    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

  8. #8
    Join Date
    Jun 2010
    Posts
    36
    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 :
    2. Issue a SET TABLESPACE CONTAINERS command for each table space whose containers you want to redefine. For example, in a Windows® environment:
    db2 set tablespace containers for 5 using (file 'f:\ts3con1'20000, file 'f:\ts3con2'20000)
    To verify that the containers of the restored database are the ones specified in this step, issue the LIST TABLESPACE CONTAINERS command for every table space whose container locations are being redefined.
    Taken from : IBM DB2 9.5 Information Center for Linux, UNIX, and Windows

    Here is the command I used :
    Code:
    db2 RESTORE DATABASE DOD015 FROM "E:\BACKUP" TAKEN AT 20100628140601 TO "E:" INTO DOS015 REDIRECT WITHOUT PROMPTING
    Anyone could assist please?

    Thank you very much

    C

  9. #9
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If the orginal database used automatic storage this problem could have been averted without a redirected restore.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  11. #11
    Join Date
    Jun 2010
    Posts
    36
    Quote Originally Posted by Marcus_A View Post
    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

  12. #12
    Join Date
    Jun 2010
    Posts
    36
    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

  13. #13
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  14. #14
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by CharleyDC5 View Post
    The original database uses automatic storage. What would you recommend?

    Cheers
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  15. #15
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    deleted dup post.
    Last edited by Marcus_A; 06-30-10 at 16:50.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •