Fala, pessoal!

Continuando a nossa série sobre o Dbatools, neste artigo mostrarei como automatizar a exportação de scripts do SQL Server.

Pra você que está chegando agora: eu dei uma introdução básica ao Dbatools no artigo abaixo:

• https://imasters.com.br/banco-de-dados/dbatools-parte-01

O SSMS (SQL Server Management Studio) tem a opção de gerar scripts dos objetos do SQL Server. Se você ainda não conhece essa função, pode abrir o SSMS, clicar com o botão direto do mouse sobre seu banco de dados, depois em Tasks > Generate Scripts, ou clicar sobre qualquer objeto desejado e ele terá a opção de gerar o script.

O SSMS utiliza as bibliotecas SMO para fazer a geração dos scripts. No Dbatools temos diversos comandos que também utilizam essa biblioteca para manipular objetos do SQL Server, permitindo que tenhamos o mesmo resultado obtido no SSMS, mas tudo via script PowerShell.

Isso permite que você automatize alguma rotina onde seja necessário extrair frequentemente os scripts, ou até mesmo para manter backup de scripts dos seus objetos, sincronizar objetos entre servidores, entre outras funcionalidades.

Dentro da categoria de comandos para exportação, temos as seguintes opções:

• Export-DbaAvailabilityGroup
• Export-DbaLogin
• Export-DbaScript
• Export-DbaSpConfigure
• Export-DbaUser
• New-DbaScriptingOption

Para encontrar todos os comandos que começam pelo nome “Export”, podemos usar o seguinte comando:

Get-Command -Module dbatools -Name Export* -Type Function

Neste artigo vamos explorar o comando Export-DbaScript e entender um pouco do Pipeline no PowerShell.

Get-Help Export-DbaScript -Detailed

Para começar, vamos olhar a documentação do comando e entender melhor seu funcionamento. Escolhi esse comando porque frequentemente iremos utilizá-lo junto ao Pipeline no PowerShell.

No artigo anterior eu mencionei que o Pipeline é um recurso muito importante do PowerShell e que iria explicar um pouco sobre sua função.

Dentro do PowerShell, tudo é tratado como um objeto, e como objeto, ele tem suas propriedades e métodos. De forma genérica, o Pipeline que é representado pelo carácter “|” seria a passagem de objetos entre comandos, ou seja, o resultado de um comando será passado para o próximo.

‘DESKTOP-A7S2JPV’,’SQLServer02′ | Get-Member

Utilizando o comando Get-Member conseguimos ver todos os métodos e propriedades de um objeto.

‘DESKTOP-A7S2JPV’,’SQLServer02′ | Select Length

Sendo assim, podemos chamar após o Pipeline o comando Select ou Select-Object para listar apenas uma determinada propriedade como na imagem acima.

Para tentar explicar melhor sobre a passagem de objetos, utilizarei o exemplo abaixo:

‘ SQLServer01′,’ SQLServer02′,’ SQLServer03′ | Out-Host

Escrevi três strings ‘SQLServer01’, ‘SQLServer02’ e ‘SQLServer03’ – cada uma delas será tratada como um objeto no PowerShell. Logo em seguida, passei o caractere que representa o Pipeline “|” e o comando Out-Host, que tem a função de enviar saída para o console – poderia ser qualquer outro comando, como Find-DbaInstance.

Perceba na imagem acima que se eu não passar nenhum valor para o comando Out-Host, ele não irá apresentar nada ao console. No exemplo, o comando Out-Host recebe três objetos do Pipeline e envia para o console.

Essa é a funcionalidade do Pipeline no PowerShell. É muito importante ficar claro esse conceito, pois é muito utilizado em diversos outros shells.

Mais um exemplo para refrescar a sua mente:

‘DESKTOP-A7S2JPV’,’SQLServer02′ | Get-DbaSQLService

Passando uma lista de string que representa minha lista de servidores, e em seguida solicitando via comando Get-DbaSQLService listar todos os serviços de SQL Server desses servidores.

Repare que, para o comando Get-DbaSQLService, nenhum parâmetro foi passado após o comando, pois ele utiliza o resultado do Pipeline, executando o comando individualmente para cada objeto recebido. Caso você execute apenas o comando Get-DbaSQLService sem nenhum parâmetro, ele assumirá como default a máquina que está executando.

Para saber se um comando aceita valor do Pipeline, podemos usar o comando abaixo:

Get-Help Get-DbaSQLService -Full

Podemos ir além, exportar esse resultado para um arquivo .txt utilizando mais um Pipeline “|”:

‘DESKTOP-A7S2JPV’,’SQLServer02′ | Get-DbaSQLService | Out-File “C:\Temp\SQLServers.txt”

Ou melhor, prefere em .CSV?

‘DESKTOP-A7S2JPV\SQLSERVER2016′,’SQLServer02’ | Get-DbaSQLService | Export-Csv -Path “C:\Temp\Servers.csv”

Utilizando um arquivo .txt como lista de servidores e exportando os serviços para .CSV:

Get-Content -Path C:\temp\servidores.txt | Get-DbaSqlService | Export-Csv -Path “C:\Temp\Servers.csv”

Já imaginou ter toda informação do seu parque de servidores SQL Server com apenas um comando? Legal, né?

Abaixo, um link para consultar mais detalhes do Pipeline no PowerShell:

• https://docs.microsoft.com/pt-br/powershell/scripting/getting-started/fundamental/understanding-the-windows-powershell-pipeline?view=powershell-6

Uma vez que temos um entendimento básico do Pipeline, vamos por em prática junto com o comando Export-DbaScript, pegar os objetos passados via Pipeline e exportar o script com as opções desejadas. Começaremos exportando nossos Jobs do SQL Server.

Primeiramente, preciso acessar os Jobs que eu quero exportar, disponibilizá-los em forma de objetos e enviar via Pipeline ao comando Export-DbaScript. Para isso, também utilizarei o Dbatools:

Get-DbaAgentJob -SqlInstance DESKTOP-A7S2JPV\SQLSERVER2016

Através do comando Get-DbaAgentJob consegui acessar a lista de todos os jobs que tenho na minha instância de SQL Server, nomeada como SQLSERVER2016.

Agora é só utilizarmos nossa combinação de Pipeline com o comando Export-DbaScript.

Get-DbaAgentJob -SqlInstance DESKTOP-A7S2JPV\SQLSERVER2016 | Export-DbaScript -Path “C:\Temp\JobsScript.sql”

Pronto! Temos o script de todos os nossos Jobs dentro de um arquivo .sql. Podemos utilizar essa mesma função para qualquer objeto do SQL Server, basta acessar o objeto via SMO ou com o Dbatools.

Exportar todos os logins da minha instância de SQL Server:

Get-DbaLogin -SqlInstance DESKTOP-A7S2JPV\SQLSERVER2016 | Export-DbaScript -Path “C:\Temp\LoginsScript.sql”

Exportar Linkedservers com a opção -Append para gravar no mesmo script que estão meus logins:

Get-DbaLinkedServer -SqlInstance DESKTOP-A7S2JPV\SQLSERVER2016 | Export-DbaScript -Path “C:\Temp\LoginsScript.sql” -Append

Exportar o script de todas as tabelas de um banco de dados:

Get-DbaTable -SqlInstance DESKTOP-A7S2JPV\SQLSERVER2016 -Database Northwind | Export-DbaScript -Path “C:\Temp\TablesScript.sql”

Com o comando acima, é gerado script de todas as tabelas, sem as suas dependências e opções avançadas. Existe um comando para auxiliar nessa exportação dos scripts com as opções avançadas, como compressão, dependências, scripts de Drop, índices, triggers, continuar script após um erro, entre outras opções que você pode encontrar no link do comando New-DbaScriptingOption, ou vá direto nas propriedades disponíveis com o link abaixo:

• https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.management.smo.scriptingoptions?redirectedfrom=MSDN&view=sqlserver-2016

$options = New-DbaScriptingOption
$options.ScriptDrops = $false
$options.WithDependencies = $true
$options.Indexes = $true
$options.Triggers = $true
Get-DbaTable -SqlInstance DESKTOP-A7S2JPV\SQLSERVER2016 -Database Northwind | Export-DbaScript -Path “C:\Temp\TablesScript.sql” -ScriptingOptionObject $options

E o mais legal: você pode criar um Job no SQL Server para rodar esses comandos de acordo com a frequência que você precisa exportar ou realizar backups desses objetos:

Para isso, certifique-se que o Dbatools foi instalado a nível global na máquina, e não apenas no escopo do seu usuário. Veja como criar Jobs para executar seus comandos do Dbatools de forma mais eficiente em:

• https://dbatools.io/agent/.

E daqui pra frente, a sua imaginação e curiosidade é o limite.

Caso real de uso: um cliente que possuí um alto volume de criação de Jobs para atender a sua regra de negócio precisava enviar semanalmente o script de todos os Jobs para um fornecedor do sistema. Esse processo era feito manualmente, pois até então eles desconheciam outra forma de exportar o script de Jobs sem ser via SSMS.

Solução simples e eficiente, implantamos o Dbatools, criamos o script de exportação para um arquivo .sql (muito similar ao exemplo acima). Criamos um Job no SQL Server que roda semanalmente com o comando de exportar os scripts e já envia para um FTP onde é acessado pelo fornecedor!

Bom, galera, por aqui finalizo o artigo sobre Export-DbaScript com o Dbatools. Espero que você tenha captado a mensagem, inclusive sobre a utilização do Pipeline. Muitas coisas legais ainda estão por vir – me siga nessa imersão.

Abraços!