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.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.|||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.|||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.|||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? :eek: 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.