Sql-server – Script to Add a Job to a SQL Server DB

backupjobssql servert-sql

I want to add a job to my DB to make a back up weekly but i want to do it from an script, i've serched and with what i found i made this:

CREATE SCHEMA job
go
CREATE PROC BACKUPS @dir varchar(max)
AS BEGIN
    DECLARE @comando nvarchar(max) = N'BACKUP DATABASE STAZIONE TO DISK = '''+@dir+'-'+convert(char(8),getdate(),104)+'.bak'' WITH  COMPRESSION,  ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = BackupEncryptCert), STATS=1'
    EXEC job.sp_add_job @job_name = @job ;--Add a job
    EXEC job.sp_add_jobstep @job_name = @job, @step_name = N'Backing',  @subsystem = N'TSQL', @command = @comando   --Schedule the job at a specified date and time
    EXEC job.sp_add_jobschedule @job_name = @job, @name = N'DB_BACKUP', @freq_type=8, @freq_interval =1, @active_start_date = @startdate,   @active_start_time = @starttime -- Add the job to the SQL Server Server will run weekly
    EXEC job.sp_add_jobserver @job_name =  @job, @server_name = @servername
END
GO

But when executin i get this:

The 'BACKUPS' module depends on the missing object 'job.sp_add_job'. Even so, the module will be created; however, you can not run successfully until the object exists.

How can i create a job for my DB ?

Best Answer

Instead of writing your own solution, I would highly recommend to use Ola Hallengren's SQL Server Backup Solution.

Refer to : Bad habits to kick : avoiding the schema prefix by Aaron Bertrand.

Below should work for you (I have not tested it):

CREATE SCHEMA job
go
CREATE PROC job.BACKUPS @dir varchar(max)
AS BEGIN
    DECLARE @comando nvarchar(max) = N'BACKUP DATABASE STAZIONE TO DISK = '''+@dir+'-'+convert(char(8),getdate(),104)+'.bak'' WITH  COMPRESSION,  ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = BackupEncryptCert), STATS=1'
    EXEC msdb.dbo.sp_add_job @job_name = @job ;--Add a job
    EXEC msdb.dbo.sp_add_jobstep @job_name = @job, @step_name = N'Backing',  @subsystem = N'TSQL', @command = @comando   --Schedule the job at a specified date and time
    EXEC msdb.dbo.sp_add_jobschedule @job_name = @job, @name = N'DB_BACKUP', @freq_type=8, @freq_interval =1, @active_start_date = @startdate,   @active_start_time = @starttime -- Add the job to the SQL Server Server will run weekly
    EXEC msdb.dbo.sp_add_jobserver @job_name =  @job, @server_name = @servername
END
GO
Related Question