Generate SQL for a Different Target Database

In Adaptive Server 15.7 SP100, you can generate SQL from the dump history file and dump information to create a new target database has a different name than the source database.

The dump history file records the dump database (both full and cumulative) and dump transaction commands that were executed against a database, so that the same database can be loaded to any point in time at a later stage. For example, to print the SQL used to restore a database to its most recent version, enter:

load database src_dbname with listonly=load_sql

You can use the dump history file, together with information held within a dump, to create the database into which the load sequence takes place. To print the SQL used to create the target database, enter:

load database src_dbname with listonly=create_sql

You can combine these two commands, but ensure that src_dbname is an exact match to the name of the database that was originally dumped:

load database src_dbname with listonly=(create_sql, load_sql)

Adaptive Server uses src_dbname as a key to the dump history file to locate the information to generate the create and load SQL. The example output is:

1> load database sourcedb with listonly=(create_sql, load_sql)
2> go
disk init
     name = 'master'
   , physname = 'd:/dbs/d_master.dev'
   , size = '450M'
go
create database sourcedb
     on master = '10M'
     log on master = '10M'
with override
for load
go
load database sourcedb FROM 'd:/dbs/full.dmp'
go
load tran sourcedb FROM 'd:/dbs/tran1.dmp'
go
load tran sourcedb FROM 'd:/dbs/tran2.dmp'
go
1>

When the target of the load sequence is a database with a different name entirely, use:

load [database | transaction] 
[src_dbname as target_dbname | target_dbname = src_dbname]

where:

src_dbname – is the name of the source database that was dumped, the entries of which are in the dump history file and are used to generate the create and load SQL for the target database.
target_dbname – is the name of the target database.

This example is based on the dump history created from the source database in the previous load database example:

1> load database sourcedb as targetedb
2>       with listonly=(create_sql, load_sql)
3> go
disk init
    name = 'master'
    , physname = 'd:/dbs/d_master.asecarina'
    , size = '450M'
go
create database targetdb
   on master = '10M'
   log on master = '10M'
   with override
   for load
go
load database targetdb from 'd:/dbs/full.dmp'
go
load tran targetdb from 'd:/dbs/tran1.dmp'
go
load tran targetdb from 'd:/dbs/tran2.dmp'
go
1>

You can specify a database mapping clause in the load database or load transaction command only if you use the listonly=create_sql or listonly=load_sql options.

This example uses the from clause:

1> load tran sourcedb as targetdb from "d:/dbs/tran1.dmp"
2>       with listonly=create_sql
3> go
disk init
    name = 'master'
    , physname = 'd:/dbs/d_master.asecarina'
    , size = '450M'
go
create database targetdb
    on master = '10M'
    log on master = '10M'
with override
for load
go