Restoring differential backup creates DEFUNCT log file?

Question

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.

Any ideas?

Answer 1

Here are the steps to reproduce this, tested on SQL 2012 SP1 Developer Edition. This does not occur on SQL 2008. To summarize, a database created in SQL 2012 while the model database is in SIMPLE recovery, that has a full backup taken while an extra log file exists, cannot create usable differential backups if that extra log file is ever deleted.

ALTER DATABASE [model] SET RECOVERY SIMPLE
GO
CREATE DATABASE [DefunctTest]
GO
ALTER DATABASE [DefunctTest] ADD LOG FILE ( NAME = N'DefunctTest_log2', FILENAME = N'D:\DefunctTest_log2.ldf' , SIZE = 25600KB , FILEGROWTH = 10%)
GO
BACKUP DATABASE [DefunctTest] TO DISK = 'D:\DefunctTestPostLogFile.bak' WITH INIT
GO
ALTER DATABASE [DefunctTest]  REMOVE FILE [DefunctTest_log2]
GO

BACKUP DATABASE [DefunctTest] TO DISK = 'D:\DefunctTestFull.bak' WITH INIT
GO
BACKUP DATABASE [DefunctTest] TO DISK = 'D:\DefunctTestDiff.bak' WITH DIFFERENTIAL, INIT
GO
--Show that the backups only have the one log file.
RESTORE FILELISTONLY FROM DISK = 'D:\DefunctTestFull.bak'
RESTORE FILELISTONLY FROM DISK = 'D:\DefunctTestDiff.bak'
GO
RESTORE DATABASE [DefunctTest2] FROM DISK = 'D:\DefunctTestFull.bak' WITH 
MOVE 'DefunctTest' TO 'D:\DefunctTest2.mdf',
MOVE 'DefunctTest_log' TO 'D:\DefunctTest2_log.ldf', REPLACE, NORECOVERY
GO
--This restore will have the error.
RESTORE DATABASE [DefunctTest2] FROM DISK = 'D:\DefunctTestDiff.bak' WITH RECOVERY
GO

USE [DefunctTest2]
SELECT * FROM sys.database_files
GO

I submitted a Connect item for this bug here. The only way I've been able to remove this defunct file is to detach the database, and re-attach with ATTACH_REBUILD_LOG.

UPDATE: The bug that creates this scenario in my repro script seems to have been fixed by this KB: https://support.microsoft.com/en-us/kb/2830400. From the comments it appears an additional fix is available for SQL2012/2014, the scenarios seem very similar: https://support.microsoft.com/en-us/kb/3009576