1

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 ?

  • I see that you created a schema named job, but the schema for those system stored procedures should be dbo. – RLF Aug 7 '14 at 20:03
2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.