How can I script an MS SQL Server database with pr

问题:

I'm trying to script an MS SQL Server 2005 database to a single file. So far I've tried using SQL Management Studio and the MS Database Publishing Wizard.

Both tools will script the database objects without a problem although neither tool is scripting objects in the right order of creation. For instance the script might script a view which requires a table that's not going to be created until later down the line.

Do you know how to script the database schema with the proper order of object creation?

回答1:

We use this to create the db scripts, i'd have to run it up again to ensure it creates objects dependants first... but i haven't had a problem yet. http://www.red-gate.com/products/SQL_Compare/index.htm

回答2:

My answer will be a bit complicated, and it works only if you script your entire database (i.e. keep everything in SQL scripts). What we did in a massive project was organize the scripts into the following structure:

• _ddl: holds the table changes, such as new columns, indexes, relations etc..
• _fn: stored functions
• _prc: stored procedures
• _static: obviously, "static data", or data which has to be in the database upon deployment
• _tab: tables (create scripts)
• _trg: triggers
• _views: view scripts

Folder names are of course our random choice, you can always arrange things differently. After that, we've created a batch script to merge all those files in a single SQL in the following order:

• _tab
• _ddl
• _trg
• _views
• _fn
• _prc
• _static

The key trick is to write your scripts in a way they can be run a thousand times. This means: drop your procedures before you create them, check if a table exists before creating it, check if a row exists before adding it in static, etc..

It's not perfect, but it gets the job done.

回答3:

There is a naive, but surprisingly effective way, to solve the ordering problem: keep running each script. An individual script will either eventually work (after other scripts are run first) or fail > # of scripts (in which case, it's a bad script). You may be able to detect a bad script quicker/easier - but I've never needed to.

If you have 1 giant script, it's probably split by GO statements. It should be enough to run it as many times as there are unique GO statements. Any attempts to CREATE an object that already exists will fail, and abort the batch. The next batch will run uninterrupted. Eventually, you have the necessary objects created - and rerunning the entire script will create the dependent object (and fail on the already created independent objects). You'll never get the script to run without errors, though.

If you want to get a bit fancier, you can split the giant script into individual batches and run them individually. Now, you can track what order is needed to get them to work. Just recombine them in that order, and output the new script. It should run without errors.

Or, spend the $500 to buy a tool that already does this (RedGate, Visual Studio Ultimate/Database Edition, etc.).

回答4:

Lecter has a good approach. You can concatenate these scripts using a powershell script (or other language)

run the script:

PS builddir:\> .\buildsql.ps1 -currentbuilddir "C:\Documents and Settings\sam\My Documents\svn\ticketing" -buildfile "sqlbuild.sql" -teardownfile
"teardown.sql"

powershell script:

param($currentbuilddir,$buildfile1,$teardownfile)

new-psdrive -name builddir -PSProvider filesystem -Root (resolve-path $currentbuilddir)

cd builddir:

rm $buildfile1
rm $teardownfile


Get-item Scripts_Build_1* | ForEAch-object {cat $_ >> $buildfile1; "GO --SYSTEM INSERTED GO--------------" >> $buildfile1} 

Get-item Scripts_Build_3* | ForEAch-object {cat $_ >> $teardownfile; "GO --SYSTEM INSERTED GO------------" >> $teardownfile} 

Here, I am deleting a build file and tear down files - I was actually working on applying sql native encryption to a database, so the teardown may not be applicable. I had all my scripts in one directory, so you might have to alter this script to do some recursion, I would think.

回答5:

You can try xSQL Object at http://www.xsqlsoftware.com/Product/Sql_Schema_Compare.aspx It has worked great for me. The script is created in the correct order whenever it's possible (there are cases where the generated script cannot be executed directly, but in most cases it works)