Script em lote para backup de todos os bancos de dados do SQL Server

Já cobrimos como fazer backup de um banco de dados do SQL Server a partir da linha de comando, e se você quiser fazer backup de todos os seus bancos de dados de uma só vez? Você poderia criar um script em lote que executasse o comando de backup para cada banco de dados, mas esse script teria que ser atualizado sempre que um banco de dados fosse adicionado ou removido. Além disso, os backups de banco de dados serão todos anexados a um arquivo, que crescerá de acordo com o tamanho do novo backup sempre que for executado. Em vez disso, no verdadeiro modo "definir e esquecer", criaremos um script em lote que se adaptará ao seu SQL Server à medida que novos bancos de dados forem adicionados e removidos.

Para ir direto ao ponto, este é o script de backup:

@ ECHO OFF
SETLOCAL

REM Obter data no formato AAAA-MM-DD (assume que a localidade é os Estados Unidos)
FOR / F “tokens = 1,2,3,4 delims = /” %% A IN ('Data / T') DO SET NowDate = %% D - %% B - %% C

REM Cria uma lista de bancos de dados para backup
SET DBList =% SystemDrive% SQLDBList.txt
SqlCmd -E -S MeuServidor -h-1 -W -Q “SET NoCount ON; SELECT Nome FROM master.dbo.sysDatabases WHERE [Nome] NOT IN ('mestre', 'modelo', 'msdb', 'tempdb') ”>“% DBList% ”

REM Backup de cada banco de dados, prefixando a data no nome do arquivo
FOR / F “tokens = *” %% I IN (% DBList%) DO (
ECHO Fazendo backup do banco de dados: %% I
SqlCmd -E -S MyServer -Q “BACKUP DATABASE [%% I] para o disco =" D: backup% NowDate% _ %% I.bak "”
ECO.
)

REM Limpe o arquivo temporário
SE EXISTE "% DBList%" DEL / F / Q "% DBList%"

ENDLOCAL

Supondo que a data seja 1/13/2009 e você tenha 3 bancos de dados chamados 'MyDB', 'AnotherDB' e 'DB Name with Spaces', o script produzirá 3 arquivos no local de backup especificado:

• 2009-01-13_AnotherDB.bak
• 2009-01-13_DB Name com Spaces.bak
• 2009-01-13_MyDB.bak

Customizando e executando o script em lote

Claro, você vai querer personalizar o script para o seu ambiente, então aqui está o que você precisa fazer:

• Se a localidade da sua máquina não estiver definida como EUA, o comando 'Data / T' poderá não retornar a data no formato “Tue 13/01/2009”. Se esse for o caso, a variável NowDate não produzirá o formato desejado e deverá ser ajustada. (1 lugar)
• Mude 'MyServer' para ser o nome do seu SQL Server (adicione o nome da instância, se aplicável). (2 lugares)
• Os bancos de dados denominados 'master', 'model', 'msdb' e 'tempdb' são bancos de dados fornecidos com o SQL Server. Você pode adicionar nomes de bancos de dados adicionais a essa lista se não quiser que eles sejam armazenados em backup. (1 lugar)
• Altere o local de backup de 'D: Backup' para o local em que você deseja armazenar os arquivos de backup do banco de dados.

Depois de ter personalizado o script em lote, agende-o para ser executado através do Agendador de Tarefas do Windows como um usuário com direitos de Administrador e está tudo pronto.