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

Question

In SSMS when click on database "Tasks -> Generate Scripts" I get an output script. How to make the same with non graphical tool?

Answer 1

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.

Answer 2

For the data, you can use a bulk export utility called bcp which allows you to dump data from SQL Server tables into files, e.g. a CSV file, or a tab-delimited file.

I don't know of any SQL Server supplied utility that would create SQL scripts with INSERT statements as the SQL Server Management Studio does.

Answer 3

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.