Banco de Dados

3 mai, 2017

Restaurando bases de dados em instâncias diferentes

Publicidade

É muito comum precisarmos restaurar um backup de uma base em uma instância diferente. Isso acontece, por exemplo, quando se restaura o backup de uma base produtiva no ambiente de testes. O processo é bem simples, mas às vezes acontecem pequenos problemas por falta de atenção aos detalhes da operação.

Neste artigo, eu explico como executar a operação de restauração de um backup full entre instâncias diferentes e que usam discos e drives diferentes.

Como funciona a restauração de um backup full

Quando fazemos um backup full de um banco de dados, estamos, na verdade, criando uma imagem completa dos filegroups usados, ou seja, o primário, os secundários (se existirem) e log de transações.

Os filegroups, evidentemente, estão associados aos datafiles do banco e, portanto, eles serão incluídos no backup. Parte do log de transações também será incluída no backup full para manter consistência de transações que estavam abertas ao final da geração do backup.

Então, quando verificarmos o conteúdo de um arquivo de backup, veremos:

  • Os nomes lógicos de todos datafiles;
  • Os nomes físicos destes arquivos;
  • O caminho completo onde cada um será restaurado;
  • Propriedades físicas (tamanho atual e máximo de cada arquivo);
  • Detalhes sobre o conteúdo do backup.

Estas são as informações que precisamos para restaurar este banco em qualquer outro ambiente.

Exemplo prático: a base original

Para ilustrar esta operação, eu considero a base de dados meuBD, descrita a seguir.

CREATE DATABASE [meuBD] ON  
 PRIMARY 
   ( NAME = N'meuBD_Data', FILENAME = N'C:\Data\meuBD_Data.mdf' , 
     SIZE = 500MB , MAXSIZE = 32GB, FILEGROWTH = 10MB ), 
   ( NAME = N'meuBD_Data2', FILENAME = N'F:\Data\meuBD_Data2.mdf' , 
     SIZE = 500MB , MAXSIZE = 32GB, FILEGROWTH = 10MB ),

 filegroup [SECONDARY] 
   ( NAME = N'meuBD_Data3', FILENAME = N'H:\Data\meuBD_Data3.ndf' , 
     SIZE = 500MB , MAXSIZE = 32GB, FILEGROWTH = 10MB ) ,
  ( NAME = N'meuBD_Data4', FILENAME = N'F:\Data\meuBD_Data4.ndf' , 
    SIZE = 500MB , MAXSIZE = 32GB, FILEGROWTH = 10MB ) ,

 filegroup [INDEX] 
  ( NAME = N'meuBD_Index', FILENAME = N'H:\Index\meuBD_Index.ndf' , 
    SIZE = 50MB , MAXSIZE = 32GB, FILEGROWTH = 10MB )

 LOG ON 
  ( NAME = N'meuBD_Log', FILENAME = N'F:\Log\meuBD_Log.ldf' , 
    SIZE = 50MB , MAXSIZE = 32GB , FILEGROWTH = 10% ),
  ( NAME = N'meuBD_Log2', FILENAME = N'F:\Log\meuBD_Log2.ldf' , 
    SIZE = 50MB , MAXSIZE = 32GB , FILEGROWTH = 10%)
GO


USE meuBD
GO
CREATE LOGIN [NT SERVICE\MSSQL$SQL2016] FROM WINDOWS;/*WINDOWS_GROUP*/
CREATE LOGIN [meuLoginSQL] WITH PASSWORD = '123DeOliveira4';/*SQL_LOGIN*/
GO
CREATE USER [ServicosAdministrativos] 
      FOR LOGIN [NT Service\MSSQL$SQL2016] WITH DEFAULT_SCHEMA=[dbo]
CREATE USER [meuLoginSQL] FOR LOGIN [meuLoginSQL] WITH DEFAULT_SCHEMA=[dbo]
GO

Observe neste script que a base meuBD usa dois datafiles para log de transações e mais cinco datafiles para dados e índices, estes últimos distribuídos em três filegroups: PRIMARY, SECONDARY e INDEX.

Isso permite um controle melhor da utilização dos recursos de disco para cada objeto do banco. Neste exemplo, esta estrutura lógica foi criada para que as tabelas pequenas e médias sejam gravadas no filegroup PRIMARY, tabelas grandes (mais de um milhão de registros) destinadas ao SECONDARY e índices (que não sejam clusterizados) gravados no filegroup INDEX.

Note também que o banco usa mais de um datafile nos filegroups mais importantes e que estes datafiles são distribuídos em vários discos diferentes para melhorar as operações de I/O.

Finalmente, a base tem dois usuários, um de domínio e outro autenticado pelo próprio SQL. Mas acho difícil que você receba um arquivo de backup de qualquer banco de dados acompanhado de explicações como estas. Portanto, é hora de se virar J.

Levantamento de informações do backup e da nova instância

Até aqui você recebeu um arquivo de backup (meuBD.bak) e não sabe nada além da infraestrutura da instância onde pretende restaurar este banco. Você criou uma instância SQL no seu computador pessoal, que está no mesmo domínio da instância de produção, mas que tem apenas os drives C:\ e K:\. Para organizar os arquivos, recomendo que se crie nos dois drives o diretório SQLServer com os subdiretórios DATA, INDEX e LOG. Esta é uma boa prática e ajuda a localizar arquivos no futuro.

Agora é necessário identificar a estrutura dos arquivos contidos no backup. Para isso, vamos usar o comando RESTORE FILELISTONLY, que traz as informações necessárias. Veja, a seguir, a linha de comando e o seu resultado. Para simplificar, omiti algumas colunas e alterei as informações de tamanho para a escala de Gigabyte.

RESTORE FILELISTONLY FROM DISK =’c:\temp\meuBD.bak’

GO

LogicalName PhysicalName Type filegroupName Size (GB) MaxSize (GB) FileId
meuBD_Data C:\Data\meuBD_Data.mdf D PRIMARY 0,5 32,0 1
meuBD_Data2 F:\Data\meuBD_Data2.mdf D PRIMARY 0,5 32,0 3
meuBD_Data3 H:\Data\meuBD_Data3.ndf D SECONDARY 0,5 32,0 4
meuBD_Data4 F:\Data\meuBD_Data4.ndf D SECONDARY 0,5 32,0 5
meuBD_Index H:\Index\meuBD_Index.ndf D INDEX 0,5 32,0 6
meuBD_Log F:\Log\meuBD_Log.ldf L NULL 0,5 32,0 2
meuBD_Log2 F:\Log\meuBD_Log2.ldf L NULL 0,5 32,0 7

Restaurando backup: refazendo mapeamento

Agora, temos as informações necessárias para começarmos a restauração. Precisamos, então, redefinir os caminhos para coincidir com os drives existentes no novo servidor. A próxima listagem mostra o comando RESTORE com as devidas adaptações.

USE MASTER 
GO
RESTORE DATABASE [meuBD] 
	FROM  DISK = N'C:\temp\meuBD.bak' 
	WITH  FILE = 1,  
	MOVE N'meuBD_Data'  TO N'C:\SQLServer\Data\meuBD_Data.mdf',  
	MOVE N'meuBD_Data2' TO N'K:\SQLServer\Data\meuBD_Data2.mdf',  
	MOVE N'meuBD_Data3' TO N'K:\SQLServer\Data\meuBD_Data3.ndf',  
	MOVE N'meuBD_Data4' TO N'C:\SQLServer\Data\meuBD_Data4.ndf',  
	MOVE N'meuBD_Index' TO N'K:\SQLServer\Index\meuBD_Index.ndf',  
	MOVE N'meuBD_Log'   TO N'K:\SQLServer\Log\meuBD_Log.ldf',  
	MOVE N'meuBD_Log2'  TO N'C:\SQLServer\Log\meuBD_Log2.ldf',  
	NOUNLOAD,  REPLACE,  STATS = 10
GO

Verificando eventuais problemas com usuários órfãos

O SQL Server, desde suas primeiras versões, trata separadamente os conceitos de logins e usuários de cada banco. No caso de restaurar uma base para uma nova instância, é provável que ocorram erros de consistência, ou seja, o banco inclui usuários que podem não ter correspondência com nenhum login da instância.

Quando se restaura o backup em outra máquina do mesmo domínio, não teremos problema com os usuários associados a logins autenticados pelo WINDOWS. Mas aqueles vinculados a logins com autenticação SQL provavelmente causarão problemas.

Para verificar esta questão, executamos o procedimento SP_CHANGE_USERS_LOGIN usando o parâmetro REPORT. O comando e o resultado são exibidos a seguir.

USE meuBD
GO
EXEC SP_CHANGE_USERS_LOGIN 'Report'
GO
UserName UserSID
meuLoginSQL 0x504B7022588FED4BBBE7D1414BDE0BCC

Como era esperado, não houve nenhum problema com o usuário ServicosAdministrativos, porque este é vinculado a um login de Windows e estamos trabalhando dentro do mesmo domínio. Mas o usuário meuLoginSQL não encontrou na nova instância um login correspondente e permanecerá desabilitado até que o problema seja resolvido.

Para corrigir esta situação, basta executar novamente o procedimento, usando, desta vez, o parâmetro AUTO_FIX. Neste caso, é preciso informar mais três parâmetros: qual usuário será corrigido, qual login será associado a ele (use NULL se for o mesmo nome) e qual a senha desejada. A próxima listagem mostra este comando e o seu resultado.

USE meuBD
GO
EXEC SP_CHANGE_USERS_LOGIN 'Auto_fix', 'meuLoginSQL', NULL , 'SenhaTemporaria'
GO

Ao bloquear um conflito, a linha do usuário ‘meuLoginSQL’ será corrigida atualizando seu vínculo com um novo logon.

  • Número de usuários órfãos corrigidos através da atualização dos usuários: 0
  • Número de usuários órfãos corrigidos através da adição de novos logons e subsequente atualização dos usuários: 1

Alguns cuidados importantes

Tenha em mente que a mecânica do processo é simples, mas é necessário atenção.

Por exemplo, a escolha dos caminhos para restauração de cada datafile não é aleatória. Caso haja possibilidade de alocar estes arquivos em tipos de disco diferentes, como SATA, SSD ou FLASH, por exemplo, é recomendável alocar arquivos de log e de índices nos discos mais rápidos e os dados nos demais.

Outra questão importante: não basta designar a alocação de arquivos com base no espaço disponível nos discos e o tamanho atual dos arquivos. É recomendável basear seu planejamento no tamanho máximo de crescimento destes arquivos.

Muitas vezes, não damos a atenção devida ao processo de alocação e consideramos nossas premissas em função do tamanho de cada datafile individualmente. Mas na realidade, costumamos alocar vários datafiles por disco, o que aumenta muito a probabilidade de ocorrer erros que podem levar à parada da sua instância inteira. Planejar a alocação com base na soma dos tamanhos máximos de cada arquivo reduz este risco sensivelmente.

Observe também os nomes lógicos e físicos destes datafiles. Não é raro que o DBA responsável se engane e adicione na base original datafiles com mesmo nome físico gravados em discos diferentes. Isso não é um problema em si, mas pode causar problemas na restauração desta base numa nova instância.

É claro que este problema pode ser evitado restaurando estes datafiles de mesmo nome em caminhos diferentes. Eu considero que nesses casos, o DBA seja alertado para corrigir este problema assim que possível.

Esta correção não é simples, porque, apesar da correção ser muita rápida, a base fica em modo offline durante o processo. Mas este procedimento é uma garantia que não aconteçam mais esses erros, que em última análise podem comprometer até mesmo a migração de bases dentro do ambiente produtivo!

Com isso, terminamos.

Espero que essas dicas possam ajudá-lo.

Leituras sugeridas

  1. Understanding SQL Server Backups por Paul Randal.