Usando o provedor do SQL Server PowerShell

O provedor do SQL Server para Windows PowerShell expõe a hierarquia de objetos do SQL Server em caminhos semelhantes aos caminhos do sistema de arquivos. Você pode usar os caminhos para localizar um objeto e usar os métodos dos modelos de Objeto de Gerenciamento do SQL Server (SMO) para executar ações nos objetos.

A hierarquia do SQL Server PowerShell

Produtos cujos modelos de objetos ou de dados podem ser representados em uma hierarquia usam os provedores do Windows PowerShell para expor as hierarquias. A hierarquia é exposta usando uma unidade e uma estrutura de caminho semelhantes às usadas pelo sistema de arquivos do Windows.

Cada provedor do Windows PowerShell implementa uma ou mais unidades. Cada unidade é o nó raiz de uma hierarquia de objetos relacionados. O provedor do provedor do SQL Server implementa uma unidade SQLSERVER:. A unidade SQLSERVER: possui quatro pastas principais. Cada pasta e suas respectivas subpastas representam o conjunto de objetos que podem ser acessados por meio de um modelo de objeto de gerenciamento do SQL Server. Quando o foco é uma subpasta em um caminho que começa com uma dessas pastas primárias, você pode usar os métodos do modelo de objeto associado para executar ações no objeto representado pelo nó. As pastas do Windows PowerShell implementadas pelo provedor do SQL Server 2008 R2 são listadas na tabela a seguir.

Pasta	Namespace do modelo de objeto do SQL Server	Objetos
SQLSERVER:\SQL	Microsoft.SqlServer.Management.Smo Microsoft.SqlServer.Management.Smo.Agent Microsoft.SqlServer.Management.Smo.Broker Microsoft.SqlServer.Management.Smo.Mail	Objetos do banco de dados, como tabelas, exibições e procedimentos armazenados.
SQLSERVER:\SQLPolicy	Microsoft.SqlServer.Management.Dmf Microsoft.SqlServer.Management.Facets	Objetos de gerenciamento baseado em políticas, como políticas e aspectos.
SQLSERVER:\SQLRegistration	Microsoft.SqlServer.Management.RegisteredServers Microsoft.SqlServer.Management.Smo.RegSvrEnum	Objetos de servidor registrados, como grupos de servidores e servidores registrados.
SQLSERVER:\Utility	Microsoft.SqlServer.Management.Utility	Objetos de utilitário, como instâncias gerenciadas do Mecanismo de Banco de Dados.
SQLSERVER:\DAC	Microsoft.SqlServer.Management.DAC	Objetos de aplicativo da camada de dados como pacotes do DAC e operações como implantação de um DAC.
SQLSERVER:\DataCollection	Microsoft.SqlServer.Management.Collector	Objetos de coletor de dados, como conjuntos de coleta e repositórios de configuração.

Por exemplo, você pode usar a pasta SQLSERVER:\SQL para iniciar caminhos que podem representar qualquer objeto suportado pelo modelo de objeto SMO. A parte à esquerda de um caminho SQLSERVER:\SQL\ComputerName\InstanceName. Você deve especificar um nome de computador. É possível especificar host local ou `(local`) para o computador local. Você sempre tem que especificar o nome de instância, até mesmo para instâncias padrão. Em instâncias padrão, especifique DEFAULT. Os nós após o nome da instância se alternam entre classes de objetos (como Database ou View) e nomes de objetos (como AdventureWorks2008R2). Os esquemas não são representados como classes de objeto. Ao especificar o nó para um objeto de alto nível em um esquema, como uma tabela ou exibição, é preciso especificar o nome do objeto no formato SchemaName.ObjectName.

Esse é o caminho da tabela Vendor no esquema Purchasing do banco de dados AdventureWorks2008R2 em uma instância padrão do Mecanismo de Banco de Dados no computador local:

SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks2008R2\Tables\Purchasing.Vendor

Para obter mais informações sobre a hierarquia de modelos de objeto do SMO, consulte Diagrama de modelos de objetos SMO.

Nós de classe de objeto em um caminho são associados a uma classe de coleção no modelo de objeto associado. Nós de nomes de objetos são associados a uma classe de objeto no modelo de objeto associado, como na seguinte tabela.

Caminho	Classe SMO
SQLSERVER:\SQL\MyComputer\DEFAULT\Databases	DatabaseCollection
SQLSERVER:\SQL\MyComputer\DEFAULT\Databases\AdventureWorks2008R2	Database

Sempre que você fizer referência a uma instância do Mecanismo de Banco de Dados em um caminho, o provedor do SQL Server usará o SMO para abrir uma conexão de Autenticação do Windows para a instância. A conexão é criada usando as credenciais da conta do Windows executando a sessão do Windows PowerShell. O provedor do SQL Server não usa a Autenticação do SQL Server.

Navegando pelos caminhos do SQL Server

O Windows PowerShell implementa os cmdlets para navegar pelas hierarquias do provedor e executar operações básicas no objeto atual. Como os cmdlets são usados com frequência, eles têm aliases pequenos, canônicos. Também existe um conjunto de aliases que mapeia os cmdlets para comandos semelhantes ao prompt de comando e outro conjunto aos comandos shell do UNIX.

O provedor do SQL Server implementa um subconjunto de cmdlets do provedor, mostrados na tabela a seguir:

cmdlet	Alias canônico	Alias de cmd	Alias de shell UNIX	Descrição
Get-Location	gl	pwd	pwd	Obtém o nó atual.
Set-Location	sl	cd, chdir	cd, chdir	Altera o nó atual.
Get-ChildItem	gci	dir	ls	Lista os objetos armazenados no nó atual.
Get-Item	gi			Retorna as propriedades do item atual.
Rename-Item	rni	rn	ren	Renomeia um objeto.
Remove-Item	ri	del, rd	rm, rmdir	Remove um objeto.

Por exemplo, você pode usar um dos seguintes conjuntos de cmdlets ou aliases para recuperar uma listagem de instâncias do SQL Server disponível, navegando para a pasta SQLSERVER:\SQL e solicitando a lista de itens filho da pasta:

• Usando nomes de cmdlet completos:

  Set-Location SQLSERVER:\SQL
  Get-ChildItem
  

• Usando aliases canônicos:

  sl SQLSERVER:\SQL
  gci
  

• Usando aliases de cmd:

  cd SQLSERVER:\SQL
  dir
  

• UNIX aliases de shell UNIX:

  cd SQLSERVER:\SQL
  ls
  

  Importante
  Alguns identificadores do SQL Server (nomes de objeto) contêm caracteres não suportados pelos nomes de caminho do Windows PowerShell. Para obter mais informações sobre como usar os nomes que contém esses caracteres, consulte Usando identificadores do SQL Server no PowerShell.

Usando Get-ChildItem

A informações retornadas por Get-ChildItem (ou seus aliases dir e ls) dependem de seu local no caminho SQLSERVER:\SQL.

Local do caminho	Resultados do Get-ChildItem
SQLSERVER:\SQL	Retorna o nome do computador local. Se você usou o SMO ou o WMI para estabelecer conexão com as instâncias do Mecanismo de Banco de Dados em outros computadores, esses computadores também estarão listados.
SQLSERVER:\SQL\ComputerName	A lista de instâncias do Mecanismo de Banco de Dados no computador.
SQLSERVER:\SQL\ComputerName\InstanceName	A lista de tipos de objeto de alto nível na instância, como Pontos de extremidade, Certificados e Bancos de Dados.
Nó da classe de objeto, como Bancos de Dados	A lista de objetos desse tipo, como a lista de bancos de dados: master, model, AdventureWorks20008R2.
O nó do nome de objeto, como AdventureWorks2008R2	A lista de tipos de objeto contida dentro do objeto. Por exemplo, um banco de dados poderia listar os tipos de objeto, como tabelas e exibições.

Por padrão, Get-ChildItem não lista nenhum objeto de sistema. Use o parâmetro Force para ver objetos de sistema, como os objetos no esquema sys.

Este exemplo lista o computador local e qualquer computador para os quais você criou uma conexão SMO ou WMI:

Set-Location SQLSERVER:\SQL
Get-ChildItem

Este exemplo lista as instâncias do Mecanismo de Banco de Dados no computador local:

Set-Location SQLSERVER:\SQL\localhost
Get-ChildItem

Este exemplo, lista as classes principais de objetos disponíveis em uma instância padrão do Mecanismo de Banco de Dados. A lista inclui nomes como Pontos de extremidade, Certificados e Bancos de Dados:

Set-Location SQLSERVER:\SQL\localhost\DEFAULT
Get-ChildItem

Este exemplo lista os bancos de dados disponíveis em uma instância padrão do Mecanismo de Banco de Dados. O parâmetro Force é usado para incluir os bancos de dados do sistema, como master e model:

Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases
Get-ChildItem -force

Executando ações nos nós do caminho

Depois de navegar até um nó em um caminho do Windows PowerShell, você poderá executar dois tipos de ações:

• Executar os cmdlets do Windows PowerShell que funcionam nos nós, como Rename-Item.

• Chamar os métodos do modelo de objeto de gerenciamento do SQL Server associado, como SMO. Por exemplo, se você navegar até o nó Banco de Dados em um caminho, poderá usar os métodos e as propriedades da classe Database.

O provedor do SQL Server é usado para gerenciar os objetos em uma instância do Mecanismo de Banco de Dados. Ele não é usado para trabalhar com dados em bancos de dados. Se você navegou até uma tabela ou exibição, você não pode usar o provedor para selecionar, inserir, atualizar ou excluir dados. Use o cmdlet Invoke-Sqlcmd para consultar ou alterar dados em tabelas e exibições do ambiente Windows PowerShell. Para obter mais informações, consulte Usando o cmdlet do Invoke-Sqlcmd.

Listando métodos e propriedades

Você pode usar o cmdlet Get-Member para exibir os métodos e as propriedades disponíveis para objetos ou classes de objetos específicos.

Este exemplo define uma variável do Windows PowerShell para a classe do SMO Database e lista os métodos e propriedades:

$MyDBVar = New-Object Microsoft.SqlServer.Management.SMO.Database
$MyDBVar | Get-Member –Type Methods
$MyDBVar | Get-Member -Type Properties

Você também pode usar Get-Member para listar os métodos e as propriedades associadas ao nó final de um caminho do Windows PowerShell.

Este exemplo navega até o nó Bancos de Dados em um caminho SQLSERVER: e lista as propriedades da coleção:

Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases
Get-Item . | Get-Member -Type Properties

Este exemplo navega até o nó AdventureWorks2008R2 em um caminho SQLSERVER: e lista as propriedades dos objetos:

Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks2008R2
Get-Item . | Get-Member -Type Properties

Usando métodos e propriedades

Você pode fazer referência às propriedades do SMO em comandos do Windows PowerShell. Este exemplo usa a propriedade do SMO Schema para obter uma lista de tabelas do esquema Sales em AdventureWorks2008R2:

Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks2008R2\Tables
Get-ChildItem | where {$_.Schema -eq "Sales"}

Este exemplo usa o método do SMO Script para gerar um script que contenha as instruções CREATE VIEW necessárias para recriar as exibições no AdventureWorks2008R2:

Remove-Item C:\PowerShell\CreateViews.sql
Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks2008R2\Views
foreach ($Item in Get-ChildItem) { $Item.Script() | Out-File -Filepath C:\PowerShell\CreateViews.sql -append }

Este exemplo usa o método SMO Create para criar um banco de dados e usa a propriedade State para mostrar se o banco de dados existe:

Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases
$MyDBVar = New-Object Microsoft.SqlServer.Management.SMO.Database
$MyDBVar.Parent = (Get-Item ..)
$MyDBVar.Name = "NewDB"
$MyDBVar.Create()
$MyDBVar.State

Definindo unidades personalizadas

O Windows PowerShell permite que os usuários definam unidades virtuais que são conhecidas como unidades PowerShell. Elas mapeiam os nós iniciais de uma instrução de caminho. Em geral, elas são usadas para encurtar caminhos que são digitados com freqüência. SQLSERVER: os caminhos podem se tornar longos, ocupando muito espaço na janela do Windows PowerShell e exigindo muita digitação. Se estiver trabalhando em um nó de caminho específico, poderá definir uma unidade Windows PowerShell personalizada que mapeie esse nó. Por exemplo, se você estiver trabalhando muito no banco de dados AdventureWorks2008R2, poderá criar uma unidade AWDB:.

New-PSDrive -Name AWDB -Root SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks2008R2

Em seguida, é possível usar a unidade AWDB: para reduzir o caminho para os objetos do AdventureWorks2008R2, como a tabela Purchasing.Vendor:

Set-Location AWDB:\Tables\Purchasing.Vendor

Gerenciando conexões de autenticação do SQL Server

Por padrão, o provedor do SQL Server usa a conta do Windows sob a qual ele está sendo executado para fazer uma conexão de Autenticação do Windows conexão com o Mecanismo de Banco de Dados. Para estabelecer uma conexão de Autenticação do SQL Server, você deve associar as credenciais de logon do SQL Server a uma unidade virtual e usar o comando alterar diretório (cd) para conectar-se a essa unidade. No Windows PowerShell, credenciais de segurança só podem ser associadas a unidades virtuais.

Esse script cria uma função chamada sqldrive que você pode usar para criar uma unidade virtual associada ao logon de Autenticação do SQL Server especificado e à instância.

function sqldrive
{
    param( [string]$name, [string]$login = "MyLogin", [string]$root = "SQLSERVER:\SQL\MyComputer\MyInstance" )
    $pwd = read-host -AsSecureString -Prompt "Password"
    $cred = new-object System.Management.Automation.PSCredential -argumentlist $login,$pwd
    New-PSDrive $name -PSProvider SqlServer -Root $root -Credential $cred -Scope 1
}

É possível criar uma unidade virtual denominada SQLAuth: executando este comando:

sqldrive SQLAuth

A função sqldrive solicita que você digite a senha do seu logon, mascarando-a à medida que você a digita. Em seguida, sempre que você usar o comando para alterar o diretório (cd) para conectar-se a um caminho usando a unidade SQLAuth:, todas as operações serão executadas usando as credenciais de logon de Autenticação do SQL Server que você forneceu ao criar a unidade.

Usando o namespace Microsoft.SqlServer.Managment.Smo.Wmi

Além dos namespaces do SQL Server Management Object Model que estão associados às pastas \SQL, \SQLPolicy e \SQLRegistration, você também pode usar classes no namespace Microsoft.SqlServer.Management.Smo.Wmi. O uso mais comum desse namespace é consultar e gerenciar o estado dos serviços que implementam cada instância do Mecanismo de Banco de Dados ou um armazenamento de diretivas.

Este exemplo mostra como usar a classe ManagedComputer para parar e iniciar o serviço que está executando uma instância padrão do Mecanismo de Banco de Dados.

# Get a reference to the ManagedComputer class.
cd SQLSERVER:\SQL\localhost
$Wmi = (get-item .).ManagedComputer
# Display the object properties.
$Wmi
# Get a reference to the default instance of the Database Engine.
$DfltInstance = $Wmi.Services["MSSQLSERVER"]
# Display the state of the service.
$DfltInstance
# Stop the service.
$DfltInstance.Stop(); write-host "Stopped"
# Refresh the cache and look at the state.
$DfltInstance.Refresh(); $DfltInstance
# Start the service again.
$DfltInstance.Start(); write-host "Started"

Observação
Para usar as classes neste namespace contra computadores remotos, você deve configurar seu Firewall do Windows para permitir conexões WMI DCOM. Para obter mais informações, consulte Configurando o Firewall do Windows para permitir acesso ao SQL Server.

Gerenciando Tab-Completion

O tab-completion do Windows PowerShell reduz a quantidade de digitação necessária. Ao digitar parte do nome de um caminho ou cmdlet, você pode acionar a tecla Tab para obter uma lista de itens cujos nomes correspondem aos dados que você já digitou. Você pode selecionar o item na lista sem precisar digitar o restante do nome.

Se você estiver trabalhando em um banco de dados que tenha muitos objetos, as listas tab-completion poderão se tornar muito grandes. Alguns tipos de objeto do SQL Server, como exibições, também apresentam muitos objetos de sistema.

Os snap-ins do SQL Server apresentam três variáveis de sistema que podem ser usadas para controlar a quantidade de informações apresentadas por tab-completion e Get-ChildItem.

• **$SqlServerMaximumTabCompletion =**n
  Especifica o número máximo de objetos a serem incluídos em uma lista tab-completion. Se você selecionar Tab em um nó de caminho com mais objetos de n, a lista tab-completion será truncada em n. n é um inteiro. 0 é a configuração padrão e significa que não há limite para a quantidade de objetos na lista.

• **$SqlServerMaximumChildItems =**n
  Especifica o número máximo de objetos exibido por Get-ChildItem. Se Get-ChildItem for executado em um nó de caminho com mais do que n objetos, a lista aparecerá truncada em n. n é um inteiro. 0 é a configuração padrão e significa que não há limite para a quantidade de objetos na lista.

• $SqlServerIncludeSystemObjects = { $True | $False }
  Se $True, os objetos de sistema serão exibidos na lista e em Get-ChildItem. Se $False, nenhum objeto de sistema será exibido. A configuração padrão é $False.

O exemplo a seguir define todas as três variáveis e lista suas configurações:

$SqlServerMaximumTabCompletion = 20
$SqlServerMaximumChildItems = 10
$SqlServerIncludeSystemObjects = $False
dir variable:sqlserver*

Consulte também

Conceitos

Executando o SQL Server PowerShell

Outros recursos

Visão geral do SQL Server PowerShell