December 16, 2008 at 9:52 am
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
Change is inevitable... Change for the better is not.
December 16, 2008 at 9:56 am
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
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
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
Change is inevitable... Change for the better is not.
December 16, 2008 at 10:05 am
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
Change is inevitable... Change for the better is not.
December 16, 2008 at 10:15 am
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
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
Change is inevitable... Change for the better is not.
December 16, 2008 at 10:22 am
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
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
Change is inevitable... Change for the better is not.
December 16, 2008 at 10:33 am
: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
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
Change is inevitable... Change for the better is not.
December 16, 2008 at 3:53 pm
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
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
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply