Copiando bases SQL Server com Copy-SQLDatabase

Copiar bases entre instâncias é procedimento muito comum em qualquer ambiente SQL, na maioria dos casos para manter ambientes de homologação, teste, desenvolvimento, etc. Esta tarefa pode ser muito simples, quando a base é pequena e tem poucos arquivos, ou complicada, quando a base é grande e há muitos arquivos envolvidos. A complexidade pode aumentar mais quando é preciso manter as permissões, executar scripts de expurgo, etc.  Pensando nisso, desenvolvi um script em powershell que tenta facilitar ao máximo este processo ao mesmo tempo que fornece uma série de recursos para este procedimento. Este artigo vai apresentar algum destes recursos!

Há um série de “pequenos probleminhas” que me incomodam bastante ao realizar restores de bancos de dados no SQL Server. Destaco os seguintes que contribuem para que este processo fique chato e cansativo:

1. Estrutura de discos diferentes na instância de origem e destino
   Mount points, letras diferentes, espaço disponível, etc., são elementos que resultam em retrabalhos e podem acarretar erros no processo de RESTORE.
2. Manter as permissões na base de destino
   Ao longo do tempo, diferentes usuários ganham diferentes tipos de acesso à base, e manter este controle para um posterior restore pode deixar mais caro ainda.
   Este é um tipo de solicitação bastante comum: Sobrescreva a base, mantendo os usuários e permissões!
3. Executar scripts após a cópia
   É comum que um ou mais scripts precisem ser executados após o restore para, por exemplo, alterar a senha de todos os usuários para 123456. Estes scripts podem variar conforme a necessidade do responsável pela mesma.

Copy-SQLDatabase cuida de todos estas questões e muito mais, permitindo uma maneira mais flexível de copiar bases.

Pré-Requisitos

Para executar os exemplos a seguir você irá precisar do seguinte:

• Powershell 2.0 ou superior (Instalado junto com o Windows a partir da versão 7/2008)
• Módulo CustomMSSQL
  CustomMSSQL é um modulo em powershell, contendo vários cmdlets, inclusive Copy-SQLDatabase.
  O link para download está acima e a instalação é fácil e rápida.
  Para executar os comandos, é preciso importar o módulo usando o comando Import-Module. No link do download existem instruções de instalação e uso do módulo.
• Pelo menos uma instância SQL Server 2005 ou superior (Óbvio que você não irá usar a produção para testes, ok?)
• Ambiente powershell
• • Permita a execução de scripts: Abra o powershell como Administrador e execute o comando:

    Set-ExecutionPolicy Unrestricted

  • Certifique-se que sua máquina tenha acesso ao servidor de destino (computador onde a instância de destino está)
  • • Verifique se o seguinte comando executa normalmente: Get-WmiObject Win32_Volume –ComputerName <IP-ou-Nome-Destino> | select Name
      • Em caso de falhas, verifique as permissões de acesso do WMI na máquina de destino.

Exemplo 1: Cópia simples

Copy-SQLDatabase –SourceServerInstance SQL1 –SourceDatabase MeuBanco –DestinationServerInstance SQL2 –DestinationDatabase Meubanco –BackupFolder “\\SQL2\c$”

Neste exemplo, estamos usando a forma mais básica de cópia, através do método Backup/Restore via T-SQL. Apenas especificamos as informações de conexão como instância de origem e de destino, além do nome da base na origem e o nome que a base irá ter no destino. Por padrão, Windows Authentication será usado. O Copy-SQLDatabase ainda não permite a conexão usando SQL Authentication, mas deverá ser implementando na próxima versão. O diretório especificado no parâmetro “BackupFolder” indica o local onde o Backup será gerado. As contas de serviço das instâncias envolvidas deverão ter as devidas permissões neste diretório. Na origem, Copy-SQLDatabase irá tentar usar as opções COMPRESSION e COPY_ONLY, se suportados.

Note que não foi necessário especificar nenhum local para os arquivos. Este é uma das grandes facilidades fornecidas por Copy-SQLDatabase. O cmdlet irá tentar determinar o melhor local para os arquivos da base. Usando o backup de origem, ele irá determinar os arquivos existentes, bem como o tamanho, e irá gerar o comando de RESTORE adequadamente. Há alguns algoritmos que Copy-SQLDatabase irá usar para distribuir os arquivos nos volumes existentes no destino. O algoritmo usado vai depender de alguns fatores, como o conjunto de parâmetros informados. O comportamento padrão é o seguinte:

• Se a base de destino não existe, então ele irá partir do maior arquivo e encontrar o volume com maior espaço livre. O processo vai se repetir até que todos os arquivos estejam com seus volumes determinados. Se não houver volume com espaço livre para algum arquivo, o script irá encerrar a execução reportando o erro “NO_VOLUME_FOUND_FOR_FILE”.
• Se a base de destino existe, então ele irá alocar os arquivos no mesmo diretório que os arquivos existentes (mapeando através do nome lógico do arquivo). Se houver um arquivo a mais, ele irá escolher um dos diretórios usados pela base aleatoriamente para alocar o novo arquivo. Isso poderá resultar em erros de espaço retornados pelo comando de RESTORE, já que o cmdlet assume que se a base existe, ela está no melhor local possível e, portanto, não valida o espaço. Se isto ocorrer, você pode usar o parâmetro “NoUseCurrentFilesFolder” para forçar o cmdlet a usar o algoritmo anterior, mesmo para a base existente:

  Copy-SQLDatabase –SourceServerInstance SQL1 –SourceDatabase MeuBanco –DestinationServerInstance SQL2 –DestinationDatabase Meubanco –BackupFolder “\\SQL2\c$” –NoUseCurrentFilesFolder

Por padrão, o script irá manter as permissões, caso a base exista. Antes de sobrescrever a base, as permissões, roles e seus membros, serão lidas e salvas na memória. Após o restore, as permissões serão aplicadas na base restaurada. Para desativar esse comportamento, utilize o parâmetro “-KeepPermissions $false”

Exemplo 2: Cópia simples com execução de scripts

Copy-SQLDatabase –SourceServerInstance SQL1 –SourceDatabase MeuBanco –DestinationServerInstance SQL2 –DestinationDatabase Meubanco –BackupFolder “\\SQL2\c$” –PostScripts “SELECT DB_NAME() as DBName”,”FILE:\\ARQUIVOS\SQL\Restore\Script.sql”,”FOLDER:\\ARQUIVOS\SQL\Restore\MeuBanco\*.sql”

Um das minhas features favortias deste cmdlet é a capacidade de executar scripts após o restore. Isso é útil quando existe uma série de scripts que o responsável pela base precisa que seja executado sempre que o restore for feito. O Parâmetro “-PostScripts” aceita um Array de strings onde cada string especifica um comando SQL, um arquivo ou um diretório contendo scripts para serem executados. Cada script é executado na ordem em que foi especificado, e no caso de diretórios os scripts serão executados na ordem do nome do arquivo. No exemplo acima, a ordem será:

1. O script SQL
2. Arquivo Script.sql
3. Arquivos  no diretório “MeuBanco”, ordenados pelo nome (seguindo as regras de ordenação do Windows)

Se um script falhar, por padrão, a execução dos scripts se encerrará e o erro do script será logado. Você pode controlar esse comportamento usando o parâmetro “–PostScriptsPolicy”. Este parâmetro indica o que o cmdlet irá fazer quando o script resultar em erro. Para ignorar o erro e executar o próximo script, especifique o valor “SkipErrors”.

Você pode se perguntar se isso não representaria um risco, pois o script pode conter código malicioso, que prejudique outras bases ou mesmo a instância. Para evitar isto, o cmdlet aceita o parâmetro “UseLimitedUser”. Com este parâmetro, Copy-SQLDatabase irá criar um login na instância de destino com permissões somente na base de destino. Assim, os scripts poderão afetar somente a base de destino (a menos que, por exemplo, a permissão de CONTROL SERVER seja garantida para a role public). Após a execução, o login/usuário que foram criados, serão deletados. O nome do login será formado pela string “CopySQLDatabaseLimited_” mais o nome da base de destino. Apesar do script gerar a senha, o comando de CREATE LOGIN é transferido a instância em texto claro e, portanto, pode estar sujeito a ataques. Versões futuras podem mudar a forma como este parâmetro é implementado.

Caso o script executado retorne um RESULTSET, o mesmo será exibido no error log, linha por linha, o que pode encher o log do cmdlet.  As próximas versões irão apresentar melhorias neste processo, permitindo maiores controles sobre os resultados gerados pelo script.

Copy-SQLDatabase possui muitas funcionalidades que iriam estender mais ainda este post. Você pode usar o comando Get-Help Copy-SQLDatabase para obter mais ajuda sobre o cmdlet e seus parâmetros. Atualmente, apenas o método Backup/Restore usando T-SQL é suportado, com algumas variações. Em versões futuras, suporte a ferramentas de backup de terceiros podem ser adicionadas, bem como outros métodos de cópia.  Antes de finalizar, deixo mais alguns recursos interessantes de Copy-SQLDatabase:

• Logging
  Copy-SQLDatabase tem uma poderosa engine de log que permite usar arquivos e até scripts powershell como destino das mensagens de log gerada. Há um modo especial para uso com o SQL Server Agent, devido ao modo como ele lida com logs e resultados.
• Arquivos
  Apesar do cmdlet tentar escolher o melhor local dos arquivos, podem existir casos onde ele erre ou não tenha as permissões suficientes. Alguns parâmetros permitem especificar manualmente o mapeamento dos arquivos, como se estivesse usando a cláusula MOVE. Também, é possível especificar filtrar volumes e ainda especificar quais volumes são permitidos para logs e para dados.
• Fonte do Backup
  Além de permitir que o backup em tempo real seja feito, o cmdlet permite usar um backup existente. Também, é possível forçar que a base de origem fiquem em READ_ONLY antes que o backup seja feito. Isto pode ser útil em migrações. Ainda, o cmdlet permite que um script T-SQL customizado de backup seja especificado.
• Destino
  Além da execução de scripts no destino, o cmdlet fornece opções como colocar a base em RECOVERY SIMPLE, ou mesmo fazer o backup da base de destino antes que a base seja sobrescrita

Copy-SQLDatabase, que ainda está na versão BETA, realmente pode ser um grande ferramenta e poupar boa parte do seu trabalho, permitindo que você concentre seus esforços em outros projetos. O mesmo já foi usado, inclusive, para migrar um ambiente de produção, onde a origem era uma instância standalone e o destino uma instância em cluster, utilizando mounts points, o que reforça o potencial. Alguns detalhes desta migração podem ser conferidos neste post.

O módulo CustomMSSQL está publicado no GitHub, portanto você pode melhorá-lo ou mesmo reportar bugs. Já existem alguns issues regitrados e você pode acompanhar as correções e melhorias implementadas, bem como fazer sugestões.

Haverão outros posts falando sobre Copy-SQLDatabase e outros cmdlets do módulo CustomMSSQL. Mas você pode usar a documentação do mesmo para aprender mais. Até a próxima!

[]’s
Rodrigo Ribeiro Gomes

Rodrigo Ribeiro Gomes

DBA Team Leader na Power Tuning