Question on dump/load db between two almost similar Sybase servers

[Max]

We have two different business units with a one prod and one dev sybase
server each,with 2 databases in each server. Let's call them server_A and
server_B, with db_1 and db_2 databases. They have the same Version of
Sybase (15.5) with the same EBF & ESD.

Initially we started out with the same set of tables/procs etc, but over a
period of time some differences have cropped up in our code/ddl.

Now we want to take a dump from Production server_A.db_1 and load into the
DEV server of server_B.db_1. but according to our dba team, server_A and
server_B were built at different times with different fragment sizes. To be
able to dump/load from server_A.db_1 to server_B.db_1, the fragments in
both databases should match. Otherwise corruption will occur, according to
our dba team.

Is it possible to take server_A.db_1 as a model and rebuild server_B.db_1
with the same fragment configuration? What are the problems we may face
doing that? Since server_B objects have some differences from server_A
objects, can we be able to restore server_B objects again to their original
state after the db rebuild?

My plan was :
1. to take a dump from server_B.db_1 as a backup
2. then rebuild server_B.db_1 to match the server_A.db_1 fragment,
3. after the rebuild restore the server_B.db_1 database from the dump taken
in step 1

But the dba said that step 3 is not possible since the backup was taken
from teh original fragment size and now we have a new fragment size after
the rebuild and hence the backup cannot be restored.

Can anyone suggest a better solution other than using bcp and dbschema.pl
to backup and restore?

[t***@gmail.com]

Post by Max
We have two different business units with a one prod and one dev sybase
server each,with 2 databases in each server. Let's call them server_A and
server_B, with db_1 and db_2 databases. They have the same Version of
Sybase (15.5) with the same EBF & ESD.
Initially we started out with the same set of tables/procs etc, but over a
period of time some differences have cropped up in our code/ddl.
Now we want to take a dump from Production server_A.db_1 and load into the
DEV server of server_B.db_1. but according to our dba team, server_A and
server_B were built at different times with different fragment sizes. To be
able to dump/load from server_A.db_1 to server_B.db_1, the fragments in
both databases should match. Otherwise corruption will occur, according to
our dba team.
Is it possible to take server_A.db_1 as a model and rebuild server_B.db_1
with the same fragment configuration? What are the problems we may face
doing that? Since server_B objects have some differences from server_A
objects, can we be able to restore server_B objects again to their original
state after the db rebuild?
1. to take a dump from server_B.db_1 as a backup
2. then rebuild server_B.db_1 to match the server_A.db_1 fragment,
3. after the rebuild restore the server_B.db_1 database from the dump taken
in step 1
But the dba said that step 3 is not possible since the backup was taken
from the original fragment size and now we have a new fragment size after
the rebuild and hence the backup cannot be restored.
Can anyone suggest a better solution other than using bcp and dbschema.pl
to backup and restore?

Hi Max,

First some general answers:

To be able to load a dump from Server_A.DB_X into Server_B.DB_X you will need a target database, which is at least the same size as the database on the source side, but it may be bigger. If the target database is smaller, the dump will not fit and will not load. If the target database is the same size or larger, it depends on the fragment sizes/order how the load will complete and leave your target database in correct DATA versus LOG fragment order.

All that matters is the original DATA - LOG - DATA - LOG sequence when a database is created and later altered. Ideally that order and the sizes should be the same. But the data is NOT corrupted after a load, only the DATA-LOG storage fragments might not be the way you expected or designed it to be.
(e.g. source DB = 100 data, 10 log, 100 data, 10 log, but your target database is 100 data, 10 log, 10 log, 100 data. Both databases have the same total data and log size, but the order is different. Once you load the source dump in the target, you will have mixed data/log fragments, which is (on a production server) not what you want, as it might not allow you to dump the transaction log separately anymore. But the data itself is not corrupted !

If I understand your question correctly, you are only concerned about the DATA - LOG sequence, sizes and order ? Not about any other internal database objects differences, right ??

So now some answers to your steps:

Yes, you can and should backup your target database (step 1) to be able to return to the initial stage.

But once you alter and load your target database (step 2), then work in that database, why would you want to restore the dump from step 1 in that same database ?

Only if you want to overwrite data, you would want to load a dump. Not for storage reallocations.

If you just want a temporary storage for your source_db on server_B to compare objects in server_B.db_1 and the loaded dump, then just create another Server_B.db_3 database and load your source dump there and work with Server_B with both databases online to allow you to compare, change etc. At the end just remove the database you won't need anymore (even a db rename is then still possible (sp_renamedb))

As I mentioned above, after you altered Server_B.db_1 to allow a load with Server_a.db_1 dump, you are still able to load the original Server_B.db_1 dump as that dump is smaller compared to the now larger Server_B.db_1 database. You will only not be able to reclaim the larger/altered space, unless you just drop the database, rebuild smaller and load the original dump.

But again, reloading with the original Server_B.db_1 will remove any changes made after the initial load from A to B and I do not expect that is what you actually want.

Hope this helps ... Tom

My first suggestion to see what the actual differences of 2 copies of databases are, is to extract the DDL statement of the objects on both sides (using ddlgen, or any other ASE client to extract DDL's) A diff on those output files should give you an idea of the real differences and where they are (table layout, procedure code etc.)

Loading a dump from source to target will indeed bring the target in "source state", but will overwrite any target data and target changes, made before the load.

[Ashwini Godage]

Post by Max
We have two different business units with a one prod and one dev sybase
server each,with 2 databases in each server. Let's call them server_A and
server_B, with db_1 and db_2 databases. They have the same Version of
Sybase (15.5) with the same EBF & ESD.
Initially we started out with the same set of tables/procs etc, but over a
period of time some differences have cropped up in our code/ddl.
Now we want to take a dump from Production server_A.db_1 and load into the
DEV server of server_B.db_1. but according to our dba team, server_A and
server_B were built at different times with different fragment sizes. To be
able to dump/load from server_A.db_1 to server_B.db_1, the fragments in
both databases should match. Otherwise corruption will occur, according to
our dba team.
Is it possible to take server_A.db_1 as a model and rebuild server_B.db_1
with the same fragment configuration? What are the problems we may face
doing that? Since server_B objects have some differences from server_A
objects, can we be able to restore server_B objects again to their original
state after the db rebuild?
1. to take a dump from server_B.db_1 as a backup
2. then rebuild server_B.db_1 to match the server_A.db_1 fragment,
3. after the rebuild restore the server_B.db_1 database from the dump taken
in step 1
But the dba said that step 3 is not possible since the backup was taken
from teh original fragment size and now we have a new fragment size after
the rebuild and hence the backup cannot be restored.
Can anyone suggest a better solution other than using bcp and dbschema.pl
to backup and restore?

Hi,

According to me , The simplest solution is take the dumps from Server_A DB and Load it with another DB name On Server_B.
After you have completed the loading.Then take the backup of original Dev DB and remove it from the server... and rename your newly dumped DB to your database name.