這裡向大家介紹一個新的生成T-SQL腳本的SQL Server命令行工具:mssql-scripter。它支持在SQL Server、Azure SQL DB以及Azure SQL DW中為資料庫生成CREATE和INSERT T-SQL腳本。 Mssql-scripter是一個跨平臺的命令行工具, ...
這裡向大家介紹一個新的生成T-SQL腳本的SQL Server命令行工具:mssql-scripter。它支持在SQL Server、Azure SQL DB以及Azure SQL DW中為資料庫生成CREATE和INSERT T-SQL腳本。
Mssql-scripter是一個跨平臺的命令行工具,功能等同於SQL Server Management Studio中的Generate and Publish Scripts Wizard。
咱們能夠在Linux、macOS和Windows上使用它生成數據定義語言(DDL-Data Definition Language)和數據操縱語言(DML – Data Manipulation Language),並且生成的T-SQL腳本可以運行在所有平臺的SQL Server、Azure SQL Database、以及Azure SQL Data Warehouse中。
Installation
1. Windows
a) 安裝Python,最新安裝包下載地址:https://www.python.org/downloads/,註意安裝的時候要選擇”Add Python to PATH”選項:
b) 安裝mssql-scripter,命令行里執行下麵命令:
pip install mssql-scripter
2. Linux
a) 檢查pip版本,是否是9.0及其以上:
pip –version
b) 如果pip未安裝或者版本低於9.0,使用如下命令安裝以及升級版本:
sudo apt-get install python-pip sudo pip install --upgrade pip
c) 安裝mssql-scripter:
sudo pip install mssql-scripter
如果系統是Ubuntu或者Debian,需要安裝libunwind8軟體包:
Ubuntu 14 & 17
執行如下命令:
sudo apt-get update sudo apt-get install libunwind8
Debian 8(暫時沒有環境,未測試)
文件‘/etc/apt/sources.list’需要更新:
deb http://ftp.us.debian.org/debian/ jessie main
執行如下命令:
sudo apt-get update sudo apt-get install libunwind8
3. macOS(暫時沒有環境,未測試)
a) 檢查pip版本,是否是9.0及其以上:
pip –version
b) 如果pip未安裝或者版本低於9.0,使用如下命令安裝以及升級版本:
sudo apt-get install python-pip sudo pip install --upgrade pip
c) 安裝mssql-scripter:
sudo pip install mssql-scripter
Usage Guide
幫助命令:
mssql-scripter -h usage: mssql-scripter [-h] [--connection-string | -S ] [-d] [-U] [-P] [-f] [--data-only | --schema-and-data] [--script-create | --script-drop | --script-drop-create] [--target-server-version {2005,2008,2008R2,2012,2014,2016,vNext,AzureDB,AzureDW}] [--target-server-edition {Standard,PersonalExpress,Enterprise,Stretch}] [--include-objects [[...]]] [--exclude-objects [[...]]] [--ansi-padding] [--append] [--check-for-existence] [-r] [--convert-uddts] [--include-dependencies] [--headers] [--constraint-names] [--unsupported-statements] [--object-schema] [--bindings] [--collation] [--defaults] [--extended-properties] [--logins] [--object-permissions] [--owner] [--use-database] [--statistics] [--change-tracking] [--check-constraints] [--data-compressions] [--foreign-keys] [--full-text-indexes] [--indexes] [--primary-keys] [--triggers] [--unique-keys] [--display-progress] [--enable-toolsservice-logging] [--version] Microsoft SQL Server Scripter Command Line Tool. Version 1.0.0a1 optional arguments: -h, --help show this help message and exit --connection-string Connection string of database to script. If connection string and server are not supplied, defaults to value in Environment Variable MSSQL_SCRIPTER_CONNECTION_STRING. -S , --server Server name. -d , --database Database name. -U , --user Login ID for server. -P , --password Password. -f , --file Output file name. --data-only Generate scripts that contains data only. --schema-and-data Generate scripts that contain schema and data. --script-create Script object CREATE statements. --script-drop Script object DROP statements --script-drop-create Script object CREATE and DROP statements. --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 Server database edition. --include-objects [ [ ...]] Database objects to include in script. --exclude-objects [ [ ...]] Database objects to exclude from script. --ansi-padding Generates ANSI Padding statements. --append Append script to file. --check-for-existence Check for database object existence. -r, --continue-on-error Continue scripting on error. --convert-uddts Convert user-defined data types to base types. --include-dependencies Generate script for the dependent objects for each object scripted. --headers Include descriptive headers for each object scripted. --constraint-names Include system constraint names to enforce declarative referential integrity. --unsupported-statements Include statements in the script that are not supported on the target SQL Server Version. --object-schema Prefix object names with the object schema. --bindings Script options to set binding options. --collation Script the objects that use collation. --defaults Script the default values. --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. --owner Script owner for the objects. --use-database Generate USE DATABASE statement. --statistics Script all statistics. --change-tracking Script the change tracking information. --check-constraints Script the check constraints for each table or view scripted. --data-compressions Script the data compression information. --foreign-keys Script the foreign keys for each table scripted. --full-text-indexes Script the full-text indexes for each table or indexed view scripted. --indexes Script the indexes (XML and clustered) for each table or indexed view scripted. --primary-keys Script the primary keys for each table or view scripted. --triggers Script the triggers for each table or view scripted. --unique-keys Script the unique keys for each table or view scripted. --display-progress Display scripting progress. --enable-toolsservice-logging Enable verbose logging. --version show program's version number and exit
相關例子:
- Dump database object schema
# generate DDL scripts for all objects in the Adventureworks database and save the script to a file mssql-scripter -S localhost -d AdventureWorks -U sa # alternatively, specify the schema only flag to generate DDL scripts for all objects in the Adventureworks database and save the script to a file mssql-scripter -S localhost -d AdventureWorks -U sa --schema-only
- Dump database object data
# generate DDL scripts for all objects in the Adventureworks database and save the script to a file mssql-scripter -S localhost -d AdventureWorks -U sa --data-only
- Dump the database object schema and data
# script the database schema and data to a file. mssql-scripter -S localhost -d AdventureWorks -U sa --schema-and-data > ./adventureworks.sql # execute the generated above script with sqlcmd sqlcmd -S mytestserver -U sa -i ./adventureworks.sql
- Include database objects
# generate DDL scripts for objects that contain 'Employee' in their name to stdout mssql-scripter -S localhost -d AdventureWorks -U sa --include-objects Employee # generate DDL scripts for the dbo schema and pipe the output to a file mssql-scripter -S localhost -d AdventureWorks -U sa --include-objects dbo. > ./dboschema.sql
- Exclude database objects
# generate DDL scripts for objects that do not contain 'Sale' in their name to stdout mssql-scripter -S localhost -d AdventureWorks -U sa --exclude-objects Sale
- Target server version
# specify the version of SQL Server the script will be run against
mssql-scripter -S -U myUser -d AdventureWorks –target-server-version “SQL Azure DB” > myData.sql
- Target server edition
# specify the edition of SQL Server the script will be run against
mssql-scripter -S -U myUser -d devDB –target-server-edition “SQL Server Enterprise Edition” > myData.sql
- Pipe a generated script to sed
下麵這個是Linux和macOS的用法。
# change a schema name in the generated DDL script # 1) generate DDL scripts for all objects in the Adventureworks database # 2) pipe generated script to sed and change all occurrences of SalesLT to SalesLT_test and save the script to a file mssql-scripter scripter -S localhost -d Adventureworks -U sa | sed -e "s/SalesLT./SalesLT_test./g" > adventureworks_SalesLT_test.sql
- Script data to a file
# script all the data to a file. mssql-scripter -S localhost -d AdventureWorks -U sa --data-only > ./adventureworks-data.sql
更詳細的Usage Guide或更新請參考:https://github.com/Microsoft/sql-xplat-cli/blob/dev/doc/usage_guide.md。
下麵執行一個命令看看效果,生成SharePoint Translation Service Database的CREATE語句:
mssql-scripter --server 10.2.53.22\ZEUS --database 'TranslationService_cd4699102b0745ba81ca0cf72d9ffe6e' --user sa --password '1qaz2wsxE' --file E:\CreateTranslationServiceDatabase.sql
執行結果的文件可以在這裡下載:http://files.cnblogs.com/files/lavender000/CreateTranslationServiceDatabase.zip。
另外還可以把連接字元串設置成環境變數:
# set environment variable MSSQL_SCRIPTER_CONNECTION_STRING with a connection string. export MSSQL_SCRIPTER_CONNECTION_STRING='Server=myserver;Database=mydb;User Id=myuser;Password=mypassword;' mssql-scripter # set environment variable MSSQL_SCRIPTER_PASSWORD so no password input is required. export MSSQL_SCRIPTER_PASSWORD='ABC123' mssql-scripter -S localhost -d AdventureWorks -U sa
[原創文章,轉載請註明出處,僅供學習研究之用,如有錯誤請留言,謝謝支持]
[原文:http://www.cnblogs.com/lavender000/p/6886560.html,來自永遠薰薰]