copiando sql. SQL. Configurações de backup. Cenários de recuperação do MS SQL

Recomenda-se personalizar backup regular de banco de dados(em caso de falhas de hardware ou software), e o melhor de tudo com backups dos últimos dias, por exemplo sete (na última semana).

Para fazer isso, você pode usar o agendador de tarefas interno do SQL Server - "SQL Server Agent" (não incluído na versão gratuita) ou o "Agendador do Windows" padrão em combinação com o utilitário SQLCMD.EXE, que permite para consultar o SQL Server na linha de comando. Você deve criar pelo menos sete trabalhos no agendador (um para cada dia da semana), cada um dos quais substituirá (uma vez por semana) um dos sete arquivos que contêm o backup de banco de dados correspondente.

Além disso, é recomendável armazenar os arquivos de backup não apenas no disco rígido do computador onde o SQL Server está instalado, mas também duplicá-los em uma fita ou disco rígido de outro computador da rede. Para fazer isso, você pode usar um software especial que permite fazer backup de todo o disco ou usar o mesmo agendador para copiar arquivos para fita ou outro computador (segunda etapa).

Usando o "Windows Scheduler" (para a versão gratuita)

Para criar uma tarefa no "Agendador do Windows" você precisa:

Execute o programa Bloco de Notas (Iniciar->Todos os Programas->Acessórios->Bloco de Notas) e digite as duas linhas a seguir e salve-as como um arquivo de lote (*.BAT):

SQLCMD -S (local) -E -Q "BACKUP DATABASE AltaSVHDb TO DISK = "D:\BACKUP\ AltaSVHDb_monday.bak" COM INIT, NOFORMAT, SKIP, NOUNLOAD"
XCOPY D:\BACKUP\ AltaSVHDb_monday.bak \\BACKUP_SERVER\Folder\*.* /Y

Onde "(local)"- nome do servidor (se você estiver instalando uma instância nomeada do SQL Server, você deve especificar o nome completo: “COMP_NAME\SQLEXPRESS”), AltaSVHDb- nome do banco de dados, "D:\BACKUP\ AltaSVHDb_monday.bak"- o nome do arquivo para criar uma cópia de backup nele (varia de acordo com o dia da semana), "BACKUP_SERVER"- o nome do computador para o qual a cópia adicional será executada, Pasta- uma pasta neste computador (deve ser compartilhada).

Inicie o assistente de agendamento de tarefas (Painel de Controle->Tarefas Agendadas->Adicionar Tarefa) e clique no botão "Avançar":

Clique no botão "Procurar" e especifique o caminho para o arquivo de lote (*.BAT) criado na etapa a):

Especifique um nome para a tarefa, selecione a opção de execução "semanal" e clique no botão "Avançar":

Marque a caixa ao lado do dia da semana desejado e, no campo "Hora de início", especifique a hora em que o processo de backup deve iniciar (geralmente isso é feito à noite) e clique no botão "Avançar":

Digite o nome de usuário e a senha (duas vezes) da conta do SO sob a qual a tarefa será executada e clique no botão "Avançar":

Atenção! Para que a tarefa seja executada com sucesso, você deve conceder à conta especificada aqui (domínio ou computador local) permissões de gravação para a pasta mencionada "\\BACKUP_SERVER\Pasta", bem como configurar o acesso ao próprio SQL Server.

Pressione o botão "Concluir"

Observação. Para verificar a operacionalidade da tarefa criada, você precisa clicar com o botão direito do mouse na tarefa de interesse na lista de tarefas (Painel de Controle->Tarefas Agendadas) e selecionar o item “Executar” no menu de contexto, em seguida, certifique-se de que o arquivo de backup do banco de dados foi criado com sucesso usando os caminhos que foram especificados na etapa a).

Usando "SQL Server Agent" (não incluído na versão gratuita)

Para criar uma tarefa no SQL Server Agent, você precisa:

Execute o utilitário SQL Server Management Studio e conecte-se ao servidor com uma conta de administrador.

Na parte esquerda da janela, clique com o botão direito do mouse na seção "Objetos do servidor / Dispositivos de backup" e selecione o item "Criar dispositivo de backup" no menu de contexto:

No campo "Nome do dispositivo", digite um nome que será associado ao arquivo de backup do banco de dados, altere o caminho no campo "Arquivo" se necessário e clique em "OK":

Na parte esquerda da janela, clique com o botão direito do mouse na seção "SQL Server Agent/Jobs" e selecione o item "Create Job" no menu de contexto:

No campo "Nome", digite o nome da tarefa:

Na página Etapas, clique no botão Criar:

Na janela que aparece, digite um nome no campo "Step Name", verifique se "Transact-SQL (T-SQL) Script" está selecionado no campo "Type" e digite a linha no campo "Command":

BACKUP DATABASE AltaSVHDb PARA AltaSVHDb_monday COM INIT, NOFORMAT, SKIP, NOUNLOAD

Onde AltaSVHDb- nome do banco de dados, AltaSVHDb_monday- o nome do dispositivo de backup criado na etapa c) (varia de acordo com o dia da semana):

Na janela anterior, clique no botão "OK", como resultado, a seguinte linha deve aparecer na página "Passos":

Para que o arquivo de backup do banco de dados seja copiado imediatamente para outro computador na rede, repita as etapas f) - h), na janela "Criando uma etapa de tarefa", selecione "Sistema operacional (CmdExec)" no campo "Tipo" , e especifique na linha de campo "Command":

XCOPY D:\MSSQL\BACKUP\AltaSVHDb_monday.bak \\BACKUP_SERVER\Folder\*.* /Y

Onde "D:\MSSQL\BACKUP\AltaSVHDb_monday.bak"- o caminho especificado no passo c) (varia de acordo com o dia da semana), "BACKUP_SERVER"- o nome do computador para o qual a cópia será feita, Pasta- uma pasta neste computador (deve ser compartilhada):

Observação. Para que a cópia do arquivo seja bem-sucedida, você precisa executar o "SQL Server Agent" em uma conta de domínio do Windows que tenha permissões de gravação na pasta acima (consulte também "SQL2005_installation.doc" ou "SQL2008_installation.doc") e configurado acesso ao próprio SQL Server (consulte a seção "Configurando direitos de acesso ao banco de dados", inclua essa conta na função "sysadmin" na página "Funções de servidor" e não faça nada nas páginas "Mapeamento de usuário" e "Objetos protegidos").

Na página "Agendamentos", clique no botão "Criar":

Insira um nome no campo Nome, certifique-se de que o campo Tipo de agendamento esteja definido como Tarefa recorrente e o campo Execuções esteja definido como Semanal. Marque a caixa ao lado do dia da semana desejado (desmarque o restante) e no campo "Tarefa única", especifique a hora em que o processo de backup deve iniciar (geralmente isso é feito à noite):

Na janela anterior, clique no botão "OK", como resultado, a seguinte linha deve aparecer na página "Agendamentos":

Pressione o botão "OK".

Observação. Para verificar a operabilidade da tarefa criada, você precisa clicar com o botão direito do mouse na tarefa de interesse na seção "SQL Server Agent / Tasks" e selecionar o item "Executar tarefa em uma etapa" no menu de contexto, selecione a primeira etapa desta tarefa na janela que aparece e clique em "OK". Uma janela aparecerá mostrando o progresso da tarefa. Se a execução da tarefa terminar com um erro, uma descrição detalhada do erro pode ser vista chamando o item "Visualizar log" do mesmo menu de contexto.

Apesar de em nossos materiais anteriores já termos abordado a questão do backup de bancos de dados Microsoft SQL Server, a resposta do leitor mostrou a necessidade de criar um material completo com um estudo mais aprofundado da parte teórica. De fato, artigos feitos com ênfase em instruções práticas permitem configurar rapidamente um backup, mas não explicam os motivos da escolha de determinadas configurações. Vamos tentar corrigir essa lacuna.

Modelos de recuperação

Antes de configurar um backup, você deve selecionar um modelo de recuperação. Para a escolha ótima, é necessário avaliar os requisitos de recuperação e a criticidade da perda de dados, comparando-os com os custos indiretos para a implementação de um determinado modelo.

Como você sabe, o banco de dados MS SQL consiste em duas partes: o próprio banco de dados e o log de transações para ele. O banco de dados contém dados de usuários e serviços no momento atual, o log de transações inclui um histórico de todas as alterações do banco de dados por um determinado período, tendo um log de transações, podemos reverter o estado do banco de dados para qualquer momento arbitrário.

Dois modelos de recuperação são oferecidos para uso em ambientes de produção: simples e completo. Há também um modelo com registro incompleto, mas é recomendado apenas como complemento ao modelo completo para o período de operações em massa em grande escala, quando não há necessidade de restaurar a base em um determinado momento.

modelo simples prevê o backup apenas do banco de dados, respectivamente, podemos restaurar o estado do banco de dados apenas no momento do backup, todas as alterações no intervalo de tempo entre a criação do último backup e a falha serão perdidas. Ao mesmo tempo, um esquema simples tem uma pequena sobrecarga: você só precisa armazenar cópias do banco de dados, enquanto o log de transações é truncado automaticamente e não aumenta de tamanho. Além disso, o processo de recuperação é o mais simples e não leva muito tempo.

modelo completo permite restaurar o banco de dados em qualquer momento arbitrário, mas requer, além dos backups do banco de dados, o armazenamento de cópias do log de transações durante todo o período em que a restauração pode ser necessária. Durante o trabalho ativo com o banco de dados, o tamanho do log de transações e, consequentemente, o tamanho dos arquivos podem atingir tamanhos grandes. O processo de recuperação também é muito mais complexo e demorado.

Ao escolher um modelo de recuperação, você deve comparar os custos de recuperação com os custos de armazenamento de backups, e também deve levar em consideração a disponibilidade e qualificação do pessoal que realizará a recuperação. A recuperação com um modelo completo requer certas qualificações e conhecimentos do pessoal, enquanto com um esquema simples será suficiente seguir as instruções.

Para bancos de dados com uma pequena quantidade de informações adicionadas, pode ser mais benéfico usar um modelo simples com alta frequência de cópia, que permitirá recuperar rapidamente e continuar trabalhando inserindo manualmente os dados perdidos. O modelo completo deve ser usado em primeiro lugar onde a perda de dados é inaceitável e sua possível recuperação está associada a custos significativos.

Tipos de backup

Cópia completa do banco de dados- como o próprio nome indica, representa o conteúdo do banco de dados e parte do log de transações ativo no momento em que o backup foi formado (ou seja, informações sobre todas as transações atuais e incompletas). Permite restaurar completamente o banco de dados para o momento em que o backup foi criado.

Cópia do banco de dados delta- uma cópia completa tem uma desvantagem significativa, contém todas as informações do banco de dados. Se os backups precisam ser feitos com bastante frequência, surge imediatamente a questão do desperdício de espaço em disco, pois os mesmos dados ocuparão a maior parte do armazenamento. Para superar essa deficiência, você pode usar cópias diferenciais do banco de dados, que contêm apenas as informações que foram alteradas desde o último backup completo.

Observe que uma cópia diferencial são dados do momento da última completo cópia, ou seja, cada cópia diferencial subsequente contém os dados da anterior (mas eles podem ser alterados) e o tamanho da cópia aumentará constantemente. A restauração requer um backup completo e um diferencial, geralmente o último. O número de cópias diferenciais deve ser escolhido com base no aumento de seu tamanho, assim que o tamanho da cópia diferencial se compara com o tamanho da metade da inteira, faz sentido fazer uma nova cópia completa.

Backup de log de transações- aplica-se apenas ao modelo de recuperação completo e contém uma cópia do log de transações a partir do momento em que a cópia anterior foi criada.

É importante lembrar o seguinte ponto - as cópias do log de transações não estão relacionadas às cópias do banco de dados de forma alguma e não contêm informações de cópias anteriores, portanto, para restaurar o banco de dados, você precisa ter uma cadeia contínua de cópias do período durante o qual você deseja poder reverter o estado do banco de dados. Nesse caso, o momento da última cópia bem-sucedida deve estar dentro desse período.

Vejamos a figura acima, se a primeira cópia do arquivo de log for perdida, você poderá restaurar o estado do banco de dados apenas no momento da cópia completa, que será semelhante ao modelo de recuperação simples, você poderá restaurar o estado do banco de dados para qualquer ponto no tempo somente após a próxima cópia diferencial (ou completa), desde que a cadeia de cópias de log a partir do banco de dados anterior à cópia e além seja contínua (na figura - a partir do terceiro e além).

Log de transações

Para entender os processos de recuperação e a finalidade dos diferentes tipos de backups, você deve considerar com mais detalhes a estrutura e a operação do log de transações. Uma transação é a menor operação lógica possível que faz sentido e só pode ser concluída em sua totalidade. Essa abordagem garante a integridade e consistência dos dados em qualquer situação, já que o estado intermediário da operação não é permitido. O log de transações foi projetado para controlar quaisquer alterações no banco de dados.

Quando qualquer operação é realizada, uma entrada sobre o início da transação é adicionada ao log de transações, cada entrada recebe um número único (LSN) de uma sequência inseparável, com qualquer alteração de dados, uma entrada correspondente é feita no log, e após a conclusão da operação, uma marca sobre o fechamento (commit) da transação aparece no log.

Em cada inicialização, o sistema analisa o log de transações e reverte todas as transações não confirmadas, enquanto, ao mesmo tempo, as alterações que são confirmadas no log, mas não gravadas no disco, são revertidas. Isso possibilita o uso de cache e write-back sem medo da integridade dos dados, mesmo na ausência de sistemas de energia de backup.

A parte do log que contém transações ativas e é usada para recuperação de dados é chamada de parte ativa do log. Ele começa com um número chamado Número Mínimo de Recuperação (MinLSN).

No caso mais simples, MinLSN é o número de registro da primeira transação pendente. Se você observar a figura acima, ao abrir a transação azul, obteremos MinLSN igual a 321, depois de fixado no registro 324, o número MinLSN mudará para 323, que corresponderá ao número da transação verde, que não foi ainda foi cometido.

Na prática, as coisas são um pouco mais complicadas, por exemplo, os dados de uma transação azul fechada podem ainda não ser liberados para o disco, e mover o MinLSN para 323 impossibilitará a recuperação dessa operação. Para evitar tais situações, foi introduzido o conceito de ponto de controle. Um ponto de verificação é criado automaticamente quando ocorrem as seguintes condições:

• Ao executar explicitamente o CHECKPOINT. O ponto de verificação é acionado no banco de dados de conexão atual.
• Quando você executa uma operação minimamente registrada em um banco de dados, como quando você executa uma operação de cópia em massa em um banco de dados que está sujeito ao modelo de recuperação bulk-logged.
• Ao adicionar ou remover arquivos de banco de dados usando a instrução ALTER DATABASE.
• Quando a instância do SQL Server é interrompida usando a instrução SHUTDOWN ou quando o serviço do SQL Server (MSSQLSERVER) é interrompido. Em ambos os casos, será criado um checkpoint para cada banco de dados na instância do SQL Server.
• Se uma instância do SQL Server cria periodicamente pontos de verificação automáticos em cada banco de dados para reduzir o tempo de recuperação do banco de dados.
• Ao criar um backup de banco de dados.
• Quando você executa uma ação que exige o encerramento de um banco de dados. Os exemplos incluem definir AUTO_CLOSE como ON e fechar a última conexão do usuário com o banco de dados ou alterar uma configuração do banco de dados que requer uma reinicialização do banco de dados.

Dependendo de qual evento aconteceu primeiro, MinLSN será definido para o número de registro do ponto de verificação ou o início da transação pendente mais antiga.

Truncamento do log de transações

O log de transações, como qualquer log, requer limpeza periódica de registros obsoletos, caso contrário, ele crescerá e ocupará todo o espaço disponível. Considerando que durante o trabalho ativo com o banco de dados, o tamanho do log de transações pode exceder significativamente o tamanho do banco de dados, esse problema é relevante para muitos administradores.

Fisicamente, o arquivo de log de transações é um contêiner para logs virtuais que são preenchidos sequencialmente à medida que o log cresce. O log lógico que contém a entrada MinLSN é o início do log ativo, os logs lógicos que o precedem estão inativos e não são necessários para a recuperação automática do banco de dados.

Se o modelo de recuperação simples for selecionado, quando os logs lógicos atingirem um tamanho igual a 70% do arquivo físico, a parte inativa do log será limpa automaticamente, o chamado. truncamento. No entanto, isso não reduz o arquivo de log físico, apenas os logs lógicos são truncados, que podem ser reutilizados após essa operação.

Se o número de transações for alto e não houver logs lógicos inativos quando o tamanho do arquivo físico atingir 70%, o tamanho do arquivo físico será aumentado.

Assim, o arquivo de log de transações com um modelo de recuperação simples crescerá de acordo com a atividade de trabalhar com o banco de dados até que contenha de forma confiável toda a parte ativa do log. Depois disso, seu crescimento vai parar.

Com um modelo completo, a parte inativa do log não pode ser truncada até que seja feito o backup completo. O truncamento do log é executado com a condição de que o log de transações tenha sido submetido a backup, após o qual um ponto de verificação foi criado.

A falha em configurar corretamente o backup do log de transações em um modelo completo pode fazer com que o arquivo de log cresça incontrolavelmente, o que geralmente é um problema para administradores inexperientes. Dicas para truncar manualmente o log de transações também são encontradas com frequência. Com um modelo de recuperação completa, isso não deve ser feito categoricamente, pois ao fazer isso você violará a integridade da cadeia de cópias de log e poderá restaurar o banco de dados apenas no momento em que as cópias foram criadas, o que corresponderá a o modelo simples.

Nesse caso, é hora de lembrar o que falamos no início do artigo, se os custos de um modelo completo excederem os custos de restauração, um modelo simples deve ser preferido.

Modelo de recuperação simples

Agora, depois de obter o conhecimento mínimo necessário, podemos passar para uma consideração mais detalhada dos modelos de recuperação. Vamos começar com um simples. Digamos que no momento da falha tenhamos uma cópia completa e duas cópias diferenciais:

O backup era realizado uma vez por dia e a última cópia era criada na noite do dia 21 para o dia 22. A falha ocorre na noite do dia 22 antes da criação da próxima cópia. Nesse caso, precisaremos restaurar sequencialmente os backups diferenciais completos e mais recentes, e os dados do último dia útil serão perdidos. Se, por algum motivo, a cópia do dia 21 também estiver danificada, podemos restaurar a cópia anterior, perdendo outro dia de trabalho, enquanto, ao mesmo tempo, o dano à cópia no dia 20 não nos impede de restaurar com sucesso dados na noite do dia 21, com cópia apropriada.

Modelo de recuperação completo

Vamos considerar uma situação semelhante, mas usando o modelo de recuperação completa. Também fazemos backups diariamente, de acordo com o princípio completo + diferencial, e o log de transações é copiado várias vezes ao dia.

O processo de recuperação neste caso será mais complicado. Antes de tudo, você precisará criar um backup manual do fragmento de log final (mostrado em vermelho), ou seja, parte do log desde o momento em que a cópia foi criada pela última vez até antes da falha.

Se isso não for feito, será possível restaurar o banco de dados apenas para o estado no momento em que a última cópia do log de transações foi criada.

Ao mesmo tempo, danos ao arquivo de cópia de log do dia anterior não nos impedirão de restaurar o estado atual do banco de dados, mas nos limitarão ao momento em que a última cópia foi criada, ou seja, dias atuais.

Em seguida, restauramos sequencialmente as cópias completas e diferenciais e a cadeia de cópias do log criada após o último backup, o último a restaurar a cópia do fragmento final do log, o que nos dará a oportunidade de restaurar o banco de dados logo no momento do acidente ou arbitrário que o precedeu.

Se a última cópia diferencial estiver danificada, no caso de um modelo simples, isso levará à perda de mais um dia útil, o modelo completo permite restaurar a penúltima cópia, após o que você precisará restaurar toda a cadeia de cópias do log de transações desde o momento da penúltima cópia até a falha. A profundidade da recuperação depende apenas da profundidade da cadeia contínua de toras.

Por outro lado, se uma das cópias do log de transações estiver danificada, digamos, a penúltima, só poderemos restaurar os dados para a hora da última cópia de backup + período na cadeia intacta de cópias de log. Por exemplo, se os logs foram feitos às 12:00, 14:00 e 16:00 e o log criado às 14:00 está danificado, então tendo uma cópia diária podemos restaurar o banco de dados até o final da cadeia contínua, ou seja, até 12 horas.

Existem várias maneiras de copiar uma tabela em um banco de dados MS SQL Server. Eu ofereço várias opções para criar uma cópia das tabelas. Qual escolher depende da estrutura da tabela, da presença de índices, gatilhos etc. nela, bem como do desejo de fazer algo com as mãos.

1. Método manual de copiar a estrutura da tabela

No Microsoft SQL Management Studio, selecione um banco de dados, selecione uma tabela, clique com o botão direito do mouse e selecione "Script Table as" -> "CREATE TO" -> "New Query Editor Window". A janela de consulta abrirá o código para criar a tabela. Nele, você precisa especificar o nome do banco de dados no qual deseja fazer uma cópia da tabela e um novo nome se o banco de dados não for alterado. Como criar código para criar a estrutura de uma tabela existente é mostrado na figura abaixo.

Este método criará índices de tabela, mas não copiará acionadores. Eles precisam ser copiados da mesma maneira.

Para copiar dados para uma tabela já criada, você precisa usar a seguinte consulta SQL:

INSERT em ..tmp_tbl_Deps SELECT * FROM ..tbl_Deps

2. Copiando uma tabela SQL com uma consulta em uma linha

Faça uma cópia da estrutura da tabela e dos dados dentro do mesmo banco de dados:

SELECT * em tmp_tbl_Dep FROM tbl_Deps

Copie a estrutura da tabela e seus dados de um banco de dados para outro:

SELECT * em ..tmp_tbl_Deps FROM ..tbl_Deps

A desvantagem desta solução é que os índices não são copiados.

E também: backup SQL, backup 1C.

O servidor 1C contém dados no banco de dados, localizado no servidor SQL. Hoje estamos considerando o MS SQL 2005/2008.

Para garantir que os dados não sejam perdidos no caso de um disco do servidor queimado ou outras situações de força maior, é necessário fazer backups desde o início.

Claro, ninguém quer fazer canetas todos os dias Backup do banco de dados SQL 1C. Existem ferramentas automáticas para isso. Vamos conhecê-los.

Configurando o BackupSQL

Configurar o SQL de backup para um banco de dados 1C não é diferente de configurar um backup para qualquer outro banco de dados.

Para configurar, execute o MS SQL Management Studio. Este programa está no grupo de programas MS SQL.

Adicionando uma tarefa de backup do banco de dados SQL 1C

As tarefas de backup automático de bancos de dados SQL estão localizadas na ramificação Planos de gerenciamento/manutenção.

Para adicionar uma nova tarefa de backup, clique com o botão direito do mouse no grupo Planos de manutenção e selecione Novo plano de manutenção.

Digite o nome da tarefa. O nome importa apenas para você. Apenas no caso, é melhor usar caracteres em inglês.

Configurando uma tarefa de backup do banco de dados SQL 1C

O Editor de Trabalhos será aberto. Observe que os trabalhos podem realizar várias operações com o banco de dados e não apenas backups.

A lista de opções para operações é exibida no canto inferior esquerdo. Selecione Fazer backup da tarefa de banco de dados clicando duas vezes ou simplesmente arrastando para a direita.

Observe a seta. Você pode arrastar e soltar várias operações diferentes ou idênticas e vinculá-las com setas. Em seguida, várias tarefas serão executadas de uma só vez na sequência especificada por você.

Na janela de configurações, selecione os bancos de dados SQL 1C necessários (você pode ter vários ou um de cada vez).

Selecione o local para salvar o backup do banco de dados SQL 1C. Você deve selecionar um disco rígido fisicamente diferente. Organizacionalmente, você pode marcar a caixa de seleção "Criar subpastas".

Agora vamos configurar o agendamento de backup. O agendamento de backup padrão foi adicionado sozinho. Mas você pode adicionar vários agendamentos (por exemplo, um diário, um semanal etc.). Clique no botão de configurações de agendamento de backup.

A captura de tela mostra um exemplo de um banco de dados SQL de backup diário 1C às 3 da manhã.

Para tornar o agendamento de backup na lista agradável e compreensível, você pode alterá-lo.

Salvando uma tarefa de backup do banco de dados SQL 1C

Clique em gravar. A tarefa aparecerá no lado esquerdo da lista.

É importante! Verifique se a tarefa Backup do banco de dados SQL foi criada corretamente. Para fazer isso, clique com o botão direito do mouse na tarefa e selecione Executar.

Como resultado, um arquivo de backup deve aparecer no caminho especificado. Se algo estiver errado, exclua a tarefa (Del) e comece do início.