Post reply

Help! Transaction Log backup failure

  • 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

  • September 9, 2004 at 3:31 pm

    #522399

    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?

  • September 9, 2004 at 3:44 pm

    #522404

    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

  • September 9, 2004 at 9:34 pm

    #522433

    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!

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

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