SQL Server 2005: Backup e Recover.

Olá

Neste artigo vou explicar os procedimentos para realizar uma das operações mais importantes no dia a dia de um DBA.Backup e Recover é o procedimento de criar-se uma cópia da base de dados de qual pode ser restaurada em um momento de falha,onde é preciso recuperar um banco de dados preservando os dados com o mínimo ou nenhuma perda de dados.Vamos discutir alguns dos tipos de Backup existentes no SQL Server 2005 bem como as variadas estratégias de backup e restore.

Backup Full: O Backup Full (Completo) copia todos os dados do banco de dados,armazenando todos os objetos do banco de dados.O Backup Full é transacionalmente consistente de qual captura toda a estrutura do banco de dados e seus dados relacionados.Como outros tipos de backup o Backup Full é online,ou seja,este pode ser executado enquanto o banco de dados segue sua utilização normalmente.Com o Backup Full o SQL Server armazena a porção do Log de Transação durante o intervalo da realização do backup,com isso o Backup Full pode ser utilizado para retornar o estado do banco de dados no momento em que se acabou a tarefa de backup.O Backup Full é utilizado como base no momento de restore,com isso antes e restaurar qualquer outro tipo de backup,deve ser restaurado o backup full e após ir restaurando os tipos adicionais de backup para completar o recovery deixando o banco de dados operacional.

Backup Differential: O Backup Differential (Diferencial) captura todos os dados alterados desde o ultimo Backup Full do banco de dados.Após realizar um Backup Full do banco de dados,uma página chamada DCM – Differential Change Map é responsável por armazenar um bit para cada página de dados alterada desde o ultimo backup full.Quando o SQL Server vai realizar o Backup Differential este ler esta pagina e captura todas as páginas listadas dentro da DCM.O Backup Differential pode economizar espaço em disco e diminuir o tempo da janela de backup em relação ao backup full,mas não pode ser esquecido que este armazena todas as páginas de dados alteradas desde o ultimo backup full,não desde o ultimo diferencial com isso pode se gerar um grande volume de informações.

Transaction Log Backups: Backups do log de transação armazena todos os registros contidos dentro do log de transação.Registros de atualização da base dados de qual podem ser utilizados para restaurar o banco de dados em um determinado momento.O backup do log de transações é limitado aos Recovery Model Full e Bulk Logged,no Recovery Model Simple não é possível utilizar backups do log de transação.

Existem mais alguns tipos de backup no SQL Server como Copy-Only,Partial Backups,File e Filegroup Backup,Differential Partial Backup,neste artigo iremos conhecer apenas os citados acima.

Agora que já entendemos alguns tipos de backups do SQL Server vamos ver algumas estratégias de backup e possíveis opções de restore.Vamos criar nosso banco de dados de exemplo,criar algumas tabelas e povoa-las para criar nosso ambiente de teste.

–Criando o banco de dados de exemplo.
CREATE DATABASE Alimentos

–Mudando o contexto para o banco Alimentos.
USE Alimentos

–Criando a tabela Frutas.
CREATE TABLE dbo.Frutas
(
     CodFruta INT NOT NULL
    ,Nome VARCHAR(50) NOT NULL
    ,Quantidade SMALLINT NOT NULL
    ,Preco MONEY NOT NULL
)

–Inserindo alguns registros.
INSERT INTO dbo.Frutas VALUES(1,‘Laranja’,10,0.10)
INSERT INTO dbo.Frutas VALUES(2,‘Manga’,3,0.50)
INSERT INTO dbo.Frutas VALUES(3,‘Uva’,25,1)

–Visualizando os dados inseridos
SELECT CodFruta,Nome,Quantidade,Preco
FROM dbo.Frutas

CodFruta    Nome                                               Quantidade Preco
———– ————————————————– ———- ——-
1           Laranja                                            10         0,10
2           Manga                                              3          0,50
3           Uva                                                25         1,00

(3 row(s) affected)

Agora que criamos o nosso banco de dados e inserimos alguns registros vamos dar uma olhada em nossa primeira estratégia de backup de acordo com a imagem abaixo.

Backup Time

Em nossa imagem definimos nossa estratégia de backup como um Backup Full na Segunda Feira as 7 Horas da manha,um Backup Differential na Terça e Quarta Feira também as 7 Horas da manha,na Quinta Feira realizamos novamente o Backup Full e nos demais dias Sexta,Sábado e Domingo realizamos o backup differential.

Esta estratégia de backup estar apenas utilizando o Backup Full e o Backup Differential com certeza não é a maneira mais eficiente de proteger os dados.Digamos que a linha vermelha corresponda as atualizações a base de dados.

Agora imaginemos que ocorra um problema em nosso servidor exatamente as 15 Horas da Quarta Feira,com isso precisamos restaurar nossos backups para recuperar o banco de dados.Como eu disse acima que o Backup Full é a base para qualquer outro tipo de backup,este deve ser restaurado antes e logo após em nosso caso o ultimo differential (Quarta Feira as 7 Horas) porque ? Porque como expliquei na descrição do backup differential,este armazena todos as páginas de dados desde o ultimo backup full,com isso o backup differential de Terça Feira não é necessário já que o backup de Quarta Feira armazena os mesmos dados da terça feira mais as alterações de terça pra quarta até as 7 horas que é a hora do termino do backup.

Agora você deve estar se perguntando: Mais e os dados de 7Horas as 15 Horas perdemos ? Sim,a resposta é cruel,como não definimos em nossa estratégia de backup os backups do log de transação estamos vulneráveis a estes longos períodos de perda de dados,já que o intervalo de um backup differential para outro é cerca de 24 horas.

Agora vamos comprovar isso através dos exemplos nos scripts abaixo.

Para fazer o backup Full do nosso banco de dados utilizaremos o comando BACKUP DATABASE de qual especifica no nome do banco de dados e localização.Antes de efetuar o comando Backup Database iremos criar um Backup Device,ou seja,um dispositivo de backup,onde iremos definir o nome e a localização física do arquivo.A store procedure sp_addumpdevice pede como parametro o tipo de dispositivo como DISK para armazenar em disco ou TAPE para armazenar em uma fita,o nome do dispositivo criado que em nosso caso é DEV-BKP-FULL e localização física do arquivo a ser gerado que é C:BACKUPSQLSegundaFeira-Full.BAK,indicando que será armazendo o backup full da segunda feira conforme o exemplo em nossa imagem acima.

–Criando o Backup Device para o backup full.
EXEC sp_addumpdevice ‘DISK’,‘DEV-BKP-FULL’,‘C:BACKUPSQLSegundaFeira-Full.BAK’

Após criar o Device podemos agora fazer o Backup Full do nosso banco de dados,especificando o device criado acima,a opção INIT do comando Backup Database define que deve formatar o device,garantindo que apenas um backup será armazenado no device.

–Criando o Backup Full.
BACKUP DATABASE Alimentos TO [DEV-BKP-FULL] WITH INIT

Processed 160 pages for database ‘Alimentos’, file ‘Alimentos’ on file 1.
Processed 1 pages for database ‘Alimentos’, file ‘Alimentos_log’ on file 1.
BACKUP DATABASE successfully processed 161 pages in 0.192 seconds (6.869 MB/sec).

Em nosso Backup Full estar armazenado os três registros listados na query acima,vamos simular umas atualizações e inserções e vamos realizar os backups diferenciais para demonstrar o que é armazenado em cada tipo de backup,até o momento da falha.

–Inserindo alguns registros após o Backup Full.
INSERT INTO dbo.Frutas VALUES(4,‘Banana’,50,0.25)
UPDATE dbo.Frutas SET Quantidade = 17 WHERE CodFruta = 3

–Visualizando os dados alterados após o backup full
SELECT CodFruta,Nome,Quantidade,Preco
FROM dbo.Frutas

CodFruta    Nome                                               Quantidade Preco
———– ————————————————– ———- —–
1           Laranja                                            10         0,10
2           Manga                                              3          0,50
3           Uva                                                17         1,00
4           Banana                                             50         0,25

(4 row(s) affected)

Verifique que essas alterações como o novo registro na tabela Frutas foram feitos após o backup full,com isso o backup diferencial irá armazenar estes valores.Agora vamos fazer o backup diferencial da Terça Feira simulando que essas alterações foram feitas no intervalo de Segunda Feira após o Backup Full e o backup differencial da quarta feira.

–Criando o backup differential da Terça Feira.
BACKUP DATABASE Alimentos TO [DEV-BKP-DIFF] WITH DIFFERENTIAL,INIT

Processed 40 pages for database ‘Alimentos’, file ‘Alimentos’ on file 1.
Processed 1 pages for database ‘Alimentos’, file ‘Alimentos_log’ on file 1.
BACKUP DATABASE WITH DIFFERENTIAL successfully processed 41 pages in 0.132 seconds (2.540 MB/sec).

Agora iremos fazer mais algumas alterações simulando o intervalo de Terça Feira após o backup diferencial até o backup diferencial da Quarta Feira.

–Inserindo alguns registros após o backup diferencial da Terça Feira.
INSERT INTO dbo.Frutas VALUES(5,‘Maçã’,30,0.75)
INSERT INTO dbo.Frutas VALUES(6,‘Limão’,30,0.12)

–Criando o backup device para o backup differencial da Quarta Feira.
EXEC sp_addumpdevice ‘DISK’,‘DEV-BKP-DIFF-QuartaFeira’,‘C:BACKUPSQLQuartaFeira-Diff.BAK’

–Criando o backup Diferencial da Quarta Feira.
BACKUP DATABASE Alimentos TO [DEV-BKP-DIFF-QuartaFeira]

Processed 160 pages for database ‘Alimentos’, file ‘Alimentos’ on file 1.
Processed 1 pages for database ‘Alimentos’, file ‘Alimentos_log’ on file 1.
BACKUP DATABASE successfully processed 161 pages in 0.177 seconds (7.451 MB/sec).

–Visualizando os dados contidos no backup diferencial da Quarta Feira.
SELECT CodFruta,Nome,Quantidade,Preco
FROM dbo.Frutas

CodFruta    Nome                                               Quantidade Preco
———– ————————————————– ———- —–
1           Laranja                                            10         0,10
2           Manga                                              3          0,50
3           Uva                                                17         1,00
4           Banana                                             50         0,25
5           Maçã                                               30         0,75
6           Limão                                              30         0,12

(6 row(s) affected)

Lembrem que agora o Backup Diferencial da Quarta Feira contém todos os registros inseridos desde o Backup Full,com isso os registros inseridos após o backup full e antes e após o backup diferencial da Terça estão contidos no backup diferencial da Quarta Feira.Como mostrado na Query acima de qual lista todos os registros da tabela,com as devidas alterações após o Backup Full.

Em nossa simulação uma falha ocorreu na Quarta Feira as 15 Horas,vamos fazer algumas alterações simulando as atividades exercidas na base de dados e logo após iremos fazer o restore.

–Inserindo alguns valores após o Backup Diferencial da Quarta.
INSERT INTO dbo.Frutas VALUES(7,‘Pêssego’,15,1.78)
INSERT INTO dbo.Frutas VALUES(8,‘Pera’,23,2.43)

–Visualizando os dados contidos na tabela após o backup diferencial da Quarta.
SELECT CodFruta,Nome,Quantidade,Preco
FROM dbo.Frutas

CodFruta    Nome                                               Quantidade Preco
———– ————————————————– ———- ———————
1           Laranja                                            10         0,10
2           Manga                                              3          0,50
3           Uva                                                17         1,00
4           Banana                                             50         0,25
5           Maçã                                               30         0,75
6           Limão                                              30         0,12
7           Pêssego                                            15         1,78
8           Pera                                               23         2,43

(8 row(s) affected)

Como expliquei previamente em nossa estratégia estamos mostrando as vantagens e desvantagens de trabalhar com Backup Full e Backup Differential somente,esta situação de falha poderia ser solucionada utilizando Backups do Log de Transação,de quais serão exemplificados mais adiante.Em nossa simulação o log de transação não foi preservado,necessitando reconstruir o banco de dados através dos backups existentes.

Vamos ao processo de Recover,o primeiro backup a ser restaurado é o Backup Full de qual é a base para os demais tipos de backup no SQL Server.Neste exemplo vou utilizar o comando Restore Database com a opção STANDBY,pois durante o processo de Restore o banco de dados fica inacessível,com isso não conseguimos visualizar os dados até termine o restore.Utilizando a opção STANDBY posso definir um arquivo onde armazenará as transações imcompletas,assim me concede o acesso somente leitura a base de dados.

–Restaurando o Backup Full.
USE MASTER
RESTORE DATABASE Alimentos FROM [DEV-BKP-FULL] WITH REPLACE,NORECOVERY,STANDBY = ‘C:BACKUPtemp.standby’

–Visualizando os dados restaurados pelo Backup Full.
USE Alimentos
SELECT CodFruta,Nome,Quantidade,Preco
FROM dbo.Frutas

CodFruta    Nome                                               Quantidade Preco
———– ————————————————– ———- —–
1           Laranja                                            10         0,10
2           Manga                                              3          0,50
3           Uva                                                25         1,00

(3 row(s) affected)

Notem que é retaurados os tres primeiros registros da tabela Frutas,de qual inserimos e fizemos nosso primeiro Backup Full.Vamos restaurar nossos backups diferenciais e acompanhar passa a passo seus arquivos restaurados.

–Restaurando o Backup Diferencial da Terça.
USE MASTER
RESTORE DATABASE Alimentos FROM [DEV-BKP-DIFF] WITH STANDBY = ‘C:BACKUPtemp2.standby’

–Visualizando os dados restaurados pelo Backup Diferencial.
USE Alimentos
SELECT CodFruta,Nome,Quantidade,Preco
FROM dbo.Frutas

CodFruta    Nome                                               Quantidade Preco
———– ————————————————– ———- —–
1           Laranja                                            10         0,10
2           Manga                                              3          0,50
3           Uva                                                17         1,00
4           Banana                                             50         0,25

(4 row(s) affected)

Agora temos realmente os dados alterados de quais foram armazenados pelo Backup Diferencial,vamos restaurar o backup diferencial da Quarta Feira e visualizar os dados.

–Restaurando o Backup Diferencial da Quarta.
USE MASTER
RESTORE DATABASE Alimentos FROM [DEV-BKP-DIFF-QuartaFeira] WITH STANDBY = ‘C:BACKUPtemp3.standby’

–Visualizando os dados restaurados pelo Backup Diferencial da Quarta Feira.
USE Alimentos
SELECT CodFruta,Nome,Quantidade,Preco
FROM dbo.Frutas

CodFruta    Nome                                               Quantidade Preco
———– ————————————————– ———- —–
1           Laranja                                            10         0,10
2           Manga                                              3          0,50
3           Uva                                                17         1,00
4           Banana                                             50         0,25
5           Maçã                                               30         0,75
6           Limão                                              30         0,12

(6 row(s) affected)

Podemos verificar que todos os registros foram modificados desde o ultimo Backup Full,esta é funcionalidade do Backup Diferencial.Voce deve estar se perguntando: “Então quer dizer que se eu restaura-se o Backup Full + meu Ultimo Backup Differential iria dar o mesmo resultado ?” Sim.O backup differential armazena todas as informações alteradas desde o ultimo Backup Full,restauramos desta maneira para demostrar cada parte do processo com a intenção do amigo leitor entender a necessidade.Notem também que neste momento acabou nossos backups e ainda falta registros.Os Registros inseridos após o backup diferencial da quarta feira(Codigo 7 e 8) de qual simulavam as atividades na base foram perdidos,essa é a desvantagem de usar o Backup Full + o Backup Differential,sem utilizar o Backup do Log de transações.

Com o Backup do log de transações podemos armazenar as modificações da base de dados em um intervalo de tempo menor,reduzindo a perda de dados.Em nosso exemplo a falha simulada,nao teria preservado o log de transação.Mas se fosse o caso onde o log estaria preservado,poderíamos fazer um backup do tail do log armazenando a porção ativa de qual continha os registros 7 e 8 e restaura-lo junto com os backups full e diferencial,vou mostrar neste exemplo,como realizar o backup do tail do log em situações onde o data file esteja comprometido,de qual pode ser útil para evitar a perda de dados.

Antes de começar o processo de Restore,poderiamos emitir o comando Backup Log especificando a opção,NO_TRUNCATE essa opção diz ao SQL Server que ele não irá verificar no Data File os registros de quais constam no Log e irá trunca-los,desta forma estamos “backupeando” o Tail do Log ou seja as informações ainda ativas no log.

Segue abaixo o script para a criação do Device para o backup do log e a criação do Backup do log,relembrem que para resolver nosso problema esse procedimento deveria ser feito antes de começar o processo de Restore com o Backup Full e os demais backups.

–Criando o Backup Device para o Backup do Log.
EXEC sp_addumpdevice ‘DISK’,‘DEV-BKP-LOG’,‘C:BACKUPSQLBKP-LOG.TRN’

–Criando o backup do Tail do Log,colocando o banco de dados em estado de Recovering.
USE MASTER
BACKUP LOG Alimentos TO [DEV-BKP-LOG] WITH NORECOVERY,NO_TRUNCATE

Depois que criamos nosso Backup do Tail do Log,refazemos todo o processo de Restore mostrado acima e por ultimo restauramos o Backup do Log de qual irá recuperar nossos registros 7 e 8 como segue.

–Restaurando o Tail do Log,terminando o processo de restore deixando o banco de dados online.
USE MASTER
RESTORE LOG Alimentos FROM [DEV-BKP-LOG] WITH RECOVERY

–Visualizando os dados finais da tabela.
USE Alimentos
SELECT CodFruta,Nome,Quantidade,Preco
FROM dbo.Frutas

CodFruta    Nome                                               Quantidade Preco
———– ————————————————– ———- —–
1           Laranja                                            10         0,10
2           Manga                                              3          0,50
3           Uva                                                17         1,00
4           Banana                                             50         0,25
5           Maçã                                               30         0,75
6           Limão                                              30         0,12
7           Pêssego                                            15         1,78
8           Pera                                               23         2,43

(8 row(s) affected)

Podemos observar que os nossos dados foram recuperados com sucesso e nosso banco de dados está operacional.Neste artigo mostrei algumas vantagens e desvantagens do backup Full,differential e Transaction log.Bem como algumas estratégias de backup e restore e disaster recover.

Espero que seja útil para comunidade,até o próximo.

Advertisements

3 Responses to SQL Server 2005: Backup e Recover.

  1. Laerte says:

    Grande Felipe, parabéns..muito bem explicado :-)

  2. Rodrigo says:

    Muito bom!Até eu que não entendo nada de SQL SERVER conseguir entender.Parabéns!

  3. Eycmen says:

    Parabéns pela matéria! Muito bom mesmo!

    Bom, mas tenho uma dúvida que não quer calar. Podemos inserir informações de um backup antigo em um banco atual, por exemplo, deletaram uma turma de uma sala de aula, mas esta turma está no backup anterior, tem como inserir no banco atual sem restauração?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: