Backups Failing as Scheduled jobs
-
I've been asked to help someone figure out why her backups are failing. We can run them on the fly, from QA but not thru scheduler (SQL Agent). Both SQL and Agent services are startup with a domain user account ...that account has domain admin privs. There is no definitive answer in the logs or event viewer as to why the job fails. She can backup Northwind but not her test or prod db's. I haven't a clue and not sure how she set things up other than using the domain account to set up/install SQL. I know I tried to stop and start the services under the local system account but they failed. It's driving me crazy going in after the fact ..but wanted to find out why or what was the reason.
Thanks in advance! -
Can you post the error you recieve?
-
I would also make sure that the job owner has access to the databases and has permission to backup the databases.
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Make sure that the domain account that you are using for the SQL Server Agent is a member of the local administrator group on the server.
-
- Can you post the jobs script (full !)
- and some job history overview ?
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Okay ..user is Domain Admin ...is also local admin on server. Has every priv. on db's. Can run the backup from EM, can run a backup in QA ..just not the scheduled ones via agent.
Here's error messages (and no errors in agent)
SQL Server Log:
BACKUP failed to complete the command BACKUP DATABASE [PCA_PROD] TO DISK = N'D:\Backups\pcaprod.bak' WITH INIT , NOUNLOAD , NAME = N'PCA_PROD backup', NOSKIP , STATS = 10, NOFORMAT
Event Viewer warning: SQLAgent$PCA (category Job Engine Event ID 208
SQL Server Scheduled Job 'PCA_PROD backup' (0x246E3657FC86CD429F5E12C4F37E15DB) - Status: Failed - Invoked on: 2004-07-20 07:11:27 - Message: The job failed. The Job was invoked by User NTGROUP\mts_user. The last step to run was step 1 (Step 1).
Event Viewer Error: MSSQL$PCA (category (6)) Event ID 17055
3041 :
BACKUP failed to complete the command BACKUP DATABASE [PCA_PROD] TO DISK = N'D:\Backups\pcaprod.bak' WITH INIT , NOUNLOAD , NAME = N'PCA_PROD backup', NOSKIP , STATS = 10, NOFORMAT
Script for backup job:
BACKUP DATABASE [PCA_PROD] TO DISK = N'D:\Backups\pcaprod.bak' WITH NOINIT , NOUNLOAD , NAME = N'PCA_PROD DB BKP', NOSKIP , STATS = 10, NOFORMAT ....(I can run this fine in QA)
-
- except for the NOINIT / INIT thing it seams OK.
- What says the next line on the sqlserver log ?
- Who is the job-owner ? (script the job)
- can you cut and paste the job's step history where the bu failed ?
- Is D the local drive or is it mapped via your windows login ?
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
That's it in the log ...(last entry)
job owner is mts_user (which is domain admin account, and also what the services start up as logon account)
job history: The job failed. The Job was invoked by User NTGROUP\mts_user. The last step to run was step 1 (Step 1).
D is a local drive
SQL error log (same as before)
BACKUP failed to complete the command BACKUP DATABASE [PCA_PROD] TO DISK = N'D:\Backups\pcaprod.bak' WITH INIT , NOUNLOAD , NAME = N'PCA_PROD backup', NOSKIP , STATS = 10, NOFORMAT
-
try setting the jobowner to sa. SQLserver has some problems with nt-users as jobowner.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
tried that already ...that has same results.
-
ok, last shot ....
Can you post the fully scripted job and jobsteps ?
(EM\Management\SQLAgent\jobs and rightclick on your job \all tasks\script it)
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Sorry for the delay ...here's the script:
-- Script generated on 7/21/2004 7:15 AM
-- By: NTGROUP\mts_user
-- Server: PCASRVR\PCA
BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Database Maintenance') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'Database Maintenance'
-- Delete the job with the same name (if it exists)
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'PCA_PROD backup')
IF (@JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''PCA_PROD backup'' since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @job_name = N'PCA_PROD backup'
SELECT @JobID = NULL
END
BEGIN
-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'PCA_PROD backup', @owner_login_name = N'NTGROUP\MTS_USER', @description = N'No description available.', @category_name = N'Database Maintenance', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Step 1', @command = N'BACKUP DATABASE [PCA_PROD] TO DISK = N''D:\Backups\pcaprod.bak'' WITH INIT , NOUNLOAD , NAME = N''PCA_PROD backup'', NOSKIP , STATS = 10, NOFORMAT ', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 0, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'Schedule 1', @enabled = 1, @freq_type = 4, @active_start_date = 20040720, @active_start_time = 180000, @freq_interval = 1, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
-
Implemented it on my testserver and it went fine.
I always get errorinfo in jobhistory and jobstephistory when applicable .
(authorities, file in use, disk full,..)
I hope you find more errorinfo in jobhistory, jobstephistory, sqlserver-log, win-eventlog.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Check the SQL Agent service account, make sure it is running with the same account as MSSQLSERVER. And if you want to copy the backup to another server, you need an id running the services that has permissions to the other server.
Check to make sure the server version is not MSDE. The 'skinny' version of sql server is known to have issues at our shop when scheduling jobs.
fyi; I have been unable to find a resolution to getting scheduled maint plan jobs working on MSDE servers.
-
try two more things:
1. make sure the destination D: drive has enough free space for the backup.
2. Use "with format" in the backup statement.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply