Backup Operation failing after Database Upgraded from SQL Server 2000 to SQL Server 2008
Backup Issue that is not common but may occur after you upgrade Database from SQL Server 2000 to SQL Server 2008 and result following error when you perform backup operation:
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 14 file id 4.
I received a call recently where customer forwarded above error message for resolution. I started analyzing this error and found that metadata for database id 14 is not correct as it has few extra files reference that are no longer valid and causing error.
Steps taken to identify Root Cause
- Queried sys.database_files to find out list of files associated with database in question and found that there is an entry under physical_name column that contains complete path for log file, is not valid on server
- Checked Files list thru SQL Server Management Studio and found mismatch as output of step 1 was not matching with list of files displayed thru Management Studio
Above steps, anyway confirmed that there is inconsistency in metadata. So prove this little more closer I performed following action:
- Detached database
- Started Attaching Database, As we know first step in Attach database thru GUI is to provide .mdf file reference and GUI pulls list of all files name along with location. In that list, I found same invalid file path that was suspected thru output from sys.database_files
- Although there was no need for this step but I still tried attaching database to capture invalid file error that I wanted to send to customer
Now it was completely proved that there is meta data issue as there is an extra log file associated with database that is no longer valid.
Step taken to Resolve Issue
- Detached Database
- Executed following statement:
CREATE DATABASE SALES ON
FILENAME = ‘F:\SalesData1\MSSQL\Data\Sales_Data.mdf’
Alternatively, you can also resolve this issue by removing file reference from system table which will require server to start in single user mode, connect as “Dedicated Administrator” and restart SQL Services in normal mode.
Metadata validation is tight in SQL Server 2008 so in order to avoid all these issue, validate meta data in SQL Server 2000 and resolve inconsistency if any.