Post reply

Redgate Backup Of "System" databases

  • December 16, 2008 at 9:52 am

    #126540

    There was a Red Gate backup job that was backing up the LOG for Model on my system. It has been running just fine for a very long time. I added the MASTER and MSDB databases to the job and now it's failing. The backups for the DATA files ofr MODEL, MASTER, and MSDB runs just fine but it doesn't seem to like backing up the LOG for MASTER or MSDB. Anyone know any tricks to do this?

    Here's the "step" from the SQL Server Agent that's trying to run but failing...

    DECLARE @exitcode int

    DECLARE @sqlerrorcode int

    exec master..sqlbackup N'-SQL "BACKUP LOG [master, model, msdb] TO DISK = ''\\bi-vault\sqlbackups\BI-DB1\Trans\ '' WITH COMPRESSION = 3, ERASEFILES = 3"', @exitcode OUTPUT, @sqlerrorcode OUTPUT

    IF (@exitcode <>0) OR (@sqlerrorcode <> 0)

    BEGIN

    RAISERROR ('SQL Backup job failed with exitcode: %d SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode)

    END

    Thanks for the help, folks.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 16, 2008 at 9:56 am

    #913271

    Jeff

    can you post the actual error from the Redgate Backup log file on the server. It defaults to

    c:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log\instancename

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • December 16, 2008 at 9:59 am

    #913275

    Jeff, you cannot do a log backup of master even if you have set it to full recovery mode (why do you need that for master?)

    I tried it via native SQL and got

    Msg 4212, Level 16, State 1, Line 1

    Cannot back up the log of the master database. Use BACKUP DATABASE instead.

    Msg 3013, Level 16, State 1, Line 1

    BACKUP LOG is terminating abnormally.

    msdb perhaps failing because it is after master backup?

    ---------------------------------------------------------------------

  • December 16, 2008 at 10:01 am

    #913279

    You bet... thanks, Perry...

    Executed as user: dbUser. SQL Backup job failed with exitcode: 880 SQL error code: 911 [SQLSTATE 42000] (Error 50000). The step failed.

    "dbUser" is a user with some high privs. Again, the job ran fine until I added Master and MSDB to the job.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 16, 2008 at 10:05 am

    #913288

    george sibbald (12/16/2008)

    Jeff, you cannot do a log backup of master even if you have set it to full recovery mode (why do you need that for master?)

    I tried it via native SQL and got

    Msg 4212, Level 16, State 1, Line 1

    Cannot back up the log of the master database. Use BACKUP DATABASE instead.

    Msg 3013, Level 16, State 1, Line 1

    BACKUP LOG is terminating abnormally.

    msdb perhaps failing because it is after master backup?

    No kidding? How the heck do you get a backup of the log for Master then? These folks (unwisely) have put stuff in the Master database and I'm thinking that the LOG should probably be backed up. Or, should I just not worry about it?

    Thanks George.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 16, 2008 at 10:15 am

    #913298

    I think MS never intended people to put stuff in there that would require a log backup. CAn't think of an explicit reason why log backups banned though other than to dissuade people from putting master in full mode and risking a full log error and therefore bringing EVERYTHING to a halt. (Heh, perhaps thats a good enough reason)

    So I think those people should move their stuff elsewhere!

    I am out of circulation for a few hours now so apologies if no more replies for a few hours.

    Does this exclude me from ever getting a mention in the 'are posts getting worse' thread............?

    ---------------------------------------------------------------------

  • December 16, 2008 at 10:17 am

    #913299

    Well, by golly... I learn something new every day. I removed Master from the run and it still failed. I removed MSDB from the run and it worked just fine. Apparently you can't backup the log for either. Guess I've got some reading on a new subject to do.

    George, Perry... thanks for the super quick response. I don't ask for help very often, but when I do, it's usually because I'm in deep Kimchie. Thanks guys.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 16, 2008 at 10:22 am

    #913302

    so quick question is................why is it possible to put the dbs in full mode? Is the space used in the logs growing? Or is checkpoint still in fact clearing them out?

    ---------------------------------------------------------------------

  • December 16, 2008 at 10:27 am

    #913311

    I'm afraid only Microsoft knows the answer to that question. I've set both databases back to simple and increased the frequency of the backups.

    Thanks again for your help.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 16, 2008 at 10:33 am

    #913318

    :blush: doh log backup missed that one

    our maint jobs dont perform log backups on system db's either. Incidentally Jeff that error is from the SQL log by the looks of it. If you check the actual log generated by Redgate in the folder i mentioned you generally get a crystal clear error message detailing the problem

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • December 16, 2008 at 10:50 am

    #913334

    Thanks, Perry... Heh... I've never used the RedGate software before... the learning curve isn't steep, but it's still a learning curve which was my reason for a bit of panic on my part. I'll do a "post mortem" on the logs to see what's up. Thanks again, Perry.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 16, 2008 at 3:53 pm

    #913492

    Info on how system dbs handle recovery modes here:

    http://technet.microsoft.com/en-us/library/ms365937(SQL.90).aspx

    so you should be able to have msdb in full mode so you have some other problem there, possibly no full backup since changed from simple to full mode?

    However, unless your msdb is really updated a lot, I'd keep it in simple mode. I've never had a need to restore msdb to a point in time.

    ---------------------------------------------------------------------

  • December 16, 2008 at 7:40 pm

    #913534

    Good link with good info. Thanks. I did do a full backup right after I changed the recovery mode to FULL... seems like the RedGate command line may be trying to protect me. 🙂

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 13 posts - 1 through 12 (of 12 total)

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