Fala Pessoal,
Nos últimos posts compartilhei dois scripts para restaurar vários arquivos de Backup de Log. Se você ainda não viu, confere lá, pois iremos utilizar esses scripts nesse post.
https://luizlima.net/script-restore-restaurando-varios-arquivos-de-backup-de-log-utilizando-o-msdb/
Hoje vou falar um pouco sobre as opções “STOPAT” e “STANDBY” que podem ser utilizadas ao executar o comando RESTORE.
Montando o Cenário:
Primeiro vamos montar um cenário que é comum no dia a dia para simular um famoso “UPDATE SEM WHERE” e como recuperar os dados utilizando os Backups de Log e as duas opções que citei anteriormente. Vou utilizar uma database chamada “BASE_PRODUCAO”. Também criei uma tabela e uma procedure que serão utilizadas depois.
OBS: Lembrando que a base de origem deve estar com o Recovery Model FULL, pois sem isso não seria possível fazer os Backups de Log e nem o Restore point in time posteriormente.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
USE BASE_PRODUCAO -- CRIA A TABELA DE TESTE IF(OBJECT_ID('Teste') IS NOT NULL) DROP TABLE Teste CREATE TABLE Teste ( ID INT IDENTITY(1,1), DATA DATETIME DEFAULT(GETDATE()), DESCRICAO VARCHAR (100) ) GO -- CRIA A PROCEDURE DE TESTE IF(OBJECT_ID('Teste_Select') IS NOT NULL) DROP PROCEDURE Teste_Select GO CREATE PROCEDURE Teste_Select AS BEGIN SELECT * FROM Teste ORDER BY ID END |
Feito isso, executo um Backup Full.
|
1 2 3 4 |
-- EXECUTA UM BACKUP FULL BACKUP DATABASE BASE_PRODUCAO TO DISK = 'C:\SQLServer\Backup\BASE_PRODUCAO_Full.bak' WITH COMPRESSION, INIT, STATS = 1 |
Agora vou inserir um novo registro na tabela “Teste” e executar um Backup de Log.
|
1 2 3 4 5 6 7 |
INSERT INTO Teste(DESCRICAO) VALUES('TESTE 1') BACKUP LOG BASE_PRODUCAO TO DISK = 'C:\SQLServer\Backup\BASE_PRODUCAO_Log_1.bak' WITH COMPRESSION, INIT, STATS = 1, NAME = 'C:\SQLServer\Backup\BASE_PRODUCAO_Log_1.bak' |
Aqui vou repetir o procedimento para inserir mais um registro e fazer mais um Backup de Log.
|
1 2 3 4 5 6 7 |
INSERT INTO Teste(DESCRICAO) VALUES('TESTE 2') BACKUP LOG BASE_PRODUCAO TO DISK = 'C:\SQLServer\Backup\BASE_PRODUCAO_Log_2.bak' WITH COMPRESSION, INIT, STATS = 1, NAME = 'C:\SQLServer\Backup\BASE_PRODUCAO_Log2.bak' |
Pronto. Nesse momento podemos verificar que existem dois registros na tabela:
|
1 |
SELECT * FROM Teste ORDER BY ID |
Contudo, nosso amigo desenvolvedor chamado Dirceu executou um UPDATE SEM WHERE e sem querer atualizou a coluna “DESCRICAO” em TODOS os registros da tabela (normal, desenvolvedor sempre fazendo besteira kkkk). Depois disso, executamos um último Backup de Log.
|
1 2 3 4 5 6 7 |
UPDATE Teste SET DESCRICAO = 'OI! MEU NOME É DIRCEU E EU EXECUTEI UM UPDATE SEM WHERE UUHUUU!!!' BACKUP LOG BASE_PRODUCAO TO DISK = 'C:\SQLServer\Backup\BASE_PRODUCAO_Log_3.bak' WITH COMPRESSION, INIT, STATS = 1, NAME = 'C:\SQLServer\Backup\BASE_PRODUCAO_Log_3.bak' |
Nesse momento a tabela possui o mesmo valor na coluna “DESCRICAO” para todos os registros e aí começam os problemas.
|
1 |
SELECT * FROM Teste ORDER BY ID |
“Luiz!!! Alguém alterou todos os registros da tabela e a produção está parada! Precisamos corrigir isso urgente! Me ajuda por favor!!!”
Após ser avisado pelo usuário, fui até o Setor de Desenvolvimento para conversar com o Dirceu e tentar descobrir a origem do problema. Chegando lá, expliquei a situação e ele informou que estava fazendo algumas alterações naquela mesma tabela, mas disse que estava fazendo tudo no ambiente de homologação. Pedi para ele mostrar o que tinha feito e nesse momento identificamos que na verdade ele tinha executado o UPDATE no ambiente de produção! Putz Dirceu!!! Perguntei o horário que ele tinha executado e informou que foi às 23:14 horas (isso mesmo, setor de TI também trabalha à noite).
OK, agora vamos começar a falar sobre as duas opções “STOPAT” e “STANDBY” e como podemos salvar o emprego do Dirceu e resolver o problema da produção.
STOPAT
Com o STOPAT nós conseguimos restaurar um backup até um horário específico que pode ser informado por parâmetro. Ou seja, podemos restaurar os dados até o momento anterior ao Dirceu ter executado o UPDATE SEM WHERE. Com isso, conseguiremos recuperar todos os dados.
“WOW! Que legal Luiz! Mas como isso é possível? Deve ser muito complicado né???”
Nada disso, é bem simples de ser resolvido. Basta incluir o parâmetro STOPAT no comando do RESTORE. Para demonstrar isso, vou utilizar o script do post “Script Restore – Restaurando vários arquivos de Backup de Log – Utilizando o MSDB” para gerar o script do RESTORE e em seguida incluir o parâmetro STOPAT manualmente. Segue abaixo o resultado:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
-- FULL -- 2019-03-27 23:11:30 RESTORE DATABASE TesteRestore_BASE_PRODUCAO FROM DISK = 'C:\SQLServer\Backup\BASE_PRODUCAO_Full.bak' WITH NORECOVERY , STATS = 1, MOVE 'BASE_PRODUCAO_2' TO 'C:\SQLServer\Data\BASE_PRODUCAO_2_TesteRestore_BASE_PRODUCAO.ndf', MOVE 'BASE_PRODUCAO_log' TO 'C:\SQLServer\Log\BASE_PRODUCAO_log_TesteRestore_BASE_PRODUCAO.ldf', MOVE 'BASE_PRODUCAO' TO 'C:\SQLServer\Data\BASE_PRODUCAO_TesteRestore_BASE_PRODUCAO.mdf' -- LOG RESTORE LOG TesteRestore_BASE_PRODUCAO from disk = 'C:\SQLServer\Backup\BASE_PRODUCAO_Log_1.bak' WITH FILE = 1, NORECOVERY -- 2019-03-27 23:12:14 RESTORE LOG TesteRestore_BASE_PRODUCAO from disk = 'C:\SQLServer\Backup\BASE_PRODUCAO_Log_2.bak' WITH FILE = 1, NORECOVERY -- 2019-03-27 23:13:05 RESTORE LOG TesteRestore_BASE_PRODUCAO from disk = 'C:\SQLServer\Backup\BASE_PRODUCAO_Log_3.bak' WITH FILE = 1, NORECOVERY -- 2019-03-27 23:14:26 -- Comando para deixar a base ONLINE RESTORE DATABASE TesteRestore_BASE_PRODUCAO WITH RECOVERY -- Comando para alterar o recovery model da base restaurada ALTER DATABASE TesteRestore_BASE_PRODUCAO SET RECOVERY SIMPLE -- Comando para validar se a base está OK DBCC CHECKDB('TesteRestore_BASE_PRODUCAO') -- Comando para excluir a base de teste -- DROP DATABASE TesteRestore_BASE_PRODUCAO |
Como o script também retorna a data e horário de cada arquivo de Backup de Log, basta verificar qual arquivo contém o horário desejado. No nosso exemplo iremos restaurar os dados até as 23:14 horas. Com isso, devemos retirar o parâmetro “NORECOVERY” e incluir o “STOPAT” no RESTORE do arquivo “BASE_PRODUCAO_Log_3.bak”, pois ele contém o intervalo de 23:13 até as 23:14 horas e irá deixar a database com o status “ONLINE” após a execução do RESTORE.
|
1 2 3 |
RESTORE LOG TesteRestore_BASE_PRODUCAO from disk = 'C:\SQLServer\Backup\BASE_PRODUCAO_Log_3.bak' WITH FILE = 1, STOPAT = '2019-03-27 23:14:00' -- 2019-03-27 23:14:26 |
Feito isso, agora podemos conferir os dados da tabela “Teste” na database que foi restaurada.
|
1 2 3 |
USE TesteRestore_BASE_PRODUCAO SELECT * FROM Teste ORDER BY ID |
Repare que agora a tabela possui os dados de antes do UPDATE que foi executado pelo Dirceu. Por fim, basta utilizar esses dados para atualizar a database de produção. Pronto! Problema resolvido e emprego do Dirceu garantido!
STANDBY
Com o STANDBY nós conseguimos restaurar um backup e deixar a database acessível para leitura. Para demonstrar isso, vamos utilizar novamente o script do post “Script Restore – Restaurando vários arquivos de Backup de Log – Utilizando o MSDB”, mas dessa vez iremos informar o parâmetro abaixo e um caminho para o arquivo que será gerado.
|
1 |
@Ds_Caminho_StandyBy = 'C:\SQLServer\Backup\BASE_PRODUCAO_StandBy' -- Informar o nome do Arquivo para o Standy By. Caso contrário, deve informar NULL. |
Com isso, o resultado do script será um pouco diferente. Segue abaixo um exemplo:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
-- FULL -- 2019-03-27 23:11:30 RESTORE DATABASE TesteRestore_BASE_PRODUCAO FROM DISK = 'C:\SQLServer\Backup\BASE_PRODUCAO_Full.bak' WITH NORECOVERY , STATS = 1, MOVE 'BASE_PRODUCAO_2' TO 'C:\SQLServer\Data\BASE_PRODUCAO_2_TesteRestore_BASE_PRODUCAO.ndf', MOVE 'BASE_PRODUCAO_log' TO 'C:\SQLServer\Log\BASE_PRODUCAO_log_TesteRestore_BASE_PRODUCAO.ldf', MOVE 'BASE_PRODUCAO' TO 'C:\SQLServer\Data\BASE_PRODUCAO_TesteRestore_BASE_PRODUCAO.mdf' -- LOG RESTORE LOG TesteRestore_BASE_PRODUCAO from disk = 'C:\SQLServer\Backup\BASE_PRODUCAO_Log_1.bak' WITH FILE = 1, STANDBY = N'C:\SQLServer\Backup\BASE_PRODUCAO_StandBy' -- 2019-03-27 23:12:14 RESTORE LOG TesteRestore_BASE_PRODUCAO from disk = 'C:\SQLServer\Backup\BASE_PRODUCAO_Log_2.bak' WITH FILE = 1, STANDBY = N'C:\SQLServer\Backup\BASE_PRODUCAO_StandBy' -- 2019-03-27 23:13:05 RESTORE LOG TesteRestore_BASE_PRODUCAO from disk = 'C:\SQLServer\Backup\BASE_PRODUCAO_Log_3.bak' WITH FILE = 1, STANDBY = N'C:\SQLServer\Backup\BASE_PRODUCAO_StandBy' -- 2019-03-27 23:14:26 -- Comando para deixar a base ONLINE RESTORE DATABASE TesteRestore_BASE_PRODUCAO WITH RECOVERY -- Comando para alterar o recovery model da base restaurada ALTER DATABASE TesteRestore_BASE_PRODUCAO SET RECOVERY SIMPLE -- Comando para validar se a base está OK DBCC CHECKDB('TesteRestore_BASE_PRODUCAO') -- Comando para excluir a base de teste -- DROP DATABASE TesteRestore_BASE_PRODUCAO |
Repare que agora o comando do RESTORE foi gerado com o parâmetro do STANDBY:
|
1 |
,STANDBY = N'C:\SQLServer\Backup\BASE_PRODUCAO_StandBy' |
Agora vamos executar o restore do backup FULL. Com isso, a database irá ficar com o status “Restoring…”.
Depois disso, vamos restaurar o primeiro backup de LOG (arquivo “BASE_PRODUCAO_Log_1.bak”). Repare que nesse momento o status da database ficou como “Standby / Read-Only”.
Ao executar um RESTORE com o parâmetro STANDBY, um arquivo será gerado no caminho que foi especificado. Esse “arquivo em espera” permite que um banco de dados seja aberto para acesso somente leitura entre restaurações do log de transações, ou seja, podemos fazer SELECT mesmo sem o banco de dados estar ONLINE ainda.
Após executar uma operação RESTORE WITH STANDBY, o “arquivo em espera” é excluído automaticamente pela próxima operação de RESTORE. Se esse “arquivo em espera” for excluído manualmente antes da próxima operação de RESTORE, o banco de dados inteiro deverá ser restaurado novamente. Enquanto o banco de dados estiver com o status “Standby / Read-Only” você deve tratar esse “arquivo em espera” com o mesmo cuidado como qualquer outro arquivo do banco de dados.
CUIDADO: Se o arquivo do STANDBY for excluído, será gerado o erro abaixo ao tentar deixar a database ONLINE.
Msg 3013, Level 16, State 1, Line 15
RESTORE DATABASE is terminating abnormally.
Msg 3441, Level 17, State 1, Line 15
During startup of warm standby database ‘TesteRestore_BASE_PRODUCAO’ (database ID 10), its standby file (‘C:\SQLServer\Backup\BASE_PRODUCAO_StandBy’) was inaccessible to the RESTORE statement. The operating system error was ‘2(The system cannot find the file specified.)’. Diagnose the operating system error, correct the problem, and retry startup.
Nesse momento, temos apenas um registro na tabela “Teste”.
|
1 2 3 |
USE TesteRestore_BASE_PRODUCAO SELECT * FROM Teste ORDER BY ID |
Agora vamos restaurar o segundo backup de LOG (arquivo “BASE_PRODUCAO_Log_2.bak”). Nesse momento, os dados da tabela “Teste” ainda não foram alterados pelo Dirceu.
Por fim, vamos executar o terceiro e último restore (arquivo “BASE_PRODUCAO_Log_3.bak”). Conforme era esperado, nesse momento os dados já foram alterados.
Como citamos anteriormente, a database com o status “Standby / Read-Only” não permite alterações. Segue abaixo um exemplo de alteração e o erro que será gerado:
|
1 2 3 4 |
USE TesteRestore_BASE_PRODUCAO UPDATE Teste SET DESCRICAO = 'OI! MEU NOME É DIRCEU E QUERO EXECUTAR MAIS UM UPDATE SEM WHERE COM A DATABASE STANDBY!!!' |
Msg 3906, Level 16, State 1, Line 5
Failed to update database “TesteRestore_BASE_PRODUCAO” because the database is read-only.
Aqui podemos perceber que também é possível executar uma procedure se ela não fizer alterações na database. Nesse caso, ela faz apenas um SELECT na tabela “Teste”.
|
1 2 3 |
USE TesteRestore_BASE_PRODUCAO EXEC Teste_Select |
Como podemos observar, o STANDBY pode ser útil quando não lembramos exatamente o horário que precisamos voltar o restore. Com isso, podemos ir validando os resultados a cada arquivo que for restaurado.
Referências:
Segue abaixo alguns links como referência:
- STOPAT:
- STANDBY:
https://www.mssqltips.com/sqlservertip/3049/different-ways-to-restore-a-sql-server-database/
Espero que tenha gostado e que isso também possa ser útil no seu dia a dia. Até o próximo post!
Me siga no LinkedIn e YouTube para ficar por dentro das novidades.
Abraço,
Luiz Vitor França Lima
Consultor SQL Server
ótimo post Luiz, com certeza vai ajudar muita gente que trabalha em ambiente crítico, recuperar as informações a um nível de horário específico é muito útil, ainda mais em exemplos como o “Update sem where” hehe.
Parabéns!
Show Tássio! Valeu!
Todo DBA vai precisar resolver um UPDATE SEM WHERE pelo menos uma vez na vida kkkk. Então é sempre bom estar preparado né =)
Abraço.
Luiz Vitor
Muito bom Luiz,
Curti mesmo saber isso, mas, neste caso o database está como modo de recuperação FULL, se caso o DB estivesse como simple, não teria como realizar o restore point in time né?
Abrass,
Fala Leonardo,
Fico feliz em saber que gostou do conteúdo! Exatamente, se a database estivesse com o recovery model SIMPLE, não seria possível nem fazer o Backup de Log e consequentemente o restore point in time. Vou incluir essa informação no post também pra ficar mais claro. Obrigado!
Abraço.
Luiz Vitor
Vai ter volta isso aí hein! rs
Que isso rapaz, Dirceu foi um nome escolhido aleatoriamente por um script super complexo aqui, foi apenas uma coincidência do destino, depois te envio o código também kkkk.
Abraço.
Luiz Vitor
Grande Luiz. Excelente conteúdo.. Outra maneira legal que pode ser usada para restaurar os dados point in time é através do parâmetro STOPATMARK, que é bem semelhante com o que você demonstrou, você pode consultar a posição do registro LSN na função FN_DBLOG se não tiver feito checkpoint ainda ou consultar no próprio arquivo do Backup de Log através da função FN_DB_DUMPLOG. E ainda tem uma outra forma interessante, não muito utilizada com o STOPATMARK que é criando uma marcação dentro de um bloco Begin Tran, aí você coloca um aliás pra essa marca e define na hora de restaurar pra parar nela. Valeu cara, sempre acompanho seus posts, muito show!!
Fala Gustavo, show hein! Esse daí acho que já ouvi falar, mas nunca cheguei a usar na prática. Obrigado por complementar com essas informações =)
Abraço,
Luiz Vitor