FIX: Cannot restore database by using the full backup and differential backup files that contain defunct files

Platí pro: SQL Server 2012 EnterpriseSQL Server 2012 StandardSQL Server 2012 Developer

Symptoms


Consider the following scenario:
  • You have a database that has two transaction log files in Microsoft SQL Server 2012 or in Microsoft SQL Server 2014.
  • You perform a full backup in full recovery mode.
  • You remove one of the transaction log files, and change the recovery mode to simple recovery mode.
  • You perform a full backup and a differential backup.
  • You restore the database by using the latest full backup file and differential backup file.
In this scenario, you receive the following error message:
Msg 3127, Level 16, State 1, Line 3
The file '%s' of restored database '%s' 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.
And the log file that have been deleted appears again in sys.master_files with the status of DEFUNCT
Additionally, when you try to back up the database, you receive the following error message:
Msg 3636, Level 16, State 2, Line 1
An error occurred while processing 'BackupMetadata' metadata for database id 16 file id 3.
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.

Resolution


The issue was first fixed in the following cumulative update of SQL Server.

Cumulative Update 5 for SQL Server 2014

Cumulative Update 3 for SQL Server 2012 SP2

Status


Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.