April 17, 2009 at 1:34 pm
Hi all-
I've created a backup job in SQL Server 2005 that runs the following sql:
DECLARE @sqlcmd varchar(500)
SET @sqlcmd =
'DECLARE @db varchar(64)
SET @db = ''?''
IF (@db <> ''tempdb'')
EXEC usp_fullBackup @db
'
EXEC sp_MSforeachdb @command1=@sqlcmd
Sometimes it works, as scheduled, but sometimes it only backs up the model and master databases and then stops. The job history indicates the job was successful, but in my eyes it wasn't, because I'm missing backups. This job is executed nightly. And when I see that the backups are missing in the morning, I re-run the job manually (not on a schedule) and it backs up ALL the databases. I have no idea why this job is acting so strange. It is the only job running on the server.
I have even tried executing the T-SQL in a Query Window multiple times to see if I am missing something (ie: syntax error), but it works like a charm every time.
Any idea what could be going on?
Thanks for your help in advance!
April 17, 2009 at 1:47 pm
How about using Maintenance Plans for doing backups?
April 17, 2009 at 2:26 pm
check your sql server error logs and windows event logs. if a backup fails it will be logged to these places.
if nothing shows there then I would come in during the scheduled time, setup and capture a profiler trace making sure to add sp:stmtcompleted event. In the results of that trace which should show something like "exec usp_fullbackup 'somedbnames'". You should see a call for each database on that server. If not, I would suspect sp_msforeachdb then... to my knowledge, as far as I know, it is an undocumented procedure.
hope this helps.
April 18, 2009 at 12:40 pm
I would advise you to use simple maintenance plan or some standard scripts to backup your system databases rather than using the undocumented commands for critical business activities and i suppose backing up all user and system databases is critical as they are a part of your DR plan.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply