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
V nice article
ReplyDeleteNice one & very good explanation too
ReplyDeleteThank you
DeleteGreat article!
ReplyDeleteAs for your issue with the target server edition, the fix was just shipped :)
Please feel free to run a pip upgrade mssql-scripter.
Thanks Ronald!
DeleteThat's awesome. I will test and update this post :)
ReplyDeleteIt was very useful for me. This was actually what I was looking for, and I am glad to came here! Thanks for sharing the such information with us.
Server 2016
Microsoft Server 2016 Migration
Thank you so much for this wonderful article really!
ReplyDeleteMicrosoft Server 2016 Repair
Microsoft Server 2016 Support
nice thank you for sharing
ReplyDeleteSQL Server DBA Online Training Bangalore
nice post SQL Server DBA Online Training
ReplyDeleteI really enjoyed while reading your article, the information you have mentioned in this post was damn good. Keep sharing your blog with updated and useful information..
ReplyDeleteRead more about ms sql Corporate training, sql server 2016 training
Its really really awesome article & helpful Soo much Sql Server dba Online Training
ReplyDeleteSolve your Simple MS SQL Bugs with Microsoft SQL Server Support
ReplyDeleteActually this is happens with mostly new MS SQL Server users, when they trying to get all duplicates emails in a table, suddenly they got an error message “Error invalid Column Name CT”, and due to the lack of technical skill they do not how to overcome of this problem. But don’t worry we help you by providing most appropriate solution i.e. Remote Infrastructure Management Support for Microsoft SQL Server or Online MS SQL Server Support. Do not waste your time in searching for other support just dial this number 1-800-450-8670 and get most trusted support through SQL Server Database Support.
For More Info: https://cognegicsystems.com/
Contact Number: 1-800-450-8670
Email Address- info@cognegicsystems.com
Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801
It is very good blog and useful for students and developer ,
ReplyDeleteSql server DBA Online Training
It's so nice article thank you for sharing a valuable content. SQL server dba Online Training
ReplyDeletehow can i do it to download job with mssql-scripter?
ReplyDeletecan I duplicate db on same server with new name ?
ReplyDeleteGreat blog. All posts have something to learn. Your work is very good and I appreciate you and hopping for some more informative posts. Azure Database Administrator Associate
ReplyDelete