Post reply

Works in QA but not as a scheduled job???

  • July 28, 2004 at 10:06 am

    #180977

    This runs great in QA but will not run as a scheduled job in SQL Server Agent.

    The problem is in the differential portion of the backups and I think in relation to using the @dbname variable.  Any help would be greatly appreciated.

    Jeff

     

    declare @IDENT INT, @sql varchar(1000), @sql1 varchar(1000),@DBNAME VARCHAR(200)

    select @IDENT=min(DBID) from SYSDATABASES WHERE [DBID] > 0 AND NAME NOT IN ('PUBS', 'NORTHWIND', 'TEMPDB')

    while @IDENT is not null

    begin

     SELECT @DBNAME = NAME FROM SYSDATABASES WHERE DBID = @IDENT

     

     SELECT @SQL = 'BACKUP DATABASE '+@DBNAME+' TO DISK = ''D:\SQL Backup\'+@DBNAME+'.BAK''WITH INIT'

     SELECT @SQL1 = 'BACKUP DATABASE '+@DBNAME+' TO DISK = ''D:\SQL Backup\Diffs\'+@DBNAME+'_Diff.BAK'' WITH DIFFERENTIAL'

    PRINT @SQL

     EXEC (@SQL)

     EXEC (@SQL1)

     select @IDENT=min(DBID) from SYSDATABASES WHERE [DBID] > 0 and DBID>@IDENT AND NAME NOT IN ('PUBS', 'NORTHWIND', 'TEMPDB')

    end

     

  • July 28, 2004 at 10:18 am

    #516605

    What's the error that you're getting in the agent?

     


    Shamless self promotion - read my blog http://sirsql.net

  • July 28, 2004 at 10:32 am

    #516612

    It just dies on that step. I can't find anything to indicate why.

  • July 28, 2004 at 10:36 am

    #516614

    The error message that I get via Messenger Service is:

    Status: Failed

    MESSAGES: The job failed. The Job was invoked by user blah blah. The last step to run was step1 (script).

  • July 28, 2004 at 10:50 am

    #516621

    Make sure that the job owner is SA, then try again.

    If you right click on the job and View Job History, check the Show Step Details box you get no details on the failure?


    Shamless self promotion - read my blog http://sirsql.net

  • July 28, 2004 at 11:08 am

    #516629

    Crap. Thanks I got it. Thanks for the direction on the job log details.

    I'm trying to do a differential backup of dbs listed in sysdatabases right after I do a full. Master will not let you do a differential on it at all, on fulls, according to the job log.

    Running this is QA just skips the differential step when @dbname = master and keeps on running until completion.

    Thanks alot for the help!

Viewing 6 posts - 1 through 5 (of 5 total)

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