Refusal to perform a log backup

Question

Refusal to perform a log backup

Elliott Berkihiser

SSCarpal Tunnel

Points: 4068
More actions
March 17, 2010 at 9:13 am

#143529

Hello,
I have not seen this problem before:
One of the vendor databases I am responsible to maintain is a "Read_Only" archive database ("abc_archive"). It was recently switched from Simple to Full mode. The job which performs full backups on all the databases has no problem, but the attempt to backup the log receives the error " BACKUP LOG cannot be performed because there is no current database backup".
I went to QA and performed a backup database command, followed immediately by a backup log command. Same result. I am able to execute a backup log with truncate_only command, but this doesn't fix the problem.
Is there something about Read_Only or the change from Simple to Full that is blocking the ability to backup the log?
Thx,
Elliott

Viewing 15 posts - 1 through 15 (of 15 total)

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

Richard M. SSCertifiable Points: 7401 More actions · Answer 1

Just curious. If it is a Read_Only DB, why changing it from Simple to Full recovery? You would need Full Recovery if you need to do a point in time recovery, but in this case no transactions are generated being a read only database.... so what's the point changing the recovery model?

_______________________________________________________________________
For better assistance in answering your questions, click here[/url]

Elliott Berkihiser SSCarpal Tunnel Points: 4068 More actions · Answer 2

Excellent question; I wish the answer was so elegant:

The Systems group at my company has recently installed a new Veritas disk-to-disk backup system. The SQL agent for this system throws errors if it does not find log backups as well as database backups. For this reason, I am attempting to put this vendor database in a parallel configuration with all the other databases. Hence, "Full" mode and scheduled database and log backups.

Elliott

Nikita-395727 SSC Eights! Points: 898 More actions · Answer 3

Since this is read_only database, it will not have any transactions to backup. Just keep it in the simple mode and take full backups may be once in a week or so or according to your backup-restore strategy.

Elliott Berkihiser SSCarpal Tunnel Points: 4068 More actions · Answer 4

The database does have a log file. I should be able to back it up. What do I have to do to accomplish that?

(I understand the advice to forget it, but that will just push the problem to another area).

Thx,

Elliott

Lowell SSC Guru Points: 323504 More actions · Answer 5

sounds more like you are trying to make your databases bend to the will of the backup program.

Veritas certainly has the ability to backup a database that is in SIMPLE mode... Maybe, because the program is new to your IT Team, noone has discovered the slight differences in settings needed for a db in FULL vs SIMPLE backup?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Elliott Berkihiser SSCarpal Tunnel Points: 4068 More actions · Answer 6

Lowell: Yes, that's exactly what I was trying to do. According to Systems, Veritas BE will need to have a seperate backup job for the "Simple" database, while the remainder (the "Fulls") are handled by the original job. [Apparently, Veritas cannot mix the two within one job]

This is not a problem, it's more a matter of me wanting to resolve the issue myself.

Also, I've been managing backups for about 5 years and I'm perplexed by my failure to convert the database to a full mode and then perform log backups following database backups. I sure would like to learn why?

Elliott

Tim Weintz SSCommitted Points: 1622 More actions · Answer 7

This piqued my curiosity. I was able to reproduce and have a solution to offer you: Take it out of read only mode, then do the full back up. Then run a tran backup. Then put back into read only mode.

After following the above procedure, I was able to run tran log and full backups in read only mode without any problems.

Good Luck!

Elliott Berkihiser SSCarpal Tunnel Points: 4068 More actions · Answer 8

Thanks for the method. I'm still surprised that being in a read_only state would preclude backing up the log.

Elliott

Richard M. SSCertifiable Points: 7401 More actions · Answer 9

I guess that being in read only mode, some things just don't get logged/updated (not even in the master db) hence the "refusal" to do a log backup no matter what recovery model was set to.

_______________________________________________________________________
For better assistance in answering your questions, click here[/url]

GTR SSCrazy Eights Points: 8007 More actions · Answer 10

" BACKUP LOG cannot be performed because there is no current database backup".

This means you don't have base backup file to support tran log backup, You need full back up first before you take any Diff or T-log backups.

Is there something about Read_Only or the change from Simple to Full that is blocking the ability to backup the log?

No.

EnjoY!

Elliott Berkihiser SSCarpal Tunnel Points: 4068 More actions · Answer 11

GTR: Thanks for responding. The first thing I did was to take a full database backup and then attempt to backup the lob (see first post). Of course, this was while the database was in the vendor's "read_only" mode.

Tim: I followed your method at the end of the day yesterday: Took the db out of read_only; took a full and log backup; returned the database to read_only. Today I used Query Analyzer and was able to take a full backup followed by a log back while the database is in "Read_only" mode. Succcess!

Richard: It's still a bit of a mystery to me, but I think the answer does lie in the explanation.

Thanks to everyone.

Elliott

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

It is due to the read-only mode. The info on whether there has been a backup since the last break in the log chain is stored in the database header page. (view with the undocumented DBCC DBINFO)

If the DB is read-only, then the database header page cannot be updated to record the full backup LSN. Hence the log backups fail because, according the the database header page, there is no full backup to start the log chain.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

GTR SSCrazy Eights Points: 8007 More actions · Answer 13

I am glad that worked. I have no idea changing from read_only to other mode and taking backup full backup would fix this, unless backup chain was broken. That error you will only get if you don't have right base backup for the database before taking Diff or Tran log backup.

EnjoY!

Richard M. SSCertifiable Points: 7401 More actions · Answer 14

Now, with Gail's explanation and what the OP did (take out of read only, Full backup, back to read only), the LSN for the Full backup registered would the one for that specific full backup, so any future Full backups will not modify it and the log backup will still "see" that value, hence creating a valid log backup. Since no transactions are added, you could only do the log backups without any full backup either....

_______________________________________________________________________
For better assistance in answering your questions, click here[/url]