none
Porque não usar Backup Log With Truncate_Only RRS feed

  • Pergunta

  • Acompanhei o PDF do Gustavo Aguiar e ele comenta para não utilizar Backup Log With Truncate_Only. Gostaria de demonstrar minha procedure de Backup que roda todo dia a cerca de 3 anos e auxílio de vocês analisando o que pode estar ruim na mesma. CREATE Procedure [dbo].[P_Backup_SQL](@p_Banco_Dados sysname = null) As set nocount on Declare @Nome_Arquivo_Backup varchar(256) Declare @Nome_Banco_Dados sysname Declare @Status_Banco_Dados varchar(10) Declare @Nome_Arquivo_Log varchar(256) Declare @Dia_Backup varchar(256) Declare @Caminho_Completo_Backup varchar(256) Declare @Cmd_Pasta_Dia varchar(256) Declare @Cmd_Pasta_Servidor varchar(256) Declare @Cmd_Pasta_Backup varchar(256) Declare @Cmd_Pasta_Banco_Dados varchar(256) --YYYYMMDD /*Set @Dia_Backup = (Select cast(year(getdate()) as varchar) + cast(month(getdate()) as varchar) + cast(day(getdate()) as varchar)) */ Set @Dia_Backup = (Select convert(varchar(20), getdate(),112)) Set @Cmd_Pasta_Servidor = 'mkdir D:\Backup\PRODUCAO' Set @Cmd_Pasta_Dia = 'mkdir D:\Backup\PRODUCAO\' + @Dia_Backup EXEC master.dbo.xp_cmdshell @Cmd_Pasta_Servidor, no_output EXEC master.dbo.xp_cmdshell @Cmd_Pasta_Dia, no_output Set @Caminho_Completo_Backup = 'D:\Backup\PRODUCAO\' + @Dia_Backup + '\' if @p_Banco_Dados is null begin -- faz backup de todos os bancos menos tempdb e model declare db_cursor cursor for Select name From master.dbo.sysdatabases Where name not in ('master','model','msdb','tempdb') open db_cursor fetch next from db_cursor into @Nome_Banco_Dados while @@fetch_status = 0 begin Set @Status_Banco_Dados = (select convert(varchar(10),databasepropertyex(@Nome_Banco_Dados,'Status'))) Set @Nome_Arquivo_Log = (select Name from sys.master_files where database_id = db_id(@Nome_Banco_Dados) and type = 1) -- não faz backup se o banco estiver em offline , restoring ou suspect If @Status_Banco_Dados = 'ONLINE' begin Set @Cmd_Pasta_Banco_Dados = 'mkdir ' + @Caminho_Completo_Backup + @Nome_Banco_Dados EXEC master.dbo.xp_cmdshell @Cmd_Pasta_Banco_Dados, no_output Set @Nome_Arquivo_Backup = @Caminho_Completo_Backup + @Nome_Banco_Dados + '\' + @Nome_Banco_Dados + '.bak' CHECKPOINT EXEC SP_SPACEUSED -- GERA UM BACKUP DO LOG, DIMINUINDO SEU TAMANHO LÓGICO BACKUP LOG @Nome_Banco_Dados WITH TRUNCATE_ONLY -- COMPACTA FISICAMENTE O ARQUIVO DE LOG DO BANCO ATUAL Execute('Use ' + @Nome_Banco_Dados + ';DBCC SHRINKFILE('''+@Nome_Arquivo_Log+''', 100)') -- COMPACTA FISICAMENTE O ARQUIVO DE DADOS DO BANCO ATUAL DBCC SHRINKDATABASE(@Nome_Banco_Dados, TRUNCATEONLY) -- COMPACTA FISICAMENTE O ARQUIVO DE DADOS DO BANCO ATUAL DBCC SHRINKDATABASE (@Nome_Banco_Dados, 3) EXEC SP_SPACEUSED Backup Database @Nome_Banco_Dados To Disk = @Nome_Arquivo_Backup with init end else print 'Não foi possível fazer backup do banco [' + @Nome_Banco_Dados + '] devido estar em ' + @Status_Banco_Dados fetch next from db_cursor into @Nome_Banco_Dados end close db_cursor deallocate db_cursor end else begin -- faz backup apenas do banco informado Set @Status_Banco_Dados = (select convert(varchar(10),databasepropertyex(@p_Banco_Dados,'Status'))) -- não faz backup se o banco estiver em offline , restoring ou suspect Set @Nome_Arquivo_Log = (select Name from sys.master_files where database_id = db_id(@p_Banco_Dados) and type = 1) if @Status_Banco_Dados = 'ONLINE' begin Set @Cmd_Pasta_Banco_Dados = 'mkdir ' + @Caminho_Completo_Backup + @p_Banco_Dados EXEC master.dbo.xp_cmdshell @Cmd_Pasta_Banco_Dados, no_output Set @Nome_Arquivo_Backup = @Caminho_Completo_Backup + @p_Banco_Dados + '\' + @p_Banco_Dados + '.bak' CHECKPOINT EXEC SP_SPACEUSED -- GERA UM BACKUP DO LOG, DIMINUINDO SEU TAMANHO LÓGICO BACKUP LOG @p_Banco_Dados WITH TRUNCATE_ONLY -- COMPACTA FISICAMENTE O ARQUIVO DE LOG DO BANCO ATUAL Execute('Use ' + @p_Banco_Dados + ';DBCC SHRINKFILE('''+@Nome_Arquivo_Log+''', 100)') -- COMPACTA FISICAMENTE O ARQUIVO DE DADOS DO BANCO ATUAL DBCC SHRINKDATABASE(@p_Banco_Dados, TRUNCATEONLY) -- COMPACTA FISICAMENTE O ARQUIVO DE DADOS DO BANCO ATUAL DBCC SHRINKDATABASE (@p_Banco_Dados, 3) EXEC SP_SPACEUSED Backup Database @p_Banco_Dados To Disk = @Nome_Arquivo_Backup with init end else print 'não foi possível fazer backup do banco [' + @p_Banco_Dados + '] devido estar em ' + @Status_Banco_Dados end
    • Editado Danilo Rogério quarta-feira, 15 de junho de 2011 12:46 Ajuste do código
    quarta-feira, 15 de junho de 2011 12:44

Respostas

  • Boa Tarde,

    Vamos então às dúvidas:

    1 - Suponhamos que eu precise voltar um backup no meio do dia. Para fazer isso eu tenho que de tempos em tempos fazer um backup diferencial durante o dia? Aí para restaurar eu preciso do Full e dos diferenciais?

    Se o seu FULL é feito somente à noite, você só volta à noite no momento do FULL. Se você precisa voltar em momentos diferentes do horário do FULL, você pode usar um diferencial. Então se você faz FULL digamos 0h e diferencial às 9h, 12h e 18h, você pode voltar em qualquer momento desde que seja exatamente 0h, 9h, 12h e 18h. Não será possível restaurar a base à 18h:30 ou às 19h por exemplo.

    2 - Trocando para Simple, eu poderia deixar a procedure de Backup da mesma maneira, tirando a parte do Backup Log? Eu teria que tirar a parte do Shrink também?

    Sim. Uma vez que a base esteja em Recovery Model Simple o TRUNCATE não faz sentido (nem funciona), pois, não haverá log para ser truncado que já não tenha sido truncado pelo funcionamento do Recovery Model Simple.

    O uso do SHRINK independente do Recovery Model e ao meu ver é tão ruim quanto o TRUNCATE. Evito ao máximo utilizar comandos de SHRINK por entender que eles só são necessário em situações de desespero e se há planejamento dificilmente haverá desespero. Idealmente falando, o SHRINK não deve ser executado nunca. Se temos de executá-lo em alguma momento é porque falhamos no planejamento ou porque não conhecemos exatamente como ele funciona. Maiores detalhes em:

    Here's a good reason not to run SHRINKDATABASE...
    http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/06/13/629059.aspx

    A SQL Server DBA myth a day: (9/30) data file shrink does not affect performance
    http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(930)-data-file-shrink-does-not-affect-performance.aspx

    Auto-shrink – turn it OFF!
    http://www.sqlskills.com/blogs/paul/post/Auto-shrink-e28093-turn-it-OFF!.aspx

    Se você faz shrink nos dados, prepare-se para observar fragmentação deles. Se você faz shrink no log, está perdendo tempo, pois, ele vai crescer de novo. Melhor já deixar no tamanho certo e evitar perder tempo, cpu e fragmentação desnecessária.

    [ ]s,por

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.wordpress.com


    Classifique as respostas. O seu feedback é imprescindível
    quinta-feira, 16 de junho de 2011 16:56

Todas as Respostas

  • Aos moderadores, não sei porque mas está marcado que possui código, mas meu tópico ficou desconfigurado
    quarta-feira, 15 de junho de 2011 13:21
  • Danilo, não sei se você tem essa informação, mas só para se ter uma idéia, Truncate_Only nem é mais permitido no SQL 2008:

    "O log de transações é truncado automaticamente quando o banco de dados está usando o modelo de recuperação simples. Se você precisar remover a cadeia de backup do log de um banco de dados, alterne para o modelo de recuperação simples." (BOL)


    Roberson Ferreira - Database Developer

    Se esta sugestão for útil, por favor, classifique-a como útil.
    Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    quarta-feira, 15 de junho de 2011 13:27
  • Roberson, grato pela informação. Só que provavelmente não mudaremos logo para o SQL 2008 e minha preocupação é de estar fazendo algo errado ou ter problemas futuros. Outra coisa, alguma explicação para o código do meu backup ter ficado tão estranho no tópico
    quarta-feira, 15 de junho de 2011 13:54
  • Danilo,

     

    Quanto ao Backup de log:

    Em recovery model simple, ele realiza a transação e faz o truncate automatico do log no final de cada transação.

    Em recovery full, o log ficara guardando todas as transações, e para voce diminuir este log, é necessario alguma solução de alta disponibilidade como um log shipping que faz o backup automatico, ou alguma estrategia de backup que utilize backup de log.

     

    Quanto a formatação do seu texto:

    Voce esta utilizando outro navegador alem do IE? de preferencia 8+....

    Alguns navegadores podem não trabalhar 100% em relação ao forum TechNet/MSDN.


    Oracle OCA11g, MCC 2011! Dicas e novidades: www.fabrizziocaputo.wordpress.com
    quarta-feira, 15 de junho de 2011 13:58
    Moderador
  • Danilo, não sei o porquê de o script ter ficado assim. Eu costumo jogar para o Word e depois pra cá. Mas dei uma arrumada no seu script e vou colocá-lo aqui para ajudar:

    CREATE Procedure [dbo].[P_Backup_SQL] (@p_Banco_Dados sysname = null) As

     

    Set nocount on

     

    Declare @Nome_Arquivo_Backup varchar(256)

    Declare @Nome_Banco_Dados sysname

    Declare @Status_Banco_Dados varchar(10)

    Declare @Nome_Arquivo_Log varchar(256)

    Declare @Dia_Backup varchar(256)

    Declare @Caminho_Completo_Backup varchar(256)

    Declare @Cmd_Pasta_Dia varchar(256)

    Declare @Cmd_Pasta_Servidor varchar(256)

    Declare @Cmd_Pasta_Backup varchar(256)

    Declare @Cmd_Pasta_Banco_Dados varchar(256)

     

    --YYYYMMDD

    /*Set @Dia_Backup = (Select cast(year(getdate()) as varchar) + cast(month(getdate()) as varchar) + cast(day(getdate()) as varchar)) */

    Set @Dia_Backup = (Select convert(varchar(20), getdate(),112))

    Set @Cmd_Pasta_Servidor = 'mkdir D:\Backup\PRODUCAO'

    Set @Cmd_Pasta_Dia = 'mkdir D:\Backup\PRODUCAO\' + @Dia_Backup

     

    EXEC master.dbo.xp_cmdshell @Cmd_Pasta_Servidor, no_output

    EXEC master.dbo.xp_cmdshell @Cmd_Pasta_Dia, no_output

     

    Set @Caminho_Completo_Backup = 'D:\Backup\PRODUCAO\' + @Dia_Backup + '\'

     

    if @p_Banco_Dados is null

      begin

        -- faz backup de todos os bancos menos tempdb e model

        declare db_cursor cursor for

        Select name From master.dbo.sysdatabases Where name not in ('master','model','msdb','tempdb')

     

        open db_cursor fetch next from db_cursor into @Nome_Banco_Dados

     

        while @@fetch_status = 0

        begin

          Set @Status_Banco_Dados = (select convert(varchar(10),databasepropertyex(@Nome_Banco_Dados,'Status')))

          Set @Nome_Arquivo_Log = (select Name from sys.master_files where database_id = db_id(@Nome_Banco_Dados) and type = 1)

     

          -- não faz backup se o banco estiver em offline , restoring ou suspect

          If @Status_Banco_Dados = 'ONLINE'

            begin

              Set @Cmd_Pasta_Banco_Dados = 'mkdir ' + @Caminho_Completo_Backup + @Nome_Banco_Dados

       

              EXEC master.dbo.xp_cmdshell @Cmd_Pasta_Banco_Dados, no_output

       

              Set @Nome_Arquivo_Backup = @Caminho_Completo_Backup + @Nome_Banco_Dados + '\' + @Nome_Banco_Dados + '.bak'

         

              CHECKPOINT

         

              EXEC SP_SPACEUSED

       

              -- GERA UM BACKUP DO LOG, DIMINUINDO SEU TAMANHO LÓGICO

              BACKUP LOG @Nome_Banco_Dados WITH TRUNCATE_ONLY

       

              -- COMPACTA FISICAMENTE O ARQUIVO DE LOG DO BANCO ATUAL

              Execute('Use ' + @Nome_Banco_Dados + ';DBCC SHRINKFILE('''+@Nome_Arquivo_Log+''', 100)')

       

              -- COMPACTA FISICAMENTE O ARQUIVO DE DADOS DO BANCO ATUAL

              DBCC SHRINKDATABASE(@Nome_Banco_Dados, TRUNCATEONLY)

       

              -- COMPACTA FISICAMENTE O ARQUIVO DE DADOS DO BANCO ATUAL

              DBCC SHRINKDATABASE (@Nome_Banco_Dados, 3)

       

              EXEC SP_SPACEUSED

       

              Backup Database @Nome_Banco_Dados To Disk = @Nome_Arquivo_Backup with init

            end

          else

            print 'Não foi possível fazer backup do banco [' + @Nome_Banco_Dados + '] devido estar em ' + @Status_Banco_Dados

       

          fetch next from db_cursor into @Nome_Banco_Dados

        end

     

        close db_cursor

        deallocate db_cursor

      end

    else

      begin

        -- faz backup apenas do banco informado

        Set @Status_Banco_Dados = (select convert(varchar(10),databasepropertyex(@p_Banco_Dados,'Status')))

       

        -- não faz backup se o banco estiver em offline , restoring ou suspect

        Set @Nome_Arquivo_Log = (select Name from sys.master_files where database_id = db_id(@p_Banco_Dados) and type = 1)

       

        if @Status_Banco_Dados = 'ONLINE'

          begin

            Set @Cmd_Pasta_Banco_Dados = 'mkdir ' + @Caminho_Completo_Backup + @p_Banco_Dados

           

            EXEC master.dbo.xp_cmdshell @Cmd_Pasta_Banco_Dados, no_output

           

            Set @Nome_Arquivo_Backup = @Caminho_Completo_Backup + @p_Banco_Dados + '\' + @p_Banco_Dados + '.bak'

           

            CHECKPOINT

           

            EXEC SP_SPACEUSED

           

            -- GERA UM BACKUP DO LOG, DIMINUINDO SEU TAMANHO LÓGICO

            BACKUP LOG @p_Banco_Dados WITH TRUNCATE_ONLY

           

            -- COMPACTA FISICAMENTE O ARQUIVO DE LOG DO BANCO ATUAL

            Execute('Use ' + @p_Banco_Dados + ';DBCC SHRINKFILE('''+@Nome_Arquivo_Log+''', 100)')

           

            -- COMPACTA FISICAMENTE O ARQUIVO DE DADOS DO BANCO ATUAL

            DBCC SHRINKDATABASE(@p_Banco_Dados, TRUNCATEONLY)

           

            -- COMPACTA FISICAMENTE O ARQUIVO DE DADOS DO BANCO ATUAL

            DBCC SHRINKDATABASE (@p_Banco_Dados, 3)

           

            EXEC SP_SPACEUSED

           

            Backup Database @p_Banco_Dados To Disk = @Nome_Arquivo_Backup with init

          end

        else

          print 'não foi possível fazer backup do banco [' + @p_Banco_Dados + '] devido estar em ' + @Status_Banco_Dados

      end

     

    Set nocount off


    Roberson Ferreira - Database Developer

    Se esta sugestão for útil, por favor, classifique-a como útil.
    Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    quarta-feira, 15 de junho de 2011 14:04
  • Outra coisa: não sei se o PDF que você viu do Gustavo possui o mesmo conteúdo que está na página dele. Mas dá uma olhada nas duas partes:

    "Piores Práticas – Utilizar o comando BACKUP LOG com a opção WITH TRUNCATE_ONLY – Parte I" e "Piores Práticas – Utilizar o comando BACKUP LOG com a opção WITH TRUNCATE_ONLY – Parte II".

    As duas partes estão em http://gustavomaiaaguiar.wordpress.com/category/piores-praticas/.

     


    Roberson Ferreira - Database Developer

    Se esta sugestão for útil, por favor, classifique-a como útil.
    Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    quarta-feira, 15 de junho de 2011 14:14
  • Roberson, olharei estes links. Grato pela resposta

     

    Analisando minha procedure existe algo que eu esteja fazendo que não seja viável?

    Outra coisa, já partindo para o artigos do gustavo que você me indicou. Meus backups são todos feitos a noite. Valeria a pena modificar meus recoverys models para simple?
    quarta-feira, 15 de junho de 2011 15:51
  • Danilo, acredito que não. O modelo de recuperação Full é o que garante maior possibilidade de você recuperar informações no caso de um desastre.

    Mas avalie se é necessário fazer um Backup Full toda noite. Isso vai depender muito do seu cenário. Pode ser que o backup Full possa ser feito somente uma vez por semana, e a cada noite você faria um backup diferencial, por exemplo.


    Roberson Ferreira - Database Developer

    Se esta sugestão for útil, por favor, classifique-a como útil.
    Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    quarta-feira, 15 de junho de 2011 17:32
  • Boa Noite,

    Se há um TRUNCATE do Log, então certamente está errado. Independente de qualquer motivo, truncar o log está errado sempre (tanto que no 2008 isso não é mais suportado).

    Se você faz backup de log regularmente, dificilmente o log irá encher
    Se o log encheu de forma não planejada, faça um backup de log
    Se o log encheu de forma não planejada e não dá tempo de fazer um backup de log, então crie outro arquivo de log para ter tempo de fazer o backup

    Eu concordo com a colocação do Roberto "O modelo de recuperação Full é o que garante maior possibilidade de você recuperar informações no caso de um desastre.". É isso mesmo, o Recovery Model Full irá te assegurar que você poderá recuperar sua base em qualquer momento específico do passado (até nos momentos em que não havia backup), mas isso tem um preço de administração (você terá que implementar backups de log). Se você não quer essa possibilidade, ou seja, se o último full com ou sem o último diferencial são suficientes no caso de um desastre ou necessidade de restauração, então abra mão desse recurso e facilite sua administração escolhendo o Recovery Model Simple. Se você realmente deseja voltar o backup até um milissegundo específico e topa pagar o ônus de realizar backups de log, escolhe o Recovery Model Full. Como normalmente a produção é crítica, o FULL é o padrão. Quem é que não quer a possibilidade de voltar em qualquer milissegundo ? É ótimo para solicitações de auditoria, perda mínima de dados, investigar fraudes, etc. Claro que cada caso é uma caso, mas em linhas gerais eu recomendo.

    Truncar o log é escolher o ônus da administração e abrir mão do benefício de voltar a base em um momento específico. Se é assim qual o sentido nisso ? Por que ter um log que temos de truncar ? É por isso que o 2008 retirou essa possibilidade. Ou se usa o Recovery Model Simple (que trunca automaticamente) ou se usa o Recovery Model Full. O que dita a escolha são as possibilidades de restore, mas truncar o log nunca jamais.

    Colocar Recovery Model Simple ? Seria a solução correta a tomar se você não quer o log pra nada. É uma opção muito melhor do que truncar o log que alias, será dispensável no Recovery Model Simple

    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.wordpress.com


    Classifique as respostas. O seu feedback é imprescindível
    quinta-feira, 16 de junho de 2011 05:17
  • Gustavo, bom dia. Muito obrigado pelos esclarecimentos, e me tire só mais algumas dúvidas.

    Realmente o Backup Full que faço todo dia já me resolve em uma necessidade de restore. Então estou pensando em mudar para Simple o recovery model. Mas deixe-me entender mais uns pontos.

    1 - Suponhamos que eu precise voltar um backup no meio do dia. Para fazer isso eu tenho que de tempos em tempos fazer um backup diferencial durante o dia? Aí para restaurar eu preciso do Full e dos diferenciais?

    2 - Trocando para Simple, eu poderia deixar a procedure de Backup da mesma maneira, tirando a parte do Backup Log? Eu teria que tirar a parte do Shrink também?

     

    Mudando de assunto, tem um tópico meu relacionado com usuários no SQL server que eu refiz algumas pertuntas.

    O tópico é esse: http://social.msdn.microsoft.com/Forums/pt-BR/admingeralpt/thread/a4830e10-7b19-444a-bd52-122309495d72

    Se você puder me auxiliar e depois editar essa pergunta minha tirando o link do outro tópico.


    quinta-feira, 16 de junho de 2011 14:57
  • Boa Tarde,

    Vamos então às dúvidas:

    1 - Suponhamos que eu precise voltar um backup no meio do dia. Para fazer isso eu tenho que de tempos em tempos fazer um backup diferencial durante o dia? Aí para restaurar eu preciso do Full e dos diferenciais?

    Se o seu FULL é feito somente à noite, você só volta à noite no momento do FULL. Se você precisa voltar em momentos diferentes do horário do FULL, você pode usar um diferencial. Então se você faz FULL digamos 0h e diferencial às 9h, 12h e 18h, você pode voltar em qualquer momento desde que seja exatamente 0h, 9h, 12h e 18h. Não será possível restaurar a base à 18h:30 ou às 19h por exemplo.

    2 - Trocando para Simple, eu poderia deixar a procedure de Backup da mesma maneira, tirando a parte do Backup Log? Eu teria que tirar a parte do Shrink também?

    Sim. Uma vez que a base esteja em Recovery Model Simple o TRUNCATE não faz sentido (nem funciona), pois, não haverá log para ser truncado que já não tenha sido truncado pelo funcionamento do Recovery Model Simple.

    O uso do SHRINK independente do Recovery Model e ao meu ver é tão ruim quanto o TRUNCATE. Evito ao máximo utilizar comandos de SHRINK por entender que eles só são necessário em situações de desespero e se há planejamento dificilmente haverá desespero. Idealmente falando, o SHRINK não deve ser executado nunca. Se temos de executá-lo em alguma momento é porque falhamos no planejamento ou porque não conhecemos exatamente como ele funciona. Maiores detalhes em:

    Here's a good reason not to run SHRINKDATABASE...
    http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/06/13/629059.aspx

    A SQL Server DBA myth a day: (9/30) data file shrink does not affect performance
    http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(930)-data-file-shrink-does-not-affect-performance.aspx

    Auto-shrink – turn it OFF!
    http://www.sqlskills.com/blogs/paul/post/Auto-shrink-e28093-turn-it-OFF!.aspx

    Se você faz shrink nos dados, prepare-se para observar fragmentação deles. Se você faz shrink no log, está perdendo tempo, pois, ele vai crescer de novo. Melhor já deixar no tamanho certo e evitar perder tempo, cpu e fragmentação desnecessária.

    [ ]s,por

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.wordpress.com


    Classifique as respostas. O seu feedback é imprescindível
    quinta-feira, 16 de junho de 2011 16:56