11

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?

  • Could you please show us the statements you're using to do the backups and restores? – Jon Seigel Apr 16 '14 at 16:14
  • Nothing out-of-the-ordinary. RESTORE DATABASE DatabaseName FROM DISK = 'D:\Full.bak' WITH REPLACE, NORECOVERY Then RESTORE DATABASE DatabaseName FROM DISK = 'D:\Diff.bak' WITH RECOVERY – FilamentUnities Apr 16 '14 at 16:17
5

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

  • I would include your script in the connect comments to help people reproduce. – Kenneth Fisher Apr 25 '14 at 16:16
  • 1
    I do not get any errors running your script on SQL Server 2012 Enterprise Edition, 11.0.3412 (CU9 for SP1) – Shawn Melton Apr 25 '14 at 18:12
  • The repro script is in the Connect item, if you click the Details button. – FilamentUnities Apr 25 '14 at 18:36
  • 1
    Shawn, looking through the fixes in the CU's, I think this one probably fixed this behavior: support.microsoft.com/kb/2830400 – FilamentUnities Apr 25 '14 at 18:49
  • 2
    I had a battle with this last week and this thread help me sort it out so thank you. It looks like the fix is in SQL 2012 SP2 CU3: support.microsoft.com/en-us/kb/3009576 – Richard Dec 7 '15 at 1:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.