Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2002
    Location
    Shropshire, UK.
    Posts
    25

    Question Unanswered: Creating DB schema from a script or stored procedure?

    Hi there,

    I am a fairly experienced programmer, but new to SQL Server - I understand basic DB theory well enough, but don't have much practical experience with using SQL Server.

    I'm working on a project at the moment, where, as part of the spec, users can create 'systems' in the database. For example, in a parts database for a pumping station, there may be 10,000 parts. Rather than have one huge database for, say, 10 pumping stations, we would prefer to have 10 smaller databases, each dedicated to its own system. The schemas would be identical.

    I think one approach to this would be have an empty database in SQL server (with the correct tables/schemas/relationships etc) and then copy that within SQL server, with a new name (the system name), probably using a stored procedure.

    My question: Is this possible, is there already a stored procedure in SQL Server (2000) to do this, or do I have to write one? Writing a SP to physically create the database from scratch would be a nightmare, I'm hoping there is a simple 'copy_db to new_db' type stored procedure. Maybe there is a program can read a DB and create a script to re-create the DB under a new name?

    Any information greatly appreciated.

    Mark Wills.
    Mark Wills.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    In my experience the multi-database approach is never worth the considerable administrative requirements to support it. Unless you anticipate the database size to increase way beyond the ability of a single database server to support it, you should serious rethink your plan and consider adding an additional field or table to your schema to make it scalable.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I am in agreement with Blindman but if you persist with this scheme you can create your initial database by scripting it out or using the Enterprise Mangeler (whoops) Manager (EM).

    Once you have your template defined, in the EM you can right click on your database and go to ALL TASKS and choose Generate SQL Scripts. You can make the EM script out all or some of your objects. then I guess you can wrap all of that code in a SP.

    There is another way. If all of your DBs are going to have the same structure and objects on this instance of SQL server I believe you can alter your model db and everytime you issue CREATE DATABASE, you should get all of the objects you created in model. I have never used this second method so I would read up on it first before trying.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Dec 2004
    Posts
    46
    Unless you would like to be able to backup and recover per station separately, otherwise single database is ok. I have database with 1.5 tera bytes, it works ok.

  5. #5
    Join Date
    Mar 2002
    Location
    Shropshire, UK.
    Posts
    25

    Ok :-)

    Quote Originally Posted by ronin
    Unless you would like to be able to backup and recover per station separately, otherwise single database is ok. I have database with 1.5 tera bytes, it works ok.
    1.5 TERA BYTES??? Holy cow, thats one hell of an MP3 collection

    Ok guys, thanks for the information. I'll do it like you say. As I say, whilst I'm vaguely familar with DB theory, SQL Server is a whole new ballgame for me - it's a bit daunting, so I'm sure I'll be back with many questions! Thanks for taking the time to reply.

    Mark.
    Mark Wills.

Posting Permissions

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