Backup can not be performed on database

Question

Post reply

Backup can not be performed on database

Helen-84411

Hall of Fame

Points: 3320
More actions
December 2, 2005 at 7:40 am

#152645

Hi there,
I have a database on which I perform transactional log backup using maint plan. The TLog backup fails. The error that I can see in the log file is as follows.
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server 'CCE_XXXX' as 'XXX\SQLuser' (trusted)
Starting maintenance plan 'CCE_XXXX' on 12/2/2005 8:00:10 AM
Backup can not be performed on database 'XXXX - SU'. This sub task is ignored.
Deleting old text reports... 0 file(s) deleted.
End of maintenance plan 'CCE_XXXX' on 12/2/2005 8:00:10 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
Can anyone tell me why?

Helen
--------------------------------
Are you a born again. He is Jehova Jirah unto me

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

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

SQLZ SSChampion Points: 12872 More actions · Answer 1

Hi Helen,

Check the error log to see if there's a more detailed answer.

Couple of things that first spring to mind and are worth checking. Check the database is not in simple-recovery mode. Have you taken a full backup of the database?

Karl
source control for SQL Server

Helen-84411 Hall of Fame Points: 3320 More actions · Answer 2

Hi Karl,

The error log does not contain any detail about the error. Infact I checked this to find out why?. Let me check if it is in Simple REcovery model. Full db backup is running perfectly.

Yes the db is in Simple Recovery model

Thanks for your help

Helen
--------------------------------
Are you a born again. He is Jehova Jirah unto me

SQLBill SSC Guru Points: 51440 More actions · Answer 3

You can not do transaction log backups if:

The database is Master

The database is MSDB

The database is in SIMPLE recovery mode.

-SQLBill

asim kaghzi Grasshopper Points: 18 More actions · Answer 4

I am a new to sqlserver therefore forgive me for asking this:

why transaction log does not get backed up for master, since master is still a database and need to be fully recoverable transactionally speaking?

EdVassie SSC Guru Points: 60478 More actions · Answer 5

For a reason best known to themselves, Microsoft have decided that transaction log bacukps of master are not possible.

It is possible to do transaction log backups of msdb, provided that you have switched it to Full Recovery. In SQL Server 2000, whenever you start SQL Agent msdb is set to simple recovery but in SQL Serve 2005 this is not done.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 6

Master, MSDB, and TempDB are defaulted to "Simple" recovery. Master can be changed to Full Recovery and then I think you can do a transaction log backup. Not 100% sure, though.

--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)

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 7

p.s. Don't ever change the TempDB recovery mode... it's not necessary and will drop some performance.

--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)

JeremyE SSCoach Points: 16213 More actions · Answer 8

Can't backup master tran log even though it will allow you to change the recovery model.

asim kaghzi Grasshopper Points: 18 More actions · Answer 9

Thank you guys for replying.

I have changed the recovery option to full for master which went well, however when i try to go to backup screen, transaction log radio button is greyed out.

regards,

Judith-458214 SSC Enthusiast Points: 163 More actions · Answer 10

There is no way for you to restore the transaction logs for Master - really no way at all - which is why Microsoft greyed out the radio button for transaction log backups.

Your master dB should be small enough to do a complete backup.

andrew trice Grasshopper Points: 10 More actions · Answer 11

My jobs for backing up the OnePoint database (MOM) were failing with this message. I changed the Model to Full and now the backups are working. It looks like this affects all MS databases in sql 2000.

Vidhya Sagar SSCrazy Eights Points: 8216 More actions · Answer 12

Im my view changing the recovery model and taking tran log backup is merely unnecessary one.

As you know we are not going to change \ do frequent transactions in master db, hence the the tlog wont grow much compared to other user db's. Hence changing the recovery model to full and taking tlog is merely unnecessary.

Its better to keep master db in simple recovery and taking a full backup daily is the best one.

Regards..Vidhya Sagar
SQL-Articles

EdVassie SSC Guru Points: 60478 More actions · Answer 13

Never put the master database into Full Recovery mode.

Think what this is doing... all changes to master are now saved in the transaction log. But a transaction log of master is not possible. This means the log for master will keep growing, and growing, and growing. It will keep on growing until you put master back into simple mode.

If you think you need master in Full Recover to satisfy audit requirements, then you need to re-think the solution so that master is kept in Simple mode. A good way to tackle audit of master is to set up a Profiler trace to show all activity in master, and use that as your audit.

Note that many other DBMSs do allow their equivalent of Full Recovery mode for their equivalent of master, but SQL Server does not allow this to work. You may need to educate your audit people about these differences if they want you to put master into Full Recovery.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Brandie Tarvin SSC Guru Points: 173080 More actions · Answer 14

I was under the impression, generally speaking, that the master db doesn't change that much or very often at all. That being the case, why would you need transaction log backups? In fact, why would you need a transaction log at all?

Because this db is the most static of all dbs, I just don't see the need to change it from SIMPLE to FULL nor do I see the need for the translog backup. Especially since the transaction logs of the system dbs seem to checkpoint and cleanup themselves (unless tempdb runs out of hard drive space, that is). As long as you have good working copies of regular "complete database" backups, you should be good to go.

Anyway, that's just my two cents. @=)

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.