Home Dashboard Directory Help
Search

SQL2012 fails to restore a SQL2005 database by Ekrem Önsoy


Status: 

Active


2
0
Sign in
to vote
Type: Bug
ID: 778387
Opened: 2/3/2013 3:01:17 AM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

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.
Details
Sign in to post a comment.
Posted by Microsoft on 5/6/2014 at 11:38 AM
Thanks for reporting this issue, we have been unable to reproduce this at this time. If you still continue to face this problem, please engage Microsoft Support at http://support.microsoft.com to assist you.

Sincerely,
Ajay.
Posted by Microsoft on 11/15/2013 at 5:33 PM
Hi Ekrem, I checked the repro you have written in the forum thread. However, unfortunately we were not able to repro the problem.

It is normal that dropped log files are indicated as OFFLINE before you take a log backup. If a db is using FULL recovery model, the db will still retain the metadata for dropped log files even after log backup, but they are not just visible using sys.master_files or sys.database_file views--they can be only viewed by looking at system tables under DAC.

DB backups from SQL Server 2005--whether they show the dropped log files with the views or not--were able to be restored without problem (taking a new backup from 2012 afterwards was fine as well). So we need more detailed repro steps that can lead to this problem.
Posted by Ekrem Önsoy on 8/28/2013 at 11:29 AM
It was FULL Recovery Model.
Posted by Microsoft on 8/28/2013 at 11:21 AM
Thanks for providing this feedback. We are currently investigating this issue and would appreciate your input on the following:

What recovery mode was the original database set to?
Could you please provide details about the procedure used to empty/remove the first T-log file?

Thanks again for your continued support in our products.
Posted by Microsoft on 4/23/2013 at 8:30 PM
Hi Ekrem

Thanks for reporting the issue. We are investigating.
Posted by Ekrem Önsoy on 2/4/2013 at 2:28 AM
I detailed all the phases of this problem in the Technet forum for the good of the public: http://social.technet.microsoft.com/Forums/en/sqldatabaseengine/thread/e8347e09-9bfb-4d7c-bc48-b1e7837b6897
Sign in to post a workaround.