backup job runs from SSMS but not from job

Question

backup job runs from SSMS but not from job

Tara-1044200

SSCoach

Points: 15951
More actions
August 14, 2019 at 12:47 pm

#3671291

i am able to run backups for all sytem dbs using a script from SSMS but the same script is backing up only master from the sql agent job but not msdb or model, what am i missing?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Michael L John One Orange Chip Points: 27065 More actions · Answer 1

Can you publish the script?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Phil Parkin SSC Guru Points: 246991 More actions · Answer 2

Do you receive any error messages?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Tara-1044200 SSCoach Points: 15951 More actions · Answer 3

declare @dbname as varchar(80)
declare @msgdb as varchar(80)
declare @dbbkpname as varchar(80)
declare @datepart as char(1)

declare rs_cursor CURSOR for select name from master.sys.databases 
where state_desc = 'ONLINE'  and name <> 'tempdb' order by name

set @datepart = datepart(dw,getdate()) -1 

open rs_cursor 
Fetch next from rs_cursor into @dbname
WHILE @@FETCH_STATUS = 0 and @datepart IN (1,2,3,4)
BEGIN
  select @msgdb= 'database backup in progress: ' + @dbname
  PRINT @msgdb
  if @datepart  = 5  or @dbname = 'master' -- Make full backups on Sunday
  BEGIN
select @dbbkpname='\\PVPStorage\STRev_PD\full\' + @dbname + '7.bak'
exec master.dbo.xp_backup_database 
@database = @dbname, 
@filename = @dbbkpname,
@compressionlevel = 2,
@init = 1,
@threads = 3
  END
  else
    if (select count(*) from msdb.dbo.backupset where database_name = @dbname and type = 'D') > 0 
    BEGIN
       select @dbbkpname='\\PVPStorage\STRev_PD\' + @dbname + @datepart + '.bak'
exec master.dbo.xp_backup_database 
@database = @dbname, 
@filename = @dbbkpname,
@compressionlevel = 2,
@init = 1,
@threads = 3
, @with = 'DIFFERENTIAL' 
    END
    else
    BEGIN
select @dbbkpname='\\PVPStorage\STRev_PD\full\' + @dbname + '7.bak'
exec master.dbo.xp_backup_database 
@database = @dbname, 
@filename = @dbbkpname,
@compressionlevel = 2,
@init = 1,
@threads = 3
     END
  FETCH NEXT FROM rs_cursor INTO @dbname
END
CLOSE rs_cursor
deallocate rs_cursor
GO

Tara-1044200 SSCoach Points: 15951 More actions · Answer 4

i dont think there is any issue with the script as it works fine from SSMS but it does not work from SQL Agent job though it backups only master and skips the rest of databases with no errors.

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 5

Tara-1044200 wrote:

i dont think there is any issue with the script as it works fine from SSMS but it does not work from SQL Agent job though it backups only master and skips the rest of databases with no errors.

It IS possible that the SQL Agent Service has a different level of privs than you do. Check on the privs (especially at the OS level) that the SQL Agent Service actually has.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Tara-1044200 SSCoach Points: 15951 More actions · Answer 6

all sql server services are running under same account, its weird that only master database backups 🙁

Michael L John One Orange Chip Points: 27065 More actions · Answer 7

Tara-1044200 wrote:

all sql server services are running under same account, its weird that only master database backups 🙁

Ok, but does this account have permissions to do this?

Here are a couple things.

Add an output file to the steps of the job. Add some more print statements to the code. See what is captured in the output file.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/