Automate installation SqlJobs SqlServer

Is there a way to install SqlJobs on SqlServer by a script? I would like the sqljobs to be开发者_JAVA技巧 installed when setting up a Database remotely at a customer. Is this possible?

If you're meaning to add SQL Agent jobs, you need only look as far as the sp_add_job(step|schedule|server) stored procedures in msdb, which are reasonably well described in BOL

If, on the other hand, you're talking about ensuring the the agent is installed, running, etc. then I do not believe there is a way to do this via script.

If, on the third hand, you're talking about some particular product/database, when talking about SQLJobs, then this answer won't help you at all.

Edit

And the easiest way to get the right invocations (again, if we're talking SQL Agent jobs) is to build them on your local machine, then script them through SSMS/EM. Biggest gotcha I've found is you need to add a server to a job, even if it's only ever intended to be a local server job.

Edit 2

In response to first comment - your call to sp_add_jobserver can specify the server name as '(Local)', which will make it a local server job.

Here's a short but complete (and hopefully still working) script that automates restoring a database overnight. Notice that it makes no reference to which server it's on, so will add the job to whatever server it's being run against:

USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'RESTORE_DatabaseN', 
        @enabled=1, 
        @notify_level_eventlog=2, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'No description available.', 
        @category_name=N'Database Maintenance', 
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'step 1', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=3, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=1, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'DECLARE @BakFile varchar(120),  
        @OLD_DB sysname,
        @NEW_DB sysname,
        @RestoreData sysname,
        @RestoreLog sysname,
        @DBLogical varchar(255),
        @LogLogical varchar(255),

SET @OLD_DB     = ''DatabaseN''
SET @NEW_DB      = ''DatabaseN''
SET @BakFile = ''\\remoteserver\Backups\Server2\DatabaseN\DatabaseN.BAK''

SET @RestoreData = ''E:\sqldata\'' + @NEW_DB + ''.mdf''
SET @RestoreLog  = ''F:\SQLLogs\'' + @NEW_DB + ''_log.ldf''

create table #filelist (LogicalName varchar(255), PhysicalName varchar(255), Type varchar(20), FileGroupName varchar(255), Size varchar(20), MaxSize varchar(20) )
insert #filelist exec (''RESTORE FILELISTONLY FROM disk = '''''' + @BakFile +  '''''''')
select @DBLogical = LogicalName from #filelist where Type = ''D''
select @LogLogical = LogicalName from #filelist where Type = ''L''

RESTORE DATABASE @NEW_DB FROM DISK = @BakFile
WITH MOVE @DBLogical TO @RestoreData,
MOVE @LogLogical TO @RestoreLog, REPLACE
EXEC ('' USE '' + @NEW_DB + '' BACKUP LOG '' + @NEW_DB + '' WITH Truncate_Only'')

EXEC ('' USE '' + @NEW_DB + '' ALTER DATABASE '' + @NEW_DB + '' SET RECOVERY SIMPLE'')

--EXEC ('' USE '' + @NEW_DB + '' dbcc shrinkfile('' + @LogLogical + '', 2000)'')
', 
        @database_name=N'master', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'daily', 
        @enabled=1, 
        @freq_type=8, 
        @freq_interval=127, 
        @freq_subday_type=1, 
        @freq_subday_interval=0, 
        @freq_relative_interval=0, 
        @freq_recurrence_factor=1, 
        @active_start_date=20070708, 
        @active_end_date=99991231, 
        @active_start_time=11500, 
        @active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO