title

description

author

ms.author

ms.date

ms.service

ms.subservice

ms.topic

helpviewer_keywords

monikerRange

Database Engine Scripting

Learn how you can use the Microsoft PowerShell scripting environment to manage instances of the SQL Server Database Engine, and how you can build and run Database Engine queries that contain Transact-SQL and XQuery.

markingmyname

maghan

03/14/2017

sql

ssms

conceptual

scripts [SQL Server], PowerShell

scripts [SQL Server]

scripting [SQL Server Database Engine]

scripting [SQL Server Database Engine], PowerShell

>=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current

Database Engine Scripting

[!INCLUDESQL Server Azure SQL Database Synapse Analytics PDW] The [!INCLUDEssDEnoversion] supports the [!INCLUDEmsCoName] PowerShell scripting environment to manage instances of the [!INCLUDEssDE] and the objects in the instances. You can also build and run [!INCLUDEssDE] queries that contain [!INCLUDEtsql] and XQuery in environments very similar to scripting environments.

SQL Server PowerShell

[!INCLUDEssNoVersion] includes two [!INCLUDEssNoVersion] PowerShell snap-ins that implement:

A [!INCLUDEssNoVersion] PowerShell provider that exposes the [!INCLUDEssNoVersion] management object model hierarchies as PowerShell paths that are similar to file system paths. You can use the [!INCLUDEssNoVersion] management object model classes to manage the objects represented at each node of the path.
A set of [!INCLUDEssNoVersion] cmdlets that implement [!INCLUDEssNoVersion] commands. One of the cmdlets is Invoke-Sqlcmd. This is used to run [!INCLUDEssDE] Query scripts to be run with the sqlcmd utility.

[!INCLUDEssNoVersion] provides these features for running PowerShell:

The sqlps PowerShell module that can be imported to a PowerShell session, the module then loads the [!INCLUDEssNoVersion] snap-ins. You can interactively run ad hoc PowerShell commands. You can run script files using a command such as .\MyFolder\MyScript.ps1.
PowerShell script files can be used as input to [!INCLUDEssNoVersion] Agent PowerShell job steps that run the scripts either at scheduled intervals or in response to system events.
The sqlps utility that starts PowerShell and imports the [!INCLUDEssNoVersion] module. You can then perform all actions supported by the module. You can start the sqlps utility either in a command prompt or by right-clicking on the nodes in the [!INCLUDEssNoVersion] Management Studio Object Explorer tree and selecting Start PowerShell.

Database Engine Queries

[!INCLUDEssDE] query scripts contain three types of elements:

[!INCLUDEtsql] language statements.
XQuery language statements
Commands and variables from the sqlcmd utility.

[!INCLUDEssNoVersion] provides three environments for building and running [!INCLUDEssDE] queries:

You can interactively run and debug [!INCLUDEssDE] queries in the [!INCLUDEssDE] Query Editor in SQL Server Management Studio. You can code and debug several statements in one session, then save all of the statements in a single script file.
The sqlcmd command prompt utility lets you interactively run [!INCLUDEssDE] queries, and also run existing [!INCLUDEssDE] query script files.

[!INCLUDEssDE] query script files are typically coded interactively in SQL Server Management Studio by using the [!INCLUDEssDE] Query Editor. The file can later be opened in one of these environments:

Use the SQL Server Management Studio File/Open menu to open the file in a new [!INCLUDEssDE] Query Editor window.
Use the -iinput_file parameter to run the file with the sqlcmd utility.
Use the -QueryFromFile parameter to run the file with the Invoke-Sqlcmd cmdlet in [!INCLUDEssNoVersion] PowerShell scripts.
Use [!INCLUDEssNoVersion] Agent [!INCLUDEtsql] job steps to run the scripts either at scheduled intervals or in response to system events.

In addition, you can use the [!INCLUDEssNoVersion] Generate Script Wizard to generate [!INCLUDEtsql] scripts. You can right-click objects in the SQL Server Management Studio Object Explorer, then select the Generate Script menu item. Generate Script launches the wizard, which guides you through the process of creating a script.