copydb Command--Copy and Restore a Database
The copydb command creates command files containing the SQL statements required to copy and restore a database. The command creates the following two command files in the current directory:
• copy.out contains SQL commands to copy all tables, views, and procedures owned by the user into files in the specified directory.
• copy.in contains SQL commands to copy the files into tables, recreate views, procedures, and indexes, and perform modifications.
To copy the database, you must execute the SQL commands in the copy.in and copy.out command files.
The name of a file created by copy.out consists of the name of the table followed by an extension made up of the owner’s login name. Any non-standard characters are mapped to underscores, for example, $ingres becomes _ingres. If such modified file names collide, a unique digit is appended to all but one of the filenames. For example, both foo#table and foo$table would generate the filename foo_table; these are resolved by being renamed foo_table and foo_table0.
Copydb can be used to change ownership of tables.
System catalogs cannot be copied using copydb. Use unloaddb to copy a complete database, including system catalogs.
Notes:
• It is important that the database be recreated with copy.in before doing any work (for example, creating tables or reports) in the new database. After recreating a database, be sure to run sysmod to optimize storage structures.
• Copydb output is not encrypted. To protect sensitive data, encrypt the copydb output files as needed.
The copydb command has the following format:
copydb [-param_file=filename] | [vnode::]dbname [-c]
[+user=[authuser]] [-uusername] [-Ggroupid]] [-journal] [-P]
[-source=dirname] [-dest=dirname] [-ddirname] [-with_tables] [-with_modify]
[-with_data] [-all] [‑order_ccm] [-with_index] [-with_constr] [-with_views]
[-with_synonyms] [-with_comments] [-with_roles] [-with_sequences] [-no_seq]
[-with_permits] [‑with_blankline] [‑with_csv | ‑with_ssv] [-add_drop]
[-infile=filename] [-outfile=filename] [-relpath] [-no_loc] [-no_perm] [-noint]
[-no_warn] [‑on_logfull=commit|abort|notify] [‑nvarchar]
[‑include=|‑exclude=] [‑compress] [‑filesize=size] {tablename|viewname}] [-help]
-param_file=filename
Reads filename for all other command line flags, database names, and any other command line arguments. This file must contain only one flag per line (see the examples that follow). If this flag is specified, no other flags or arguments can appear on the command line; they must, however, appear in the specified file.
dbname
-c
Creates a printable data file.
Copydb cannot represent the following types of data using printable characters: (1) binary data stored in varchar columns, and (2) user-maintained logical keys.
+user=[authuser]
-uusername
-Ggroupid
Specifies a group identifier, as described in
Standard Flags and Parameters. You must enclose this parameter in double quotation marks (“
-Ggroupid”).
-journal
Replaces the SET NOJOURNALING statement in the copy.in scripts with the SET JOURNALING statement, and disables specifying the WITH NOJOURNALING option on each CREATE TABLE statement in copy.in script.
-P
Prompts for password if the session requires one.
-source=dirname
Specifies the directory that contains the data files and from which copy.in will be run. An empty dirname specification (“”) denotes the current directory. The –source specification overrides a –d specification for the copy in file.
If a source is specified without a destination (no –d or –dest), the default copy out directory is used.
The source directory specification is not checked for validity or existence. This allows the scripts to be moved to another machine.
-dest=dirname
Specifies the directory where the data files created by copy.out will be stored. An empty dirname specification (“.”) denotes the current directory. The –dest specification overrides a –d specification for the copy out file.
If a destination is specified without a source (no –source) then the default copy in directory is used.
The destination directory specification is not checked for validity or existence. This allows the scripts to be moved to another machine. The destination directory must be different from the database directory, $II_DATABASE/ingres/data/default/dbname, because the files have the same names as the table files.
-ddirname
Stores the copy.in and copy.out files in the specified directory instead of the default current directory. The file name must be fully specified.
-with_tables
Prints only the CREATE statements.
-with_modify
Prints only the MODIFY statements.
-with_data
Prints only the COPY statements.
-all
Prints all the statements related to the database.
-order_ccm
Determines the order in which the COPY and MODIFY statements are written for the table. The default is to modify and then copy. If –order_ccm is specified, the order is to copy and then modify.
-with_index
Prints statements only related to index.
-with_constr
Prints statements only related to constraints, such as ALTER TABLE statements.
-with_views
Prints statements only related to views.
-with_synonyms
Prints statements only related to synonyms.
-with_comments
Prints statements only related to comments.
-with_roles
Prints statements only related to roles.
-with_sequences
Prints statements only related to sequences.
-no_seq
Does not print sequence related statements.
-with_permits
Prints statements only related to permits.
‑with_blankline
Generates redundant blank lines after every row in each data file generated. By default, redundant blank lines are not generated. This option applies only when using the –c flag to generate ASCII data files.
-with_csv
Generates a comma-separated data file.
-with_ssv
Generates a semicolon-separated data file.
-add_drop
Writes a DROP statement also, before writing the CREATE statements. This is useful when the scripts are run repeatedly in case of errors, and tables are already created.
-infile=filename
Specifies an input file name for the copy.in file, so user can run copydb with different options and give different names for infile.
-outfile=filename
Specifies an output file name for the copy.out file.
-relpath
Removes the paths from the file names; the files will thus be created and copied from the current directory.
-noint
Runs copydb uninterrupted for all the tables.
-no_loc
Does not write the LOCATION clause for CREATE TABLE, CREATE INDEX, or MODIFY statements.
-no_perm
Does not print GRANT statements.
-no_warn
Suppresses the following message when copydb processing ends: “COPYDB has created the scripts copy.out and copy.in. From the command prompt, run sql databasename < copy.out to copy the data.”
-on_logfull=commit|abort|notify
Adds a SET SESSION WITH ON_LOGFULL=value statement to the generated copy.in file.
-nvarchar
Exports strings in UTF-8 encoding so that the data files are portable. On CREATE TABLE statements, column data types char, varchar, and long varchar are changed to nchar, nvarchar, and long nvarchar, respectively. The ‑nvarchar flag changes the default copy specification for character types from varchar to nvarchar. You do not need to specify the ‑c flag.
Note: The generated copy.in script cannot be used to reload data into a non-Unicode database.
-include= | -exclude=
Includes or excludes the specified object types in the generated copy scripts. The flags are mutually exclusive of each other and of other flags that specify objects that should [not] be generated.
For example, generate a copy.in that contains only the CREATE and MODIFY statements for each table in the mydb database:
copydb -include=table,modify mydb
-compress
Includes WITH COMPRESSION clause in the generated copy scripts.
‑filesize=size
Splits the output file into multiple parts, where size is the number of megabytes per part.
tablename|viewname
Specifies the tables to be copied. If omitted, all tables are copied. This could also be a list of views; in that case only the given views are copied.
Note: No more than 100 objects can be specified. This limit can be raised by modifying the utexe.def file.
-help
Displays command syntax online.
copydb Examples
Windows:
The following commands make a copy of olddb. In this example, replace the named directory (\mydir\backup) with your own:
cd \mydir\backup
copydb olddb
sql olddb<copy.out
The following example creates a new database newdb:
createdb newdb
sql newdb<copy.in
sysmod newdb
The following command runs copydb with parameters supplied in a file called flagfile:
copydb -param_file=flagfile
where flagfile can contain the following entries:
dbname
-order_ccm
-relpath
-no_loc
-all
This is equivalent to the command:
copydb dbname -order_ccm -relpath -no_loc -all 
Linux: The following commands:
1. Create a printable data file encoded with the UTF8 character set (copydb ‑c flag). Remove the paths from the file names (‑relpath flag) so that the files will be created and copied from the current directory.
2. Generate the user defined table files in the database dbname.
3. Archive all the files that were just generated in the current directory.
copydb -c -relpath dbname
sql dbname <copy.out
tar -cvf backup.tar *
You can move the archive file to your backup server.
