How To Automatically Script SQL Server: 'Generate Scripts' for SQL Database

Question

I want to run scheduled nightly exports of my database code into my SVN source.

It's easy to schedule automated check-in's into svn from a folder, but scheduling the export from SQL in SQL Management Studio is

Right click target database, choose Tasks > Generate Scripts.

Follow the wizard and presto you've got scripts in a folder.

Is it possible to extract a single script that the wizard generates, and stuff that into a stored proc which I can run nightly?

Ideas?

Answer 1

I am not sure about single script but you can script the objects by using SQL Server Management Objects (SMO)

Answer 2

Not with a stored procedure, but using the SQL Server SMO Scripter object: See http://stackoverflow.com/questions/1309822/generate-sql-server-scripts-from-command-line

Answer 3

I wrote a tool call SMOscript which uses the SMO API mentioned by Giorgi. Creates a single file, or one file per object.