Tip: How to use Windows PowerShell to Manage SQL Server

Follow Our Daily Tips

• facebook.com/TechNetTips
• twitter.com/TechNetTips
• blogs.technet.com/tnmag

SQL Server 2008 includes SQL Server PowerShell for when you want to work from the command line. To work with SQL Server via Windows PowerShell, you must first open a Command Prompt window or Windows PowerShell prompt and then start SQL Server PowerShell by typing sqlps at the command line.

For administration at the PowerShell prompt, you use Invoke-Sqlcmd to run Transact-SQL (T-SQL) or XQuery scripts containing commands supported by the SQLCMD utility. Invoke-Sqlcmd fully supports T-SQL and the XQuery syntax supported by the Database Engine but does not set any scripting variables by default. Invoke-Sqlcmd also accepts the SQLCMD commands. By default, results are formatted as a table, with the first result set displayed automatically and subsequent result sets displayed only if they have the same column list as the first result set.

The basic syntax you use most often with Invoke-Sqlcmd looks like this:
Invoke-Sqlcmd [-ServerInstance ServerStringOrObject]
[-Database DatabaseName] [-EncryptConnection]
[-Username UserName][-Password Password]
[[-Query] QueryString][-DedicatedAdministratorConnection]

[-InputFile FilePath][ | Out-File –filepath FilePath]

The command’s parameters are used as follows:
–Database Specifies the name of the database that you want to work with.

–DedicatedAdministratorConnection Ensures a dedicated administrator connection (DAC) is used to force a connection when one might not be possible otherwise.

–EncryptConnection Enables Secure Sockets Layer (SSL) encryption for the connection.

–InputFile Provides the full path to a file that should be used as the query input. The file can contain T-SQL statements, XQuery statements, SQLCMD commands, and scripting variables. Note that spaces are not allowed in the file path or file name.

–Password Sets the password for the SQL Server Authentication login ID that is specified in –Username.

–Query Defines one or more queries to be run. The queries can be T-SQL queries, XQuery statements, or SQLCMD commands. You can separate multiple queries with a semicolon. (You do not need to use the SQLCMD GO command. Escape any double quotation marks included in the string and consider using bracketed identifiers such as [EmpTable] instead of quoted identifiers such as "EmpTable". To ensure message output is returned, add the –Verbose parameter. –Verbose is a parameter common to all cmdlets.)

–ServerInstance Specifies the name of an instance of the Database Engine that you want to work with. For default instances, specify only the computer name (such as "DbServer18"). For named instances, use the format "ComputerName\InstanceName" (such as "DbServer18\EmployeeDb").

–Username Sets the login ID for making a SQL Server authentication connection to an instance of the Database Engine. You must also set the password for the login ID.

Note that Invoke-Sqlcmd, by default, will attempt a Windows authentication connection by using the Windows account running the PowerShell session. Windows authentication connections are preferred. To use a SQL Server authentication connection instead, specify the user name and password for the SQL login ID that you want to use.

From the Microsoft Press book Microsoft SQL Server 2008 Administrator’s Pocket Consultant, Second Edition by William R. Stanek.

Looking for More Tips?

For more tips on Microsoft products and technologies, visit the TechNet Tips library.