MSSQL-SCRIPTER - New SQL Server Command Line Tool for generating T-SQL Scripts

Microsoft recently introduced two new command line tools for SQL Server. 

• mssql-scripter tool enables developers, DBAs, and sysadmins to generate CREATE and INSERT T-SQL scripts for database objects in SQL Server, Azure SQL DB, and Azure SQL DW from the command line.
• The DBFS tool enables DBAs and sysadmins to monitor SQL Server more easily by exposing live data from SQL Server Dynamic Management Views (DMVs) as virtual files in a virtual directory on Linux operating systems.

In this blog post we will see how to use mssql-scripter tool. You can use mssql-scripter on Linux, macOS, and Windows to generate data definition language (DDL) and data manipulation language (DML) T-SQL scripts for database objects in SQL Server running anywhere, Azure SQL Database, and Azure SQL Data Warehouse. This tool is built using Python, so to get this tool installed first you need to install Python on your machine. To download Python click here. 

After installing python, you can try and check the help for PIP. PIP is the package management system used to install and manage packages written in Python.

PIP --help

Now we are good to install the mssql-scripter tool. 

    Pip install mssql-scripter

To check the existence of the package

Pip show mssql-scripter

Let’s have a look at the general usage guide of the mssql-scripter tool.

This tools gives you lot of options to customize your scripting. I have just listed few below

--data-only -> By default only the schema is scripted. if supplied, generate scripts that contains data only.
--schema-and-data -> By default only the schema is scripted. if supplied, generate scripts that contain schema and data.
--target-server-version -> {2005,2008,2008R2,2012,2014,2016,vNext,AzureDB,AzureDW}. Script only features compatible with the specified SQL Version.
--target-server-edition -> {Standard,PersonalExpress,Enterprise,Stretch}. Script only features compatible with the specified SQL
--extended-properties -> Script the extended properties for each object scripted.
--logins -> Script all logins available on the server, passwords will not be scripted.
--object-permissions -> Generate object-level permissions.
--change-tracking -> Script the change tracking information.
--data-compressions -> Script the data compression information.

First let’s try with just scripting schema alone for a particular table [sales.SalesOrderDetail] in AdventureWorks database and write the output to a .sql file.

mssql-scripter -S SQLInstance\vNext -d AdventureWorks2012 --object-schema --include-objects [sales.SalesOrderDetail] >> Sales.sql

--target-server-version and --target-server-edition option looks interesting. I have enabled compression on SalesOrderDetail table which is an enterprise only feature.

Now I am going to script out the schema with specifying target version and edition as 2005 and standard respectively. 

mssql-scripter -S SQLInstance\vNext -d AdventureWorks2012 --object-schema --include-objects sales.SalesOrderDetail --target-server-version 2005 --target-server-edition Standard >> sales2005.sql

I was expecting some sort of warning/error about compression as this feature is not supported in the target version and edition. But in the output it had the syntax for data compression as well.

I am not sure, if I am missing something here. But still it’s the first release of this tool, I am sure this tool is going to be getting better in the future. With this command line tool, moving a database from higher version to lower version can be done in easier way. Instead of going through the Generate SQL Server Script wizard, with this tools it’s just simple one line of code and we are done!

I will explore more and will update my findings here. Stay Tuned

You can download this tool from here.

Thanks for reading!

UPDATE 1 - 02-Jun-2017

Above issue is reported on Microsoft GitHub repository -> https://github.com/Microsoft/sql-xplat-cli/issues/89

Cheers,

Naveen