Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Location
    Princeton, NJ
    Posts
    12

    Unanswered: Transaction log backup

    We have a couple of databases in same server, and I set up the job on SQL server agent for backup transactions, a few dbs log backup successful, but a few are always failed, no error message be given, only can check the report --- "Backup can not be performed on database 'Agrdev'. This sub task is ignored ....," I checked all db property and set up , couldn't find out what's going on. Did anyone can help me?

  2. #2
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311

    Re: Transaction log backup

    What's the Recovery Mode setting for those failed? Full or Simple?

  3. #3
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    THe similar type of scenario is explaned in this KBA and check whether the setup falls in this criteria.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  4. #4
    Join Date
    Mar 2004
    Location
    Princeton, NJ
    Posts
    12
    The problem is recovery model"simple".. so now it works. Thanks a lot.

  5. #5
    Join Date
    Mar 2004
    Location
    Princeton, NJ
    Posts
    12

    Sorry again.

    All my transaction log backup work good except Master db, I know due to Master db can't forward recovery any more, so doen't need Transaction log backup, but My boss required me to do the tran log backup for master db, so I changed the model from "Simple" to "Full", but it still couldn't get seccessful, the alert is "Backup can not be performed on database 'master'. This sub task is ignored.". Anyone can help me? Thanks a lot.

  6. #6
    Join Date
    Sep 2003
    Location
    KY
    Posts
    146

    You

    You can't backup log for master databases . Use Complete backup and you can do more frequently than once a day. However, there should not be any users table created in master database and so should have no user running transactions to backup

  7. #7
    Join Date
    Mar 2004
    Location
    Princeton, NJ
    Posts
    12
    Thanks aashu.

    Could you give me more detail for why can't take master db tran log backup?

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    The only usefull FOR YOU information that is stored in master is your user database specs (location of files and filegroups), server settings, and logins.

    Even if you did setup "all" other databases to Full recovery mode, you're really overdoing it. First, TEMPDB does not need to be backed up at all. Second, MODEL does not need to be backed up at all, unless you made modifications to this database so that all consecutive "CREATE DATABASE" statements use whatever is in MODEL. Third, PUBS and NORTHWIND don't need to have any backups. And lastly, if you set MSDB to Full, you'll be unpleasantly surprised to see that after the first time you reboot your server, transaction log dump for this database will start failing. This is because it is automatically set to Simple recovery mode at startup. So you should leave it that way.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Create a text file for the system databases. Call them master_log_backup_datetime.bak, etc. so nobody knows the difference. Have a batch file copy them every 15 minutes with the new time. Tell your boss you did what he wanted you to do and call it a day.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

Posting Permissions

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