title

Database Engine Scripting | Microsoft Docs

ms.custom

ms.date

03/14/2017

ms.prod

sql

ms.prod_service

sql-tools

ms.technology

scripting

ms.reviewer

ms.suite

sql

ms.tgt_pltfrm

ms.topic

conceptual

helpviewer_keywords

scripts [SQL Server], PowerShell

scripts [SQL Server]

scripting [SQL Server Database Engine]

scripting [SQL Server Database Engine], PowerShell

ms.assetid

9978a884-59a2-4e7f-a82a-335149f3a261

caps.latest.revision

author

MightyPen

ms.author

genemi

manager

craigg

monikerRange

>= aps-pdw-2016 || = azuresqldb-current || = azure-sqldw-latest || >= sql-server-2016 || = sqlallproducts-allversions

Database Engine Scripting

[!INCLUDEappliesto-ss-asdb-asdw-pdw-md] 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 [!INCLUDEssManStudioFull]. 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 [!INCLUDEssManStudioFull] by using the [!INCLUDEssDE] Query Editor. The file can later be opened in one of these environments:

Use the [!INCLUDEssManStudioFull] 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 [!INCLUDEssManStudioFull] Object Explorer, then select the Generate Script menu item. Generate Script launches the wizard, which guides you through the process of creating a script.