Como usar os backups do Microsoft SQL Server para recuperação pontual no Compute Engine

Last reviewed 2023-06-27 UTC

---

Observação: neste documento ou seção, há menções a um ou mais termos que o Google considera desrespeitosos ou ofensivos. Os termos são usados porque são palavras-chave no software descrito no documento.

Os termos: master

Neste tutorial, você fará backups em uma instância do SQL Server do Compute Engine. No tutorial, mostramos como gerenciar esses backups e armazená-los no Cloud Storage e como restaurar um banco de dados para um determinado momento.

Este tutorial será útil se você for administrador de sistemas, desenvolvedor, engenheiro, administrador de banco de dados ou engenheiro de DevOps e quiser fazer backup dos dados do SQL Server.

Observação: embora o gerenciamento de backups seja fundamental para qualquer plano de recuperação de desastres, este tutorial não aborda o tópico de recuperação de desastres no SQL Server.

Neste tutorial, presumimos que você esteja familiarizado com estes produtos e serviços:

• Microsoft Windows
• Microsoft SQL Server
• Backups completos, diferenciados e de registros de transações do SQL Server
• Compute Engine
• Cloud Storage

Objetivos

• Iniciar uma instância do SQL Server e criar um banco de dados.
• Executar backups completos, diferenciados e de registros de transações.
• Fazer o upload dos backups no Cloud Storage.
• Restaurar o banco de dados de um backup do Cloud Storage.

Custos

Neste documento, você usará os seguintes componentes faturáveis do Google Cloud:

• Compute Engine
• Rede
• Cloud Storage
• SQL Server (premium com Compute Engine)

Para gerar uma estimativa de custo baseada na projeção de uso deste tutorial, use a calculadora de preços. Novos usuários do Google Cloud podem estar qualificados para uma avaliação gratuita.

Antes de começar

1. Faça login na sua conta do Google Cloud. Se você começou a usar o Google Cloud agora, crie uma conta para avaliar o desempenho de nossos produtos em situações reais. Clientes novos também recebem US$ 300 em créditos para executar, testar e implantar cargas de trabalho.

2. No console do Google Cloud, na página do seletor de projetos, selecione ou crie um projeto do Google Cloud.

   Observação: se você não pretende manter os recursos criados neste procedimento, crie um projeto novo em vez de selecionar um que já existe. Depois de concluir essas etapas, é possível excluir o projeto. Para fazer isso, basta remover todos os recursos associados a ele.

   Acessar o seletor de projetos

3. Verifique se a cobrança está ativada para o seu projeto do Google Cloud.

4. Ative a API Compute Engine.

   Ative a API

5. No console do Google Cloud, na página do seletor de projetos, selecione ou crie um projeto do Google Cloud.

   Observação: se você não pretende manter os recursos criados neste procedimento, crie um projeto novo em vez de selecionar um que já existe. Depois de concluir essas etapas, é possível excluir o projeto. Para fazer isso, basta remover todos os recursos associados a ele.

   Acessar o seletor de projetos

6. Verifique se a cobrança está ativada para o seu projeto do Google Cloud.

7. Ative a API Compute Engine.

   Ative a API

8. Instale um cliente do Protocolo de Área de trabalho remota (RDP, na sigla em inglês) de sua escolha. Saiba mais em Clientes da Área de trabalho remota da Microsoft. Se você já tiver um cliente RDP instalado, pule esta tarefa.

Ao concluir as tarefas descritas neste documento, é possível evitar o faturamento contínuo excluindo os recursos criados. Saiba mais em Limpeza.

Como preparar a instância do SQL Server

Nesta seção, você inicia a instância do SQL Server, prepara o banco de dados e configura uma chave de criptografia.

Iniciar a instância do SQL Server

Sua primeira tarefa é iniciar uma instância do SQL Server e criar a pasta de backup.

1. Abra o Cloud Shell:

   ACESSAR o Cloud Shell

2. Inicie uma instância do SQL Server:

   gcloud compute instances create sqlserver \
       --zone=us-central1-c \
       --machine-type=n1-standard-1 \
       --image-family=sql-std-2019-win-2019 \
       --image-project=windows-sql-cloud \
       --boot-disk-size=50GB \
       --boot-disk-type=pd-standard \
       --tags=sqlserver \
       --scopes=https://www.googleapis.com/auth/cloud-platform
   

3. Acesse a página Instâncias de VM no Console do Google Cloud e encontre a instância do Windows à qual você quer se conectar:

   ACESSAR A PÁGINA "INSTÂNCIAS DE VM"

4. Defina a senha inicial da instância. Armazene a senha em um local seguro.

5. Na seção "Compute Engine" do console do Google Cloud, clique no menu suspenso RDP e selecione a opção Fazer o download do arquivo RDP para fazer o download do arquivo RDP da instância. Use esse arquivo para se conectar à instância usando um cliente RDP. Saiba mais em Clientes da Área de trabalho remota da Microsoft.

Instalar o SQL Server Management Studio

Para instalar o Microsoft SQL Server Management Studio (SSMS), faça o seguinte:

1. Na sessão RDP, minimize todas as janelas e inicie o app Windows PowerShell ISE.

2. No prompt do PowerShell, faça o download do instalador do SSMS e execute-o:

   Start-BitsTransfer `
       -Source "https://aka.ms/ssmsfullsetup" `
       -Destination "$env:Temp\ssms-setup.exe"
   & $env:Temp\ssms-setup.exe
   

3. Aceite a solicitação para permitir alterações.

4. No instalador do SSMS, clique em Instalar.

5. Quando a instalação for concluída, clique em Reiniciar para reiniciar a máquina remota. Com isso, a sessão RDP será encerrada.

6. Para se reconectar, clique em Conectar na janela RDP. Se a máquina remota ainda não tiver reiniciado, aguarde alguns instantes e tente se conectar novamente.

7. Insira seu nome de usuário e a senha que você salvou antes (deixe o campo Domínio em branco) e clique em OK para se reconectar.

Preparar as pastas de backup e restaurar

1. Na sessão RDP, minimize todas as janelas e abra o Shell do SDK do Google Cloud (não o Cloud Shell) na área de trabalho do Windows.

2. Crie uma pasta de backup:

   mkdir c:\backup
   

3. Crie uma pasta de restauração:

   mkdir c:\restore
   

Preparar o banco de dados

1. No Cloud Shell, crie um banco de dados de teste na instância:

   osql -E -Q "create database testdb"
   

   Observação: se você receber uma mensagem de falha no login, execute o Shell do SDK do Google Cloud como administrador.

2. Crie uma tabela de teste:

   osql -E -Q "create table testdb.dbo.testtable(status varchar(255))"
   

Configurar a chave de criptografia

1. No Cloud Shell, crie uma chave do banco de dados principal:

   osql -E -Q "USE master;CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyPassword!';"
   

   Observação: neste tutorial, você vai usar a senha MyPassword! para a chave de criptografia, mas é possível escolher a senha que quiser.

2. Crie um certificado de backup:

   osql -E -Q "USE master; CREATE CERTIFICATE testdbcert WITH SUBJECT = 'testdb certificate';"
   

Como fazer backups

SQL Server 2019 e anteriores

Nesta seção, você cria backups completos, diferenciais e de registro de transações enquanto altera o banco de dados entre cada um.

1. Adicione uma linha à tabela de teste:

   osql -E -Q "insert into testdb.dbo.testtable ([status]) VALUES ('Initial')"
   

2. No Cloud Shell, faça um backup completo:

   osql  -E -Q "BACKUP DATABASE testdb TO DISK='c:\backup\testdb.bak' WITH INIT,
       COMPRESSION,
       ENCRYPTION
       (
           ALGORITHM = AES_256,
           SERVER CERTIFICATE = testdbcert
       )  "
   

   Observação: você receberá um aviso de que não fez backup do certificado. Fazer o backup do certificado está além do escopo deste documento. Para mais informações, consulte CERTIFICADO DE BACKUP (Transact-SQL).

3. Adicione uma linha à tabela de teste:

   osql -E -Q "insert into testdb.dbo.testtable ([status]) VALUES ('After Full Backup')"
   

4. Faça um backup diferencial:

   osql  -E -Q "BACKUP DATABASE testdb TO DISK='c:\backup\testdb-diff.bak' WITH DIFFERENTIAL,
   COMPRESSION,
   ENCRYPTION
       (
       ALGORITHM = AES_256,
       SERVER CERTIFICATE = testdbcert
       ) "
   

5. Adicione uma linha à tabela de teste:

   osql -E -Q "insert into testdb.dbo.testtable ([status]) VALUES ('After Diff Backup')"
   

6. Execute um backup do registro de transações:

   osql  -E -Q "BACKUP LOG testdb TO DISK='c:\backup\testdb-log.bak' WITH COMPRESSION,
   ENCRYPTION
       (
       ALGORITHM = AES_256,
       SERVER CERTIFICATE = testdbcert
       ) "
   

7. Adicione uma linha à tabela de teste:

   osql -E -Q "insert into testdb.dbo.testtable ([status]) VALUES ('Bad Row')"
   

8. Verifique as linhas na tabela:

   osql -E -Q "select * from testdb.dbo.testtable"
   

   A saída será assim:

   Initial
   
   After Full Backup
   
   After Diff Backup
   
   Bad Row
   

SQL Server 2022

Nesta seção, você cria backups completos, diferenciais e de registro de transações enquanto altera o banco de dados entre cada um. Use os comandos integrados de backup e restauração com o Cloud Storage.

O SQL Server 2022 (16.x) oferece suporte à integração estendida de armazenamento de objetos, introduzindo um novo conector que usa uma API REST para se conectar a qualquer provedor de armazenamento de objetos compatível com S3. É possível usar os comandos BACKUP TO URL e RESTORE FROM URL no SQL Server 2022 com qualquer destino de armazenamento compatível com S3 como o URL.

O SQL Server usa credenciais para se conectar a recursos fora de si mesmo. Uma credencial é um registro que contém informações de autenticação. Para autenticar e autorizar seu acesso à interface do Cloud Storage S3, você precisa criar e usar uma chave de acesso e uma chave secreta. Em seguida, armazene essas chaves na sua credencial do SQL Server.

1. Crie uma chave de acesso e uma chave secreta para sua conta de usuário no bucket do Cloud Storage:

   1. Acesse o Cloud Storage
   2. Acesse Configurações.
   3. Acesse INTEROPERABILITY.
   4. Acesse Chaves de acesso para sua conta de usuário.
   5. Role para baixo e clique em Criar uma chave para criar uma nova chave de acesso e uma chave secreta.

2. Crie uma credencial na sua instância do SQL Server 2022:

   Execute o exemplo de código a seguir para criar uma credencial. Escolha um nome para sua credencial. Substitua os campos ACCESS_KEY e SECRET pelos valores gerados na etapa anterior.

   osql -E -Q "
       CREATE CREDENTIAL [CREDENTIAL_NAME]
       WITH
           IDENTITY = 'S3 Access Key',
           SECRET = 'ACCESS_KEY:SECRET'
       "
   

   Observação: a definição de identidade IDENTITY = 'S3 Access Key' é importante porque instrui o SQL Server a usar o S3 Connector. Não altere esse campo.
   .

3. Adicione uma linha à tabela de teste:

   osql -E -Q "insert into testdb.dbo.testtable ([status]) VALUES ('Initial')"
   

4. Execute a operação BACKUP no bucket do Cloud Storage:

   Execute o comando de backup do banco de dados com o URI do bucket do Cloud Storage definido como o valor de parâmetro URL e o nome da credencial definida anteriormente como o valor da opção WITH CREDENTIAL. Esse comando permitirá que o SQL Server crie o arquivo de backup e faça upload dele ao mesmo tempo para o bucket do Cloud Storage, sem necessidade de espaço extra em disco local.

   osql -E -Q "
       BACKUP DATABASE testdb
       TO URL = 's3://storage.googleapis.com/BUCKET_NAME/FOLDER_NAME/testdb.bak'
       WITH
           CREDENTIAL = 'CREDENTIAL_NAME',
           FORMAT,
           STATS = 10,
           MAXTRANSFERSIZE = 10485760,
           BLOCKSIZE = 65536,
           COMPRESSION;
       "
   

   Observação: verifique se você está usando o prefixo 's3://storage.googleapis.com', porque ele informa ao SQL Server para usar o conector compatível com S3. Isso também implica o uso de HTTPS.

A lista a seguir explica os parâmetros da instrução WITH:

• FORMATO: substitui os backups existentes e cria um novo conjunto de mídia.
• STATS: solicita que o SQL Server forneça informações sobre o andamento do backup.
• COMPRESSION: solicita que o SQL Server compacte o arquivo de backup, tornando-o menor e mais rápido para fazer upload para o Cloud Storage.
• As opções MAXTRANSFERSIZE = 10485760, BLOCKSIZE = 65536 ajudam a evitar erros de dispositivo de E/S com arquivos de backup maiores.

1. Adicione uma linha à tabela de teste:

   osql -E -Q "insert into testdb.dbo.testtable ([status]) VALUES ('After Full Backup')"
   

2. Faça um backup diferencial:

   osql -E -Q "
       BACKUP DATABASE testdb
       TO URL = 's3://storage.googleapis.com/BUCKET_NAME/FOLDER_NAME/testdb-diff.bak'
       WITH
           DIFFERENTIAL,
           CREDENTIAL = 'CREDENTIAL_NAME',
           STATS = 10,
           MAXTRANSFERSIZE = 10485760,
           BLOCKSIZE = 65536,
           COMPRESSION;
       "
   

3. Adicione uma linha à tabela de teste:

   osql -E -Q "insert into testdb.dbo.testtable ([status]) VALUES ('After Diff Backup')"
   

4. Execute um backup do registro de transações:

   osql -E -Q "
       BACKUP LOG testdb
       TO URL = 's3://storage.googleapis.com/BUCKET_NAME/FOLDER_NAME/testdb-log.bak'
       WITH
           CREDENTIAL = 'CREDENTIAL_NAME',
           STATS = 10,
           MAXTRANSFERSIZE = 10485760,
           BLOCKSIZE = 65536,
           COMPRESSION;
       "
   

5. Adicione uma linha à tabela de teste:

   osql -E -Q "insert into testdb.dbo.testtable ([status]) VALUES ('Bad Row')"
   

6. Verifique as linhas na tabela:

   osql -E -Q "select * from testdb.dbo.testtable"
   

   O resultado é semelhante a:

   Initial
   
   After Full Backup
   
   After Diff Backup
   
   Bad Row
   

Como gerenciar seus backups

Nesta seção, você armazena seus backups remotamente no Cloud Storage, configura seu bucket do Cloud Storage para remover automaticamente os backups antigos e agenda seus backups. Se você estiver executando vários servidores de banco de dados, considere a criação de vários diretórios no seu bucket do Cloud Storage para representar servidores diferentes.

Fazer o upload dos backups para o Cloud Storage

SQL Server 2019 e anteriores

Agora que você já tem alguns arquivos de backup, faça o upload deles para o Cloud Storage:

1. No Cloud Shell, crie um bucket do Cloud Storage. Os nomes de buckets precisam ser globalmente exclusivos no Google Cloud. Para garantir que o nome do bucket seja único, use-o em um namespace com o nome do projeto, como mostrado a seguir:

   gsutil mb "gs://${DEVSHELL_PROJECT_ID}-sql-backups"
   

2. Na janela do shell na sessão RDP, copie seus arquivos para o bucket do Cloud Storage. No comando abaixo, substitua BUCKET_NAME pelo nome do bucket que você acabou de criar.

   gsutil cp -n c:\backup\testdb*.bak gs://BUCKET_NAME
   

   Observação: a sinalização -n impede que a ferramenta gsutil substitua arquivos idênticos.

   É possível usar o comando gsutil cp para criar estruturas inteiras de diretório e fazer upload de vários arquivos por vez.

SQL Server 2022

Os arquivos de backup já estão no seu bucket porque o SQL Server 2022 é compatível com o backup diretamente no Cloud Storage.

Configurar a remoção automática de arquivos no Cloud Storage

Os backups mais antigos podem deixar de ser úteis, portanto, é necessário removê-los. Para automatizar esse processo, o Cloud Storage tem um mecanismo de gerenciamento do ciclo de vida que pode ser usado para gerenciar o ciclo de vida de seus arquivos de backup.

Aviso: o mecanismo de ciclo de vida exclui os arquivos permanentemente, sem a possibilidade de recuperá-los. Ao testar sua configuração de ciclo de vida, use arquivos de dados não críticos.

Para configurar o gerenciamento de ciclo de vida dos objetos do bucket:

1. No Cloud Shell, crie um arquivo de configuração do ciclo de vida JSON. Este arquivo instrui o Cloud Storage a excluir arquivos após 30 dias:

   bash -c 'cat <<EOF >  lifecycle.json
   {
       "lifecycle": {
           "rule": [{
               "action": { "type": "Delete" },
               "condition": { "age": 30 }
           }]
       }
   }
   EOF'
   

2. Defina a configuração do ciclo de vida para o bucket do Cloud Storage. Substitua BUCKET_NAME pelo nome do seu bucket:

   gsutil lifecycle set lifecycle.json gs://BUCKET_NAME
   

Agendar backups

Em geral, fazer um backup completo periodicamente e executar backups diferenciais até o próximo backup completo é uma prática recomendada. No Windows, uma maneira de agendar backups é usando tarefas agendadas.

Se você criar um script de backup que receba uma série de backups, inclua algumas etapas de validação lógica em cada ponto para verificar a conclusão bem-sucedida. Se a validação falhar, verifique se o script gera um alerta do Windows. Além disso, para evitar o preenchimento do disco local, verifique se o script remove o arquivo de backup local após fazer o upload para o Cloud Storage.

Como restaurar do backup

Nesta seção, você restaura o banco de dados do SQL Server dos arquivos de backup armazenados no Cloud Storage.

SQL Server 2019 e anteriores

1. No Cloud Shell, na sessão RDP, faça o download dos arquivos de backup do Cloud Storage. Substitua BUCKET_NAME pelo nome do seu bucket de armazenamento de backup do SQL Server:

   gsutil cp gs://BUCKET_NAME/testdb*.bak c:\restore
   

2. Abra o console do SQL Server Management.

3. Clique no botão "Iniciar" e, em seguida, clique em Ferramentas do Microsoft SQL Server 18 > Microsoft SQL Server Management Studio 18.

4. Deixe os campos Conexão como estão e clique em Conectar.

Observação: se você receber uma mensagem de falha no login, execute o Management Studio como administrador.

1. No painel esquerdo, expanda a opção Bancos de dados.
2. Clique com o botão direito do mouse em testdb e, no menu pop-up, clique em Tarefas> Restaurar> Banco de dados.
3. Importe os arquivos de backup para o console:
   1. Para Fonte, selecione Dispositivo.
   2. Clique no botão .
   3. Na caixa de diálogo exibida, clique em Adicionar, selecione todos os arquivos em C:\restore e clique em OK.
   4. Clique em OK.

4. Para visualizar as opções de restauração pontuais:

   1. Clique em Linha do tempo.
   2. Em Restaurar para, clique em Data e hora específicas.
   3. Para Intervalo da linha do tempo, selecione hora.
   4. Escolha um horário no intervalo Backup do registro de transações.

   5. Clique em Cancelar para sair da tela da linha do tempo.

      Observação: para restaurar seu banco de dados para um momento específico, pressione OK em vez de Cancelar.

5. Neste tutorial, você restaura o banco de dados para o estado anterior ao backup do registro de transações. Para fazer isso, na lista de backups, limpe a linha do registro de transações:

   

6. Inicie o processo de restauração:

   1. Clique em OK.
   2. Aguarde até que o banco de dados conclua o processo de restauração e, quando vir a mensagem Database 'testdb' restored successfully, clique em OK.

7. Liste as linhas na tabela de teste:

   osql -E -Q "select * from testdb.dbo.testtable;"
   

   A saída mostra duas linhas:

   "Initial
   "After Full Backup"
   

   Você verá todas as linhas inseridas na tabela antes de fazer o backup do registro de transações.

SQL Server 2022

Você pode executar a operação RESTORE. Defina o caminho do arquivo do Cloud Storage como o valor do parâmetro URL. Por exemplo, este script T-SQL restaura o backup completo diretamente do Cloud Storage:

```
osql -E -Q "
RESTORE DATABASE testdb
FROM
    URL = 's3://storage.googleapis.com/BUCKET_NAME/FOLDER_NAME/testdb.bak'
WITH
    CREDENTIAL = 'CREDENTIAL_NAME';
    "
```

1. Abra o console do SQL Server Management.
2. Clique no botão "Iniciar" e, em seguida, clique em Ferramentas do Microsoft SQL Server 19 > Microsoft SQL Server Management Studio 19.

Observação: é necessário instalar um Microsoft SQL Server Management Studio versão 19.x.

1. Deixe os campos Conexão como estão e clique em Conectar.

Observação: se você receber uma mensagem de falha no login, execute o Management Studio como administrador.

1. No painel esquerdo, expanda a opção Bancos de dados.
2. Clique com o botão direito do mouse em testdb e, no menu pop-up, clique em Tarefas> Restaurar> Banco de dados.

3. Importe os arquivos de backup para o console:

   1. Para Fonte, selecione Dispositivo.
   2. Clique no botão .
   3. Na caixa de diálogo aberta, clique em Backup media type e selecione S3 URL.

   4. Na caixa de diálogo exibida, clique em Adicionar. Adicione os três locais dos seus arquivos de backup no bucket do Cloud Storage e a chave secreta e de acesso que você forneceu quando criou a credencial.

      Observação :use o mesmo formato para fazer o backup do URL do S3.

   5. Clique em OK.

4. Para visualizar as opções de restauração pontuais:

   1. Clique em Linha do tempo.
   2. Em Restaurar para, clique em Data e hora específicas.
   3. Para Intervalo da linha do tempo, selecione hora.
   4. Escolha um horário no intervalo Backup do registro de transações.

   5. Clique em Cancelar para sair da tela da linha do tempo.

      Observação: para restaurar seu banco de dados para um momento específico, pressione OK em vez de Cancelar.

5. Neste tutorial, você restaura o banco de dados para o estado anterior ao backup do registro de transações. Para fazer isso, na lista de backups, limpe a linha do registro de transações:

   

6. Inicie o processo de restauração:

   1. Clique em OK.
   2. Aguarde até que o banco de dados conclua o processo de restauração e, quando vir a mensagem Database 'testdb' restored successfully, clique em OK.

7. Liste as linhas na tabela de teste:

   osql -E -Q "select * from testdb.dbo.testtable;"
   

   A saída mostra duas linhas:

   "Initial
   "After Full Backup"
   

   Você verá todas as linhas inseridas na tabela antes de fazer o backup do registro de transações.

Limpeza

Exclua o projeto

Cuidado: excluir um projeto tem os seguintes efeitos:
• Tudo no projeto é excluído. Se você tiver usado um projeto existente para as tarefas neste documento, a exclusão dele incluirá a exclusão de quaisquer outros trabalhos feitos no projeto.
• Os IDs do projeto personalizados são perdidos. Ao criar o projeto, você pode ter criado um código do projeto personalizado para ser usado no futuro. Para preservar os URLs que usam o ID do projeto, como um URL appspot.com, exclua recursos específicos do projeto, em vez de excluir o projeto inteiro.
1. No Console do Google Cloud, acesse a página Gerenciar recursos.

   Acessar "Gerenciar recursos"

2. Na lista de projetos, selecione o projeto que você quer excluir e clique em Excluir .
3. Na caixa de diálogo, digite o ID do projeto e clique em Encerrar para excluí-lo.

A seguir

• Leia sobre práticas recomendadas do SQL Server no Google Cloud.
• Leia sobre como configurar grupos de disponibilidade do SQL Server.
• Leia o guia de planejamento de recuperação de desastres no Google Cloud.
• Confira arquiteturas de referência, diagramas e práticas recomendadas do Google Cloud. Confira o Centro de arquitetura do Cloud.