Hi all


I have a sybase version 12.5 on sun, and both data and log are in the
same device, I have re created a new database in different devices
then used dump and load, but unfortunately the load over wrote my
devices to the original mess..... How can bcp out the entire tables
and then bcp in into the new database. I'm not very familiar with bcp.
I have been using dump and load. So I have to clue as how to use it,
I really appreciate any help

Thanks
Teresa

On Fri, 04 Jul 2003 13:06:07 -0700, Teresa wrote:

> Hi all
>
>
> I have a sybase version 12.5 on sun, and both data and log are in the same
> device, I have re created a new database in different devices then used
> dump and load, but unfortunately the load over wrote my devices to the
> original mess..... How can bcp out the entire tables and then bcp in into
> the new database. I'm not very familiar with bcp. I have been using dump
> and load. So I have to clue as how to use it, I really appreciate any
help

I've used the following script to bcp all the tables from a database. Note
that all the tables where owned by "dbo" in my case - you'd have to adjust
accordingly if you have tables owned by different users.

Michael
--
Michael Peppler Data Migrations, Inc.
mpeppler@peppler.org http://www.mbay.net/~mpeppler
Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short or
long term contract positions - http://www.mbay.net/~mpeppler/resume.html

#!/usr/bin/perl -w
#
# $Id: bcp_all.pl,v 1.2 2002/12/12 22:08:32 mpeppler Exp $

use strict;

use Sybase::Simple;

my $pwd = 'somepwd';
my $srv = 'THE_SERVER';
my $db = 'the_database';

my $dbh = new Sybase::Simple 'sa', $pwd, $srv;
$dbh->ExecSql("use $db");
my $tables = $dbh->ArrayOfScalar("
select name from sysobjects
where type = 'U'
and name not like 'rs_%' -- skip replication tables
and sysstat2 & 1024 != 1024 -- skip proxy tables
");


# I have TEXT and IMAGE columns - make sure that we copy the full length
# of the image/text columns
my $text_size = 1500000;


foreach my $tab (@$tables) {
my $start = time;
my $start_str = localtime($start);
warn "Processing $tab ($start_str)\n";
system("bcp $db..$tab out $tab.bcp -c -t'!~~!' -r'(**)\n' -Usa -P$pwd
-S$srv -Jutf8 -T$text_size >$tab.log 2>&1");

my $tot = time - $start;
warn "$tab done in $tot seconds (exit code $?)\n";
}