dbtran Database Administration Utility

Use the dbtran log translation utility, at the command prompt, to translate a transaction log into a .sql command file.

Syntax

Running against a database server:

dbtran [ options ] -c { connection-string } -n SQL-file

Running against a transaction log:

dbtran [ options ] [ transaction-log ] [ SQL-file ]

Parameters

Option	Description
`@data`	Reads in options from the specified environment variable or configuration file.
-a	Controls whether uncommitted transactions appear in the transaction log. The transaction log contains changes made only before the most recent COMMT by any transaction. If you do not specify -a, only committed transactions appear in the output file. If you specify -a, any uncommitted transactions found in the transaction log appear.
-c `"keyword=value; ..."`	Specifies the connection string when running the utility against a database server.
-d	Specifies that transactions are written in order from earliest to latest. This feature is intended for auditing database activity: do not apply dbtran output against a database.
-ek `key`	Specifies the encryption key for strongly encrypted databases. If you have a strongly encrypted database, you must provide the encryption key to use the database or transaction log. Specify either -ek or -ep, but not both. The command fails if you do not specify the correct encryption key. If you are running dbtran against a database server using the -c option, specify the key using a connection parameter instead of using the -ek option. For example, the following command gets the transaction log information about database `enc.db` from the database 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	Prompts for the encryption key. This option causes a window 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. Specify either -ek or -ep, but not both. The command fails if you do not specify the correct encryption key. If you are running dbtran against a database server using the -c option, specify the key using a connection parameter, instead of using the -ep option. For example, the following command gets the transaction log information about database `enc.db` from the database 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	Outputs only transactions completed since the last checkpoint.
-g	Adds auditing information to the transaction log if the auditing database option is turned on.
-ir `offset1,offset2`	utputs a portion of the transaction log between two specified offsets.
-is `source,...`	Outputs operations on rows that have been modified by operations from one or more of the following sources, specified as a comma-separated list: All – all rows. This is the default setting. SQLRemote – include only rows that were modified using SQL Remote. You can also use the short form “SR”. RepServer – include only rows that were modified using the Replication Agent (LTM) and Replication Server. You can also use the short form “RS”. Local – include only rows that are not replicated.
-it `owner.table,...`	Outputs operations on the specified, comma-separated list of tables. Specify each table as owner.table.
-j date/time	Translates only transactions from the most recent checkpoint prior to the given date or time. The user-provided argument can be a date, time, or date and time, enclosed in quotes. you omit a time, the default is 00:00. If you omit a date, the current day is the default. The acceptable format for the date and time is: "YYYY/MMM/DD HH:NN".
-k	Prevents partial .sql files from being erased if an error is detected. If an error is detected while dbtran is running, the .sql file generated until that point is normally erased to ensure that a partial file is not used. Specifying this option may be useful if you are attempting to salvage transactions from a damaged transaction log.
-m	Specifies a directory that contains transaction logs. Use this option with the -n option.
-n `filename`	Specifies the output file that holds the SQL statements when you run dbtran against a database server.
-o `filename`	Writes output messages to the named file.
-r	Removes any uncommitted transactions. This is the default behavior.
-rsu `username,...`	Specifies a comma-separated list of user names to override the default Replication Server user names. By default, the -is option assumes the default Replication Server user names of dbmaint and sa.
-s	Controls how UPDATE statements are generated. If you do not use this option, and there is no primary key or unique index on a table, dbtran generates UPDATE statements with a nonstandard FIRST keyword in case of duplicate rows. If you do use this option, the FIRST keyword is omitted for compatibility with the SQL standard.
-sr	Includes in the output file generated comments describing how SQL Remote distributes operations to remote sites.
-t	Controls whether triggers are included in the command file. By default, actions performed by triggers are not included in the command file. If the matching trigger is in the database, when the command file is run against the database, the trigger performs 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 run.
-u `userid,...`	Limits the output from the transaction log to include only specified users.
-x `userid,...`	Limits the output from the transaction log to exclude specified users.
-y	Replaces existing command files without prompting for confirmation. If you specify -q, you must also specify -y or the operation fails.
`transaction-log`	Specifies the log file to be translated. Cannot be used with -c or -m options.
`SQL-file`	Names the output file containing the translated information. For use with `transaction-log` only.

Usage

You can run dbtran:

Against a database server — 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. The BACKUP DATABASE system privilege is required to run in this way. For example, this command translates log information from the iqdemo server and places the output in a file named iqdemo.sql:
```
dbtran -c "eng=iqdemo;dbn=iqdemo;dbf=iqdemo.db;uid=DBA;pwd=sql" -n iqdemo.sql
```
Against a transaction log file — acts directly against a transaction log file. Protect your transaction log file from general access to prevent users from running this statement.
```
dbtran iqdemo.log iqdemo.sql
```

dbtran shows the earliest log offset in the transaction log, which you can use to determine the order in which multiple log files were generated.

dbtran-c attempts to translate the online transaction log file, and 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, you may see an error. To avoid this, 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) might lose the spanning transactions. For dbtran to generate complete transactions, use the -c or -m options with the transaction log files in the directory.

Exit codes are 0 (success) or nonzero (failure).

This utility accepts @filename parameters.