Script to Add a Job to a SQL Server DB

Question

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 ?

• RLF: I see that you created a schema named job, but the schema for those system stored procedures should be dbo.

Answer 1

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