Sql-server – sql server – Restoring Windows Database Backup File on a Linux Server Instance

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

So what it sounds like is happening is:

1. You create a database called foo with a logical data file name of foo.
2. You back up that database.
3. You restore that database as a different database (say, bar).
4. You backup the bar database.
5. You try to restore the bar backup as yet another database name, and your code assumes that the logical file name for the data file for the database bar should be bar. But it's not, it's still foo!

You could also interject some random ALTER DATABASE x MODIFY FILE (name = 'y', newname = 'z'); in there to make it even more confusing for you (never mind your code). Particularly if - like it turns out is true in your case - you are storing multiple backups in a single backup file. This can be bad regardless of whether it is the same database but the logical file names change over time, or different databases being backed up to the same file. You need to determine which file number you're actually looking for, and specify that in FILELISTONLY's WITH FILE = option. Better yet, stop reusing the same file for multiple backups.

You should be sure that when you restore a new database based on the backup of another, you use WITH MOVE to give the logical file names meaningful physical paths, and then use MODIFY FILE immediately afterward to make the logical names themselves meaningful.

To help figure out where the actual discrepancy is coming for a specific case you are experiencing (I couldn't reproduce a case where RESTORE FILELISTONLY showed different values than the restore UI in SSMS), you could provide a .bak file on some file sharing service, and we could look ourselves. Your description of what the file names are and what they should be is extremely hard to follow.