Maintain consistency between 2 Sybase user tables during backup

[Ray]

I need to make a "consistent backup" for 2 user tables in Sybase 15.
That means the content of the 2 tables should be consistent at a
particular point of time.

Without turning the database in "Read only" mode, is there any
utilities or ways which can archive this ?

Appreciated if anyone would kindly advise on this.

[Michael Peppler]

Post by Ray
I need to make a "consistent backup" for 2 user tables in Sybase 15.
That means the content of the 2 tables should be consistent at a
particular point of time.
Without turning the database in "Read only" mode, is there any
utilities or ways which can archive this ?

If the tables are in the same database then a backup will have the
tables in a consistent state, provided that any updates are done within
a transaction.

Michael

[Ray]

Michael,
Thank you for your reply.

We are not able to do it using "dump database" command due to the size
of the database. Instead, we just want the consistent backup of 2
transaction tables only.

Can we do that using the bcp utility, or any other utilities come with
Sybase 15 ?

In order to maintain the consistency between the Tables during backup
(this may take around 30 mins), what mechanism is used by ASE ? Would
anybody show me where can I get the information ?

Thks for your help in advance.

Ray

[m***@peppler.org]

Post by Ray
Michael,
Thank you for your reply.
We are not able to do it using "dump database" command due
to the size of the database.

Err - how big is your database?

Post by Ray
Instead, we just want the
consistent backup of 2 transaction tables only.

I don't see any way of doing this short of locking the
tables first, which is certainly unacceptable.

If using database dumps is not possible then you could use
incremental dumps (i.e. do a dump database during low use
time, and then do transaction dumps the rest of the time.)
The incremental dumps only dump the transaction log and will
be quite fast (unless your transaction log is several 10s of
GBs in size!)

Other possible techniques including using an OS level split
disk mirror (i.e. run quiesce database, then split the disk
mirror, unquiesce the database and use the splitted disk(s)
to start a secondary instance of the dataserver where the
data is static). However this requires that your disk
devices are on a SAN or similar that supports these sort of
operations.

Michael

[Eugene Korolkov]

Ray,

AFAIK no way till Sybase implement snapshot isolation without locking.

HTH,
Eugene

Post by Ray
Michael,
Thank you for your reply.
We are not able to do it using "dump database" command due to the size
of the database. Instead, we just want the consistent backup of 2
transaction tables only.
Can we do that using the bcp utility, or any other utilities come with
Sybase 15 ?
In order to maintain the consistency between the Tables during backup
(this may take around 30 mins), what mechanism is used by ASE ? Would
anybody show me where can I get the information ?
Thks for your help in advance.
Ray

[Mikhail T.]

Post by Ray
We are not able to do it using "dump database" command due to the size
of the database. Instead, we just want the consistent backup of 2
transaction tables only.

Can you make copies of your two tables (using a "select into" inside a
transaction), and then dump (via bcp) those copies?

Come to think of it, will you even need to bcp them out? These copies will
be your backups on their own...

-mi