O que acontece durante um backup do SQL Server ao vivo?

Question

Alguns de meus colegas de trabalho ficaram surpresos quando eu disse a eles que posso fazer backup de um banco de dados do SQL Server enquanto ele ainda está em execução e imaginei como isso é possível. Eu sei que o SQL Server é capaz de fazer backup de um banco de dados enquanto ele ainda está online, mas não sei como explicar por que é possível. Minha pergunta é o efeito que isso tem no banco de dados?

Se os dados forem modificados (por uma inserção, atualização ou exclusão) enquanto o backup estiver em execução, o backup conterá essas alterações ou será adicionado ao banco de dados posteriormente?

Estou assumindo que o arquivo de log tem um papel importante aqui, mas não tenho certeza de como.

edit: Apenas como uma nota, o meu caso envolve o backup dos bancos de dados usando o SQL Server Agent e os efeitos das modificações do banco de dados durante esse processo.

    

Answer 1

Backup completo contém os dados e o log. Para dados, ele simplesmente copia cada página do banco de dados no backup, como está no momento em que lê a página. Em seguida, anexa na mídia de backup todo o registro "relevante". Isso inclui, no mínimo, todo o log entre o LSN no início da operação de backup e o LSN no final da operação de backup. Na realidade, há mais log geralmente, pois ele precisa incluir todas as transações ativas no início do backup e o log necessário para a replicação. Veja Debunking um par de mitos em torno de pleno backups de banco de dados .

Quando o banco de dados é restaurado, todas as páginas de dados são copiadas para os arquivos do banco de dados e, em seguida, todas as páginas de registro são copiadas para o (s) arquivo (s) de log. O banco de dados é inconsistente neste momento, pois contém imagens de páginas de dados que podem estar fora de sincronia umas com as outras. Mas agora uma recuperação normal é executada. Como o log contém todo o log durante o backup, no final da recuperação, o banco de dados é consistente.

    

Answer 2

Durante o backup, o instantâneo será criado para o banco de dados e os dados serão lidos para backup a partir desse instantâneo. As operações atuais do DB não afetarão a operação de backup.

    

Answer 3

Você não pode simplesmente copiá-lo, pois pode haver alterações na cópia intermediária do banco de dados, como você aludiu na pergunta.

Tem que ser feito com agentes que estão cientes da funcionalidade do banco de dados e, em seguida, obtêm um "instantâneo" através de funções do sistema operacional ou podem usar um utilitário para despejar o banco de dados em um estado seguro (como mysqldump, se usando mysql). / p>

Caso contrário, você recebe um backup que pode ser corrompido e você não saberá até que você o restaure. Eu acho que Joel e Jeff recentemente falaram sobre isso em um recente podcast StackOverflow.

E você está certo em que o arquivo de log é importante. Se o arquivo de registro / diário estiver fora de sincronia com os dados reais, a restauração dos arquivos resultará em corrupção.

Tudo se resume a um backup feito usando um estado seguro do banco de dados, seja por meio de um agente com reconhecimento de banco de dados ou aplicativo ou aplicativo de instantâneo que esteja ciente de como conectar corretamente o banco de dados a dados sem interferir nas atualizações durante os dados. despejar o backup do arquivo resultante.

    

Answer 4

Existem muitas maneiras de fazer isso (em geral, não faz ideia de como o MSSQL normalmente faz isso), como simplesmente despejar o banco de dados em um arquivo, acrescentando alterações em um arquivo de log que é confirmado após a conclusão do dump. recursos de instantâneos específicos do sistema, como o VSS no Windows.

    

Answer 5

Você pode usar o que é conhecido como backup somente de cópia. Não afetará o banco de dados enquanto estiver on-line