Como posso fazer backup de um grande banco de dados SQL Server sem afetar o desempenho?

1. Isole a E/S de backup do restante da E/S em seu sistema. Embora possa demorar mais e ser mais propenso a soluços, fazer backup pela rede em vez de em um disco local pode ajudar a aliviar o impacto direto na instância. Mesmo em uma máquina virtual, você deve poder expor outro armazenamento para que o SQL Server possa gravar em um subsistema de E/S diferente.
2. É improvável que faça alguma diferença, mas, se isso for executado a partir de um trabalho, por que você precisa usar a STATSopção? Tem certeza de que precisa das outras opções ( NOUNLOAD, NOSKIP, NOFORMAT)? Eu não fiz nenhum teste de desempenho extensivo em toda a matriz de opções, mas IMHO você deve usar apenas as opções que você sabe que precisa.
3. Execute seus backups completos em um horário diferente que não interfira na atividade regular do usuário. Isso nem sempre é possível, mas mesmo em operações 24 horas por dia, 7 dias por semana, há horários de pico e de calmaria.
4. Se você mudar para 2008+, poderá aproveitar a compactação de backup (Enterprise em 2008, Standard ou Enterprise em 2008 R2+). Se você não puder mudar para 2008 ou não tiver a edição adequada, existem ferramentas de backup de terceiros que farão a compactação para você, e todas são muito boas nisso. Desde 2008, posso usar a compactação nativa e estou bastante satisfeito com a velocidade e a % de compactação, sem a necessidade de investir mais em terceiros. Mas com 2005 eu me lembro de ter um bom sucesso com o Red-Gate SQL Backup ; O Quest LiteSpeed ​​também é muito bom, mas não tenho ideia de que tipo de esforço está sendo colocado nele desde a aquisição da Dell.
5. Se seus bancos de dados estiverem em recuperação total, você poderá demorar mais entre os backups completos, trocando isso pelo conhecimento de que, se precisar recuperar até um determinado momento, terá potencialmente mais logs para restaurar. Você pode combinar essa opção com a sugestão de Ali abaixo de fazer diffs ao longo da semana - se você tivesse que se recuperar na quinta-feira, precisaria restaurar 1 full, 1 diff e os logs desde o diff.
6. Se você tiver muitos dados obsoletos e imutáveis, considere hospedá-los em um banco de dados diferente com um plano de recuperação diferente ou pelo menos em grupos de arquivos diferentes e dividir suas operações de backup dessa maneira. Se você tiver dados de referência ou arquivamento que podem ser tornados somente leitura e se estiver em recuperação simples, poderá movê-los para seu próprio grupo de arquivos somente leitura, fazer backup uma vez e não precisar incluí-los em seus backups noturnos nunca mais. Consulte estes tópicos do MSDN:

Executando restaurações graduais

Exemplo: restauração fragmentada de apenas alguns grupos de arquivos (modelo de recuperação simples)

Este é um problema comum, existem várias soluções e realmente depende do seu ambiente. Vamos percorrê-los:

1- Compressão de backup em tempo real

Em 2008 R1 Backup Compression ficou disponível em Enterprise, em 2008R2 ficou disponível em Standard. Isso é ENORME. Vai lhe poupar MUITO tempo. Se você pode atualizar, vá em frente. Se você não puder, confira o utilitário HyperBak da RedGate , ou Quest LiteSpeed ​​. Ambos têm um teste gratuito.

2- Backups Completos e Diferenciais

Eu herdei um banco de dados de produção de 2 TB, causando muitos tempos limite para uma grande empresa de Internet 24 horas por dia, 7 dias por semana, que trabalhei. Habilitamos backups completos e diferenciais que nos pouparam muito tempo. Eu faria um backup completo no domingo às 12h, quando a atividade estava baixa, e faria as diferenças durante a semana. Isso economizou muito espaço. O trabalho do Diff é diferente dos logs de transação, pois eles trabalham em quais páginas do banco de dados foram alteradas. Todas as páginas alteradas são armazenadas em backup. Assim, você faz uma restauração completa e, em seguida, a restauração diff para adicionar as páginas modificadas.

3- Qual é o seu gargalo?

A análise do gargalo é importante para diagnosticar. Você está fazendo backup na mesma matriz de disco dos seus arquivos de dados? Seus arquivos de dados estão sendo indexados? Qual é o seu DISCO SEC/READ e DISCO SEC/WRITE para os discos de dados durante os backups? Modifiquei os backups para criar 4 arquivos. Cada arquivo tem seu próprio gravador de encadeamento e em nossa SAN funcionou muito bem. Teste, eu economizei 45 minutos criando apenas 4 arquivos de backup. Apenas certifique-se de que suas métricas de disco listadas acima sejam baixas. Obtenha uma linha de base.

4- Replicar para um servidor diferente e fazer backup disso

Este é um pouco avançado. Você precisa ter certeza de que seu banco de dados replicado está atualizado e precisa de monitoramento adequado para isso. Se for, você pode apenas fazer backup do banco de dados replicado.

Você pode usar estes parâmetros:

BLOCKSIZE - Escolha o tamanho 512, 1024, 2048, 4096, 8192, 16384, 32768, 65536. (em KB)

BUFFERCOUNT - Especifica o número total de buffers de E/S a serem usados ​​para a operação de backup. Você pode especificar qualquer número inteiro positivo; no entanto, um grande número de buffers pode causar erros de "memória insuficiente" devido ao espaço de endereço virtual inadequado no processo Sqlservr.exe. - do MSDN

MAXTRNASFERSIZE - É de 65536 bytes (64 KB) a 4194304 bytes (4 MB)

Tente. ele resolveu o problema de tempo limite expirado enquanto db de tamanho grande.

Private Sub Command1_Click()
On Error Resume Next
Dim con As New Connection
Dim tm As String
con.CommandTimeout = 500'''Command timeout should be 500


 With con
    .ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=dbiBMS;Data Source=192.168.103.4"
    .Open
 End With
 tm = CStr(Time)

con.Execute " backup database dbiBMS to disk='E:\Database_Backup\Test1.bak' with format "

con.Close
MsgBox tm
Exit Sub
x:
MsgBox Err.Description

End Sub