quarta-feira, 16 de maio de 2012

Script de backup de todas as databases no SQL Server

Nas versões pagas do SQL Server, existem assistentes que facilitam o agendamento de tarefas de backup que gravem todos os databases existentes, sem a preocupação de que ao criar novas databases precise alterar estes procedimentos.
Em ambientes sem estes recursos, ou onde você prefirar criar manualmente estas atividades, sugiro o script abaixo, que irá selecionar todos os databases existentes na instância do servidor e executar para cada um a instrução de backup.
Para automatizar este procedimento copie o código abaixo e coloque em um arquivo neste exemplo chamado BACKUP.SQL.
Observe no início do script que tem o destino dos arquivos gerados pela rotina, altere conforme a tua necessidade.

DECLARE @name VARCHAR(150) -- Nome do Database  
DECLARE @path VARCHAR(256) -- Caminho do arquivo de backup
DECLARE @fileName VARCHAR(256) -- Arquivo do backup  

-- Define caminho de destino do backup
SET @path = 'D:\Backup\'  

-- Cria um cursor para selecionar todas as databases,  
--  excluindo model, msdb e tempdb
DECLARE db_cursor CURSOR FOR  
   SELECT name 
     FROM master.dbo.sysdatabases 
    WHERE name NOT IN ('model','msdb','tempdb')  

-- Abre o cursor e faz a primeira leitura 
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

-- Loop de leitura das databases selecionadas
WHILE @@FETCH_STATUS = 0   
BEGIN   
   SET @fileName = @path + @name + '.BAK'  
   -- Executa o backup para o database
   BACKUP DATABASE @name TO DISK = @fileName WITH FORMAT;  

   FETCH NEXT FROM db_cursor INTO @name   
END   

-- Libera recursos alocados pelo cursor
CLOSE db_cursor   
DEALLOCATE db_cursor 

A seguir crie outro arquivo chamado BACKUP.BAT com o conteúdo abaixo:
osql -E -S Servidor -i "d:\backup\sql\BackupBancos.SQL"

Agora basta criar um agendamento do próprio Windows chamando o arquivo BACKUP.BAT.

12 comentários:

  1. Show de bola, me economizou varias horas de trabalho ..

    ResponderExcluir
  2. O script funcionou perfeitamente (só inclui o banco master para não ser backupiado também), mas o BAT não esta funcionando.

    ResponderExcluir
  3. Tiago, faltava fechar as aspas na instrução do arquivo BAT, já editei o post, por favor tente novamente com a nova instrução.

    ResponderExcluir
  4. No meu caso não rolou devido a problemas de login no banco. É possível editar o script para incluir as credenciais de login?

    ResponderExcluir
  5. Para colocar as credenciais de login, utilize os parâmetros -U e -P e remova o -E. Por exemplo:
    osql -U sa -P senha -S Servidor -i "d:\backup\sql\BackupBancos.SQL"

    ResponderExcluir
    Respostas
    1. Boa tarde Cesar. Muito bacana sua dica.
      Me diz uma coisa, pois sou leigo em sql server, o que devo colocar no campo Servidor?

      Abraço.

      Excluir
  6. Cesar Funcionou perfeitamente!
    Você teria alguma explicação para executarmos por cada base, ao invés de fazer todos de uma vez?
    Obrigado,

    ResponderExcluir
    Respostas
    1. Me desculpa mas não entendi a pergunta.

      Excluir
    2. Acho que isso responde a sua pergunta:

      BACKUP DATABASE AdventureWorks2012
      TO DISK = 'Z:\SQLServerBackup\AdventureWorks2012.bak';
      GO

      Excluir
  7. Me ajudou muito, fiz umas implementacoes e pode servir para outros. Abaixo as alteracoes:
    --inclui essa variavel para pegar o dia do backup
    DECLARE @dia VARCHAR(10) -- dia do backup
    --formata o dia no padrao iso (yymmdd)
    SET @dia = CONVERT (varchar,GETDATE(), 112)
    --remove os espacos
    SET @fileName = LTRIM( @path + @name + @dia + '.bak')



    ResponderExcluir
  8. Cesar e Luiz, muito obrigado pelo Script e boa vontade! Valeu a força!

    ResponderExcluir
  9. E como seria para backup incremental?

    ResponderExcluir