Results 1 to 7 of 7

Thread: Schedule Jobs

  1. #1
    Join Date
    Oct 2002
    Posts
    2

    Unanswered: 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...

  2. #2
    Join Date
    Oct 2002
    Posts
    4

    Re: Schedule Jobs

    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...

  3. #3
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    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

  4. #4
    Join Date
    Oct 2002
    Posts
    2

    Schedule Jobs

    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

  5. #5
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    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

  6. #6
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    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

  7. #7
    Join Date
    Oct 2002
    Posts
    369

    Post Re: Schedule Jobs

    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:

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •