A primeira coisa que devemos ter em mente é que para realizar o backup em uma máquina remota é necessário que a conta de usuário que inicia os serviços do SQL Server (MSSQLSERVER, SQLServerAgent) tenha acesso à rede. Sendo assim, o SQL Server e o SQL Server Agent não poderão estar rodando com a conta de usuário system account. Eles deverão estar rodando com uma conta de usuário de domínio que tenha permissão de administração na máquina local e permissão para efetuar gravação na máquina remota. Se você utilizar um usuário que seja administrador no domínio (faça parte do grupo administrator no controlador de domínio), isto será resolvido.
Uma vez configurado o usuário, é possivel realizar o backup de duas formas:
1) Criando um device de backup lógico usando um endereço UNC para o servidor remoto.
Isto pode ser feito conforme o exemplo abaixo o qual adiciona no SQL Server um device de backup lógico para o banco Pubs. O arquivo resultante do backup será armazenado no servidor remoto.
USE master
— Cria o device lógico remoto no SQL Server
EXEC sp_addumpdevice ‘DISK’, ‘Pubs_BKP’,’servernamesharenamePubs-BKP.BAK’
GO
— Faz o backup do banco Pubs no device remoto
BACKUP DATABASE Pubs TO Pubs_BKP WITH INIT
Lembre-se que caso você utilize esta opção, o device lógico deverá ser criado apenas uma vez. Para posteriores Backups, basta executar o comando de backup. A opção INIT faz com que os backups existentes no device lógico sejam substituidos pelo backup mais recente.
2) Executar o backup diretamente para um device físico o qual aponta para um servidor remoto. É importante destacar que este procedimento não cria um device lógido no SQL Server.
USE MASTER
GO
BACKUP DATABASE Pubs TO DISK = ‘servernamesharenamePubs-BKP.BAK’ WITH INIT
Pronto, caso queira automatizar o procedimento de backup, basta criar um Job no SQL Server utilizando um dos dois exemplos acima.
Segue abaixo um exemplo de código Transact-SQL para criar um job de backup utilizando a primeira opção. Neste exemplo eu considero que o device de backup lógico (Pubs_BKP) já foi criado em um passo anterior.
Para criar o Job basta colocar o código no QA e teclar F5.
— Cria Job de backup para o banco Pubs. O backup é realizado de Segunda a Sexta às 22:00hs.
— Script generated on 5/10/2004 13:59
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 do Banco Pubs’)
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 do Banco Pubs” 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 do Banco Pubs’
SELECT @JobID = NULL
END
BEGIN
— Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N’Backup do Banco Pubs’, @owner_login_name = N’sa’, @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 do Banco Pubs’, @command = N’BACKUP DATABASE Pubs TO Pubs_BKP’, @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”, @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 do Banco Pubs’, @enabled = 1, @freq_type = 8, @active_start_date = 20041005, @active_start_time = 220000, @freq_interval = 62, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = 1, @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:
Para maiores informações sobre o comando de Backup, consulte por BACKUP DATABASE no Books Online do SQL Server.