none
Estratégia de Backup no SQL Server 2000 RRS feed

  • Pergunta

  • Tenho que montar uma estratégia de BACKUP no SQL 2000.

    O meu banco é modificado constantemente e tenho que fazer um backup FULL diário e um backup do diferencial de 2 em 2 horas.

    Vejo que meu log do banco cresce muito rapidamente. Posso mudar o Recovery do banco para SIMPLE. Qual as consequencias dessa mudança?

    Qual a diferença de BACKUP DIFERENCIAL e BACKUP TRANSACTION LOG?

    Qual a melhor estratégia para implementar o backup? 
    1 - FULL(Append to Media)+DIF(Append to Media)?
    2 - FULL(Overwrite)+DIF(Append to Media)?
    3 - etc????

    Aguardo retorno amigos...

    Abração.

    terça-feira, 7 de julho de 2009 21:39

Respostas

Todas as Respostas

  • Ajventorin,


    A melhor estratégia é dificil de dizer ou afirmar isso vai depender da sua necessidade, aqui na empresa fazemos da seguinte forma:

    1 - A cada uma hora realizo um backup diferencial sobrepondo o último backup diff deste período das 01:00 ás 11:00.

    2- As 11:30hs e 23:30hs realizo backups full armazenados em locais e arquivos diferentes.

    3 - No período das 12:00 ás 23:00 realizo um backup diff sobrepondo o último backup diff para este intervalo armazenado em local e arquivo diferente ao outro backup diff.
    Pedro Antonio Galvão Junior - MVP - Windows Server System - SQL Server/Coordenador de Projetos/DBA
    terça-feira, 7 de julho de 2009 23:58
    Moderador
  • Ok Junior, entendo.

    O log do meu banco cresce muito rapidamente.
    Posso mudar o Recovery do banco para SIMPLE? Qual as consequencias dessa mudança?
    Qual a diferença de BACKUP DIFERENCIAL e BACKUP TRANSACTION LOG?

    quarta-feira, 8 de julho de 2009 11:32
  • ajventorin,

    Se você mudar este modelo de recuperação do seu banco de dados, o SQL Server vai descartar completamente o uso do log de transações.

    A diferença entre o backup diferencial e o backup de log, é que o backup diferencial realiza uma cópia dos dados que foram manipulados com base no último backup full, ou seja, se você realizou um backup full as 12:00 e o backup diferencial for realizado as 15:00 a diferença das 12:00 até as 15:00 seja copiada para o backup diferencial.

    O Backup de LOG, realiza uma cópia do log de transações existente no SQL Server para cada banco de dados, o backup de log vai armazenar as informações contidas no log de transações.

    Basicamente é isso.


    Pedro Antonio Galvão Junior - MVP - Windows Server System - SQL Server/Coordenador de Projetos/DBA
    quarta-feira, 8 de julho de 2009 11:44
    Moderador

  • Junior,

    Mas se eu descartar o Log, isso implicaria em problemas para meu Banco ou meu Backup?

    quarta-feira, 8 de julho de 2009 11:48
  • ajventorin,

    No backup não, quando você for realizar um backup você terá somente o backup do banco e o log não estará junto.

    O backup ocorre normalmente, mas o log não esta junto.


    Pedro Antonio Galvão Junior - MVP - Windows Server System - SQL Server/Coordenador de Projetos/DBA
    quarta-feira, 8 de julho de 2009 12:01
    Moderador
  • Ok Obrigado.
    Abração.
    quarta-feira, 8 de julho de 2009 12:13
  • Olá ajventorin,

    Como backups são procedimentos muito importantes, recomendo que você dê uma olhada no BOL sobre os tipos de backups e onde os mesmos se aplicam.

    Abraços
    Demétrio Silva
    quarta-feira, 8 de julho de 2009 12:24
  • Ola ajventorin

    Em complemento ao colegas, sugiro este webcast.

    Microsoft SQL Server 2000: Noções básicas sobre backup e restauração
    http://support.microsoft.com/default.aspx/kb/329833/pt-br


    Att.
    Marcelo Fernandes
    MCP, MCDBA, MCSA, MCTS. Se útil, classifique!!!
    quarta-feira, 8 de julho de 2009 12:38
    Moderador
  • Boa Marcelo,

    Mais prático.

    Abraços

    Demétrio Silva
    quarta-feira, 8 de julho de 2009 12:41
  • Pessoal,
    Achei algo importante sobre recovery model do SQL.
    Se selecionar o recovery model para SIMPLE não poderei fazer backup diferencial???
    Agora fiquei em dúvida... rsrsrs...

    -------------------------
     

    Antes de você começar a realizar cópias de seus bancos de dados e logs de transações, você precisa entender e configurar o modelo de recuperação que será usado para seu banco de dados. Conhecer qual modelo que você deve utilizar, afeta diretamente em que velocidade as suas cópias serão restauradas de maneira eficiente, após a ocorrência de algum incidente. O SQL Server precisa saber antecipadamente que tipos de backups você planeja realizar em um banco de dados e qual modelo de recuperação escolhido. O modelo de recuperação padrão que um banco de dados utiliza é o modelo de recuperação FULL. Você pode selecionar um dos três modelos de recuperação, de acordo com a sua estratégia de backup, que são:


     

    SIMPLE

     

    O log de transações (Transaction Log) é usado muito pouco neste modelo de recuperação. De fato, quase nada é registrado no log. Isto significa dizer que a escolha em usar este modelo para seus banco de dados só será possível recuperar o último backup. Qualquer modificação feita em seu banco de dados depois do último backup ter sido executado será perdida, porque estas alterações não são registradas no log de transações. Este modelo é uma boa escolha para bancos de dados de desenvolvimento onde a maior parte dos dados são dados de teste e que não têm de ser restaurados depois um incidente. Ele é também uma boa escolha para bancos de dados que não são modificados freqüentemente, por exemplo um banco de dados OLAP. Para implementar uma estratégia de backup que inclui somente cópias completas do seu banco de dados, você deve escolhe o modelo de recuperação SIMPLE.

     

    BULK-LOGGED

     

    Este modelo registra muito mais informação no log de transações do que o modelo SIMPLE. A única informação não registrada são operações de volume como: SELECT INTO, BCP, BULK INSERT,CREATE INDEX e operações com os tipos de dados texto e ntext. Isto significa que você pode recuperar a maior parte dos dados no caso de algum problema, porém só as operações citadas serão perdidas. Você pode configurar esta opção antes da execução de um operação de volume de inserção objetivando acelerar esta ação. Você deve realizar cópia do seu banco de dados imediatamente após a execução de operações de volume. Caso você escolha esta opção tudo que é inserido durante este período não estará no log de transações.

     

    Em alguns bancos de dados, esse modelo de recuperação não pode ser utilizado o tempo todo por causa das limitações do tamanho do log de transações e das questões de desempenho que surgem com operações de volume. Por isso, você precisa dimensionar a sua necessidade e aplicar o modelo de recuperação mais apropriado.

       

     

    FULL

     

    Este modelo de recuperação informa ao SQL Server que você quer realizar também o backup de log de transações. Para tornar isso possível, o SQL Server mantém todas as transações em um log de transações até que um backup do log de transações ocorra. Quando o backup do log de transações acontece, o SQL Server trunca o log de transações depois que o backup é gravado no dispositivo de backup. No modelo de recuperação SIMPLE, o log de transações é truncado depois da cada ponto de verificação, o que significa que as transações praticadas são excluídas do log de transações. Usando este modelo, você será capaz de recuperar seu banco de dados até o minuto após acontecer um determinado problema. Esta é uma boa opção de modo de recuperação para a maior parte de bancos de dados de produção, porque ele oferece o mais alto nível de proteção.

     

     Importante: Se nenhum backup de log de transações for feito quando você tiver utilizando o modelo de recuperação FULL, o seu arquivo de log do banco de dados aumentará até o seu tamanho máximo.

    quarta-feira, 8 de julho de 2009 12:53
  • Bom Dia,

    Gostaria de fazer algumas considerações:

    O RECOVERY MODEL SIMPLE aceita todos os tipos de backup (menos os de LOG). Isso inclui a possibilidade de fazer o backup diferencial. Segue exemplo abaixo:

    -- Cria o banco
    CREATE DATABASE BD
    -- Muda o RECOVERY MODEL
    ALTER DATABASE BD SET RECOVERY SIMPLE
    -- Faz um backup full do banco (não tem nada)
    BACKUP DATABASE BD TO DISK = 'C:\TEMP\BDFULL.BAK'
    -- Cria uma tabela no banco (não tem no BKP Full)
    CREATE TABLE BD.DBO.TB (COD INT)
    INSERT INTO BD.DBO.TB VALUES (1)
    INSERT INTO BD.DBO.TB VALUES (2)
    -- Faz um backup diferencial
    BACKUP DATABASE BD TO DISK = 'C:\TEMP\BDDIF.DIF' WITH DIFFERENTIAL
    -- Dropa o banco
    DROP DATABASE BD
    -- Recria o banco a partir do FULL
    RESTORE DATABASE BD FROM DISK = 'C:\Temp\BDFULL.BAK' WITH NORECOVERY
    -- Aplica o Diferencial
    RESTORE DATABASE BD FROM DISK = 'C:\Temp\BDDIF.DIF'
    -- Seleciona os dados
    SELECT COD FROM BD.DBO.TB


    Embora o RECOVERY MODEL SIMPLE também aceite backups de arquivo e FILE GROUP, uma vez que o RECOVERY MODEL é SIMPLE, tal estratégia pode não garantir o RESTORE em alguns cenários com esses tipos de backups

    O Backup diferencial pode ser aplicado (e restaurado) perfeitamente a bancos com RECOVERY MODEL SIMPLE, já que esse tipo de backup baseia-se não em LSN como os backups de Log, mas sim em páginas do Differential Change Map, na qual o SQL Server através de um mapa de páginas irá levar para o backup diferencial apenas as páginas de dados alteradas e não as transações que ocorreram desde o último backup full. Embora logicamente o efeito seja o mesmo, o fato de trabalhar com páginas faz com que o backup diferencial não dependenda em nada do log de transações. A contrapartida é que ao contrário do backup de log, o diferencial não permite restaurações do tipo Point In Time.

    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.spaces.live.com

    Certificados, Cross Database Ownership Chaining e Segurança
    http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!622.entry


    Classifique as respostas. O seu feedback é imprescindível
    quinta-feira, 9 de julho de 2009 13:55
  • Ok Gustavo.
    Respondeu minha dúvida.
    Já havia percebido isso e feito alguns testes.
    Posso então alterar o recovery dos meus bancos para SIMPLE. Assim o log não cresce mais como estava crescendo.
    Programa um BACKUP FULL e DIFFERENCIAL de 2 em 2 horas.
    Obrigado Gustavo.

    Agradeço também a todos os colegas do fórum que contribuiram.

    Abração.

    Alessandro.

    quinta-feira, 9 de julho de 2009 14:05
  • Ops,

    Valeu pela correção Gustavo.

    Abraços
    Demétrio Silva
    • Marcado como Resposta Richard Juhasz terça-feira, 3 de janeiro de 2012 21:02
    • Editado Demétrio Silva segunda-feira, 13 de outubro de 2014 22:18
    quinta-feira, 9 de julho de 2009 14:19
  • Olá ajventorin,

    Pois é. Dessa forma você evita o log crescer, mas também evita a possibilidade de restaurar o banco em momentos específicos que não o full e o diferencial. Avalie esse TradeOff. Peço também que classifique todas as respostas que o ajudaram com a sua dúvida.

    Pois é Demétrio. Eu também só uso o FULL e Bulk Logged nos meus ambientes (raras exceções).

    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.spaces.live.com

    Certificados, Cross Database Ownership Chaining e Segurança
    http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!622.entry


    Classifique as respostas. O seu feedback é imprescindível
    • Marcado como Resposta Richard Juhasz terça-feira, 3 de janeiro de 2012 21:02
    quinta-feira, 9 de julho de 2009 14:33