no capítulo de abertura do Livro de Craig Mullin, Administração de banco de dados, ele diz “de muitas maneiras, negócios hoje são dados”. Na maioria das organizações, a pessoa responsável pela proteção de dados é o administrador do banco de dados… você.

isso mesmo; todo o negócio está em suas mãos capazes, em execução no servidor que não nunca falha, com todos aqueles usuários finais que já não cometer erros usando aplicativos, construída por aqueles desenvolvedores que escrevem irrepreensível código primeira vez, de cada vez, com a assistência de um novo co-op que tem ‘sa’ privilégios, graças ao seu chefe.

OK. Pára de chorar. Há coisas que você pode fazer para proteger os dados do SQL Server sob seus cuidados e um dos mais importantes é executar backups regulares de banco de dados.

Backups

Microsoft, no SQL Server Books Online, define backups como:

uma cópia de dados que é usada para restaurar e recuperar dados após uma falha do sistema

Backups SQL podem ser criados de várias maneiras e podem incorporar todos ou alguns dos dados, bem como alguma parte do log de transações. Embora este artigo seja focado na sintaxe de 2005, a maioria dos conceitos é aplicável a 2000. Este é um tema enorme. Na melhor das hipóteses, vou arranhar a superfície e dar-lhe informações suficientes para que você não comece a chorar novamente. Depois de ler isso, você deve ser capaz de configurar um conjunto razoável de backups para o seu sistema.

modelos de recuperação

para começar a trabalhar em backups, as necessidades da empresa definem um modelo de recuperação de banco de dados. Em essência, um modelo de recuperação define o que você vai fazer com os dados do log de transações.

existem três modelos de recuperação: Full, Simple E Bulk loged. Estes são muito fáceis de definir:

• simples – no modo de recuperação simples, o log de transações não é feito backup para que você só possa recuperar para o backup completo ou diferencial mais recente.
• modo de recuperação completo você faz backup do banco de dados e do log de transações para que você possa recuperar o banco de dados a qualquer momento.
• modo logado em massa em massa em massa, a maioria das transações é armazenada no log de transações, mas algumas operações em massa, como cargas em massa ou criação de índice, não são registradas.

os dois modos mais usados são simples e completos. Não presuma necessariamente que, é claro, você sempre precisa usar a recuperação completa para proteger seus dados. É uma decisão de negócios. O negócio vai lhe dizer se você precisa se recuperar a um ponto no tempo ou se você simplesmente precisa do último backup completo. Ele vai definir se seus dados são recuperáveis por outros meios, como a entrada manual, ou se você tem que proteger o máximo possível que ele vem através do fio. Você usa o simple recovery se puder perder os dados armazenados desde o último backup completo ou diferencial e/ou simplesmente não precisar de recuperação a um ponto no tempo. No modo simples, você deve restaurar todos os grupos de arquivos secundários de leitura/gravação ao restaurar o primário. Você usa simples principalmente em bancos de dados secundários que não são uma parte vital absoluta da empresa ou sistemas de relatórios, com acesso somente leitura para que não haja um log de transações para se preocupar de qualquer maneira. Você usa completo se cada bit dos dados é vital, você precisa se recuperar a um ponto no tempo ou, geralmente no caso de bancos de dados muito grandes (VLDB), você precisa restaurar arquivos individuais e grupos de arquivos independentemente de outros arquivos e grupos de arquivos.

com modelos de recuperação simples e completos, agora você pode executar um backup somente para cópia que permite copiar o banco de dados para um arquivo de backup, mas não afeta o log, cronogramas de backup diferenciais ou recuperação de impacto em um ponto no tempo. Vou tentar detalhar o máximo possível desses tópicos através do artigo, mas não os arquivos e grupos de arquivos.

trabalhando com recuperação simples

conversa suficiente. Vamos começar a executar backups. Vamos supor que estamos em recuperação simples em um banco de dados pequeno a médio porte. Vou usar o AdventureWorks para todos os scripts de amostra. Para definir a simples a recuperação de:

a Sua simples estratégia de backup é executado, em intervalos regulares, o seguinte SQL Server comando de cópia de segurança, que vai executar um backup completo do banco de dados:

o que há com toda a digitação que você pergunta? Não temos ferramentas GUI para lidar com o trabalho para nós? Sim, a maioria dos backups simples pode ser realizada usando o SQL Server Management Studio. No entanto, se você quiser aprender e entender o que o Management Studio está fazendo por você, ou se você quiser algum controle refinado sobre o que é feito backup, como e onde, então você terá que quebrar o teclado e guardar o mouse.

o comando acima precipitará um backup básico para o disco. A maioria dos DBAs que conheço faz backup para arquivar e depois raspar os arquivos em uma fita ou em alguma outra mídia. Isso ocorre porque os arquivos no disco são simples e rápidos de recuperar, enquanto a mídia às vezes pode ser um pouco dolorosa. Por exemplo, geralmente temos dois a três dias de backups em nossos sistemas de arquivos para recuperação imediata. Nós só vamos para os sistemas de fita se precisarmos executar restaurações para backups mais antigos.

o que esse comando fez? Ele fez uma cópia de todos os dados comprometidos no banco de dados. Ele também copiou entradas de log não confirmadas. Eles são usados durante a recuperação para confirmar ou reverter as alterações que ocorreram nos dados durante o processo de backup.

backups somente cópia

normalmente, fazer backup de um banco de dados afeta outros processos de backup e restauração. Por exemplo, depois de executar o comando anterior, quaisquer backups diferenciais (um backup que apenas copia dados alterados desde o último backup) usariam isso como ponto de partida para alterações de dados, não o backup que você executou na noite passada. Como observado anteriormente, o SQL 2005 apresenta um novo conceito para backups, COPY_ONLY cópias de segurança, o que nos permite manter-se de interromper o ciclo:

Já encontramos um dos mais granular momentos em que o Management Studio não iria ajudá-lo. Se você quiser um backup somente cópia, você tem que usar a linha de comando.

backups diferenciais

vamos supor por um momento, que ainda estamos em recuperação simples, mas estamos lidando com um banco de dados maior, digamos algo acima de 100 GB de tamanho. Backups completos podem realmente começar a desacelerar um pouco o processo. Em vez disso, após consulta com o negócio, decidimos fazer um backup completo semanal e backups diferenciais diários. Backups diferenciais apenas fazem backup das páginas de dados que foram alteradas desde o último backup completo. A seguir está o comando SQL backup para executar um backup diferencial:

Agora, se tivéssemos a restauração do banco de dados, gostaríamos de ir primeiro para o último backup completo, restaurar e, em seguida, restaurar os backups diferenciais em ordem (mais sobre isso mais tarde).

Há uma série de outras opções de backup que eu não vou detalhar aqui. Leia os livros on-line para ver detalhes sobre BLOCKSIZE, EXPIREDATE, RETAINDAYS, senha, nome, estatísticas e assim por diante.

você também pode executar uma instrução que irá verificar a integridade de um backup de banco de dados. Ele não verifica a integridade dos dados dentro de um backup, mas verifica se o backup está formatado corretamente e acessível.

recuperação Completa e backups de log

Temos sido principalmente trabalhando em um banco de dados que estava em modo de recuperação Simples (isto costumava ser chamado de Truncar Log no ponto de verificação). Neste modo, não fazemos backup dos logs de transação para recuperação posterior. Cada backup sob este mecanismo é um backup de banco de dados. Os backups de Log simplesmente não são possíveis.

no entanto, você só protegeu os dados a partir do último bom backup, completo ou diferencial. Vamos mudar nossas suposições. Agora estamos lidando com um grande aplicativo e banco de dados de missão crítica. Queremos ser capazes de recuperar este banco de dados até o último minuto. Este é um ponto muito importante. Em teoria, como as entradas de log estão sendo armazenadas e copiadas, estamos protegidos até o ponto de qualquer falha. No entanto, algumas falhas podem causar corrupção do log, tornando a recuperação até um ponto no tempo impossível. Portanto, temos que determinar qual será o tempo mínimo razoável entre os backups de log. Neste caso, podemos viver com não mais de 15 minutos de dados perdidos.

então, vamos começar colocando nosso banco de dados no modo de recuperação total:

Então, em uma base programada, neste caso, a cada 15 minutos, vamos executar o SQL comando backup do log de transações:

este script fará backup de transações confirmadas do log de transações. Possui marcadores no arquivo que mostram o tempo de início e parada. Ele truncará o log quando for concluído com êxito, limpando do log de Transações as transações confirmadas que foram gravadas no arquivo de backup. Se necessário, você pode usar a instrução WITH NO_TRUNCATE para capturar dados do log de transações, independentemente do Estado do banco de dados, supondo que esteja online e não em um status de emergência. Isso é apenas para emergências.

observe que não estamos usando a instrução INIT neste caso, mas você pode fazê-lo se escolher. Ao fazer backups de log, você tem opções:

1. execute todos os backups em um único arquivo, onde eles serão empilhados e tudo o que você precisa fazer, na restauração (coberta mais tarde), é percorrer eles.
2. nomeie os backups exclusivamente, provavelmente usando data e hora na string.

nesse último caso, safety diz, use INIT porque você está exercendo o controle máximo sobre o que é feito backup onde, e você poderá saber exatamente o que é um backup, quando foi retirado e de onde com base no nome. Este é mais um lugar onde os backups operacionais da linha de comando oferecem mais controle do que a GUI. Usamos ambas as abordagens em nossos sistemas por diferentes razões. Você pode decidir o que é melhor para seus requisitos de tecnologia e Negócios.

a maioria das opções disponíveis para o backup do banco de dados está incluída no backup de Log, incluindo COPY_ONLY. Isso permitiria capturar um conjunto de dados de transação sem afetar o log ou o próximo backup de log agendado. Isso seria útil para levar dados de produção para outro sistema para solução de problemas, etc.

se você tiver seu banco de dados definido como recuperação total, precisará executar backups de log. Às vezes, as pessoas esquecem e o log de transações cresce a ponto de encher a unidade de disco. Nesse caso, você pode executar:

Anexar NO_LOG o backup de log, e se não especificar um local para o log, faz com que a parte inativa do log para ser removido, e ele faz isso sem uma entrada de log em si, eliminando assim o total de unidade de disco. Isso não é absolutamente recomendado porque quebra a cadeia de log, a série de backups de log dos quais você recuperaria seu banco de dados até um ponto no tempo. A Microsoft recomenda executar um backup completo imediatamente após usar esta declaração. Além disso, eles estão alertando que esta declaração pode ser descontinuada em uma versão futura.

restaurar bancos de dados

tão importante quanto os backups do SQL Server são, e eles são vitais, eles são inúteis sem a capacidade de restaurar o banco de dados.

restaurar um backup completo do banco de dados

restaurar um backup completo do banco de dados é tão simples quanto criar:

é realmente tão simples-a menos que, como estamos fazendo backup de tudo para um arquivo como se fosse um dispositivo de backup. Nesse caso, você precisará especificar qual arquivo dentro do” dispositivo ” que está acessando. Se você não souber qual arquivo, precisará gerar uma lista:

isso lhe dará a mesma lista que mostrei acima do Management Studio. Então, agora, se quiséssemos restaurar o segundo arquivo no grupo, o backup COPY_ONLY, você emitiria o seguinte comando:

infelizmente, Se você estiver acompanhando, poderá descobrir que acabou de gerar esse erro:

o Que isso significa é que seu banco de dados está no modo de recuperação total, mas ainda não fez a “cauda do registo”, ou seja, as operações realizadas desde a última vez que você executou uma cópia de segurança. Você pode substituir esse requisito se alterar a sintaxe anterior para:

Essa é a primeira vez que nós já empilhados a COM cláusulas (COM ARQUIVO=2 e COM o SUBSTITUIR é representado como COM ARQUIVO=2, SUBSTITUIR), mas não será o último. Leia os livros online. A maioria das declarações com cláusula pode ser usada em combinação com as outras.

o que acontece se quisermos restaurar para um banco de dados diferente do original? Por exemplo, queremos fazer uma cópia do nosso banco de dados a partir de um backup separado. Talvez queiramos movê-lo para um servidor de suporte de produção, onde faremos algum trabalho nele, separado da cópia de produção do banco de dados. Se tomarmos a simples abordagem, bem, tente isso:

nesse caso, você deve ver toda uma série de erros relacionados a arquivos que não estão sendo substituídos. Você realmente pode criar novos bancos de dados a partir de backups, mas se você estiver fazendo isso em um servidor com o banco de dados existente, você precisará alterar a localização dos arquivos físicos usando os nomes lógicos. Para saber os nomes lógicos dos arquivos de um determinado banco de dados, execute isso antes de tentar mover os arquivos:

Este pode então ser utilizada para identificar a lógica apropriada nomes em ordem para criar este script:

Restaurando um backup diferencial

O último método é aplicar o backup diferencial. Isso requer duas etapas. Primeiro, restauraremos o banco de dados, mas com uma torção e, em seguida, aplicaremos o backup diferencial:

a Maioria deles é, provavelmente, auto-explicativa, com base no que já cobrimos. A única ruga é a inclusão da palavra-chave NORECOVERY. Muito simplesmente, durante uma restauração, as transações podem ter começado durante o processo de backup. No final de uma restauração, as transações concluídas são encaminhadas para o banco de dados e as transações incompletas são revertidas. Definir NORECOVERY mantém as transações abertas. Isso permite que o próximo conjunto de transações seja retirado do próximo backup em ordem.Estamos lidando principalmente com backups e restaurações simples neste artigo, mas uma restauração mais avançada em 2005 permite que grupos de arquivos secundários sejam restaurados enquanto o banco de dados está online. Seu grupo de arquivos principal deve estar online durante a operação. Isso será mais útil para sistemas de banco de dados muito grandes.

restaurar bancos de dados do SQL Server a um ponto no tempo

restaurar logs não é muito mais difícil do que a restauração diferencial do banco de dados que acabamos de concluir. Há apenas um pouco mais envolvido na restauração de um momento no tempo. Supondo que você está fazendo backup de logs para um único arquivo ou dispositivo:

caso contrário, você simplesmente vai e obtém os nomes dos arquivos de que precisa. Primeiro execute a restauração do banco de dados, tomando cuidado para deixá-lo em um estado não recuperado. Acompanhe isso com uma série de restaurações de log até um ponto no tempo.

Agora, o que temos é um banco de dados que é o exato, última transação confirmada às 14:30:29 no dia 23 de outubro. Lembre-se, durante as restaurações em várias etapas, como esta, você deve deixar o banco de dados em um status de recuperação. Isso significa anexar NORECOVERY a cada instrução até que você tenha concluído o processo de restauração. Se por algum motivo você adicionou NORECOVERY para todas as suas instruções, ou se você simplesmente parar no meio, e gostaria de trazer o banco de dados on-line, você pode usar esta instrução para concluir o processo de:

instantâneos de Banco de dados

SQL Server 2005 introduziu o conceito de um instantâneo, ou uma só de leitura, visão estática de um banco de dados. Os instantâneos são criados principalmente para fornecer uma versão somente leitura de um banco de dados para fins de relatório. No entanto, eles funcionam de maneira semelhante aos backups. A principal diferença é que todas as transações não confirmadas são revertidas. Não há opção para avançar, capturar logs, etc., que os backups fornecem, nem são muitos recursos do SQL Server usados. Em vez disso, a tecnologia de disco é usada para criar uma cópia dos dados. Por causa disso, eles são muito mais rápidos do que os backups para criar e restaurar.

nota:
para obter mais detalhes sobre o instantâneo do SQL 2005, consulte http://www.simple-talk.com/sql/database-administration/sql-server-2005-snapshots/.

um bom uso de instantâneos, além de relatórios, pode ser criar um antes da manutenção depois que você já removeu todos os usuários ativos (e suas transações) do sistema. Embora os instantâneos não suportem a volatilidade dos backups ao vivo, sua velocidade e facilidade de recuperação são uma ótima ferramenta para recuperação rápida de um lançamento malsucedido. Os instantâneos são armazenados no servidor, portanto, você deve se certificar de que possui armazenamento adequado.

A sintaxe é diferente, porque você não está fazendo backup de um banco de dados; você está criando uma nova:

agora será acessível para acesso somente leitura. Como estamos preocupados principalmente em usar isso como um mecanismo de backup, vamos incluir o método para reverter um banco de dados para um instantâneo de banco de dados.

primeiro, identifique o instantâneo que deseja usar. Se houver mais de um, em qualquer banco de dados que você vai voltar, você vai precisar para eliminar todos, exceto que você está usando:

em Seguida, você pode reverter o banco de dados executando uma instrução de RESTAURO (mistura de metáforas, não é bom):

é isso. No meu sistema, executando os instantâneos de banco de dados do Adventureworks tomou 136 ms. O backup completo tomou 5,670 ms. A restauração do instantâneo tomou 905ms e a restauração do banco de dados levou 13,382 ms. Incorporar isso em um processo de lançamento de produção pode resultar em benefícios significativos

novamente, vale a pena notar que existem algumas ressalvas ao uso do instantâneo. Você tem que ter espaço em disco suficiente para uma segunda cópia do banco de dados. Você precisa ter cuidado ao lidar com instantâneos, pois a maior parte da sintaxe é semelhante à usada pelos próprios bancos de dados. Por último, embora existam instantâneos anexados a um banco de dados, você não pode executar uma restauração de um backup de banco de dados desse banco de dados.

melhores práticas

a maneira pela qual você executa backups de banco de dados não deve ser uma decisão técnica. Deve ser ditado pelo negócio. Pequenos sistemas com baixas taxas de transação e/ou sistemas de relatórios que são carregados regularmente só precisarão de um backup completo do banco de dados. Sistemas de tamanho médio e grandes sistemas tornam-se dependentes do tipo de dados gerenciados para determinar quais tipos de backup são necessários.

para um sistema de tamanho médio, um backup diário com backups de log durante o dia provavelmente responderia à maioria dos Requisitos de dados em tempo hábil.

para um banco de dados grande, a melhor abordagem é misturar e combinar os backups para garantir a máxima recuperabilidade em tempo mínimo. Por exemplo, execute um backup completo semanal. Duas vezes por dia durante a semana, execute um backup diferencial. A cada 10 minutos durante o dia, execute um backup de log. Isso lhe dá um grande número de mecanismos de recuperação.

para Bancos de dados muito grandes, você precisará entrar em execução filegroup e backups de arquivos porque fazer um backup completo ou mesmo um backup diferencial do banco de dados completo pode não ser possível. Várias funções adicionais estão disponíveis para ajudar nesta área, mas não vou entrar nelas aqui.

você deve ter tempo para desenvolver alguns scripts para executar seus backups e restaurações. Uma convenção de nomenclatura para que você saiba qual banco de dados, de qual servidor, a partir de qual data, em que backup e formato específicos serão muito propícios à sua sanidade. Um local comum para backups, log, completo ou incremental, deve ser definido. Todos os responsáveis devem ser treinados em backup e recuperação e Solução de problemas da mesma forma. Existem muitas maneiras de fazer isso, mas você pode encontrar algumas sugestões no Pop backs up e restaurações Pop.

o teste real é executar seus mecanismos de backup e, em seguida, executar uma restauração. Em seguida, tente um tipo diferente de restauração e outro e outro. Certifique-se de que, não só você fez a devida diligência na definição de como fazer backup do sistema, mas que você fez a etapa extra de garantir que você pode recuperar esses backups. Se você não praticou isso e documentou a prática e depois testou o documento, na verdade, você não está pronto para um desastre.

resumo

Backups dentro de sua empresa deve ser como votar em Chicago, cedo e muitas vezes. Configurar backups básicos é bastante simples. Adicionar backups e diferenciais de log também é fácil. Explore as opções para ver como adicionar backups e restaurações de grupo de arquivos e arquivos para aumentar a velocidade de seus backups e restaurações, o que aumentará a disponibilidade do sistema e o tempo de atividade. Mantenha um padrão de Nomenclatura Comum. Tenha cuidado ao usar instantâneos, mas certamente empregá-los. Armazene seus arquivos em um local padrão entre os servidores. Pratique suas recuperações. Finalmente, para realmente fazer seus backups cantarem, baixe uma avaliação gratuita do backup SQL do Red Gate. Ele oferece compressão de alto desempenho e resiliência de rede para tornar o processo de gravação ou cópia de backups em redes escamosas tolerantes a falhas.