• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 204
  • Last Modified:

SQL Job won't run SP

Hi,

I have created a stored procedure to have my SQL Server backup my databases to a network drive.  I have pulled the SP into a maintenance plan but it doesn't work.  I also tried to have it run as a job, but that isn't working either.  

Any ideas what I'm doing wrong?  Any help is greatly appreciated.  My SP is below.


USE [master]
GO
/****** Object:  StoredProcedure [dbo].[BackupDBs]    Script Date: 10/10/2011 13:39:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER procedure [dbo].[BackupDBs]

as 

declare @statement nvarchar(255)




select @statement = 'BACKUP database ORCA to disk = '+"'" +
'\\Storag01\SQLBkP\SRVSQL05\ORCA_Backup_' +
CONVERT(varchar(4), datepart(yy, getdate()))+
right('0' + CONVERT(varchar(2), datepart(mm, getdate())),2)+ "_" +
right('0' + CONVERT(varchar(2), datepart(dd, getdate())),2)+ "_" +
right('0' + CONVERT(varchar(2), datepart(hour, getdate())),2)+ "_" +
right('0' + CONVERT(varchar(2), datepart(minute, getdate())),2)+
'.bak'+"'"
exec sp_executesql @statement

select @statement = 'BACKUP database SQUID to disk = '+"'" +
'\\Storag01\SQLBkP\SRVSQL05\SQUID_Backup_' +
CONVERT(varchar(4), datepart(yy, getdate()))+
right('0' + CONVERT(varchar(2), datepart(mm, getdate())),2)+ "_" +
right('0' + CONVERT(varchar(2), datepart(dd, getdate())),2)+ "_" +
right('0' + CONVERT(varchar(2), datepart(hour, getdate())),2)+ "_" +
right('0' + CONVERT(varchar(2), datepart(minute, getdate())),2)+
'.bak'+"'"
exec sp_executesql @statement

select @statement = 'BACKUP database TEUTHIS to disk = '+"'" +
'\\Storag01\SQLBkP\SRVSQL05\TEUTHIS_Backup_' +
CONVERT(varchar(4), datepart(yy, getdate()))+
right('0' + CONVERT(varchar(2), datepart(mm, getdate())),2)+ "_" +
right('0' + CONVERT(varchar(2), datepart(dd, getdate())),2)+ "_" +
right('0' + CONVERT(varchar(2), datepart(hour, getdate())),2)+ "_" +
right('0' + CONVERT(varchar(2), datepart(minute, getdate())),2)+
'.bak'+"'"
exec sp_executesql @statement

Open in new window

0
etsmyname
Asked:
etsmyname
  • 5
  • 4
  • 2
2 Solutions
 
Chris MangusDatabase AdministratorCommented:
Because you're trying to backup to a resource off your server the account that runs the job needs to have rights to reach off your server.
0
 
etsmynameAuthor Commented:
O.K., I have read something to that effect, but I'm not sure how to give rights to the server account on the other server.  

Also, when the job runs, it doesn't show an error.  The log shows that it ran successfully, but no backup was done.
0
 
etsmynameAuthor Commented:

I just checked the SQLSERVERAGENT Service and it runs as NetworkName\Administrator  

This is the same network that the backup server sits on.  Adminstrator has full rights to that both servers.
0
Evaluating UTMs? Here's what you need to know!

Evaluating a UTM appliance and vendor can prove to be an overwhelming exercise.  How can you make sure that you're getting the security that your organization needs without breaking the bank? Check out our UTM Buyer's Guide for more information on what you should be looking for!

 
Chris MangusDatabase AdministratorCommented:
I don't want to assume, but, this code runs fine if you manually execute the SP, right?
0
 
etsmynameAuthor Commented:
That is correct.  I can run the SP manually and it puts a .bak file for each database in the correct directory.
0
 
Chris MangusDatabase AdministratorCommented:
When you had it set up as a job, were you able to run the job yourself, by right-clicking and choosing start job?
0
 
etsmynameAuthor Commented:
I did.   Here is the error I get:

Error,[136] Job Run Backups reported: The process could not be created for step 1 of job 0xFC00E941A91E134E9BB29CCEE21E4B62 (reason: The system cannot find the file specified)

Here is what my job looks like:  Job Step
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<Here is what my job looks like:  >>
Why are trying to run a TSQL command as an Operating systel (CmdExed) step ?
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Attached is an alternative to what you are doing

Hope this helps...
create procedure [dbo].[BackupDBs]
as
begin
declare @string_run varchar(max)
select @string_run = ''
select @string_run = @string_run + 'backup database ' + name + ' to disk =''\\Storag01\SQLBkP\SRVSQL05\'+ name + '_Backup_' +
right('0' + CONVERT(varchar(2), datepart(mm, getdate())),2)+ "_" +
right('0' + CONVERT(varchar(2), datepart(dd, getdate())),2)+ "_" +
right('0' + CONVERT(varchar(2), datepart(hour, getdate())),2)+ "_" +
right('0' + CONVERT(varchar(2), datepart(minute, getdate())),2)+
'.bak'';'
from master..sysdatabases
where name in ('ORCA', 'SQUID', 'TEUTHIS')

exec(@string_run)
end

Open in new window

0
 
Chris MangusDatabase AdministratorCommented:
As Racimo pointed out, you need to make sure to change the job type to a TSQL command.  Then, let's see how the job works.
0
 
etsmynameAuthor Commented:
Job runs fine after I changed the it to run as TSQL command.  The modified script also makes a lot of sense.  Thank you Everyone!!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now