Anyone knows a good database setup tool for SQL Server?

I have a master database where we define all information of our software. It contains

• tables
• queries
• trigger
• stored procedures
• stored functions
• meta data
• in the table (content)

At the moment, with every change I manually (with some support from SQL Management Studio) edit files where I have all the CREATE, UPDATE开发者_JS百科, INSERT statements for the stuff mentioned above. When I have to create a new database I fire-up all the xyz.sql files, which contains my SQL statements.

I know there is a database creation script wizard in management studio, but this for example doesn't include the content data. I also need to make sure the stuff is executed for creation in the right order (e.g. queries , function, etc. last then structure tables are available).

At the moment I was thinking about a .NET project where I start read all the shema tables and then create the files automatically. In Ruby on rails the system creates a shema.rb and for the data yaml files. I tried work with this, but as many tables not created by active record (old c++ stuff also running), this won't work for me.

So does anyone have any hint for me how to do this best or any tool that fits perfect to my demand?

---

You can do this very easily in .NET using the SMO frameworks.

There are integrated tools for scripting out in dependency order, and you can script out data as well if you desire.

See my answer here for some info and links.

---

SQL Compare Pro should be able to load up your DDL creation scripts and deploy them to a target in the correct order. In the Edit Project dialog make sure you load your scripts as a Scripts Folder. For the data you'll need to use SQL Data Compare Pro. If you have any trouble or have questions, let me know as I work for Red Gate so will be able to help you with these tools.

I'm a little confused about why you've got UPDATEs given that these scripts create a database from scratch. Shouldn't they all be INSERTs?

SSMS does have the ability to create data scripts as well. You need SSMS 2008 and you need to go to Tasks/Generate Scripts and in the Choose Script Options pane you have to make sure Script Data is set to True.

If you're looking to maintain these scripts as a sensible way to source control your SQL Server objects, you might want to consider SQL Source Control. This will maintain your schema objects AND static data tables as individual .sql files.

---

"I know there is a database creation script wizard in management studio, but this for example doesn't include the content data."

You have to look carefully! Of course this build-in script engine can include the content data. You just have to click the button labeled "properties" (or something like that) and there you can change all the SMO script options including a full data dump.

This ends up in the script with many INSERT INTO... statements.

In-depth description

---

Try DbSourceTools.
It is a SQL Management tool designed specifically to script SQL databases to disk ( including data ), and then re-create them using "Deployment Targets".
We are using it for database source control in an agile project.