title: "Database Engine Scripting | Microsoft Docs" ms.custom: "" ms.date: "03/14/2017" ms.prod: "sql" ms.prod_service: "sql-tools" ms.service: "" ms.component: "ssms-scripting" ms.reviewer: "" ms.suite: "sql" ms.technology:

"database-engine" ms.tgt_pltfrm: "" ms.topic: "article" 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: 23 author: "mightypen" ms.author: "genemi" manager: "craigg" ms.workload: "Inactive" 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.