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.
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.
SELECT [Recovery] = CONVERT(varchar(20), DATABASEPROPERTYEX(name, 'Recovery'))
, [Database] = name
FROM master..sysdatabases
ORDER BY
-- [Recovery],
[Database]
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.
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'
WITH
STATS = 10
-- , DESCRIPTION = 'Log backup yymmdd_hhmm'
-- , NAME = 'FooBar'
-- , COPY_ONLY
-- , NO_CHECKSUM
-- , CONTINUE_AFTER_ERROR
-- , NORECOVERY
-- , 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.