We help IT Professionals succeed at work.

SQL server 2005 backup job - step failing

brl8
brl8 asked
on
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

CERTIFIED EXPERT

Commented:
check if the user account expired. or the password changed.
CERTIFIED EXPERT

Commented:
also check if you are backing up the logs and if there's enough space on the sql drive to create tmp files for the back up.

usually you need the space both on the server and on the destination.

SQL SERVER EXPERT
CERTIFIED EXPERT
Commented:
what is you sql server 2005 sp ? fresh is better..
check if the login DOMAIN\dbuser1 is valid and has sa rights ..
if it is not valid check owner of this maint job (could be own by user that no longer with your company)

check also:
http://support.microsoft.com/kb/288577

http://social.msdn.microsoft.com/Forums/en-US/sqlgetstarted/thread/57fa3d65-459a-4dc7-9be1-1afe429c542f/
CERTIFIED EXPERT
Top Expert 2012

Commented:
Please post the relevant text regarding the error message from here:
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Log\Backup.txt

Author

Commented:
Thanks all for the responses.

EugeneZ:  I've recreated the job completely, per the suggestion in the second link, but I've not been able to test yet.  I'll let you know how it goes.

acperkins: There are only two files from each backup attempt, one for each of the databases that successfully backs up (i.e. backup2011_11_02_1030.txt and backup2011_11_02_1136.txt).  They both contain success messages.  There is no file for the step that fails.
CERTIFIED EXPERT
Top Expert 2012

Commented:
Than I am afraid I have no idea.  I don't use, have ever used or plan to use Maintenance Plans, so hopefully someone will step up to the plate.
EugeneZ ZhitomirskySQL SERVER EXPERT
CERTIFIED EXPERT

Commented:
brl8:
please run on your sql server
 select @@version
and post result

Author

Commented:
Thanks all!  

I had to recreate the job completely, and I could not copy and paste the tsql, I had to type it out.  That worked, job is running, thanks!