Post reply

Generating SQL Scripts for a Database.

  • August 30, 2004 at 5:55 pm

    #112202

    Hi Everyone

    I Was woundering if anyone had an idea how to generate SQL Scripts form an existing database, the scripts should contain all existing Table structures, trigeers.... and by no means the least also the data in any of the tables.

    Here are the thing i want in detail.

    1>Table structure

    2>Triggers

    3>Stored Procedures

    4>Views

    5>Users and User rights

    Data within all user tables.

    To generate the above 4 parts is quite easy using Enterprise Manager->Generate SQL Scripts, but unfortunately that does not create scripts with the data in the tables.

    As a result I then have to run the export data (net very friendly for my purpose).

    Any Help to do both actions on one go would be highly appreciated.

    Cheers

    Burzin

  • August 31, 2004 at 3:50 am

    #521180

    Hi,

    You want to recreate a database incluiding the data.....right.

    You can use ;

    Backup and restore within SQl server 2000.

    The exportfunction in the Enterprice Manager ( incl ODBC ) to almost any type of databases.

    Or you can use to generate the DDL (Enterprise Manager->Generate SQL Scripts) and use a sql statement to generate a complete insert string ( select 'insert into tablename values ( ' col1, col2 ' ); ' from table ).

    keep in mind that if you do this you have to deal with the constraints as defined in the DDL. This can slowdown you (insert) performance very much !!! ( each inserted row will be held to the ones already inserted. Also keep the master -child relation in mind !!!

    Or you can use an non-MS tool like Case-Studio or Embacadero.

    Hope this will puch you in the right direction.

    Any questions?  please respond.....

    GKramer

  • September 1, 2004 at 3:10 am

    #521319

    you can download the data and schema scripter which comes as part of the download for DB Ghost. It does schema and data scripting.

    check out http://www.dbghost.com

  • September 2, 2004 at 4:37 pm

    #521566

    Thanks guys I will try the DB Ghost software it appears to do what I want.

    By the way GKramer I was using the (Enterprise Manager->Generate SQL Scripts) followed by the export data function. combined they do what i want (after a lot of tweaking).

    What I am looking for is a rapid way to recreate template databases (everyday at least once). For my purposes a backup was not the correct solution as I wanted the ability to manually tweak the script (adding more inserts, updates, deletes..)

    Thanks a lot guys i will let you know how it goes

  • September 2, 2004 at 4:37 pm

    #521565

    Thanks guys I will try the DB Ghost software it appears to do what I want.

    By the way GKramer I was using the (Enterprise Manager->Generate SQL Scripts) followed by the export data function. combined they do what i want (after a lot of tweaking).

    What I am looking for is a rapid way to recreate template databases (everyday at least once). For my purposes a backup was not the correct solution as I wanted the ability to manually tweak the script (adding more inserts, updates, deletes..)

    Thanks a lot guys i will let you know how it goes

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply