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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
CREATE DATABASE [Teste] ON PRIMARY ( NAME = N'Teste', FILENAME = N'C:\Dados\Teste.mdf', SIZE = 100MB, FILEGROWTH = 500MB ) LOG ON ( NAME = N'Teste_log', FILENAME = N'C:\Dados\Teste_log.ldf', SIZE = 100MB, FILEGROWTH = 500MB ); GO ALTER DATABASE [Teste] SET RECOVERY FULL GO |
Script utilizado na criação dos objetos:
|
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 26 27 28 29 30 31 32 33 34 35 36 37 38 |
USE [Teste] GO CREATE FUNCTION fncRecupera_Objeto_ID(@Name sysname) RETURNS INT AS BEGIN RETURN (SELECT TOP(1) [object_id] FROM sys.objects WHERE [name] = @Name) END GO SELECT dbo.fncRecupera_Objeto_ID('sysallocunits') SELECT dbo.fncRecupera_Objeto_ID('fncRecupera_Objeto_ID') GO CREATE PROCEDURE stpRecupera_Informacoes_Objeto(@Name sysname) AS BEGIN SELECT * FROM sys.objects WHERE [name] = @Name END GO EXEC stpRecupera_Informacoes_Objeto @Name = 'fncRecupera_Objeto_ID' GO CREATE VIEW vwObjetos AS SELECT * FROM sys.objects WHERE is_ms_shipped = 0 GO SELECT * FROM dbo.vwObjetos |
Agora, vou apagar os objetos:
|
1 2 3 4 5 6 7 8 |
DROP FUNCTION fncRecupera_Objeto_ID GO DROP PROCEDURE stpRecupera_Informacoes_Objeto GO DROP VIEW vwObjetos GO |
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:
|
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 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
USE [master] GO CREATE PROCEDURE [dbo].[stpRecupera_Objetos_Excluidos] @Database NVARCHAR(MAX), @Dt_Inicial DATETIME, @Dt_Final DATETIME AS BEGIN DECLARE @Compatibility_Level INT = (SELECT A.[compatibility_level] FROM master.sys.databases AS A WHERE A.name = @Database) IF (ISNULL(@Compatibility_Level, 0) < = 80) BEGIN RAISERROR('O modo de compatibilidade deve ser igual ou maior que 90 (SQL SERVER 2005)', 16, 1); RETURN END DECLARE @Query VARCHAR(MAX) = ' USE [' + @Database + ']; SELECT ''' + @Database + ''' AS [DatabaseName], A.AllocUnitName, A.[Page ID], A.[Slot ID], CONVERT(VARCHAR(MAX), SUBSTRING([RowLog Contents 0], 33, LEN([RowLog Contents 0]))) AS [Script] FROM sys.fn_dblog(NULL, NULL) A WHERE [AllocUnitName] = ''sys.sysobjvalues.clst'' AND A.Operation = ''LOP_DELETE_ROWS'' AND A.Context = ''LCX_MARK_AS_GHOST'' AND EXISTS ( SELECT NULL FROM sys.fn_dblog(NULL, NULL) X WHERE [Transaction Name] IN (''CREATE/ALTER PROCEDURE'', ''CREATE/ALTER FUNCTION'', ''CREATE TABLE'', ''DROPOBJ'') AND (CONVERT(DATETIME, [Begin Time]) BETWEEN ''' + CONVERT(VARCHAR(19), @Dt_Inicial, 121) + ''' AND ''' + CONVERT(VARCHAR(19), @Dt_Final, 121) + ''') AND A.[Transaction ID] = X.[Transaction ID] ) AND SUBSTRING([RowLog Contents 0], 33, LEN([RowLog Contents 0])) <> 0' EXEC(@Query) END |
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:
|
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 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 |
USE [Teste] GO IF (OBJECT_ID('dbo.fncRecupera_Objeto_ID') IS NOT NULL) DROP FUNCTION fncRecupera_Objeto_ID GO IF (OBJECT_ID('dbo.stpRecupera_Informacoes_Objeto') IS NOT NULL) DROP PROCEDURE stpRecupera_Informacoes_Objeto GO IF (OBJECT_ID('dbo.vwObjetos') IS NOT NULL) DROP VIEW vwObjetos GO ------------------------------------------------------------------------------- BACKUP DATABASE [Teste] TO DISK ='C:\Backups\SQL\sql2016\Teste_20180707_231600.bak' GO ------------------------------------------------------------------------------- CREATE FUNCTION fncRecupera_Objeto_ID(@Name sysname) RETURNS INT AS BEGIN RETURN (SELECT TOP(1) [object_id] FROM sys.objects WHERE [name] = @Name) END GO SELECT dbo.fncRecupera_Objeto_ID('sysallocunits') SELECT dbo.fncRecupera_Objeto_ID('fncRecupera_Objeto_ID') GO CREATE PROCEDURE stpRecupera_Informacoes_Objeto(@Name sysname) AS BEGIN SELECT * FROM sys.objects WHERE [name] = @Name END GO EXEC stpRecupera_Informacoes_Objeto @Name = 'fncRecupera_Objeto_ID' GO CREATE VIEW vwObjetos AS SELECT * FROM sys.objects WHERE is_ms_shipped = 0 GO SELECT * FROM dbo.vwObjetos ------------------------------------------------------------------------------- IF (OBJECT_ID('dbo.fncRecupera_Objeto_ID') IS NOT NULL) DROP FUNCTION fncRecupera_Objeto_ID GO IF (OBJECT_ID('dbo.stpRecupera_Informacoes_Objeto') IS NOT NULL) DROP PROCEDURE stpRecupera_Informacoes_Objeto GO IF (OBJECT_ID('dbo.vwObjetos') IS NOT NULL) DROP VIEW vwObjetos GO ------------------------------------------------------------------------------- CHECKPOINT GO BACKUP LOG [Teste] TO DISK ='C:\Backups\SQL\sql2016\Teste_20180707231600.trn' GO |
Agora, vamos ler os dados do backup do log:
|
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 26 |
USE [master] GO CREATE PROCEDURE [dbo].[stpRecupera_Objetos_Excluidos_Backup] @Ds_Arquivo NVARCHAR(MAX) AS BEGIN DECLARE @Query VARCHAR(MAX) = ' SELECT A.[Page ID], A.[Slot ID], CONVERT(VARCHAR(MAX), SUBSTRING([RowLog Contents 0], 33, LEN([RowLog Contents 0]))) AS [Script] FROM sys.fn_dump_dblog(NULL, NULL, N''DISK'', 1, ''' + @Ds_Arquivo + ''', DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT) A WHERE A.Operation = ''LOP_DELETE_ROWS'' AND A.Context = ''LCX_MARK_AS_GHOST'' AND SUBSTRING([RowLog Contents 0], 33, LEN([RowLog Contents 0])) <> 0' EXEC(@Query) END |
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:
https://www.youtube.com/watch?v=GC9XedBGI44
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
USE [Teste] GO SELECT [cp].[refcounts], [cp].[usecounts], [cp].[objtype], [st].[dbid], [st].[objectid], [st].[text], [qp].[query_plan] FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp; |
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.
Portuguese 
English
Desculpe eu não entendo nada de SQL (mas vou ter que aprender.rs) herdei uma função de uma pessoa que saiu da empresa, e acidentalmente deletei um arquivo dbo, como faço para recuperar e voltar ele para o lugar?
Dirceu, como vc consegue saber o tamanho do tab Tab History?
Fala Paulo, tudo bem ?
Esse Tab History que você está falando é do Redgate SQL Prompt, né ? Se for, o arquivo SavedTabs.db fica localizado no caminho %LocalAppData%\Red Gate\SQL Prompt 9\
Se atente à versão do seu SQL Prompt.
Qualquer dúvida, é só falar.
mais uma daquelas dicas que salvam a vida do cara que faz tudo a quente….
Opa, valeu Leonardo! Que bom que você gostou