Here is my problem. I'm trying to move a database to a new server via a full restore, then cutover with a quick differential backup/restore. I can do a full restore without a problem, but when restoring the differential backup, I get the following warning:
Msg 3127, Level 16, State 1, Line 1 The file 'Database_Log2' of restored database 'DatabaseName' is being left in the defunct state because the database is using the simple recovery model and the file is marked for read-write access. Therefore, only read-only files can be recovered by piecemeal restore.
The database restores and is considered online, but any backup operation fails due to this DEFUNCT file with the following error:
Msg 3636, Level 16, State 2, Line 1 An error occurred while processing 'BackupMetadata' metadata for database id 10 file id 6. Msg 3046, Level 16, State 2, Line 1 Inconsistent metadata has been encountered. The only possible backup operation is a tail-log backup using the WITH CONTINUE_AFTER_ERROR or NO_TRUNCATE option. Msg 3013, Level 16, State 1, Line 1 BACKUP DATABASE is terminating abnormally.
If I do a RESTORE FILELISTONLY on the full and differential both give me the same output, which matches what I see from sys.database_files on the source database. Server is SQL2012 SP1, on Developer edition.
I can do a full backup, and immediately afterward do a differential, and restore these files to a different database on the same server and see the exact same problem, so there is something with how the differential is created that is causing this. If I restore the full backup WITH RECOVERY there is no problem. I do not know if this file used to exist on this database, but it is entirely possible this file used to exist and was deleted a long time ago. If I query sys.database_files on the restored database, the DEFUNCT file has a value for drop_lsn, which seems to confirm this. Currently in the source database there is only one filegroup (PRIMARY), 4 data files and one log file.