HELP! Transaction Log Backup!

[tech_guru]

Hello!!

I am relatively new to SQL and am having a problem. I have taken over
a SQL 2000 db that did not have any backup plans scheduled for it. I
created a maintenance plan that backs up the database every night and
another plan that backs up the system databases once a week. I ran
both these jobs successfully, but when I created a third plan to
backup the transaction log it fails with the following message:
Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE
42000] (Error 22029). The step failed. I then executed the following
statement: sp_grantlogin 'NT Authority\System' which did not work.
I still do not understand why I can't backup the log? The database
backs up fine with no errors so it can't be a permission thing and I
created the job with a maintenance plan so it has to be in full
recovery mode. Right? How do I tell? Thank you for all your
responses!!

NS

[Greg D. Moore \(Strider\)]

"tech_guru" <ns******@cityofpasadena.net> wrote in message
news:3f**************************@posting.google.c om...

Hello!!

I am relatively new to SQL and am having a problem. I have taken over
a SQL 2000 db that did not have any backup plans scheduled for it. I
created a maintenance plan that backs up the database every night and
another plan that backs up the system databases once a week. I ran
both these jobs successfully, but when I created a third plan to
backup the transaction log it fails with the following message:
Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE
42000] (Error 22029). The step failed. I then executed the following
statement: sp_grantlogin 'NT Authority\System' which did not work.
I still do not understand why I can't backup the log? The database
backs up fine with no errors so it can't be a permission thing and I
created the job with a maintenance plan so it has to be in full
recovery mode. Right? How do I tell? Thank you for all your
responses!!
It probably is a permissions problem.

Where are you backing up to? If it's any place other than the same box, you
need to run SQL admin as a domain account, SYSTEM only has rights on the
local system.


NS

[Erland Sommarskog]

tech_guru (ns******@cityofpasadena.net) writes:

I am relatively new to SQL and am having a problem. I have taken over
a SQL 2000 db that did not have any backup plans scheduled for it. I
created a maintenance plan that backs up the database every night and
another plan that backs up the system databases once a week. I ran
both these jobs successfully, but when I created a third plan to
backup the transaction log it fails with the following message:
Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE
42000] (Error 22029). The step failed. I then executed the following
statement: sp_grantlogin 'NT Authority\System' which did not work.
I still do not understand why I can't backup the log? The database
backs up fine with no errors so it can't be a permission thing and I
created the job with a maintenance plan so it has to be in full
recovery mode. Right? How do I tell? Thank you for all your
responses!!

I would double-check that the database is not in simple recovery mode.

One way to find out what the problem is, would be to go to the job
step, find the Advanced tab, and specify an output file. Hopefully
you can get a better error message that way.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[chrispycrunch]

I have the same problem. I output the log and recevied the following
message:
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL
Server 'O4' as 'NT AUTHORITY\SYSTEM' (trusted)
Starting maintenance plan 'LouiseTest MaintenancePlan_2' on 1/28/2005
1:59:53 PM
Backup can not be performed on database 'LouiseTest'. This sub task is
ignored.

End of maintenance plan 'LouiseTest MaintenancePlan_2' on 1/28/2005
1:59:53 PM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
What setting must I change? Is it an access thing?

Erland Sommarskog wrote:

tech_guru (ns******@cityofpasadena.net) writes:

I am relatively new to SQL and am having a problem. I have taken over a SQL 2000 db that did not have any backup plans scheduled for it. I created a maintenance plan that backs up the database every night and another plan that backs up the system databases once a week. I ran
both these jobs successfully, but when I created a third plan to
backup the transaction log it fails with the following message:
Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed. I then executed the following statement: sp_grantlogin 'NT Authority\System' which did not work. I still do not understand why I can't backup the log? The database backs up fine with no errors so it can't be a permission thing and I created the job with a maintenance plan so it has to be in full
recovery mode. Right? How do I tell? Thank you for all your
responses!!
I would double-check that the database is not in simple recovery

mode.
One way to find out what the problem is, would be to go to the job
step, find the Advanced tab, and specify an output file. Hopefully
you can get a better error message that way.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

chrispycrunch (ch***********@gmail.com) writes:

I have the same problem. I output the log and recevied the following
message:
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL
Server 'O4' as 'NT AUTHORITY\SYSTEM' (trusted)
Starting maintenance plan 'LouiseTest MaintenancePlan_2' on 1/28/2005
1:59:53 PM
Backup can not be performed on database 'LouiseTest'. This sub task is
ignored.

End of maintenance plan 'LouiseTest MaintenancePlan_2' on 1/28/2005
1:59:53 PM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
What setting must I change? Is it an access thing?

And just like the guy who had the same problem, you need to find the
job under SQL Server Agent->Jobs, right-click it and Select View job
history and check the box "Show step details" in the upper-right corner.
Then you will find what the error actually is.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[chrispycrunch]

Thanks - that worked. Another problem is transaction backups:

Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE
42000] (Error 22029). The step failed.

The database is on a network drive.
I am creating the plan locally. Could that be an issue? The same thing
is done for index tuning and database backup (i figured out you should
save the files by the default location) and those tasks work.

[Erland Sommarskog]

chrispycrunch (ch***********@gmail.com) writes:

Thanks - that worked. Another problem is transaction backups:

Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE
42000] (Error 22029). The step failed.

The database is on a network drive.

And the actual error message is?

When performing any access to network drives from SQL Server, including
backups, the account under which SQL Server runs must have access to
the network drive. Typically this is not the case if the SQL Server runs
as Local System. The remedy is run SQL Server under a domain user with
the necessary access rights.

You change the account for SQL Server by right-clicking My Computer,
select Manage. Find Services, then the MS SQL Server service. Open it,
and log-on information is on the second tab.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[chrispycrunch]

Thanks, for the help and for tolerating my easy questions...I'm very
new to the 'admin' of NT, SQL, etc.
Also, I noticed the account was locked out, so see if that does the
trick.

[chrispycrunch]

Update: that didn't work - i'm frustrated! Is there a book or website
that can give further instructions on windows security?

[Erland Sommarskog]

chrispycrunch (ch***********@gmail.com) writes:

Update: that didn't work - i'm frustrated!
And the error message is?

To repeat from earlier in the thread:

And just like the guy who had the same problem, you need to find the
job under SQL Server Agent->Jobs, right-click it and Select View job
history and check the box "Show step details" in the upper-right corner.
Then you will find what the error actually is.
Is there a book or website that can give further instructions on windows
security?

Probably, but Windows is not my speciality. Anyway, without the error
message, books are not going to help you.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp