Applies to
ApexSQL Script
Summary
This article explains the ApexSQL Script CLI switches, and their usage through the examples
Description
Connection switches
| /server | Specifies server and instance name of the source SQL instance Alias: /s Format: /server:server_name[\instance_name] |
|
Quick tip: If a server is not specified “(local)” instance is assumed |
| /database | Specifies the name of the database(s) to connect to Alias: /d Format: /database:DatabaseName Note: If you specify more than one database, then each database name must be separated by a single space |
| /user | Specifies the login when using SQL Server authentication Alias: /u Format: /user:login Note: You must use this option together with the /password switch |
|
Quick tip: If a user is not specified a trusted connection is assumed |
| /password | Specifies the login’s password when using SQL Server authentication Alias: /p Format: /password:password Note: Note: You must use this option together with the /user switch |
Create database switches
| /db_dir | The MDF file of the database created by generated script will be created in the specified directory Alias: /dbd Format: /db_dir:D:\ApexSQL |
| /db_logdir | The LDF of the database created by generated script will be created in the specified directory Alias: /dbld Format: /db_logdir:log_directory |
| /db_collation | Specifies the database collation Alias: /dbc Format: /db_collation:latin_GENERAL_CI_AS |
| /db_recovery | Specifies the recovery mode for the database:
Alias: /dbrv |
| /db_size | Specifies the size for the database in KB, MB, or GB Alias: /dbs Format: /db_size:1024KB |
Specific options switches
| /output_type | Specifies the output type:
Alias: /ot |
| /individual | Specifies whether object will be written to an individual file. Script to single file if omitted Aias: /in Format: /individual Note: This argument cannot be used with /cf switch Omitting this switch will result in scripting to a single file |
| /create_file | Creates separate file for each database Alias: /cf Format: /create_file |
| /folder | Specifies the output folder for script each object to an individual file Alias: /fl Format: /folder:”D:\ApexSQL\Scripts” |
|
Quick tip: The switch /folder should be used along with the switch /individual |
| /encoding | Specifies the output file encoding:
Alias: /enc |
| /folder_mapping | Creates folder mapping as specified in the previously created folder mapping (XML) file. Alias: /fm Format: /folder_mapping:MyMappings.xml Omitting this switch will result in setting a default mapping |
|
Quick tip: The switch /folder_mapping should be used along with the switch /individual |
| /project | Specifies a project file path Alias: /pf Format: /project:project.axsc |
|
Quick tip: If a project file is saved outside of the installation directory, specify the path where it’s located |
| /mode | Specifies the scripting mode:
Alias: /m |
| /includes | Specifies the object selection profile path Alias: /i Format: /includes:profile.xml Omitting this switch will result in including all objects |
| /include | Includes specific objects in the processing. Objects can be specified by first specifying a bitwise set and a regular expression. Multiple includes can be specified with spaces in between. Alias: /inc Format: /include:bitwise_constant:regular expression |
| /exclude | Excludes specific objects from the processing. Objects can be specified by first specifying a bitwise set and a regular expression. Multiple excludes can be specified with spaces in between. The /exclude switch is processed after the /include switch (if it is specified) Alias: /exc Format: /exclude:bitwise_constant:regular expression |
| /oe:arg [ /output_elements ] | A set of the output elements options: None [n] Author [a] DateTime [dt] Legal [l] Comments [cmt] PrintStatements [ps] Default [dft] – Combination of Comments and PrintStatements Note: Arguments can be combined. Author and Legal are paired with /author and /legal switches.
Arguments can be combined. |
| /on:arg [ /output_name ] | Output name/folder path of the SQL script, script folder or executable packages (“Script_%date%_%time24%.sql” filename, “Script_folder_%date%_%time12%” folder name, “Package_%date%_%time12%.exe” filename or “Package_%date%_%time24%” folder if omitted) |
Owner options switches
| /owner_type | Specifies the owner mapping type:
Alias: /owt |
| /owner_map | Specifies the owner map Alias: /owmp Format: /owmp:”old;new dbo;test” |
Main options switches
| /no_transaction _handling |
Exclude transaction handling Alias: /nth Format: /no_transaction_handling |
| /no_increment _version |
The version number of the project is not automatically incremented Alias: /niv Format: /no_increment_version |
| /no_scr_use_db | Do not script USE database Alias: /nscu Format: /no_scr_use_db |
Structure script switches
| /include_dependent | Include dependent database objects Alias: /sid Format: /include_dependent |
| /scr_create_db | Script CREATE database Alias: /scdb Format: /scr_create_db |
| /scr_filegroups | Script filegroups Alias: /scf Format: /scr_filegroups |
| /scr_logins | Script logins with users Alias: /sck Format: /scr_logins |
| /no_scr_names | Do not script names Alias: /scn Format: /no_scr_names |
| /scr_collations | Script collations. This argument applies only to table objects Alias: /scc Format: /scr_collations |
| /no_scr_ansi_options | Do no script ANSI options Alias: /nsca Format: /no_scr_ansi_options |
| /scr_triggers | Script triggers with parent table, or view Alias: /sct Format: /scr_triggers |
| /scr_permissions | Script database permissions Alias: /sco Format: /scr_permissions |
| /scr_drops | Script drops Alias: /scd Format: /scr_drops |
| /scr_ex_properties | Script extended properties Alias: /sce Format: /scr_ex_properties |
| /dbcl:arg [ /db_compatibility _level] |
Set script compatibility level for database (“default” if omitted)
Arguments cannot be combined. |
Data script switches
| /no_data_identity_on | Do not include SET IDENTITY_INSERT ON in the generated script Alias: /ndio Format: /no_data_identity_on |
| /data_distinct | Only distinct rows are scripted. Duplicate rows are discarded Alias: /ddr Format: /data_distinct |
| /data_delete | Use DELETE statements Alias: /dd Format: /data_delete |
| /data_rows | Set data rows to script:
Alias: /drs |
| /data_batch_rows | Batch every n rows Alias: /dbr Format: /data_batch_rows:10 Omitting this switch will result in setting 1000 rows as default number of rows for batching |
| /data_apply_date | Apply date format Alias: /dad Format: /data_apply_date |
| /data_date_format | Set date format Alias: /ddf Format: /data_date_format:”YYYYMMDD” Omitting this switch will result in setting “yyyyMMdd HH:mm:ss” as default date format |
| /data_trim | Set trailing spaces Alias: /dtt Format: /data_trim |
| /ddac [/data_disable _constraints] |
Disable all triggers and constraints prior to loading data Alias: /ddac Format: /no_data_disable_constraints |
Script formatting switches
| /no_format_comments | Exclude comments Alias: /nfc Format: /no_format_comments |
| /format_obj_footer | Include object footer Alias: /fof Format: /format_obj_footer |
| /format_obj_header | Include object header Alias: /foh Format: /format_obj_header |
| /no_format_prints | Exclude PRINT statements Alias: /nfp Format: /no_format_prints |
| /format_scr_footer | Include script footer Alias: /fsf Format: /format_scr_footer |
| /no_format_scr_header | Exclude script header Alias: /nfsh Format: /no_format_scr_header |
| /obj_footer | Object footer template file Alias: /objf Format: /obj_footer:object_footer.sql |
| /obj_header | Object header template file Alias: /objh Format: /obj_header:object_header.sql |
| /script_footer | Script footer template file Alias: /scrf Format: /script_footer:script_footer.sql |
| /script_header | Script header template file Alias: /scrh Format: /script_footer:script_header.sql |
| /adn:arg [ /alter_db_name ] |
Alter database name |
Script processing
| /pre_script | Specifies the script to be processed before the generated script is executed Alias: /pre Format: /pre_script:argument |
| /post_script | Specifies the script to be processed after the generated script is executed Alias: /pst Format: /post_script:argument |
Script version switches
| /version_major | Major version number Alias: /vmj Format: /version_major:02 |
| /version_minor | Minor version number Alias: /vmr Format: /version_minor:005 |
| /version_build | Build number Alias: /vbd Format: /version_build:0078 |
Package Options
| /package_compression | Use package compression Alias: /pkc Format: /package_compression |
| /package_error | Specify the package error handling
Alias: /pke |
| /package_author | Sets the author property for the package Alias: /pkaa Format: /package_author:”John Smith” |
| /package_copyright | Sets the copyright property for the package Alias: /pkar Format: /package_copyright:”All rights reserved” |
| /package_legal | Sets the legal information property for the package Alias: /pkal Format: /package_legal:”This script is protected by copyright law” |
| /package_comments | Sets the comments property for the package Alias: /pkac Format: /package_comments:”Comments about this script” |
| /vsv:arg [ /vs_ver ] |
Visual studio version (“2017” if omitted)
Arguments cannot be combined. |
| /ddb:arg [ /indicate_default _db ] |
Indicate default database |
| /ren [ /run_exe_now ] |
Run executable now |
| /cmf [ /create_mfs ] |
Create manifest file |
| /cdf [ /create_dat ] |
Create dat file |
| /ccf [ /create_com ] |
Create com file |
| /ovs [ /open_vs ] | Open in visual studio |
Source control switches
| /sourcecontrol_type | Specifies the source control:
Alias: /sctype |
| /sourcecontrol_server | Specifies the source control server name Alias: /scsrv Format: /sourcecontrol_server:server_name |
| /sourcecontrol_user | Specifies the source control login Alias: /scusr Format: /sourcecontrol_user:Admin |
| /sourcecontrol_password | Specifies the source control password for login Alias: /scpwd Format: /sourcecontrol_password:password Note: You must use this switch together with the /sourcecontrol_user switch |
| /sourcecontrol_project | Specifies the source control project path Alias: /scprj Format: /sourcecontrol_project:project_path |
| /sourcecontrol_repository | Specifies the repository on source control server Alias: /screp Format: /sourcecontrol_repository:repository_path |
| /sourcecontrol_ working_folder |
Specifies the path to source control working folder Alias: /scwfd Format: /sourcecontrol_working_folder:work_folder_path |
| /sourcecontrol_label | Specifies the label name to be applied to source control project Alias: /sclbl Format: /sourcecontrol_label:label_name |
| /sourcecontrol_comment | Specifies the comment for source control operations Alias: /sccmt Format: /sourcecontrol_comment:comment |
Script tags switches
| /tag_author | Sets the script author tag Alias: /sta Format: /tag_author:”John Smith” |
Examples
Note: All examples assume that the current directory is the application directory. If not, the full path to ApexSQLScript.com must be specified.
Script each object of database AdventureWorks2014 on LocalServer server into an individual file, and store these files in the “D:\scripts\common” folder:
ApexSQLScript.com /s:LocalServer /d:AdventureWorks2014 /in /fl:”D:\ApexSQL\Scripts”
Set the date format, when scripting the “Table1” table of database AdventureWorks2014 on LocalServer server in the data mode:
ApexSQLScript.com /ddf:”ddMMyyyy HH:mm:ss” /m:data /s:LocalServer /d:AdventureWorks2014 /inc:8:Table1
Script the “Table1” table objects of database AdventureWorks2014 on LocalServer server to .sql file, excluding owners:
ApexSQLScript.com /s:LocalServer /d:AdventureWorks2014 /inc:8:Table1 /owm /owt:exclude
Script database objects by using a project file and specifying his path, overwrite an existing file of the same name, and verbose messages that are generated during the script processing:
ApexSQLScript.com /pf:”D:\ApexSQL\Projects\AW2014.axsc” /f /v
Using an argument file, e.g. “argfile.xml”, to execute commands:
ApexSQLScript.com /argfile:argfile.xml
Include dependent database objects of database AdventureWorks2014 on LocalServer server, when all tables are scripted:
ApexSQLScript.com /sid /s:LocalServer /d:AdventureWorks2014 /inc:8:””
Generate the structure of the objects of database on LocalServer server AdventureWorks2014, specify the .Net executable type of the output file, overwrite an existing file of the same name, and verbose messages that are generated during the script processing:
ApexSQLScript.com /ot:exe /s:LocalServer /d:AdventureWorks2014 /f /v
Script all tables, views and dependent objects in structure mode, of database AdventureWorks2014 on LocalServer server, with login credentials, to the output folder “D:\ApexSQL\Scripts”, using the Folder Mapping file “mappings.xml”, overwrite the existing default output file, and verbose messages that are generated during the script processing:
ApexSQLScript.com /sid /o:12 /s:LocalServer /d: AdventureWorks2014 /u:Username /p:Password /in /fl:”D:\ApexSQL\Scripts” /fm:”D:\ApexSQL\Maps\mappings.xml” /m:structure /v /f
