Scripting a database

I just starting working for a new client this week. They have a custom install program written in VB.NET which installs the database to SQL Server 2000. Normally, when I want to create a SQL script to create a database, I would go into Enterprise Manager, right-click on a database, and select All Tasks | Generate SQL Script. However, that's not how this client works. Much to my horror, the CREATE DATABASE, CREATE TABLE, etc. statements are both hard-coded and hand-coded within the install program itself. When I asked them why they weren't using SQL Server's scripting capabilities to automatically generate the script, they said that SQL Server scripting is buggy and that it doesn't work properly. It was hard getting specific details but they said it would leave out indexes, for example. Now, I'm no SQL Server expert, but I've never had a problem using SQL Server's scripting capabilities. The database they use isn't that big - maybe 20-30 tables. So, before I make an issue out of it, I was wondering what everyone else's experience with SQL Server 2000's scripting features? If you've used SQL Servers scripting to recreate a database, does it work? Did you have issues?The script is the same whether you run it all together through one or more script files versus issuing the commands one be one from an installer. Perhaps they had trouble with the ordering of the index creating script so that it was being called at the wrong time. Either that or some other dependancy was out of synch. In any event it shouldn't be the script per se but the order that it is being applied.

Maybe if you can see one of the script that failed you could figure out what's going on.|||They're not using SQL Server 2000's scripting capabilities at all. All the DDL statements are hand coded in the VB.NET installation program. This seems like a time-consuming and error-prone process. They said that the reason why they are not using SQL Server's script generation feature is because it is broken.|||What I meant was that the script is just a bunch of DDL as is the hand-coding. If there is a problem with the Sql Server generated script it's probably that something is in the wrong order and/or they didn't include the necessary checkboxes to includes indices "Options" tab.

But if their hand-generated DDL is working fine then I wouldn't mess with it. What they might want to do is place it all into a text file (don't call it a script...) and have the installer read that in and execute it. This text file can then be an embedded resource inside the install so that it's not visible to users but can be accessed inside the installer as if it were just another file. This at least would get all the DDL together in one place and not need to be intermixed with the code. There is a walk through for this in the VS.Net help system in the Windows installer section.|||Right now, the client has just one developer. However, my consulting company will probably be adding another 3 developers. I'm the architect/lead developer; we don't have a DBA. Including myself, that means a total of 5 developers will probably be working on the project. I'm worried that with 5 developers, the database could get f**cked up. Obviously, I need some sort of process to ensure the database 'integrity' remains intact.|||Are you just working with a single instance of the database or do you have to worry about multiple instances of the same db installed at different locations?

Do you have SourceSafe or equivalent? You could keep the scripts or installer code that does it there in order to maintain some control and accountability. You'd perhaps want to make it fine grained enough so that it's not just one file that someone always has checked out. I like to have "If then else" scripts for most database objects. If a table doesn't exist, create it. If it does exist check each column. If a column doesn't exist create it. This is a bit of a pain to set up initially but is relatively easy to maintain thereafter.|||There will be multiple instances of the database at remote locations to worry about. These remote locations will be our customers.

No, they are not using source control. We already discussed the importance of source control with them. We'll set up VSS as soon as we get additional developers.