SQL Server - how to "Generate and Publish Scripts" automatically

Question

I want to automatically (ideally from the command prompt in a batch file) automate the generation of the schema of my SQL Server 2008 R2 database.

In SSMS, I can right-click the DB, choose "Tasks", "Generate scripts", and then follow the wizard to gen a Schema script. Is there a command-line version of this process that I can use?

Answer 1

Microsoft released a new tool a few weeks ago called mssql-scripter that's the command line version of the "Generate Scripts" wizard in SSMS. It's a Python-based, open source command line tool and you can find the official announcement here. Essentially, the scripter allows you to generate a T-SQL script for your database/database object as a .sql file. You can generate the file and then execute it. This might be a nice solution for you to generate the schema of your db (schema is the default option). Here's a quick usage example to get you started:

$ pip install mssql-scripter
# script the database schema and data piped to a file.
$ mssql-scripter -S localhost -d AdventureWorks -U sa  > ./adventureworks.sql

More usage examples are on our GitHub page here: https://github.com/Microsoft/sql-xplat-cli/blob/dev/doc/usage_guide.md

Answer 2

From this answer, there appear to be tools called SMOScript and ScriptDB that can do that.

If you find a way without third party tools please share :)