Sql-server – Restoring differential backup creates DEFUNCT log file

backuprestoresql serversql-server-2012

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?

Best Answer

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

Related Question