Schedule Jobs

can anyone tell me a way of scheduling backup jobs that would have another server as the destination of the backup files?
thanks...before you can do that, you must map first the designated server.

Originally posted by nancy
can anyone tell me a way of scheduling backup jobs that would have another server as the destination of the backup files?

thanks...|||I have neaver tryied, but id imagin that if you have a mapped drive on your sql server and then created a backup device pointing to that mapped drive you should be able to back up a database to that backup device. Just a guess though
Jim|||Hi,

Thanks for your suggestions but even if I have a mapped drive, it wouldnt be possible to point a backup device to that drive. You see, you can only point them to the the local drives on the server.

Any other suggestions? ; )

Thanks in advance !!!!

Nancy|||well i have tried what I suggested in a SQL 2000 environment and it worked. I was able to create the backup device threw the mapped drive and I backed up a databae. You may have to be on the local SQL server, not using pc anyware or any other remote software, but I know that it does work. Msg me if you have any other questions.
Jim|||Nancy correceted me you can use pc anyware to do this work, you just have to make sure you are on the SQL server to do it.
Originally posted by JDionne
well i have tried what I suggested in a SQL 2000 environment and it worked. I was able to create the backup device threw the mapped drive and I backed up a databae. You may have to be on the local SQL server, not using pc anyware or any other remote software, but I know that it does work. Msg me if you have any other questions.
Jim|||RE: can anyone tell me a way of scheduling backup jobs that would have another server as the destination of the backup files?
thanks...

Q1 [Is there a]...way of scheduling backup jobs that would have another server as the destination of the backup files?
A1 Yes, use a UNC (you may also use a mapped drive but a UNC has some advantages) Note: you may use an EM connection, or an isqlw, osql, etc. connection and TSQL (You do NOT need to be on the server, you need only have sufficient rights, and connectivity) e.g.,

Assume you have a remote server Name = RemoteServer
with a usable accessible sufficiently large share Name = DumpShare
Use the following in a job:

BACKUP DATABASE [pubs]
TO DISK = N'\\RemoteServer\DumpShare\PubsDump.Bkp'
WITH NOINIT ,
NAME = N'pubs backup', NOSKIP , STATS = 1, NOFORMAT

A2 An example scheduled job you can create in a Query Analyzer connection:
-- Script generated on 10/25/2002 4:06 PM
-- By: DBA
-- Server: Utility\Development

BEGIN TRANSACTION
DECLARE @.JobID BINARY(16)
DECLARE @.ReturnCode INT
SELECT @.ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Database Maintenance') < 1
EXECUTE msdb.dbo.sp_add_category @.name = N'Database Maintenance'

-- Delete the job with the same name (if it exists)
SELECT @.JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'pubs backup')
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 ''pubs backup'' 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'pubs backup'
SELECT @.JobID = NULL
END

BEGIN

-- Add the job
EXECUTE @.ReturnCode = msdb.dbo.sp_add_job @.job_id = @.JobID OUTPUT , @.job_name = N'pubs backup', @.owner_login_name = N'sa', @.description = N'No description available.', @.category_name = N'Database Maintenance', @.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'pubs backup Step 1', @.command = N'BACKUP DATABASE [pubs]
TO DISK = ''\\RemoteServer\DumpShare\PubsDump.Bkp''
WITH NOINIT ,
NAME = ''pubs backup'', NOSKIP , STATS = 1, NOFORMAT', @.database_name = N'master', @.server = N'', @.database_user_name = N'', @.subsystem = N'TSQL', @.cmdexec_success_code = 0, @.flags = 0, @.retry_attempts = 0, @.retry_interval = 0, @.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'pubs backup Schedule 1', @.enabled = 1, @.freq_type = 4, @.active_start_date = 20021025, @.active_start_time = 170000, @.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: