We help IT Professionals succeed at work.
Get Started

SQL server 2005 backup job - step failing

brl8
brl8 asked
on
497 Views
Last Modified: 2012-05-12
Hi All,

I am somewhat new to SQL Server, and I am having the following issue that I can't figure out.

I have a job that backs up three databases.  I set up the job a few months ago. It was working just fine until about a month into my maternity leave when the third step started failing with the error message: Executed as user: DOMAIN\dbuser1. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029).  The step failed.

I tried re-creating the third step using the other two steps as a template but the new one still fails.

Here are the details of each of the steps:
STEP 1
- backs up a large 50 GB database.
- Type: Transact-SQL script (T-SQL)
- Run as is blank
- Database: master
- Command: EXEC xp_sqlmaint ' -S sql002.rmi.local -D "SPProductionContent" -BkUpDB "\\server.domain.local\Backups\Weekly" -BkUpMedia DISK  -DelBkUps 30days -Rpt "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Log\Backup.txt" -DelTxtRpt 4weeks -VrfyBackup'

STEP 2
- backs up a much smaller database
- Type: Transact-SQL script (T-SQL)
- Run as is blank
- Database: master
- Command: EXEC xp_sqlmaint ' -S sqlserver.domain.local -D "Database2" -BkUpDB "\\server.domain.local\Backups\Weekly" -BkUpMedia DISK  -DelBkUps 30days -Rpt "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Log\Backup.txt" -DelTxtRpt 4weeks -VrfyBackup'


STEP 3
- backs up a small database
- Type: Transact-SQL script (T-SQL)
- Run as is blank
- Database: master
- Command: EXEC xp_sqlmaint ' -S sqlserver.domain.local -D "Database3" -BkUpDB "\\server.domain.local\Backups\Weekly" -BkUpMedia DISK  -DelBkUps 30days -Rpt "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Log\Backup.txt" -DelTxtRpt 4weeks -VrfyBackup'

I can back up this database manually with no problems.  There is plenty of space on the destination server.

I am not sure how to troubleshoot this further.  Any suggestions would be appreciated.  

BRL8
Comment
Watch Question
SQL SERVER EXPERT
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 8 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE