backup job runs from SSMS but not from job

  • Tara-1044200

    SSCoach

    Points: 15790

    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?

  • Michael L John

    One Orange Chip

    Points: 25713

    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: 243531

    Do you receive any error messages?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Tara-1044200

    SSCoach

    Points: 15790

    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: 15790

    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: 994238

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Tara-1044200

    SSCoach

    Points: 15790

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

  • Michael L John

    One Orange Chip

    Points: 25713

    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/

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

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