RecoverTools creating scrip with tablenames 0_Number pattern? why

[Ram K]

hi Team,

I see that RecoverTool is creating script something like this. instead of actual table name, its using 0_87 0_88. why? how can I fix that?

INSERT INTO O_87 VALUES(10606544, TIMESTAMP '2022-11-28 13:44:14.143', 1, 'recen
tCards', NULL, 635, 188, NULL);

Thank you,

Ram

[Evgenij Ryazanov]

Hi!

Table definition and table data are stored separately and they are recovered separately too.

The recovery tool works in the following way (I skipped unrelated steps for simplicity):

1. It constructs a temporary table with LOB chunks from all tables first.

2. Then, it recovers data from tables, it creates a new temporary table for each found table for that purpose. LOB values for them are constructed from chunks from (1). Actual table names, column names, data types and other attributes aren't yet known at that moment.

3. Next, it tries to recover metadata of database, including definitions of actual tables.

4. After that, If metadata of some table was recovered on step (3) and data for that table was recovered on step (2) data is copied from the temporary table into actual one.

5. Finally, all temporary tables are dropped.

This strategy isn't perfect and can potentially be improved, at least for some cases when both data and metadata are recoverable.

[Ram K]

thank you for the reply, I have gone through the generated script, some how the RunScript did not fail after creating the tamporary (O_N) tables.

I manually copied the script and ran in it in the console worked. 

I am trying to migrate to sql server,  could you please give me some tips to migrate to sql server (mssql).

Thank you,

Ram

[Evgenij Ryazanov]

Scripts generated by this tool aren't really suitable for data migration.

It will be better to use the SCRIPT command or the Script tool to produce an SQL without all this messy temporary tables and edit their resulting script as necessary. They also can be used to export different tables separately.

Unfortunately, different database systems aren't really compatible with each other even in their basic functionality, so usually you cannot execute an SQL dump from one database system in another database system without some modifications, such as replacements of data types and other.

There are some third-party tools for such migrations, but usually they support only few database systems and I don't know whether some of them supports migrations from H2 to MS SQL or not.

[Andreas Reichel]

Greetings.

regarding the Migration to MS SQL Server/T-SQL, what has worked for me in the past was:

1) Either export to SQL Script and then apply a couple of Regex/Beanshell Replacements using JEdit:

Simple Replace:

NUMBER(                   DECIMAL(

TIMESTAMP                 DATETIME2

SYSDATE                   current_timestamp

CLOB                      VARCHAR(max)

BLOB                      VARBINARY(max)

Jedit Regex + BeanShell:

TABLE (\w*)\.\"?(\w*)\"?   "TABLE [" + _1.toLowerCase() + "].[" + _2.toLowerCase() + "]"

index (\w*)\.(\w*)         "INDEX " + _2.toLowerCase()

ON (\w*)\.\"?(\w*)\"?      "ON [" + _1.toLowerCase() + "].[" + _2.toLowerCase() + "]"

(\w*)\.(\w*).NEXTVAL       "NEXT VALUE FOR " + _1.toLowerCase() + "." + _2.toLowerCase()

/\*TRANSACT: ([\w\s]*)\*/  _1.toUpperCase()

2) Alternatively, parse your exported SQL with JSQLParser and the use a customized De-Parser to rewrite it T-SQL compliant.

Illustration is here: https://www.manticore-projects.com/JSQLParser/usage.html#parse-a-sql-statements

Good luck and cheers

Andreas