Clique no banner para conhecer e adquirir o meu treinamento de Bancos de Dados no Azure

SQL Server – Como recuperar o código-fonte de objetos apagados (View, Stored Procedure, Function e Trigger)

Post Views 4,164 views
Reading time 7 minutes

Hey guys!
Tudo certo ?

Nesse artigo eu vou compartilhar com vocês uma solução que pode salvar vidas em casos de desespero quando alguém apaga um objeto do banco de dados (View, Stored Procedure, View ou Function), você não tem uma rotina de versionamento de objetos de banco e você precisa tentar recuperar o esse objeto rapidamente, sem ter que voltar backup (o que pode demorar um certo tempo, dependendo do tamanho da base).

Caso você queira evitar esse tipo de situação (que nem sempre é possível recuperar o código do objeto), sugiro implementar um controle de versão para objetos de banco de dados, disponibilizado no post How to create an Audit trigger to log object manipulation in SQL Server.

Caso você precise apenas saber se algum objeto específico foi apagado e quem apagou, você pode utilizar o Trace padrão do SQL Server (que vem habilitado por padrão) para obter essa informação. Saiba mais no post Monitoring DDL and DCL operations using SQL Server's fn_trace_gettable.

Quando você não tem nenhum controle de versão sobre seus objetos de banco de dados (View, Stored Procedure, Function e Trigger) e alguém acidentalmente apaga esse objeto, um grande problema pode ter sido criado para o DBA, especialmente quando esse objeto não existe ou não está atualizado em outros ambientes (DEV, QA, etc), pois será necessário subir uma base paralela com o último backup para recuperar o código-fonte desse objeto, o que pode demandar muito tempo e esforço.

O intuito desse artigo, é prover algumas alternativas que podem te ajudar a recuperar o fonte desses objetos, mesmo sem ter controle de versão (o que não é ideal) e sem precisar voltar backup.

Ambiente para os testes

Para os testes desse artigo, vou criar um novo database, setar o Recovery model como FULL e criar alguns objetos. Após isso, vou utilizar esses objetos e vamos tentar recuperá-los novamente.

Script utilizado na criação do banco:

Script utilizado na criação dos objetos:

Agora, vou apagar os objetos:

Como recuperar objetos apagados lendo o T-log

Uma das alternativas para se recuperar o código-fonte de objetos apagados, é lendo transações e dados da Transaction Log do SQL Server, numa dica que recebi do MVP Rafael Santos em um grupo do Whatsapp.

Caso você não saiba, a Transaction Log é um componente crítico do banco de dados que armazena todas as alterações realizadas em um banco de dados, tanto de estrutura quanto de dados. Quando o comando CHECKPOINT é executado (seja manualmente ou automaticamente), os dados da Transaction-Log são armazenados no arquivo de dados do banco e são removidos do log de transações. Para entender melhor como funciona o processo de CHECKPOINT, dê uma lida no excelente post O processo de CHECKPOINT, do Vitor Fava.

Dito isso, já ficou claro que a vida útil dos dados armazenados na Transaction Log é limitada e isso já deixa um pouco essa solução a desejar no quesito confiabilidades que será possível recuperar os dados desejados. Além disso, para que seja possível recuperar os dados do log de transações, o Recovery Model da base deve estar definido como FULL.

Dito isso, vamos tentar recuperar os objetos excluídos no início desse post. Para isso, vamos utilizar a função não-documentada sys.fn_dblog na Stored Procedure abaixo:

Exemplo de uso:

Como vocês puderem observar, conseguimos recuperar facilmente o código dos 3 objetos que excluímos. Isso só foi possível porque o recovery model estava definido como FULL e os dados ainda estavam armazenados na Transaction Log.

Em ambientes que possuem backups de log frequentes (imagino que seja a maioria), o tempo que você tem entre apagar os dados e tentar recuperá-los pode ser bem curto, uma vez que ao fazer o backup de log e/ou rodar um CHECKPOINT, esses dados não são mais acessíveis utilizando essa função.

Como recuperar objetos apagados lendo backups de log

Utilizando uma solução um pouco diferente da apresentada anteriormente, vou demonstrar como recuperar objetos apagados lendo backups de log, que é quando você faz um backup da transaction log e os dados que estavam disponíveis utilizando a função não-documentada sys.fn_dblog passam a não ser mais acessíveis, já que os dados da transaction log são armazenados nos arquivos de backup de log e o transaction log é truncado.

Para contornar essa situação, a Microsoft nos disponibiliza a função fn_dump_dblog, onde podemos ler arquivos de backup de log e extrair informações referentes às transações realizadas na instância.

Para isso, vou apagar os objetos criados, realizar um backup full da base, recriar os objetos, apagá-los novamente e realizar um backup de log:

Agora, vamos ler os dados do backup do log:

Result:

Como vocês puderam observar acima, como fiz o backup de log, a stpRecupera_Objetos_Excluidos (sys.fn_dblog) não retornou mais os comandos de criação dos objetos que desejamos recuperar.

Entretanto, utilizando a stpRecupera_Objetos_Excluidos_Backup (sys.fn_dump_dblog) e especificando o arquivo de backup de log que criamos, foi possível recuperar os comandos de criação dos objetos.

Utilizando o ApexSQL Log

Uma outra forma de se recuperar o código-fonte de objetos apagados e até mesmo visualizar os dados modificados/apagados em tabelas, é utilizando a ferramenta paga ApexSQL Log. Essa ferramenta tem como diferencial ser fácil de usar, intuitiva e permitir analisar os dados da Transaction Log e também consegue recuperar informações de backups de Transaction-Log.

Operações passíveis de recuperação:

Demonstração do ApexSQL Log:

Como recuperar consultas lendo o plan cache

Uma outra alternativa para tentar recuperar o código de objetos e consultas, é consultando o cache dos planos de execução. Quando uma consulta ou objeto é executado, o SQL Server analisa se irá armazenar essa execução em cache (para fins de performance) ou não.

Embora não seja muito aplicável para o cenário proposto nesse post (uma vez que ao alterar/excluir um objeto, geralmente a sua execução em cache é excluída também), a query abaixo pode ser útil para recuperar consultas realizadas anteriormente:

Result:

Vale ressaltar que a vida útil de uma execução em cache é bem limitada e curta, onde o cache pode ser limpo conforme as consultas vão sendo executadas ou mesmo se o serviço do SQL for reiniciado.

Como recuperar consultas utilizando o SQL Prompt

Conforme já havia demonstrado no post SQL Server – Escreva T-SQL como um Ninja utilizando o Redgate SQL Prompt, o SQL Prompt possui um recurso muito legal chamado “Tab History”, que armazena todas as consultas ou objetos que você já abriu desde que instalou o SQL Prompt.

Se você abriu/alterou recentemente esse objeto que foi apagado, tem grandes chances de você conseguir recuperá-lo utilizando o Tab History.

Esse recurso é realmente muito prático, útil e às vezes, pode salvar até vidas.. rs.. Na minha empresa anterior, meu Tab History chegou a ter quase 2 GB, com todas as queries/objetos que eu já havia aberto/visualizado o código nos últimos 4 anos (e já me poupou MUITO tempo).

Bom, é isso aí, pessoal!
Espero que tenham gostado desse post.

Um abraço e até a próxima.