Wednesday, February 27, 2008

Dump Sybase Database to File

I had a reader ask me offline how to transfer a legacy database to another instance of a database management system (DBMS) on a separate server/workstation. The reader was trying to extract historical statistics via a test database (on a test server) without affecting the production database. I recommended dumping the entire database to a flat file and then writing (tar) the file to tape. I was uncertain if my response answered the reader's initial question but this is one way I dealt with a relatively small database (less than 50 megabytes) a few years ago. By the way, the sequence below assumes the receiving Sybase database was appropriately named and sized when it was created.

Login into production DBMS
1> use master
2> go
1> dump database yourdb to "/tmp/mydatabase.dat"
2> go
1> quit
2> go

Archive flat file to tape (assumes a tape drive is attached)
# cd /tmp
# tar cvfp /dev/rmt/0 mydatabase.dat

Move tape media to receiving server/workstation (assumes a tape drive is attached)
# cd /tmp
--This should extract mydatabase.dat to the /tmp directory
# tar xvfp /dev/rmt/0
# chmod 777 mydatabase.dat

Login into your test server DBMS
1> use master
2> go
1> load database yourdb from "/tmp/mydatabase.dat"
2> go
1> online database yourdb
2> go
1> quit
2> go

Other Sybase posts

7 comments:

Noah said...

I loathe SyBase, but when I was working for a local college, I was in charge of the dental lab's new server. It stored patient records for the dental lab clinic, x-ray images, inventory and billing data.

Windows 2000 with SyBase. What a winning combo. That server is one of the few things I hated about that job.

I used a method similar to yours so that we could, likewise, back it up (with TSM).

esofthub said...

Thanks Noah for the comment.

I figured many admins have used something similar or maybe something with bcp. I'm curious as what methods others are using or have used in the past.

Dabbi said...

Sybase isn't that bad, but I do agree that you were using the wrong platform.

This is the fastest way to move a database from one Sybase ASE server to another. I would however recommend that you do a writeup of how to create the database on the receiving end since this tip is worthless if it isn't correctly created...

esofthub said...

dabbi,

If I find more time, I might write something up in the future. Frankly speaking, I assumed most readers of this blog knows how to create the database on the receiving end.

Dabbi said...

Hmmm. I can't say that I agree with you. I have seen on several occasions where people thought they were doing the right thing but in fact they were not.

If a database that is about to be moved has been altered several times then it has to be recreated in exactly the same fashion on the receiving end, otherwise you risk ending up with segments containing both log and data.

The following query will show you exactly how the database is constructed (to be run from the master database):

select size/512, segmap from sysusages where dbid=[database id]

You will need to know your database id (you can find it from running sp_helpdb) and you also need to know that the different segmap numbers mean. For example:

1> select size/512, segmap from sysusages where dbid=4;
segmap
----------- -----------
200 3
300 3
100 4
300 3
1000 3
500 3
500 3
1000 3

Here we have a database that has a single log segment that is 100MB (segmap = 4) and several data segments that are a total of 3800MB (segmap = 3). If I want to recreate this database elsewhere then you will be making a mistake if you do this:

create database newdb on data01=3800 log on log01=100 for load

This will guarantee that you will mix and match log and data.

You indicate that you think everybody knows this but my experience is the opposite. I have seen this mistake being done by Sybase admins - even in production sites.

And as a last note, I might add that if you are seeing segmap numbers other than 3 or 4 on a user database then it might mean trouble. But that is another story alltogether....

Anonymous said...

A quick tip, if you want to break up a large db to multiple files, use the "stripe on" argument.

dump database yourdb to "/tmp/mydatabase1.dat"
stripe on "/tmp/mydatabase2.dat"
...

mark said...

Thanks Dabbi. I am one who did not know how to create the target database properly

As a reference, here is another good thread that helped me out. http://www.dbforums.com/sybase/1647257-database-data-device-turn-data-log-device.html