Database Engine Scripting
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 these features for running PowerShell:
The sqlps PowerShell module that can be imported to a PowerShell session, the module then 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.
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.
The sqlps utility that starts PowerShell and imports the SQL Server 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 SQL Server Management Studio Object Explorer tree and selecting Start PowerShell.
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 -i input_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.
Database Engine Scripting Tasks
Task Description |
Topic |
|---|---|
Describes how to use the code and text editors in Management Studio to interactively develop, debug, and run Transact-SQL scripts |
|
Describes how to use the sqlcmd utility to run Transact-SQL scripts from the command prompt, including the ability to interactively develop scripts. |
|
Describes how to integrate the SQL Server components into a Windows PowerShell 2.0 environment and then build PowerShell scripts for managing SQL Server instances and objects. |
|
Describes how to use the Generate and Publish Scripts wizard to create Transact-SQL scripts that recreate one or more of the objects from a database. |