July 28, 2004 at 10:06 am
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
What's the error that you're getting in the agent?
July 28, 2004 at 10:32 am
It just dies on that step. I can't find anything to indicate why.
July 28, 2004 at 10:36 am
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
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?
July 28, 2004 at 11:08 am
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