Banco de Dados II – Aula 15

BACKUP

  • Um membro do role de servidor sysadmin pode fazer backup de qualquer banco de dados da instancia e os membros do role de banco de dados db_owner podem fazer backup de seus banco de dados.
  • Voce também pode adicionar um usuário no role de banco de dados fixo db_backupoperator para permitir ao usuário fazer backup de um banco de dados, enquanto impede qualquer outro acesso ao banco de dados.

TIPOS DE BACKUP

  • O SQL Server apresenta quatro diferentes tipos de backup :
    • Completo;
    • Diferencial;
    • Log de Transação;
    • Grupo de arquivos (filegroups)

BACKUP COMPLETO

  • Um backup completo captura todas as páginas que contêm dados dentro de um banco de dados.
  • As páginas que não contém dados não são incluídas no backup.
  • Portanto, um backup nunca é maior (e, a maioria dos casos, é menor) do que o banco de dados para o qual é criado.
  • Um backup completo é a base para a recuperação de um banco de dados e deve existir antes que você possa usar um backup diferencial ou de log de transação;
  • Como é mais comum fazer backup do que restaurar um banco de dados, o mecanismo de backup é otimizado para o processo de backup.

BACKUP

  • Quando um backup é iniciado, o mecanismo de backup captura as páginas dos arquivos de dados o mais rapidamente possível, sem considerar a ordem das páginas, vários threads podem ser usados para gravar páginas em seu dispositivo de backup.
  • Um backup pode ser executado concomitantemente com outras operações do banco de dados.
  • Como pode ser feita alterações no banco de dados enquanto um backup está em execução, o SQL Server precisa acomodá-las ao passo que também garante que os backups sejam consistentes para propósitos de restauração.
  • O fator limitante para a velocidade de um backup é o desempenho do dispositivo onde o backup está sendo gravado.
  • As únicas operações que não são permitidas durante um backup completo são: adicionar ou remover um arquivo do banco de dados e reduzir um banco de dados;
  • Os dois únicos parâmetros exigidos para um backup são o nome do banco de dados e o dispositivo de backup.
  • Quando você especifica um dispositivo de disco para backup, um diretório e um nome de arquivo podem ser especificados;
  • Se não for especificado um diretório, o SQL Server fará o backup no disco e gravará o arquivo no diretório padrão configurado na instancia;
  • Embora a maioria dos backups seja gravada em um único arquivo em disco ou em um único dispositivo em fita, você pode especificar até 64 dispositivos de backup.
  • Quando você especifica mais de um dispositivo de backup, o SQL Server divide o striped backup (backup em faixas) por todos os dispositivos especificados.
  • Quando o SQL Server coloca um striped backup em vários dispositivos, é obrigatório que todos os dispositivos restaurem com sucesso.
  • O SQL Server não fornece failover ou tolerância à falhas dentro do conjunto de faixas. O conjunto de faixas é utilizado estritamente para propósitos de desempenho.

EXEMPLO DE STRIPED BACKUP

--striped backup

use master
go 

--Em Windows
BACKUP DATABASE AdventureWorks2012
  TO DISK = 'C:\temp\AWorks2012_1.bak',
  DISK = 'C:\emp\AWoks2012_2.bak'
GO

--Em Linux
BACKUP DATABASE [AdventureWorks2016CTP3]
  TO DISK = '/var/opt/mssql/AdvWorks2016_1.bak',
  DISK = '/var/opt/mssql/AdvWorks2016_2.bak'
GO

MIRROR TO

  • A cláusula MIRROR TO oferece um recurso interno para criar até quatro cópias de um backup em uma única operação.
  • Quando você inclui a cláusula MIRROR TO, o SQL Server recupera a página uma vez do banco de dados e grava uma cópia da página em cada espelho de backup.
  • Durante a restauração, os espelhos tem um pequeno número de requisitos:
    • Todos os dispositivos de backup devem ser o mesmo tipo de mídia;
    • Cada espelho deve ter o mesmo número de dispositivos de backup;
    • WITH FORMAT deve ser especificado no comando de backup;

DIMINUINDO TEMPOS DE BACKUP

  • A sobrecarga da compactação sempre vale a pena.
  • O tempo economizado por um backup compactado supera em muito a sobrecarga associada à operação de compactação.
  • O SQL Server tem uma opção de configuração chamada padrão de compactação de backup (backup compression default)
  • Você pode configurar de modo a sempre ter backups compactados, independente de especificar a compactação explicitamente, porém está disponível somente no SQL Server Enterprise.
  • Um único dispositivo de backup pode conter vários backups
  • As opções INIT/NOINIT de um comando BACKUP controlam se um arquivo de backup existente é sobrescrito ou recebe anexação.
  • Quando você especifica NOINIT e está fazendo backup em um arquivo já existente, o SQL Server anexa o novo backup no final do arquivo.
  • Se você especifica INIT e o arquivo já existe, o SQL Server sobrescreve o arquivo com o conteúdo do novo backup.
  • Quando CHECKSUM é especificado, o SQL Server confere a soma de verificação da página, se ela existir, antes de gravar a página do backup.
  • Além disso, é calculada uma soma de verificação para o backup inteiro, a qual pode ser usada para determinar se o backup foi corrompido.

BACKUP DE LOG DE TRANSAÇÃO

  • Cada alteração física feita em um banco de dados tem uma entrada inserida no log de transação.
  • Cada linha recebe um número exclusivo internamente, chamado LSN (Log Sequence Number).
  • O LSN é um numero inteiro que começa do 0 quando o banco de dados é criado e é incrementado até o infinito
  • Um LSN nunca é reutilizado para um banco de dados e é sempre incrementado.
  • Basicamente, um LSN fornece um número em sequencia para cada alteração feita em um banco de dados.
  • O conteúdo de um log de transação é dividido em 2 partes básicas – uma ativa e uma inativa.
  • A parte inativa contém todas as alterações efetivadas no banco de dados.
  • A parte ativa contém todas as transações ainda não efetivadas.Quando um backup de log de transação é executado, o SQL Server inicia no menor LSN do log de transação e começa a gravar cada registro de log de transação sucessivo no backup.
  • Assim que o SQL Server atinge o primeiro LSN que ainda não foi efetivado (isto é, a transação aberta mais antiga), o backup de log de transação termina.
  • Então, a parte do log de transação que teve o backup feito é removida, permitindo que o espaço seja reutilizado.
  • Com base no número de sequencia é possível restaurar um backup de log de transação após o outro para recuperar um banco de dados em qualquer ponto no tempo, simplesmente seguindo o encadeamento de transações, conforme identificado pelo LSN.
  • Como os backups de log de transação se destinam a ser restaurados um após o outro, as constraints sobre eles dependem de se ter a sequencia de LSNs inteira intacta.
  • Um backup de log de transação reúne todas as transações efetivadas no log desde o último backup de log de transação.
  • Conteúdo, como um backup de log de transações contém apenas as transações que foram executadas no banco de dados, você precisa de um ponto de partida para o encadeamento de logs de transação.
  • Antes de executar um backup de log de transação, você deve fazer um backup completo.
  • Após o primeiro backup você pode restaurar o banco de dados em qualquer ponto no tempo, desde o encadeamento de logs de transação não seja interrompido.
  • Backups completos adicionais podem ser criados para se ter um ponto de partida mais recente para uma operação de restauração.
  • Independente do número de backups completos criados, desde que você não tenha introduzido uma lacuna no encadeamento de LSNs, pode começar com qualquer backup completo e restaurar qualquer log de transação desse ponto em diante para recuperar um banco de dados.

SINTAXE DO BACKUP DE LOG DE TRANSAÇÃO

Sem título6

BACKUP DIFERENCIAIS

  • Um backup diferencial captura todas as extensões que mudaram desde o último backup completo.
  • O principal objetivo de um backup diferencial é reduzir o número de backups de log de transação que precisam ser restaurados.
  • Um backup diferencial precisa ser aplicado em um backup completo e não pode existir até que um backup completo seja criado.
  • O SQL Server controla cada extensão que foi alterada após um backup completo usando uma página especial no cabeçalho de um banco de dados, chamado DCM (Differential Change Map – mapa de alteração diferencial)
  • Um backup completo zera o conteúdo do DCM.
  • Quando são feitas alterações em extensões dentro do banco de dados, o SQL Server ajusta para 1 o bit correspondente à extensão.
  • Quando um backup diferencial é executado, o SQL Server lê o conteúdo do DCM para localizar todas as extensões que foram alteradas desde o último backup completo.
  • Um backup diferencial não é o mesmo que um backup de log de transação. Um backup diferencial contém todas as páginas alteradas desde o último backup completo.

OPÇÃO COPY ONLY

  • Uma das opções que podem ser especificadas para qualquer tipo de backup é a COPY_ONLY.
  • Cada backup executado em um banco de dados tem um efeito no ponto de partida de uma recuperação e em quais backups podem ser usados.
  • Os backups diferenciais contém todas as extensões que mudaram desde o ultimo backup completo; portanto, todo backup completo executado altera o ponto de partida em que um backup diferencial é baseado.
  • Quando um backup de log de transação é executado, as transações que tiveram o backup feito são removidas do log de transação.
  • Ocasionalmente você precisa gerar um backup para criar um banco de dados para um ambiente de desenvolvimento ou teste.
  • Voce quer ter o conjunto de dados mais recente, mas não quer afetar o conjunto de backup do ambiente de produção.
  • A opção COPY_ONLY permite gerar um backup que pode ser usado para criar o ambiente de desenvolvimento ou teste, mas não afeta o estado do banco de dados nem o conjunto de backups que está em produção.
  • Um backup completo com a opção COPY_ONLY não reinicia a página do mapa de alterações de diferencial e, portanto, não tem nenhum impacto nos backups diferenciais.
  • Um backup de log de transação com a opção COPY_ONLY não remove transações de log de transação.

BACKUP DE GRUPOS DE ARQUIVOS (FILE GROUPS)

  • Embora os backups completos capturem todas as páginas usadas no banco de dados inteiro, o backup completo de um banco de dados grande pode consumir uma quantidade significativa de espaço e tempo.
  • Se você precisa reduzir o tempo de backup, pode contar com o recurso de backups de grupo de arquivos, que permite ter como alvo parte de um banco de dados.
  • Backups de grupo de arquivos podem ser usados em conjunto com os backups diferenciais e de log de transação para recuperar uma parte do banco de dados no caso de uma falha.
  • Além disso, desde que você não precise restaurar o grupo de arquivos principal e esteja usando o SQL Server Enterprise, o banco de dados poderá permanecer online e acessível para os aplicativos durante a operação de restauração.
  • Somente a parte do banco de dados que está sendo restaurada fica offiline.

BACKUPS PARCIAIS

  • Os grupos de arquivo podem ser marcados como somente leitura.
  • Um grupo de arquivos somente leitura não pode ter alterações feitas nos objetos nele armazenados.
  • Como os objetivo dos backups é capturar as alterações para que você possa reconstruir um banco de dados no estado mais atual durante uma operação de recuperação, o backup de grupos de arquivo que não podem mudar consome espaço de forma desnecessária dentro do backup.
  • Para reduzir o tamanho de um backup para apenas os grupos de arquivo que podem mudar, você pode fazer um backup parcial. Os backups parciais são feitos especificando se a opção READ_WRITE_FILEGROUPS

Sem título7

  • Quando um backup parcial é executado, o SQL Server faz o backup do grupo de arquivos principal, de todos os grupos de arquivo de leitura/gravação e de todos os grupos de arquivos somente leitura especificados explicitamente.

CORRUPÇÃO DE PÁGINA

  • A partir do SQL Server 2005, houve a introdução da capacidade de colocar páginas corrompidas em quarentena, enquanto permitia ao banco de dados permanecer online.
  • Executando-se o comando abaixo, o SQL Server detecta páginas corrompidas e as coloca em quarentena.

Sem título8

  • Geralmente a corrupção de páginas são causadas por componentes de hardware que falham, especialmente controladoras e unidades de disco.
  • Antes de uma falha, as controladoras ou unidades de disco podem corromper páginas de dados realizando gravações incompletas;
  • Quando  o SQL Server grava uma página no disco, é calculada uma soma de verificação para a página.
  • Quando você ativa a verificação de página, sempre que uma página é lida do disco o SQL Server calcula uma nova soma de verificação e a compara com a soma de verificação armazenada na página.
  • Se as somas de verificação não corresponderem, o SQL Server retornará um erro e registrará a página em uma tabela no banco de dados msdb;
  • Embora páginas corrompidas possam ser colocadas em quarentena, o SQL Server tem um mecanismo de proteção em vigor para proteger seu banco de dados de uma corrupção generalizada.
  • Há um limite de 1.000 páginas corrompidas no total em um banco de dados.
  • Quando você atinge o limite de páginas corrompidas, o SQL Server tira o banco de dados do ar e coloca em um estado suspeito para protege-lo de danos maiores;

VALIDANDO BACKUP

  • Como os backups são sua apólice de seguro para um banco de dados, você precisa garantir que os backups criados sejam válidos e utilizáveis.
  • Para validar um backup, execute o seguinte comando:

Sem título9

  • Quando um backup é validado, o SQL Server realiza as seguintes verificações:
    • Calcula uma soma de verificação para o backup e compara com a soma de verificação armazenada no arquivo de backup;
    • Verifica se o cabeçalho do backup está gravado corretamente e é válido;
    • Percorre o encadeamento de páginas para certificar-se de que todas as páginas estejam no banco de dados e passam ser localizadas;

EXEMPLOS (MIRROR E COMPACTAÇÃO)

--backup com mirror e compactado

USE master
GO

--Em Windows
BACKUP DATABASE AdventureWorks2012
  TO DISK = 'c:\temp\AWorks_B1.bak'
  MIRROR TO DISK = 'c:\temp\AWorks_B2.bak'
  WITH COMPRESSION, INIT, FORMAT, CHECKSUM, STOP_ON_ERROR


--Em Linux
BACKUP DATABASE [AdventureWorks2016CTP3]
  TO DISK = '/var/opt/mssql/AdvWorks2016_compress.bak'
  MIRROR TO DISK = '/var/opt/mssql/AdvWorks2016_compress2.bak'
  WITH COMPRESSION, INIT, FORMAT, CHECKSUM, STOP_ON_ERROR

EXEMPLOS (BACKUP DE LOG DE TRANSAÇÃO)

--backups de transacao

USE AdventureWorks2012
GO

INSERT INTO HumanResources.Department (Name, GroupName)
VALUES ('TESTE1','Research and Development')
GO

BACKUP LOG AdventureWorks2012
TO DISK = 'c:\temp\AdventureWorks2012_1.trn'
WITH COMPRESSION, INIT, CHECKSUM, STOP_ON_ERROR
GO

--alteracao e backup de log novamente
INSERT INTO HumanResources.Department (Name, GroupName)
VALUES ('TESTE2','Research and Development')
GO

BACKUP LOG AdventureWorks2012
TO DISK = 'c:\temp\AdventureWorks2012_2.trn'
WITH COMPRESSION, INIT, CHECKSUM, STOP_ON_ERROR
GO

EXEMPLOS (BACKUP DIFERENCIAL)

--backup diferencial

USE AdventureWorks2012
GO

INSERT INTO HumanResources.Department (Name, GroupName)
VALUES ('TESTE3','Research and Development')
GO

BACKUP DATABASE AdventureWorks2012
TO DISK = 'c:\temp\AdventureWorks2012_1.dif'
MIRROR TO DISK = 'c:\temp\AdventureWorks2012_2.dif'
WITH DIFFERENTIAL, COMPRESSION, INIT, FORMAT, CHECKSUM, STOP_ON_ERROR
GO

EXEMPLOS (UTILIZANDO DISPOSITIVOS)

–criando um dispositivo de backup. mesmo que o caminho nao exista, ele vai
–criar,pois esta referenciando um dispositivo e nao um arquivo fisico

--teste de caminho inválido

exec sp_addumpdevice 'disk','meudispositivo','c:\a\backup.bak'
go


--caminho válido
exec sp_addumpdevice 'disk','meudispositivo','c:\temp\backup.bak'
go

-- excluindo um dispositivo
exec sp_dropdevice 'meudispositivo'
go

--até agora, foi apenas criado o dispositivo, mas nao o backup em si
backup database AdventureWorks2012 to meudispositivo
go

--para restaurar o banco de dados através do dispositivo
restore database AdventureWorks2012 from meudispositivo
go

--imaginando que seu banco corrompeu e voce nao consegue dar
-- drop no banco de dados
restore database AdventureWorks from meudispositivo with replace

--verificar sempre o modo de compatibilidade de restaurancao do bd
--FULL
--Simple - nao deixa fazer restore de um log
--Bulk log - somente guarda as transacoes do tipo bulk
-- o restore precisa ser compatível com o backup

--criando 2 dispositivos, um para backups full e outro para backup de log
exec sp_addumpdevice 'disk', 'meufull', 'c:\temp\MeuFull.bak'
go

exec sp_addumpdevice 'disk', 'meulog', 'c:\temp\MeuLog.bak'
go

--backup com with init, para nao deixar 2 arquivos no mesmo dispositivo
backup database AdventureWorks2012 to meufull with init

--backup do log
backup log AdventureWorks2012 to meulog
go

 
--simulando mais movimentacao no bd
use AdventureWorks
go

select * from Person.AddressType
go
insert into Person.AddressType (name, rowguid) 
values ('teste1',NewID())
go

 
--voltando ao banco master
use master
go

--backup do log, somente do log
backup log AdventureWorks2012 to meulog
go

 
--demonstrar em ambliente gráfico os arquivos dentro de um dispositivo

--restaurando o backup full e seus logs
--fez o restore somente do full, nao utilizando os logs

restore database AdventureWorks2012 from meufull
go

--fazendo backups novamente com init

backup database AdventureWorks2012 to meufull with init
go

backup log AdventureWorks2012 to meulog with init
go

--esperando pelos logs, em modo restoring
restore database AdventureWorks2012 from meufull with norecovery
Go

--restaurando o primeiro arquivo do log
restore log AdventureWorks2012 from meulog with file=1, recovery
go

-- exemplo de backup diferencial
exec sp_addumpdevice 'disk', 'meudiff', 'c:\temp\meudiff.bak'
go

backup database AdventureWorks2012 to meudiff with differential
go

Video 01

Anúncios

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s