Results 1 to 11 of 11
  1. #1
    Join Date
    May 2007
    Posts
    10

    Red face Unanswered: dump a database without data

    Dear all,

    Can any please explain me how to dump a database without data or is their any other solution to dump only the table structure or just schema on Adaptive server enterprise (ASE 15.0)


    Thanks you
    Lakshan

  2. #2
    Join Date
    Mar 2007
    Posts
    167

    Give these a spin.... :)

    This is one of the most trusted solutions out there. I have rolled these out in our production environments and love'n it! Life has been so much easier since implementation.

    The best part... Ed Barlow has been so kind to provide these stored procdures for free.

    http://www.edbarlow.com/gem/procs_only/index.htm

    The below stored procedures you will most likely be interested in are...

    sp__revalias
    sp__revbindings
    sp__revdb
    sp__revdevice
    sp__revgroup
    sp__revindex
    sp__revkey
    sp__revlogin
    sp__revmirror
    sp__revrole
    sp__revrule
    sp__revsegment
    sp__revtable
    sp__revtype
    sp__revuser


    P.S. I don't delieve there is a way to dump a database without data or just the schema. This is normally done by reverse engineering your database (generating DDL scripts) using IDE and/or IAE tools (DB Artisan is a good example, ERWin, etc.) or by keeping the DDL scripts you used originally for the production rollout (... of course maintaing the scripts as objects are added or dropped).
    Last edited by ftmjr; 05-24-07 at 22:58.

  3. #3
    Join Date
    May 2007
    Posts
    10

    Red face Dumping a databse without data

    Thanks for your reply

    Yes what I have realize is that too but their should be a way right what does bcp or Bulk Copy for imports table data from flat files and exports data out to files do u aware about this can you explain me how to carry out on this, creating DDL under each and every table can be done but I have around 150 tables so I need them with the data that cant be done if I use DDL

    Thanks you
    Lakshan

  4. #4
    Join Date
    Mar 2007
    Posts
    167

    Using an IDE / IAE Tool is the most efficiant and best way...

    Sure, I'll give it my best shot to explain the best I can...

    BCP tools and/or utilities are used to bulk copy data "out" and/or "in" from a table to a flat file (or vise versa)... Or copy data from a table on one server to another table on another server. Please keep in mind, this is only copying data out in a deliminated format. In no way is it copying out the schema or DDL information with it. SQSH is a great tool for this (a very powerful utility with both isql and bcp built into one).

    This is where the database option... "Allow Bulk Copy" comes in. It allows you to use BCP utilities and copy data in/out of a table in that specific database. If this database option is not set / not enabled, the BCP will fail.

    Yet, this doesn't solve or address your problem. This is something you will do after creating the destination database and desired tables/objects.

    ------------------------------------------------

    Okay, so this brings us to tying to find the best way to address your challenge... getting the DDL or schema for your database tables in the most efficiant way...

    Since you have so many tables, your best bet is using an IDE and/or IAE (i.e. DB Artisan, ERWIN, or any tool that has database reverse engineer funtionality). Keep in mind though, you are putting a lot of trust into the tool, hoping that it spits everything out correctly. Personally, I would use one of these tools and then comb through the DDL that it generates to confirm that all looks good.

    Oh... I just came up with another idea. The second idea is to create a Perl or Korn Shell script that loops a call to the sp__revtable stored procedure (free stored procedure at Ed Barlow's website), using the sysdatabases system table as the loop critiria. Have the isql spit out the results to an output file. Actully, the more I think, the more I realize that there's a million ways to do this. I'm sure a person can think of a "million ways to skin a cat".... So the short answer is...

    Option 01 - Create a script that calls a procedure that can reverse engineer the table schema and output the DDL (i.e. use a combination between Ed Barlow's sp__revtable and sp__revdb stored procedure, isql and your prefered handy dandy scripting language).

    Option 02 - Use a IDE / IAE that can reverse engineer you database and output the DDL to a file.

    Optoin 03 - Pay someone off the street to do it for you, but don't tell anyone you did so.

    Anyway, any way you look at it... you are going to have to pay the price somewhere (in man hours or the price of a good DBA Software Suite).

    Hope this helps and doesn't discourage you.

  5. #5
    Join Date
    May 2007
    Posts
    10

    Post dumping databse without data table stucture

    Thanks for your excellent ideas and the solutions which comes under that

    I will give a try on that (coz I have to get the tables with data now its really huge amount)

    But Im not that good in scripting, especially on dragging 100 tables from the database

    If you have any sample could you kindly post it here

    Thanks you
    Lakshan

  6. #6
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Quote Originally Posted by Lakshan Post #1
    dump a database without data...only the table structure or just schema
    Use ddlgen
    Quote Originally Posted by Lakshan Post #3
    creating DDL under each and every table can be done but ... I need them with the data
    Database dump and load will be the fastest way to copy all your tables
    Last edited by pdreyer; 05-28-07 at 11:18.

  7. #7
    Join Date
    May 2007
    Posts
    10

    Red face Dump using bcp utility data + tables at ones or one by one

    Thanks for the site and the information you provide

    But I have problem executing bcp command I have enable the bcp as follows on the database

    use abs_mediation
    go
    master..sp_dboption abs_mediation, "select into/bulkcopy/pllsort", true
    go
    checkpoint
    go

    It successfully applied but when I try to execute the command it says

    Commands :-
    use abs_mediation
    go
    bcp abs_mediation.dbo.IC_CLI_TRUNK out pub_out c t , -r \\r
    go

    Error:-
    Server Message: Number 102, Severity 15
    Server 'SYBASEASE', Line 1:
    Incorrect syntax near '.'.

    bcp abs_mediation..IC_CLI_TRUNK out IC_CLI_TRUNK
    go
    Error:-
    Server Message: Number 179, Severity 15
    Server 'SYBASEASE', Line 1:
    Can't use the OUTPUT option when passing a constant to a stored procedure.

    And when I try to execute this command on $SYBASE/$SYBASE_OCS/bin as
    bcp abs_mediation.dbo.IC_CLI_TRUNK out pub_out c t , -r \\r
    go

    it prompt for the password when I leave it blank just give an error saying Establishing connection fail

    Can some one please explain me what was wrong with this command and the syntax or what is the correct command to execute and how to enable and make use bcp utility

    Thanks you
    Lakshan

  8. #8
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    bcp is not a sql command. It should be executed from the operating system command line.
    Use the -U and -P flags to specify user name and password
    And if the DSQUERY environment variable is not set to the value you need use the -S flag to specify the sybase server name
    see
    Adaptive Server® Enterprise 15.0 Utility Guide
    Chapter 8: Utility Commands Reference
    bcp

  9. #9
    Join Date
    May 2007
    Posts
    10

    Smile post 'dump a database without data'‎

    Thanks for both of you replying and answering solution for my question
    Finally I be able to solve this and here is the solution which I use

    This is way how to get the Table data and the table filed dump from the database

    bcp avameddb..ORGANIZATIONS out pub_out24 -J iso_1 -Usa -SAVAMEDDB

    This make the data and the table field load to the newly created database

    bcp avameddb..IC_CLI_TRUNK in pub_out -f pub_form -J iso_1 -Usa SSYBASEASE

    Thanks again
    Lakshan Fernando (Sri lanka)
    (Sybase dba)

  10. #10
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Good to see you had success
    Creating a format file can be a pain
    An alternative is to create a view on the source to convert it to look like the target table then bcp out from the view using -n or -c switch e.g.
    bcp avameddb..ORG_TO_CLI_VIEW out org2cli.dat -Usa -SAVAMEDDB -n
    bcp avameddb..IC_CLI_TRUNK in org2cli.dat -Usa –SSYBASEASE -n

    And for improved speed, if our Sybase server is configured to allow a larger network packet size add e.g. -A8704
    Last edited by pdreyer; 06-05-07 at 04:51.

  11. #11
    Join Date
    May 2007
    Posts
    10

    Smile Reply to post 'dump a database without data'‎

    Thanks again

    I will try that too

    Thanks you
    Lakshan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •