Script Restore – Restaurando vários arquivos de Backup de Log – Opções STOPAT e STANDBY

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-plano-de-manutencao/

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.

Feito isso, executo um Backup Full.

Agora vou inserir um novo registro na tabela “Teste” e executar um Backup de Log.

Aqui vou repetir o procedimento para inserir mais um registro e fazer mais um Backup de Log.

Pronto. Nesse momento podemos verificar que existem dois registros na tabela:

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.

Nesse momento a tabela possui o mesmo valor na coluna “DESCRICAO” para todos os registros e aí começam os problemas.

 “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:

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.

Feito isso, agora podemos conferir os dados da tabela “Teste” na database que foi restaurada.

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.

Com isso, o resultado do script será um pouco diferente. Segue abaixo um exemplo:

Repare que agora o comando do RESTORE foi gerado com o parâmetro do 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”.

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:

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”.

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:

https://docs.microsoft.com/pt-br/sql/relational-databases/backup-restore/restore-a-sql-server-database-to-a-point-in-time-full-recovery-model?view=sql-server-2017

  • STANDBY:

https://docs.microsoft.com/pt-br/sql/t-sql/statements/restore-statements-arguments-transact-sql?view=sql-server-2017

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

8 comentários em “Script Restore – Restaurando vários arquivos de Backup de Log – Opções STOPAT e STANDBY

  1. Tássio Carlini Responder

    ó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!

    • Luiz Lima Autor do postResponder

      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

  2. Leonardo Medeiros Responder

    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,

    • Luiz Lima Autor do postResponder

      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

    • Luiz Lima Autor do postResponder

      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

  3. Gustavo Larocca Responder

    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!!

    • Luiz Lima Autor do postResponder

      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

Deixe uma resposta para Luiz Lima Cancelar resposta