|
Translates a transaction log into a SQL command file.
Running against a database server:
dbtran [ options ]
Running against a transaction log:
dbtran [ options ] [ transaction-log ] [ SQL-file ]
| Option | Description |
|---|---|
| @data |
Use this option to read in options from the specified environment variable or configuration file. See Using configuration files. If you want to protect passwords or other information in the configuration file, you can use the File Hiding utility to obfuscate the contents of the configuration file. See File Hiding utility (dbfhide). |
| -a |
The transaction log contains any changes made before the most recent COMMIT by any transaction. Changes made after the most recent commit are not present in the transaction log. If -a is not used, only committed transactions appear in the output file. If -a is used, any committed transactions found in the transaction log are output followed by a ROLLBACK statement. |
| -c "keyword=value; ..." |
When running the utility against a database server, this parameter specifies the connection string. See Connection parameters. DBA authority is required to run dbtran. |
| -d | Transactions are output in order from earliest to latest. This feature is provided primarily for use when auditing database activity: the output of this command should not be applied against a database. |
| -ek key |
This option allows you to specify the encryption key for strongly encrypted databases directly in the command. If you have a strongly encrypted database, you must provide the encryption key to use the database or transaction log in any way. For strongly encrypted databases, you must specify either -ek or -ep, but not both. The command will fail if you do not specify the correct key for a strongly encrypted database. If you are running against a database server (using the -c option), make sure you specify the key using a connection parameter, not using the -ek option. For example, the following command gets the transaction log information about database enc.db from server sample, and saves its output in log.sql. dbtran -n log.sql -c "ENG=sample;DBF=enc.db;UID=DBA;PWD=sql;DBKEY=mykey" |
| -ep |
This option allows you to specify in the command that you want to be prompted for the encryption key. This option causes a dialog box to appear, in which you enter the encryption key. It provides an extra measure of security by never allowing the encryption key to be seen in clear text. For strongly encrypted databases, you must specify either -ek or -ep, but not both. The command will fail if you do not specify the correct key for a strongly encrypted database. If you are running against a database server (using the -c option), make sure you specify the key using a connection parameter, not using the -ep option. For example, the following command gets the transaction log information about database enc.db from server sample, and saves its output in log.sql. dbtran -n log.sql -c "ENG=sample;DBF=enc.db;UID=DBA;PWD=sql;DBKEY=mykey" |
| -f | Only transactions that were completed since the last checkpoint are output. |
| -g |
If the auditing database option is turned on, auditing information is added to the transaction log. You can include this information as comments in the output file using this option. See auditing option [database]. The -g option implies the -a, -d, and -t options. |
| -ir offset1,offset2 | Output a portion of the transaction log between two specified offsets. |
| -is source,... |
Output operations on rows that have been modified by operations from one or more of the following sources, specified as a comma-separated list:
|
| -it owner.table,... | Output those operations on the specified, comma-separated list of tables. Each table should be specified as owner.table. |
| -j date/time | Only transactions from the most recent checkpoint prior to the given date and/or time are translated. The user-provided argument can be a date, time or date and time enclosed in quotes. If the time is omitted, the time is assumed to be the beginning of the day. If the date is omitted, the current day is assumed. The following is an acceptable format for the date and time: "YYYY/MMM/DD HH:NN". |
| -m | Use this option to specify a directory that contains transaction logs. This option must be used in conjunction with the -n option. |
| -n filename | When you run the dbtran utility against a database server, use this option to specify the output file that holds the SQL statements. |
| -o filename | Write output messages to the named file. |
| -q | Run in quiet mode—do not display messages. |
|
-r | Remove any transactions that were not committed. This is the default behavior. |
| -rsu username,... | By default, the -is option assumes the default Replication Server user names of dbmaint and sa. You can override this assumption using the -rsu option with a comma-separated list of user names. |
| -s | If the option is not used, and there is no primary key or unique index on a table, the Log Translation utility generates UPDATE statements with a non-standard FIRST keyword in case of duplicate rows. If the option is used, the FIRST keyword is omitted for compatibility with the SQL standard. |
| -sr | Place generated comments in the output file describing how SQL Remote distributes operations to remote sites. |
| -t | By default, actions performed by triggers are not included in the command file. If the matching trigger is in place in the database, when the command file is run against the database, the trigger will perform the actions automatically. Trigger actions should be included if the matching trigger does not exist in the database against which the command file is to be run. |
| -u userid,... | This option allows you to limit the output from the transaction log to include only specified users. |
| -x userid,... | This option allows you to limit the output from the transaction log to exclude specified users. |
| -y | Choosing this option automatically replaces existing command file(s) without prompting you for confirmation. If you specify -q, you must also specify -y or the operation will fail. |
| -z | Transactions that were generated by triggers will be included only as comments in the output file. |
| transaction-log | Log file to be translated. Cannot be used together with -c or -m options. |
| SQL-file | Output file containing the translated information. For use with transaction-log only. |
The dbtran utility takes the information in a transaction log and places it as a set of SQL statements and comments into an output file. The utility can be run in the following ways:
Against a database server Run in this way, the utility is a standard client application. It connects to the database server using the connection string specified following the -c option, and places output in a file specified with the -n option. DBA authority is required to run in this way.
The following command translates log information from the server demo10 and places the output in a file named demo.sql.
dbtran -c "ENG=demo10;DBN=demo;UID=DBA;PWD=sql" -n demo.sql
Against a transaction log file Run in this way, the utility acts directly against a transaction log file. You should protect your transaction log file from general access if you want to prevent users from having the capability of running this statement.
dbtran demo.log demo.sql
When the dbtran utility runs, it displays the earliest log offset in the transaction log. This can be an effective method for determining the order in which multiple log files were generated.
If -c is used, dbtran attempts to translate the online transaction log file, as well as all the offline transaction log files in the same directory as the online transaction log file. If the directory contains transaction log files for more than one database, dbtran may give an error. To avoid this problem, ensure that each directory contains transaction log files for only one database.
A transaction can span multiple transaction logs. If transaction log files contain transactions that span logs, translating a single transaction log file (for example dbtran demo.log) can cause the spanning transactions to be lost. In order for dbtran to generate complete transactions, use the -c or -m options with the transaction log files in the directory. See Recovering from multiple transaction logs.
You can access the Log Translation utility in the following ways:
From Sybase Central, using the Translate Log File wizard.
At a command prompt, using the dbtran command. This is useful for incorporating into batch or command files.
Exit codes are 0 (success) or non-zero (failure). See Software component exit codes.