Help! Transaction Log backup failure

Question

Post reply

Help! Transaction Log backup failure

Noelle Stimely

SSC Veteran

Points: 225
More actions
September 9, 2004 at 2:29 pm

#109134

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Clark Cruz-131251 SSC Journeyman Points: 82 More actions · Answer 1

Hi Noelle,

You mentioned that you wanted to run TLog backup for the system databases.

msdb and model are OK. master does not permit a TLog backup.

Or, do you have another issue?

hoo-t SSChampion Points: 12713 More actions · Answer 2

You can check the recovery model of your databases by right clicking on the database name, and select "Properties". From there, go to the "Options" tab. Recovery model is shown in the drop down box in the center of the window. In order to perform transaction log backups, the recovery model does need to be set to "full". If you change the recovery model from simple, take a full backup of the database before attempting to take transaction log backups.

Steve

kim03 SSC Veteran Points: 243 More actions · Answer 3

Check directory path of the target folder that specified to store db backup or tlog backup. The directory path may contain "-S" string. Do not use strings that contain "-S". The Database Maintenance Plan Wizard creates a job that includes a step to run the xp_sqlmaint extended stored procedure. The xp_sqlmaint extended stored procedure calls the sqlmaint utility with a string that contains various switches to Sqlmaint.exe. One of those switches is the -S parameter, which is used for the server name. The -S parameter is misinterpreted; therefore, the error occurs. As a consequence, you may experience this problem if you use the xp_sqlmaint extended stored procedure in conjunction with named instances of SQL Server 2000.

good luck!