Copy Sybase DB from machine to machine

I'm a total newbie to Sybase, While I do have MySQL / MSSQL knowledge.
Anyway a company I've just started working for has an application running on Sybase 10 /  Windows2003.

I want to have the same DB running on my WindowsXP laptop so I can experiment.
The IT manager is going to arrange a copy of Sybase for me to install on the laptop.

But how do I backup/Dump the DB on the production server then restore a copy on my laptop ?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

1  After you get the database onto your laptop, what are you going to do with it ?

That's an awfully arduous way to get at the data.  It would be simple to CONNECT from your laptop (the client) to the the server.  That's the whole idea of client/server; keep the data in one place; not replicate an instance every time you need to do something.  Leave the data where it is and simply login from your laptop.  Assuming you have the nec. privileges you can do anything the companny has permitted you to do.  Depending on what you are going to do with it, you:
a.  use Sybase client-side s/w
b.  buy a program for that purpose (DBArtisan, RapdSQL, etc) and use it (make sure the version you get runs against V10.)
Now that s/w sits on your PC, the data stays where it is on the server.  If you need a private copy of the database, simply create a database to the server, and load the dump into that.  If you do not add users to the db, it will be yours alone (do add yourself as an user).

2  If you are dead-set on replicating the corporate database onto your laptop ...

" biggest problem is V10 is almost 20years old, long past End Of Life, no CDs available, etc.
" second problem is your need Windows 2003 on your laptop.  The Sybase you have is unlikely to execute on anything else (it uses low-level calls to the o/s, in order to obtain performance).  No doubt you will try.
" third is the method you are going to use to get the dumpfile accessible from your pc; note the size restrictions of any media you choose.

a. make sure that you have the disk space and the memory.  I assume your have decided that you have the grunt on the laptop to run up a server.
b.  copy the entire directory tree under sybase on the "server" onto your laptop.  
c.  Do not boot the Sybase server, it is NOT set up.  Look for a binary called something like sybconfig.exe and install a new server.  It has to be reasonably like the server you are replicating but not exactly the same.
d.  create a database of the same size, and in the exact original sequence of fragments, as the source db.
e.  dump the db on the corp server to whatever media you are going to read it from.
f.  load that media into your laptop (or connect your laptop to the external drive or whatever).
g.  load database.

i.  Now you still have to do (1.a) or (1.b)

That's the view from 10,000 metres.

There are two other journeys you can take:
x.  Obtain and install a current version of Sybase 12.5.4 on your laptop-o/s.  Cross platform dump/load works for V11.9 upwards, yours is too old.
y.  use a client-side tool and generate your db from scratch, from DDL (use ddlgen on the source server to generate the DDL file)
z.  bcp-out all tables on the source server; bcp-in on the laptop.


Incisiveone bring up some good points buy I have to warn you that copying the entire directory tree under sybase to your laptop will NOT work.  This is Windblows, not Linux or Unix.  There are all kinds of registry entries required to make anything actually run so you will have nothing after your copy effort.

If you can find a copy of the installation CDs for v10, it will probably run O.K. on Windows XP.  Server 2003 is the server equivalent of XP and most of the differences are minor and in the packaging.

Do you know how large the corporate database is?  If it is just a few gigabytes, you can probably run it on your laptop.  If it is substantially larger than that, you will have to subset the data or all you will have ia boat anchor.

Unless you get v10 running on your laptop, you will not be able to do a dump and restore.  Prior version dump/restore does not support v10.  To move the schema, you can use PowerDesigner.  See if you can find a copy of Sybase ASE 12.5.x for Windows.  There is a free copy of PowerDesigner 10 on the installation disk.  You can then reverse engineer the old database and generate it on the target.

You can move the data with bcp.  You can generate all the scripts you need easily and automatically with a query against the sysobjects table.  Of course, if the database is large, you probably will have to move the files via the network rather than stuffing it all on DVDs.

At this point, you might as well get the developers edition of Sybase ASE 15.0.2 since there is no reason to stay with an unsupported and very antique version.  The organization is going to have to look at an upgrade anyway so you will be well positioned as the guinea pig for the process.  BTW, you don't have to worry about cross platform dump/load since everything you are doing is Wintel.


> copying the entire directory tree under sybase to your laptop will NOT work <

Read again.  I advised that step as an alternative to loading from CD, not as a final step, with the explicit warning (2.c) that it will not work simply as is.  The step following builds a NEW server.

But I do agree that loading from the original CD is a better alternative to (2.b).  I do not think that distribution media existed then.

> Server 2003 is the server equivalent of XP and most of the differences are minor and in the packaging. <

That's why I think using the binaries will work.

The rest of your advice is the same as mine.



As I said, this is the view from 10,000 metres.  This is going to be a long thread, with a fair amount of interaction.  The series of steps has to be thought out (based on actual experience) and consistent with other steps.  I am willing to provide advice and help but I will not get into arguments with other experts about the "right" method to use a each step.  Example above.

Can you reassure us that you have a valid licence for (a) the existing server, and (b) the new server on your laptop.  There will be a requirement for a couple of "cheat" steps coming up.

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Matthew_WayAuthor Commented:
Wow this all sounds totally scary.
On MySQL, it's just a case of running "mysql dump" which creates all the SQL to recreate the DB.
Including both the DDL and DML inserts.

The IT manager was going to source the CD's for me, to install from.
Yes it is legal we have spent close to 100K USD for software so far.
So I assume the sybase license is included.

I hope my laptop has the horse power;
Dual core Centrino 2Ghz, 2GB RAM, 250GB SATA disk
WinodwsXP SP2

The reason I want to have a copy is so I can experiment.
Our previous application was based on MSSQL, when the implementation people migrated the DB they left out some critical data.
I now want to extract that data from MSSQL and then apply it to the Sybase DB.
And of course I want to test it on a backup copy of the DB, rather than the live copy.

So in the next day or two I find out if the IT Manager has the install CD's for me.

Joe WoodhousePrincipal ConsultantCommented:
I'm wondering (with all the talk of running this on a laptop at all) if this is in fact Sybase Anywhere (or ASA) 10 rather than the enterprise database which was called SQL Server back in the v10 days.

Matt, can you tell us the exact product name? Sybase has at least four database products and they work very very differently...

Note though that even if this is ASA 10, copying won't work under any circumstances. Too many registry entries, and not all of the product installs to a directory under C:\Sybase anyway. (You'd need ODBC setup which lives under the Windows directory, for instance.)

Nothing you can do will avoid the need for a software install. This is not a Word document file you can open, all Sybase database products are database engines that must be running locally to access some areas of disk that have been allocated for its storage.

Bill's question is a good one - I don't understand why you even want a copy. Assuming you do in fact want a full copy of the environment, you will need to:

1) install the same version of the Sybase product
2) backup the database from the server
3) load the database into the software you installed on your laptop

The syntax for step 2 totally depends on which product you have...
Matthew_WayAuthor Commented:
It's SQL Anywhere version 10
The IT manager has found the install CD

I want a backup copy because I'm going to directly manipulate the database, bellow the application.
to reapply data that was missed in the migration process by my predecessor 12 months ago.
Joe WoodhousePrincipal ConsultantCommented:
Mm, it seemed unlikely it was SS 10, that's ancient. :)

Check you don't have licensing issues in installing it on another machine, Sybase usually counts total installs and often considers the license locked to the platform it was licensed for. (This might be an issue if you're in a company that strictly requires 100% licensed software for auditing or compliance purposes.)

So per above:

1. Install ASA. Pretty straightforward.
2. Backup your current one - issue the "BACKUP DATABASE" command specifying one or more files to backup to.
3. Load into the freshly installed ASA with "RESTORE DATABASE". There is an interesting wrinkle where you have to have a database running to connect to to run the RESTORE command - ASA ships with a demo database you can use for this.

You can find this written up well in the Sybase manuals available for free (no registration required) at: 

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LOL (at myself)

"Sybase 10" or System 10, as you've just found out is an enterprise product, now called Sybase Adaptive Server Enterprise.
Sybase Adaptive Server Anywhere is quite a different PC-based product.

> (This might be an issue if you're in a company that strictly requires 100% licensed software for auditing or compliance purposes.) <

And legal purposes.  As I understand the Terms of Service for this site, we do not deal with illegal software.  But my mention of licensing issues was related ASE which has a much higher price tag than ASA.
Matthew_WayAuthor Commented:
Okay, we are using SQL Anywhere version 10 by Sybase.
The IT manager found the install CD.

Software licenses we are an international company and everything is above board.

But before I update 43,000 contacts in our database I would like to check it first.
Thats why I want the copy on my laptop.

SQLAnywhere comes with a Windows Utility called Sybase Central.
Which will allow us to backup the whole DB to a single backup file.
It's 1.8GB but only takes a couple of minutes to shuffle down the Gigabit ethernet.

Will take a copy tonight when all the users are tucked in bed sleeping...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.