That’s a tall order, but if you’re ready, here we go.
You might see this message once in a while when restoring a database:
There are a few reasons why we do this:
1) We trap the SQL Server Error thrown on the back end so that one of our support staff can research and review it, interpret it for you and guide you to a solution. Because we have a diversity of customers who contact us with varying technical skills and knowledge, displaying an error might be meaningful to an experienced developer or tech geek but not so much to the average user or a beginning developer who has his application working locally but is finding difficulty getting it to work in our hosting environment.
2) We have International customers and bridging the language barrier can be already difficult. If we displayed the error message, it might lead to even more confusion trying to explain it.
3) It wouldn’t look pretty. 🙂
In this walk-through, I will cover some of the more common errors that our customers run into, show you how it may have been created and provide you with a solution. The errors are:
“More than 2 file list in mdf file” or “More than 1 data or log file in bak file”
To simplify things, our hosting platform only supports restore operations for databases containing only one data file and one log file. And here are a couple of ways on how to create the problem.
Method 1: Adding a Full Text Catalog
To create this problem, I’m going to download the sample AdventureWorks database for SQL Server 2005 here.
(Note: You will need to double click AdventureWorksDB.msi to extract the files.)
Then I am going to upload the AdventureWorks_Data.mdf file only to my hosting account and use the Attach tool to restore it to my DiscountASP.NET database. Now, I am going to log into my database here at DiscountASP.NET using SQL Server Management Studio and execute this T-SQL statement to create a Full Text Index/Catalog:
USE [DiscountASP.NET database name]; GO CREATE UNIQUE INDEX ui_ukJobCand ON HumanResources.JobCandidate(JobCandidateID); CREATE FULLTEXT CATALOG ft AS DEFAULT; CREATE FULLTEXT INDEX ON HumanResources.JobCandidate(Resume) KEY INDEX ui_ukJobCand; GO
After that, I am going to use the DiscountASP.NET Control Panel to create a backup of the database. You shouldn’t run into a problem if you perform a restore to the same version of SQL Server 2005. The problem occurs when you try to restore to a higher version of SQL Server such as 2008, 2008R2, or 2012. This is the error that will occur:
“More than 2 file list in mdf file”
Now if I download that .bak file and try to restore it on my local machine where I have full rights to my SQL Server instance then I can see the problem. The Full Text Catalog is part of the file list.
You can also view this information by executing this T-SQL statement:
RESTORE FILELISTONLY FROM DISK = N'[Path to Backup File]'
To fix the problem, I will go ahead and restore the database on my local machine. Once restored, open a new query window and execute this T-SQL statement to drop the index:
DROP FULLTEXT INDEX ON HumanResources.JobCandidate
Then right click on the database and select Properties. In the Database Properties window, select Files and then the Full Text Catalog. Use the Remove button to remove it. Click on the OK button.
Prior to removing the catalog, you should scroll all the way to the right. You’ll notice that the Full Text Catalog is stored as a secondary database file or .ndf file. Secondary database files are used for a lot of reasons including organizing data and spreading it across different disk partitions to improve performance. It’s more useful in a dedicated server environment and very large databases where you may need the performance increase.
If we backup the current database (without the Full Text catalog) locally and upload it, then we will be able to perform the restore using the tools in the DiscountASP.NET Control Panel. Make sure the version of SQL Server running on your local machine matches the version on the DiscountASP.NET server, or you will run into a different error.
This is something you may want to keep in mind when upgrading your database, and it fails. You can re-create the Full Text Catalog after the database has been restored.
Method 2: Adding an .ndf file
The second way to create this problem is to create a secondary database (.ndf) file. To do this, I will go ahead right click on my local database and select Properties. Then I will click on Files and the Add button. Notice a new row has been appended under the Database files section. I’ll give the new row a logical name of SecondaryFile.
Now, scroll to the right and under Filename, type Secondary.ndf and click on the OK button.
If you were to backup this database, upload it, and try the DiscountASP.NET Restore tool, you would get this error message on the back end:
“More than 1 data or log file in bak file”
And the solution is similar to the one above. If you have data stored on the secondary file, you will need to move it by executing this command:
DBCC SHRINKFILE (Logical Name, EMPTYFILE)
So for example:
DBCC SHRINKFILE (SecondaryFile, EMPTYFILE);
Highlight your database, right click on it and select Properties. Go back to the Files section and use the Remove button to remove the secondary file. You will now be able to restore the backup using the DiscountASP.NET tools.