Microsoft
recently introduced two new command line tools for SQL Server.
Cheers,
- 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.
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