Printable View
I'm trying to backup a database using the DB Maintenance plan and it keeps failing. I want backup the transaction logs every hour and the DB every night then delete the preivous nights backups including the transaction logs. It keeps coming up with the following error. "Executed as user: NT AUTHORITY\SYSTEM. sqlmain.exe failed. [SQLSTATE 42000] (Error 22029). The step failed."
It has worked in the past but hasn't worked for the last week.
Can someone please help me.
Thanks
kris1
Database maintenance fails when
1. somebody deletes a database that has been entered in data maintenance
2. some change the database option to "Simple mode" or truncate log on checkpoint.
Did you check the detailed error message?. Do you have enough disk space for backup?
Are you backing up to a local drive or a network drive?
All I have set up is DB maintenance plan that has Optimization set evey Sunday at 1:00AM, Integrity checks every Sunday at 12:00AM, a complete backup every Sunday at 2:00AM to a network drive and a transaction log every day at 12:00AM to a network drive. I also have backups that have no optimization or integrity checks that also fail. If I right click on the database and just choose backup database it has no problem backing up to the same network drive. I also have another server that is backing up to the local drive and it won't delete the day before backups even though it's meant to. It does the backup and then fails on the deletion. I'm trying all different methods and not having much luck.
Thanks
Kris1
What kind of account you use to run sql server agent service? Make sure that it is a domain user account with sufficient rights on the network share.
When you run backup by right clicking the database in enterprise manager, you are using the security settings of the account that is used to register the server in your enterprise manager.