Como escrever um backup para cada banco de dados em um mecanismo MSSQL?

Question

Precisamos fazer backup de 40 bancos de dados dentro de um mecanismo do MS SQL Server. Nós fazemos backup de cada banco de dados com o seguinte script:

BACKUP DATABASE [dbname1] TO  DISK = N'J:\SQLBACKUPS\dbname1.bak' WITH NOFORMAT, INIT,  NAME = N'dbname1-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'dbname1' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'dbname1' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''dbname1'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM  DISK = N'J:\SQLBACKUPS\dbname1.bak' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
GO

Gostaríamos de adicionar ao script a funcionalidade de pegar cada banco de dados e substituí-lo no script acima. Basicamente, um script que criará e verificará cada backup de banco de dados de um mecanismo.

Estou procurando algo assim:

For each database in database-list
    sp_backup(database) // this is the call to the script above.
End For

alguma ideia?

    

Answer 1

Você pode fazer isso usando um procedimento de loop de banco de dados não documentado.

use [master]
go

set quoted_identifier off

exec sp_MSforeachdb "
if ( '?' not in ( 'master', 'msdb', 'model', 'tempdb' ) )
begin

BACKUP DATABASE [?] TO  DISK = N'J:\SQLBACKUPS\?.bak' WITH NOFORMAT, INIT,  NAME = N'?-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

declare @backupSetId as int

select @backupSetId = position from msdb..backupset where database_name=N'?' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'?' )

if ( @backupSetId is null )
begin
    raiserror(N'Verify failed. Backup information for database ''?'' not found.', 16, 1)
end

RESTORE VERIFYONLY FROM  DISK = N'J:\SQLBACKUPS\?.bak' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND

end
"

    

Answer 2

Algo como:

1. Crie um cursor para 'SELECT name FROM master..sysdatabases WHERE nome NOT IN (' list ',' of ',' dbs ',' para ',' ignorar ')
2. Iterar através disso e em cada loop:
   1. crie seu script como uma string SQL ad-hoc
   2. (tenha o log SQL ad-hoc acima algum resultado em um banco de dados de log pequeno em algum lugar)
   3. executá-lo com EXEC (@sql)
3. Produza um relatório simples com base nos resultados armazenados para verificação de integridade

deve fazer o truque.

Há também vários produtos no mercado que oferecem opções de backup muito flexíveis para o SQL Server, que podem ser melhores para uso em produção se o script ficar mais complicado (embora, sem dúvida, não seja barato).

    

Answer 3

Eu sei que isso não é uma resposta direta à sua pergunta, já que você quer algo com o seu script específico, mas eu pensei em jogar isso lá fora.

Se você tiver a versão completa do MS SQL, e não do MS SQL Express, poderá fazer a rota do plano de manutenção. Aqui está um PDF da Sunbelt Software que dá uma boa idéia de como usá-lo, você pode selecionar aqui os bancos de dados que você deseja fazer backup: SQL-2005-manutenção-plan.pdf

    

Answer 4

Eu melhoraria seu script:

:: copyonlybackup.bat
IF EXIST "%1_COPYONLY.BAK" (
  sqlcmd -U sa -P xxxxxxx -S SQL-DB -Q "BACKUP DATABASE %1 TO DISK = '%1_COPYONLY.BAK' WITH COPY_ONLY,INIT;"
echo Overwriting previous COPY_ONLY backup of %1 .
) ELSE (
  sqlcmd -U sa -P xxxxxxx -S SQL-DB -Q "BACKUP DATABASE %1 TO DISK = '%1_COPYONLY.BAK' WITH COPY_ONLY;"
  echo Creating new COPY_ONLY backup of %1 .
)

E, em seguida, chame-o com

:: RUNSCRIPTS.bat
F:
CD "F:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup"

@REM  Doing simple copies just in case
call COPYONLYBACKUP.bat db1
call COPYONLYBACKUP.bat db2
call COPYONLYBACKUP.bat db3
...

Finalmente, se você precisar de um loop, faça isso no arquivo RUNSCRIPTS.bat que mostro acima, usando algo parecido com isto:

for /f %%a IN (myfile.txt) do call copyonlybackup.bat %%a

Para gerar o myfile.txt, você provavelmente pode obter SQL para gerá-lo de alguma forma:

EXEC sp_msForEachDB 'PRINT ''?'''