Microsoft SQL Server Tips Series: One Data and Log File

Ray HuangIn this series of articles I hope to cover some of the common problems our customers face when backing up and restoring a database, centralize the information scattered throughout our site and forums over the years, show you how the problem may have been created in the first place and provide you with a step-by-step guide on how you can identify the problem and resolve it.

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:

DatabaseRestoreFailed

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.)

AdventureWorks

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.

RestoreFullTextCatalog

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.

DatabaseProperties

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.

SecondaryFile

Now, scroll to the right and under Filename, type Secondary.ndf and click on the OK button.

SecondaryFilePart2

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.

5 thoughts on “Microsoft SQL Server Tips Series: One Data and Log File

  1. Hey Ray, in one of your upcoming articles, do you want to talk about how to optimize sql performance? eg. SQL Index defragging (eg. ALTER INDEX …REBUILD), not doing database shrink, etc?) …just a thought. Thx.

    1. I could try to cover some basic concepts, but database optimization can be a very in-depth and broad topic (e.g. rebuilding indexes, making sure your tables are in 3rd normal form, making sure your columns/attributes use the right data type, etc.)

Leave a Reply

Your email address will not be published. Required fields are marked *