"Backup can not be performed on database" message 2

[mkrausnick]

In my SQL 2000 database maintenance plan, I checkd "All User Databases". In the backup, only about half the databases get backed up. The rest fail with the message:

Backup can not be performed on database 'xxx'. This sub task is ignored.

I don't think it's because the databases were in use, because it backed up NORTHWIND but did not back up PUBS. Also, several test and inactive databases failed while several active production databases worked.

What can I do to get all my databases to back up?

Mike Krausnick
Dublin, California

 

[SQLBill]

What kind of backups were you performing? Full, Differential, Log?

As an aside, why are you backing up PUBS and Northwind? They are just default install databases and aren't really used for anything (except your own training).

Look in the Windows Event Viewer Logs to see if there are any error messages related to this issue. Also, look in the SQL Server Error Logs.

-SQLBill

Posting advice: FAQ481-4875

 

[mkrausnick]

Bill,
Thanks for responding. I'm running full backups.

Regarding Pubs and Northwind, I decided to just check 'All User Databases' in the maintenance plan one time in the past when I discovered that I had added a database to the system and forgot to update the maintenance plan to maintain it. Pubs/Northwind are tiny, so I just let them get backed up with all the others.

I'll check the logs when I get back in the office next week.


Mike Krausnick
Dublin, California

 

[SQLBill]

I don't think it's because the databases were in use

It won't be for that reason; SQL Server is designed to backup databases while they are in use.

1. Could the databases have been offline/detached?

2. Are all the database files on the same drive(s)?

-SQLBill

Posting advice: FAQ481-4875

 

[grimwyre]

From what I remember, check if any databases are not in "full" recovery mode. If they are not, some backups may fail. If there is a mixture of recovery modes, try defining two Mplans, one for those in "Full" recovery and one for those not in full recovery.

 

[SQLBill]

grimwyre,

It doesn't matter what recovery mode the databases are in. Mike says it's FULL Backups that are failing. No matter what recovery mode a db is set for, you can still do full backups. Now Differential and Log backups are another matter.

-SQLBill

Posting advice: FAQ481-4875

 

[mkrausnick]

Some more info:
After studying the maintenance plan history, I have realized that it is only the transaction logs that are not being backed up - the databases are all being backed up successfully. Of 9 logs, 6 are failing and 3 are succeeding. All 9 database files are backing up ok.

The logs are all on the same RAID volume as the databases, although some are in the same directory as their database files while others are in the LOGS directory. The only pattern I can see is that the 3 most active databases are succeeding and the other 6 lesser used are failing.

All the databases are attached as far as I know - I use most of them at least occasionally. Some are test databases and some are obsolete databases that we still use for reporting.

I was incorrect in my initial post when I said Northwind got backed up. The database did, but the log file didn't.

Also, all other db maintenance operations are successful - it's only the transaction log backup that's failing.

Any ideas you have would be greatly appreciated.

Mike Krausnick
Dublin, California

 

[SQLBill]

Mike,

That's different that what you originally said. That being the case, follow grimwyre's directions - check the recovery mode of each of the databases. If they are set for SIMPLE, then you can not do differential or log backups.

How to check? Easiest way is using Enterprise Manager. Expand down to the databases. Right click on a database and select Properties. Go to the Options tab. Look for Recovery Mode. Make sure it's set to FULL. Some system databases you can not set to Full - TempDb, MSDB, Model do not really need to be set to Full. Master really doesn't either. Just do Full backups of those, except TempDB, when you make changes to them.

-SQLBill

Posting advice: FAQ481-4875

 

[mkrausnick]

OK, that's the answer. The recovery mode is SIMPLE on the ones that fail and FULL on the ones that succeed. Stars to you both, thanks!

I actually don't necessarily need to back up the transaction logs - the DB utilization is very light. The reason I wanted to back up the tlogs is that one of them is 10GB and I wanted to shrink it. The only method for adding to this particular DB is through DTS, so the transaction log gets huge and never shrinks. How can I shrink it if I don't back it up first?

Mike Krausnick
Dublin, California

 

[SQLBill]

You have options.

Change the Recovery Mode to SIMPLE, this SHOULD create checkpoints and automatically truncate the log. (See notes at bottom).

Change the Recovery Mode to FULL (once you change a recovery mode, you need to do a full backup), do a log backup. Run the DBCC SHRINKFILE command on the log file (you can find the name of the data and log files by right clicking on the database in EM, select Properties and go to the Data tab and Log tab. In the top window on each of those tabs, you will find the file name.)

TRUNCATE the log. You do this by running, BACKUP LOG <dbname> WITH TRUNCATE_ONLY. When you do this, you need to do a FULL backup once it's done.

Note: One thing to remember......when you backup the log, use SIMPLE mode, issue checkpoints, etc. all that does is remove the information from the log file. IT DOES NOT REMOVE THE EMPTY SPACE. So your log is 10GB, you do whatever you need to do..your log space will still be 10GB until you SHRINK the log file or database. SHRINKING returns the designated amount of empty space back to the operating system.

I prefer to use DBCC SHRINKFILE instead of DBCC SHRINKDB, but that's personal preference. SHRINKFILE lets me shrink just the file I want, when I want.

-SQLBill

Posting advice: FAQ481-4875