SQLTeam.com | Weblogs | Forums

Error on transaction log backup

One of my customers has a database in Full Recovery Mode. When making a backup of the transaction log, SQL Server gives an error telling me that it can't be done in Simple mode.

It's the only database with this problem. The other make backups of the logs without problems.

I've compared the properties of these databases with the one with the problem, but there are no differences apart from the collation that's slightly different.

Anyone an idea of how to solve this?

Sounds like it was changed from Simple to Full recovery without making a database backup since. The error message should state that though. In any case try making a full database backup first, then try log backups.

is it trying to backup "all databases"- and someone has added a new DB in SIMPLE mode? (i.e. might not be the Database that you think it is?)

Report of all database, showing Recovery Model

SELECT	[Recovery]	= CONVERT(varchar(20), DATABASEPROPERTYEX(name, 'Recovery'))
	, [Database]	= name
FROM	master..sysdatabases
--	[Recovery],

I am able to make a full backup of the db. At first a job that made a transaction log backup of all dbs, but I also tried to make a backup of the log of only that one db. I have checked and its really in full rec mode.

What's the exact, full, error message please?

Did you try the backup "manually" or using some "utility"? I'd be happier with a manual backup, in SQL, as that is most likely to give a clear message about the problem and will prevent anything else getting in the way and possibly giving a false error message.

BACKUP LOG MyDatabaseName 
	TO DISK = 'x:\MSSQL\BACKUP\MyDatabaseName_yymmdd_hhmm.BAK'
	STATS = 10
--	, DESCRIPTION = 'Log backup yymmdd_hhmm'
--	, NAME = 'FooBar'
--	, NO_TRUNCATE -- equivalent to COPY_ONLY, CONTINUE_AFTER_ERROR. Use if database is SUSPENDED
--	, NO_TRUNCATE, NORECOVERY -- for a best-effort log backup

If you get errors try commenting in some of the database corruption work-around commands.

Note that the log backup file created is required to maintain the CHAIN of backups, in the event of a restore, so the location where it is stored and its subsequent backup to Tape/whatever is important.

You might want to use COPY_ONLY or NO_TRUNCATE so that the normal Log Backup will include the log data in the normal way (once the problem is solved). However, that will not release log file space so if log file is growing, and disk space is an issue, then you probably do need to allow the log to be truncated.