SQL server 2005 backup job - step failing
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\Bac
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\Bac
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\Bac
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
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\Bac
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\Bac
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\Bac
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
p_nuts
check if the user account expired. or the password changed.
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.
usually you need the space both on the server and on the destination.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Please post the relevant text regarding the error message from here:
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Log\B
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Log\B
ASKER
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)
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)
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.
brl8:
please run on your sql server
select @@version
and post result
please run on your sql server
select @@version
and post result
ASKER
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!
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!