I am attempting to script an MS SQL Server 2005 database to some single file. To date I have attempted using SQL Management Studio and also the MS Database Posting Wizard.

Both tools will script the database objects with no problem although neither tool is scripting objects within the right order of creation. For example the script might script a view which takes a table that's not really produced until later down the road.

Are you aware how you can script the database schema using the proper order of object creation?

We make use of this to produce the db scripts, i'd need to run up again to make sure it produces objects dependants first... however i haven't were built with a problem yet. http://www.red-gate.com/products/SQL_Compare/index.htm

My answer is going to be a little complicated, and delay pills work only when you script your whole database (i.e. keep my way through SQL scripts). What we should did inside a massive project was organize the scripts in to the following structure:

  • _ddl: supports the table changes, for example new posts, indexes, relations etc..
  • _fn: saved functions
  • _prc: saved methods
  • _static: clearly, "static data", or data which needs to maintain the database upon deployment
  • _tab: tables (create scripts)
  • _trg: triggers
  • _sights: view scripts

Folder names are obviously our random choice, you could arrange things in a different way. Next, we have produced a load script to merge all individuals files in one SQL within the following order:

  • _tab
  • _ddl
  • _trg
  • _sights
  • _fn
  • _prc
  • _static

The important thing trick would be to write your scripts in ways they may be operate a 1000 occasions. What this means is: drop your methods before you decide to create them, see if a table is available before creating it, see if a row is available before adding it in static, etc..

It isn't perfect, however it will get the task done.

Lecter has a good idea. You are able to concatenate these scripts utilizing a powershell script (or any 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'm removing a build file and destroy files - I had been really focusing on using sql native file encryption to some database, therefore the teardown might not be relevant. I'd my scripts in a single directory, so you may need to alter this script to complete some recursion, I'd think.

You can test xSQL Object at http://www.xsqlsoftware.com/Product/Sql_Schema_Compare.aspx It's labored ideal for me. The script is produced within the correct order whenever it is possible (you will find cases when the produced script can't be performed directly, but generally it really works)