Scripting A SQL Server 2000 Database

[Cyberskull]

Hey guys and galls,
First time poster long time reader. I think I'll definitely have to become a part of the community.
What I'm trying to do:
Programmatically script a SQL Server 2000 database. I'd like to script everything from tables to stored procedures.

What I need To Do It By Code:
We role out new patches to hundreds of websites on a weekly basis. Along with having to keep all the running websites up to
date, we have to keep our script which creates new sites up to date as well.
We currently have a sql script that generates our database, all the tables and stored procedures on the fly. We created this script
manually and have to keep updating it every time we push out a patch.
So I thought it may be easier if we keep a database on SQL which we keep up to date and use that to script every new site.

Could there be a better way?
Does anyone maybe know a better way we can do this? I detaching a database and copying it from one to another but
I think the problem there was that the database name was the same for every database.

Any help would be greatly appreciated.

[szlamany]

Scripting is the way to go...

We script everything here - table creation, index creation - table modificaton...

But we do it all in separate .SQL text files - one for creating a SPROC - one creating a this or that...

We must have over 1000 of these .SQL text files.

Keep them all in source safe - so we have version control.

We know which ones to execute on a customer machine because of the "date modified". We even wrote a little VB utility to find all the .SQL's that have been modified since such and such a date and it creates a .BAT file that uses OSQL (or ISQL - I can never remember which one is the new one) to load these onto a customer machine.

[brucevde]

Could there be a better way?
Does anyone maybe know a better way we can do this? I detaching a database and copying it from one to another but
I think the problem there was that the database name was the same for every database.

What about using the Model database? Any new database created on the server has the exact same structure as the Model database. It would emulate the current version of your software. When its time to script, simply generate the scripts for all objects from Model.

Do you have customers that don't upgrade their software? Do you need to track which customer is on which version or do all customers get the new version immediately?

[Cyberskull]

brucevde,

Thanks for the reply.

I was thinking about having that model database and then scripting that database manually every time we push out updates. We do something similar where we just updat the actualy script manually but I'm not sure which way would take more time.

We don't track version updates as all of our clients are updated automatically. All the sites we update run on our servers.

[brucevde]

We don't track version updates as all of our clients are updated automatically. All the sites we update run on our servers.

Then utilizing the Model database would be perfect when creating new sites.

[Cyberskull]

Then utilizing the Model database would be perfect when creating new sites.

Have you ever tried detaching a model database, copying it, re-attaching it and also attaching the new copied database and also givin the new copied database a new name?

[brucevde]

You don't attach/detach the model database.

You create a new database, giving it whatever name you need. The new database would be a copy of Model. Then you detach the new database and attach it whereever....

[szlamany]

Is your goal to manage changes to a production database - with several customers? Or simply script the initial creation of the DB??

[Cyberskull]

You don't attach/detach the model database.

You create a new database, giving it whatever name you need. The new database would be a copy of Model. Then you detach the new database and attach it whereever....

I'm not sure that I follow your concept. I've created a model database let's say i called it "SQL_NewCustomersDB".

I included that database in all my patches so when ever I have a database patch that database will be updated with it.

Now that I have an uptodate database, do you mean I should manually script that database and use the code to create my new databases or maybe and hopefully there is some sort of function that allows me to copy everything within one database to another by code?

If that's what you mean then That's what I'd love to do.

Is your goal to manage changes to a production database - with several customers? Or simply script the initial creation of the DB??

szlamany,

We have a number of customers which we update on a weekly basis. Our update scripts work perfeclty where they update all the sites and databases at once.

Now becuase we update every customers website at the same time, we don't track the updates. So what we have to do every time we push an update to the new sites we have to update our script that creates a new database for a new customer every time.

What I'm trying to figure out is if there is a way I can do this automatically by using code.

Idealy I would like to:

By code, script an existing datbase of all its tables, primary key's, stored procedures and so on. And then by using that script I could by code create a new database on the fly.

Or

If there is a way where I can have a model database, somehow copy that database and create a new copy of the database with a different database name and also a different place to where i want to store the database files.

Thanks for all your help guys. It's much aprecaited