how to bcp entire database out

how to bcp entire database out

Post by Tere » Sun, 06 Jul 2003 05:06:07



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

 
 
 

how to bcp entire database out

Post by Michael Pepple » Mon, 07 Jul 2003 15:45:07



> 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.

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;


    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";

Quote:}


 
 
 

1. Replicating an entire database

I have been told that we intend to use replication to maintain a copy of our
entire database.
This is being done partially as a backup but also to provide a copy of live
data on which reports
and ad hoc querys created by users can be run.

Our live database is reasonablely well used.

What are the implications of replicating the entire database?

Regards
Ian Wyld

2. MAPI description

3. * How to migrate the entire database

4. Create a parameter with the text datatype

5. Copy entire database to remote machine

6. changing the db in a stored procedure

7. Importing Entire VFP database into SQL Server

8. Presenting -- TESTING COMPUTER SOFTWARE (TCS2001) Conference Summary

9. Export entire database to Flat file

10. Search entire database for specific value

11. SQL-DMO Script entire database???

12. Upper Case on Entire Database