SQL2012 fails to restore a SQL2005 database by Ekrem Önsoy

Backup from a SQL2005+SP3 Instance, restore to SQL2012+SP1 instance. Database is replicated in SQL2005 and using fulltext indexes.

DBCC CHECKDB in the SQL 2005 instance does not return any error.

There are 3 data files, 3 FULLTEXT files and one T-Log file currently in the SQL2005 database. There were two T-Log files of the mentioned database but I removed one of them as it was unnecessary a while ago. First emtied the file and then removed properly.

SQL Server 2012 restores the database "successfully", however when I query sys.master_files, I see the deleted T-Log file when the database was in SQL 2005 instance. This is a new backup file, how can that deleted file record be there? When I get to the SQL 2005 instance and query the sys.master_files, I do not see that record which is expected because I deleted it about 1 month ago.

I first realized this problem when I tried to perform a backup in SQL2012 instance and the backup operation failed with this error: "BACKUP DATABASE is terminating abnormally. 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. An error occurred while processing 'BackupMetadata' metadata for database id 8 file id 1."

I tried to perform a DBCC CHECKDB in the SQL 2012 instance and it is completed with no errors.

I tried detaching the database from the SQL 2012 instance and attach with proper info regarding to the file paths etc and it failed saying there's a problem with the T-Log file. Error msg: "An error occurred while processing the log for database 'X'. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log."

I restored the database all over again and restarted the SQL 2012 database engine service in single user mode and updated the sysaltfiles manually (desperately) and again restarted the service without the -m switch and it failed to open the database saying there might be a problem with the T-Log file.

I can successfully restart the SQL Server 2005 Database Engine service without any of these kind of problems with the mentioned database.

I want to stress out that I tried this all with another day's backup file too and the results are the same.

Now, I am performing some other tests such as:

- I have installed a SQL2008 instance and am able to restore the database successfully. The file records where as expected in the sys.master_files. I am able to backup the database successfully and detach\attach it and restart the database engine service without any problems as ı do in SQL2005 instance

- If I use the same database's backup that was taken from a SQL2008 instance and restore it to a SQL2012 instance then it restores really successfully. File records in the sys.master_files are as expected. There are no extra records for the deleted T-Log file and 2 dublicated rows for two of the FULLTEXT files.

While the database being restored in a SQL2012 instance I see the following rows in the sys.master_files:

name                             type_desc
aData                             ROWS
alog                                 LOG
aDataSec                        ROWS
aData2                            ROWS
ftrow_g_t_s_a_s            FULLTEXT
ftrow_g_a_a_s             FULLTEXT
sysft_g_a_a_s_i             FULLTEXT

However, after the restore completion, the result is changed and I now see the incorrect records in the sys.master_files:

name                             type_desc
aData                             ROWS
alog                                 LOG
alog2                             LOG -> I deleted this T-Log file from the SQL2005 database 1 month ago!
aDataSec                        ROWS
aData2                            ROWS
ftrow_g_t_s_a_s            ROWS -> This is a dublicate of the FULLTEXT file below!
ftrow_g_a_a_s             ROWS -> This is a dublicate of the FULLTEXT file below!
ftrow_g_t_s_a_s            FULLTEXT
ftrow_g_a_a_s             FULLTEXT
sysft_g_a_a_s_i             FULLTEXT

The extra 3 rows are not supposed to be there and they were not there until the end of the restore completion. I had already deleted "alog2" about 1 month ago from the Production server, why does it appear? This is a database backup I have taken yesterday! Also two of the the FULLTEXT records seen as ROWS besides FULLTEXT! This really looks messy to me, hopefully I'll sort it out with the help of yours too.