- SQL
- Microsoft SQL Server
- Query Syntax
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
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.
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/
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/
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
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)
Premium Content
You need an Expert Office subscription to comment.Start Free Trial