SQL Server 2008 - create database script (schema + data) with command-line

Question

In SSMS when I click on the database "Tasks -> Generate Scripts", I get an output script.

How do I do the same with a non-graphical tool?

Answer 1

The graphical tool is just a wrapper around the SMO classes that actually implement scripting, like the Scripter class. There is an example of scripting all tables in a database with SMO in MSDN: Scripting:

//Connect to the local, default instance of SQL Server. 
{ 
   Server srv = default(Server); 
   srv = new Server(); 
   //Reference the AdventureWorks database. 
   Database db = default(Database); 
   db = srv.Databases("AdventureWorks"); 
   //Define a Scripter object and set the required scripting options. 
   Scripter scrp = default(Scripter); 
   scrp = new Scripter(srv); 
   scrp.Options.ScriptDrops = false; 
   scrp.Options.WithDependencies = true; 
   //Iterate through the tables in database and script each one. Display the script. 
   //Note that the StringCollection type needs the System.Collections.Specialized namespace to be included. 
   Table tb = default(Table); 
   Urn[] smoObjects = new Urn[2]; 
   foreach ( tb in db.Tables) { 
      smoObjects = new Urn[1]; 
      smoObjects(0) = tb.Urn; 
      if (tb.IsSystemObject == false) { 
         StringCollection sc = default(StringCollection); 
         sc = scrp.Script(smoObjects); 
         string st = null; 
         foreach ( st in sc) { 
            Console.WriteLine(st); 
         } 
      } 
   } 
} 

There are many more examples how to use it on various other sites.

Answer 2

You can use powershell to do this. An example here for scripting tables. http://www.simple-talk.com/sql/sql-tools/using-powershell-to-generate-table-creation-scripts/ I'm guessing that it should be possible to extend for other types of database object.

Edit Just noticed that the title says data as well as schema. I'm not aware of anything free that does this from the command line. Redgate SQL Compare Suite is automatable from the command line if you buy the right version or you could write an application/power shell script to do it.