Tem sido cada vez mais comum encontrar nos fóruns pergunta sobre como agendar backups no SQL Server para que eles sejam feitos automaticamente. Primeiramente é preciso que você saiba que este processo de agendamento somente é possível se a edição do SQL Server que você possui tiver o serviço SQL Server Agent. Para que o agendamento de tarefas funcione, este serviço deve estar ativo.
Você pode verificar a presença e o status deste serviço olhando o Service Manager do SQL Server 7/2000 (Iniciar| Programas| Microsoft SQL Server) ou em SQL Server 2005 Services no utilitário SQL Server Configuration Manager do SQL Server 2005 (Iniciar| Programas| Microsoft SQL Server 2005| Configuration Tools). Para quem estiver utilizando o SQL Server 20005 Express, infelizmente esta edição do SQL Server 2005 não vem com o serviço SQL Server Agent. Neste caso, leia a dica “Automatizando Backups no SQL Server Express” para saber como automatizar backups no SQL Express.
Após garantir que o serviço SQL Server Agent está ativo, pegue o script de criação da procedure usp_backupdb apresentado abaixo e execute no Query Analyzer (SQL Server 7/2000) ou Query Editor (SQL Server 2005).
A procedure aceita dois parâmetros:
@banco: o nome do banco a ser feito backup. Se não for informado nenhum banco, será feito backup de todos os bancos de dados do servidor, excluindo apenas os bancos de sistema model e tempdb.
@caminho: este é o caminho onde os arquivos de backup deverão ser armazenados. O caminho default é C:/Backup.
É importante observar também que a procedure é criada no banco de sistema master e somente fará backup dos bancos que estiverem com o status de ONLINE. Não será feito backup de bancos que estejam em processo de restore, offile ou em suspect (corrompido).
Após a criação da procedure, você poderá executá-la seguindo alguns do exemplos:
— Faz backup de todos os bancos armazenando os backups no caminho default.
EXEC master..usp_backupdb
— Faz backup apenas do banco Pubs, armazenando o backup no caminho default.
EXEC master..usp_backupdb Pubs
— Faz backup apenas do banco Pubs, armazenando o backup no caminho G:/Backup.
EXEC master..usp_backupdb Pubs, G:/Backup
Nota: Altere a barra (/) para a barra de separação de diretório.
Script para criação da procedure usp_backupdb
/**** Script para criar procedure usp_backupdb ****/
USE master
GO
IF EXISTS (SELECT name FROM master..sysobjects WHERE name=’usp_backupdb’AND type=’P’)
DROP PROCEDURE usp_backupdb
GO
CREATE PROCEDURE usp_backupdb
(
@banco sysname = NULL,
@caminho VARCHAR(256) =’C:/Backup‘ — Não esqueça de alterar a barra (/)
)
AS
SET NOCOUNT ON
DECLARE @ArqNome VARCHAR(256)
DECLARE @ArqData VARCHAR(20)
DECLARE @bancos sysname
DECLARE @DbStatus VARCHAR(10)
SELECT @ArqData = CONVERT(VARCHAR(20),GETDATE(),112)
IF @banco IS NULL
BEGIN
— Faz Backup de Todos os Bancos menos TEMPDB e Model
DECLARE db_cursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN (‘model’,’tempdb’)
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @bancos
WHILE @@FETCH_STATUS = 0
BEGIN
SET @DbStatus = (SELECT CONVERT(VARCHAR(10),DATABASEPROPERTYEX(@bancos,’Status’)))
— Não faz backup se o banco estiver em OFFLINE , RESTORING ou SUSPECT
IF @DbStatus = ‘ONLINE’
BEGIN
SET @ArqNome = @caminho + @bancos + ‘_’ + @ArqData + ‘_BKP.BAK’
BACKUP DATABASE @bancos TO DISK = @ArqNome
END
ELSE
PRINT ‘Não foi possível fazer backup do banco [‘ + @bancos + ‘] devido estar em ‘ + @DbStatus
FETCH NEXT FROM db_cursor INTO @bancos
END
CLOSE db_cursor
DEALLOCATE db_cursor
END
ELSE
BEGIN — Faz backup apenas do banco informado
SET @DbStatus = (SELECT CONVERT(VARCHAR(10),DATABASEPROPERTYEX(@banco,’Status’)))
— Não faz backup se o banco estiver em OFFLINE , RESTORING ou SUSPECT
IF @DbStatus = ‘ONLINE’
BEGIN
SET @ArqNome = @caminho + @banco + ‘_’ + @ArqData + ‘_BKP.BAK’
BACKUP DATABASE @banco TO DISK = @ArqNome
END
ELSE
PRINT ‘Não foi possível fazer backup do banco [‘ + @banco + ‘] devido estar em ‘ + @DbStatus
END
GO
Para criar um job que possa executar os backups de forma automática, execute o scrip de criação do job (abaixo) no Query Analyzer ou Query Editor. O script cria um job de nome Backup_Database que executa a procedure usp_backupdb todos os dias as 23:00hs fazendo o backup de todas as bases de dados. Também é criado um arquivo de nome Backup_Database.txt que grava o log de execução dos backups. Este log é criado no caminho default C:/Backup, caso queira alterar este caminho, localize C:/Backup/Backup_Database.txt no script e altere pelo caminho de sua preferência.
Nota: Altere as barras (/) dentro do script para a barra de separação de diretório.
Caso deseje alterar o horário ou período dos backup, você pode alterar as propriedades do job abrindo o Enterprise Manager, expandindo Management, SQL Server Agent, Jobs. Clique com o botão direito sobre o job Backup_Database e selecione propriedades.
/*** CRIA O JOB PARA REALIZAÇÃO DO BACKUP AUTOMÁTICO ***/
USE master
GO
BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]’) < 1
EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]’
— Delete the job with the same name (if it exists)
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N’Backup_Database’)
IF (@JobID IS NOT NULL)
BEGIN
— Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
— There is, so abort the script
RAISERROR (N’Unable to import job ”Backup_Database” since there is already a multi-server job with this name.’, 16, 1)
GOTO QuitWithRollback
END
ELSE
— Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @job_name = N’Backup_Database’
SELECT @JobID = NULL
END
BEGIN
— Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N’Backup_Database’, @owner_login_name = N’WINXPLABAdministrador’, @description = N’No description available.’, @category_name = N'[Uncategorized (Local)]’, @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
— Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N’Backup_Database’, @command = N’EXEC master..usp_backupdb’, @database_name = N’master’, @server = N”, @database_user_name = N”, @subsystem = N’TSQL’, @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N’C:/Backup/Backup_Database.txt‘, @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
— Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N’Backup_Database’, @enabled = 1, @freq_type = 4, @active_start_date = 20060928, @active_start_time = 230000, @freq_interval = 1, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
— Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
Como vocês devem ter observado, a procedure que fazer o backup é bem simples, inclusive não faz tratamento de nenhuma erro. A idéia é apenas permitir que os backups possam ser feitos e automatizados. Fiquem a vontade para alterar a procedure e adaptá-la às suas necessidades.
Um abraço
Nilton Pinheiro