Designs For Glory

User Account Info

Welcome Login
You are not logged in. To post a comment, please log in.

Blog Home

Posts: 13
Comments: 0

Read my personal testimony.

Do you have long term pain issues? Join my "crowd."

Blog User Help

Blog Post:

Friday, July 21, 2006
SQL Database Transfer Issue

I love the new Microsoft SQL Server 2005. It contains a bundle of new features that should, over time, assist anyone needing databases. What is more, Mircosoft has been kind to developers by offering a free version called SQLExpress. The Express version does not have all of the features of the full version, but is more than powerful enough to allow for full development of major projects or for the preparation of demonstrations.

In addition, the SQ Server Management Studio offers a quick, simple way to handle all of the administrative functions involved with developing a website or a full desktop application. Included are tools for copying a database and importing or exporting data.

These tools are particularly important for development purposes. At least one normal approach to creating a dynamic website is to create your site on a development desktop using the SQLExpress database and then move the entire operation to a production environment. This allows you to test and cure all of your bugs without bothering an existing or new production environment.

There are, of course, issues with this approach. There have been several articles written about moving your database from your development system to your production system. However, most of these involve moving from the SQLExpress version to the full SQL Server 2005 version.

I have the benefit of having a full SQL Server 2005 database loaded on my Windows XP development machine. This, at least, avoids the need for working with the issues discussed in moving from the Express development database to a full version. However, I faced a different issue.

People chose web hosts for a variety of reasons. In my case I am perfectly satisfied with my host, but they, like many web hosting services, do not currently offer SQL Server 2005. They are running SQL Server 2000 and will not upgrade all machines to the new server due to cost reasons, although as thye add new database servers, the new ones will be SQL Server 2005. So, I was faced with the need to transfer the development database from the SQL Server 2005 system to a SQL Server 2000 machine.

I encountered two issues. I did not research any issues prior to transfering the data. I used the copy wizard built into the SQL Server Management Studio. On the surface the transfer went well. The wizard showed a successful completion to the transfer. However, there were two issues. One was, in a broad sense minor. Some of the table names were slightly changed so the stored procedures would not work properly. Manually renaming the talbe solved this issue.

However, due to either the difference between the 2000 and 2005 versions, or due to having incorrect settings, not all of the details for the tables transferred. Upon attempting to perform some of the functions, such as creating rolls and the login system on the production system. It develops that the fault sat with the default values for unique identifiers. The default values did not transfer to the production tables. Now this may or may not be setting in the copy wizard. I have not gone back to investigate. However, an examination of the design of the tables showed that the production tables lacked formulas for the creation of default values for some columns. As a result, the rows would not update since there could be no NULL values, but the default was not operating in the manner anticipated. The formula was designed to insert a unique identifier. However, since the formula was missing, no such value was being inserted.

Manually investigating the tables and inserting the default value formula fixes the issue and the production system works fine. Blessedly, in my case the amount of data involved is minimal. However, if you had a large database this could be a major problem. As I said, this may relate to the copy wizard settings, or it may be involved with the issues of moving backwards in terms of versions.  In either case, it is an issue to note.

Maybe not many of you will be attempting to move backwards from SQL Server 2005 or SQLExpress 2005 to SQL Server 2000. If you do, maybe this will help.

Jim A

Posted Comments

In order to control spam and moral content (such as language), all comments will be reviewed prior to publishing. I will not edit your post. If the moral content is incorrect, I will email you and provide your the opportunity to correct your comments. Accordingly, in order to post a comment, please Login.