• Article

Scripting (Database Engine)

The SQL Server Database Engine supports the Microsoft PowerShell scripting environment to manage instances of the Database Engine and the objects in the instances. You can also build and run Database Engine queries that contain Transact-SQL and XQuery in environments very similar to scripting environments.

SQL Server PowerShell

SQL Server includes two SQL Server PowerShell snap-ins that implement:

  • A SQL Server PowerShell provider that exposes the SQL Server management object model hierarchies as PowerShell paths that are similar to file system paths. You can use the SQL Server management object model classes to manage the objects represented at each node of the path.

  • A set of SQL Server cmdlets that implement SQL Server commands. One of the cmdlets is Invoke-Sqlcmd. This is used to run Database Engine Query scripts to be run with the sqlcmd utility.

SQL Server provides two features for running PowerShell:

  • The sqlps utility that starts PowerShell and loads the SQL Server snap-ins. You can interactively run ad hoc PowerShell commands. You can run script files using a command such as .\MyFolder\MyScript.ps1. You can start sqlps either in a command prompt or by right-clicking on the nodes in the SQL Server Management Studio Object Explorer tree and selecting Start PowerShell.

  • PowerShell script files can be used as input to SQL Server Agent PowerShell job steps that run the scripts either at scheduled intervals or in response to system events.

Database Engine Queries

Database Engine query scripts contain three types of elements:

  • Transact-SQL language statements.

  • XQuery language statements

  • Commands and variables from the sqlcmd utility.

SQL Server provides three environments for building and running Database Engine queries:

  • You can interactively run and debug Database Engine queries in the Database Engine 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 Database Engine queries, and also run existing Database Engine query script files.

Database Engine query script files are typically coded interactively in SQL Server Management Studio by using the Database Engine 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 Database Engine 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 SQL Server PowerShell scripts.

  • Use SQL Server Agent Transact-SQL job steps to run the scripts either at scheduled intervals or in response to system events.

In addition, you can use the SQL Server Generate Script Wizard to generate Transact-SQL 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. For more information, see Documenting and Scripting Databases.