Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2005
    Posts
    165

    Unanswered: Transaction log backup fails on Master database

    Hi. I'm trying to backup the transaction logs as part of a maintenance plan. I'm backing up two production databases and the master database. Whenever the job runs, it always fails, and it only backs up the transaction log for the production databases. This is the error I get in Windows event viewer:

    SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan 'Master ApptPro TFit Full Backup'' (0x5E9E32B6A995CB469FF1F918521B1ADE) - Status: Failed - Invoked on: 2010-01-07 15:29:29 - Message: The job failed. The Job was invoked by User server\sql_server. The last step to run was step 1 (Step 1).

    I'm trying to find any additional information about why the job is failing; but, I may be looking in the wrong places. I'm using SQL Server 2000 on Windows 2003. What could be causing this to fail? Thanks!

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    In management studio right click the job and chose the "view history" option. Find the failure and expand the node and you will hopefully find some more detail!
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2005
    Posts
    165
    This is the error I get:

    Executed as user: server\sql_server. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.

    This is also reported in the job history log:

    Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server 'server' as 'server\sql_server' (trusted)
    Starting maintenance plan 'DB Maintenance Plan2' on 01/08/2010 8:54:26 AM
    Backup can not be performed on database 'master'. This sub task is ignored.
    Last edited by bla4free; 01-08-10 at 09:57.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    The master database should be in SIMPLE recovery mode. You might be able to change it, but are you really doing so many changes that you need point in time recovery for master?

  5. #5
    Join Date
    Jan 2005
    Posts
    165
    Well, from my understanding, most of the DB settings were stored in the master database. Would I be better off just backing up the database only and restoring if it need be?

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    That is what I would do. For my own environment, Master is backed up in full every night.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •