f



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

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?

0
Max
8/29/2013 4:45:50 PM
comp.databases.sybase 3279 articles. 0 followers. Post Follow

2 Replies
634 Views

Similar Articles

[PageSpeed] 1

Op donderdag 29 augustus 2013 18:45:50 UTC+2 schreef Max:
> We have two different business units with a one prod and one dev sybase=
=20
> server each,with 2 databases in each server. Let's call them server_A and=
=20
> server_B, with db_1 and db_2 databases. They have the same Version of=20
> Sybase (15.5) with the same EBF & ESD.
>=20
> Initially we started out with the same set of tables/procs etc, but over =
a=20
> period of time some differences have cropped up in our code/ddl.
>=20
> Now we want to take a dump from Production server_A.db_1 and load into th=
e=20
> DEV server of server_B.db_1. but according to our dba team, server_A and=
=20
> server_B were built at different times with different fragment sizes. To =
be=20
>=20
> able to dump/load from server_A.db_1 to server_B.db_1, the fragments in=
=20
> both databases should match. Otherwise corruption will occur, according t=
o=20
> our dba team.
>=20
> Is it possible to take server_A.db_1 as a model and rebuild server_B.db_1=
=20
> with the same fragment configuration? What are the problems we may face=
=20
> doing that? Since server_B objects have some differences from server_A=20
> objects, can we be able to restore server_B objects again to their origin=
al=20
> state after the db rebuild?=20
>=20
> My plan was :
>=20
> 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,=20
> 3. after the rebuild restore the server_B.db_1 database from the dump tak=
en=20
>    in step 1
>=20
> But the dba said that step 3 is not possible since the backup was taken=
=20
> from the original fragment size and now we have a new fragment size after=
=20
> the rebuild and hence the backup cannot be restored.
>=20
> Can anyone suggest a better solution other than using bcp and dbschema.pl=
=20
> 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 ne=
ed a target database, which is at least the same size as the database on th=
e 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 si=
ze or larger, it depends on the fragment sizes/order how the load will comp=
lete and leave your target database in correct DATA versus LOG fragment ord=
er.

All that matters is the original DATA - LOG - DATA - LOG sequence when a da=
tabase is created and later altered. Ideally that order and the sizes shoul=
d be the same. But the data is NOT corrupted after a load, only the DATA-LO=
G storage fragments might not be the way you expected or designed it to be.
(e.g. source DB =3D 100 data, 10 log, 100 data, 10 log, but your target dat=
abase is 100 data, 10 log, 10 log, 100 data. Both databases have the same t=
otal data and log size, but the order is different. Once you load the sourc=
e 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 t=
he transaction log separately anymore. But the data itself is not corrupted=
 !

If I understand your question correctly, you are only concerned about the D=
ATA - 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.=20

But once you alter and load your target database (step 2), then work in tha=
t 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 comp=
are 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 e=
nd just remove the database you won't need anymore (even a db rename is the=
n still possible (sp_renamedb))=20

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 d=
ump as that dump is smaller compared to the now larger Server_B.db_1 databa=
se. You will only not be able to reclaim the larger/altered space, unless y=
ou just drop the database, rebuild smaller and load the original dump.

But again, reloading with the original Server_B.db_1 will remove any change=
s 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 datab=
ases are, is to extract the DDL statement of the objects on both sides (usi=
ng ddlgen, or any other ASE client to extract DDL's)  A diff on those outpu=
t files should give you an idea of the real differences and where they are =
(table layout, procedure code etc.) =20

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


0
tom
9/2/2013 3:58:53 PM
On Thursday, 29 August 2013 22:15:50 UTC+5:30, Max  wrote:
> 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?

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.

0
Ashwini
10/18/2013 5:51:04 AM
Reply: