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?

share|improve this question
2  
Why did you tag "database-backups"? Scripting a DB is very different to taking a backup... – gbn May 1 '10 at 14:29

4 Answers

up vote 6 down vote accepted

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.

share|improve this answer

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.

share|improve this answer
graphic utility can dump data – dynback.com May 1 '10 at 17:53

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.

share|improve this answer

You can script schema and data using the SQL Server publishing wizard. This can be done from the command line. Version 1.4 is the one you want it's bundled with Visual Studio as of version 2008 and also with SQL Server 2008. You can find it in Program Files/Microsoft SQL server/90/Tools/Publishing/1.4/SqlPubWiz I beleive it's also a project on CodePlex.

Type SQlPubWiz /? to see command line options

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.