I have been testing my system on my development PC using SQL Express. Great! Now its all working and I have 4.5Mb of data, and about 50 tables plus queries.
HOW DO I GET THIS TO MY NEWLY SETUP HOST ENVIRONMENT!
Argh... There seems to be no way to export from SQL Express to SQL 2005. The host company cannot restore from backup (I think this is reasonable.)
What was microsofts plan here? What am I missing? I have searched the net, and I have found about 20 people asking the same question with no answer.
Best answer I found was on this forum where somebody said download SQL 2000 as a trial, then steal a tool called DTS which apparently does not expire. Not clear if DTS 2000 will work with 2005 express so I haven't tried that yet.
Somebody else said download SQL 2005 as a trial. I made the mistake of doing this. It doesn't load unless I unload my SQL Express. If I do that then how do I make development changes after the trial ends?
Somebody else says use SQL 2005 developer edition. Only 80 bucks. GREAT... I'll take it. So I search for how to get this wonder tool, and all the links end up with the generic 2005 system. No mention of how to obtain this "developer edition"
I really feel I must be missing something here.
HOW DID MICROSOFT FIGURE that people would deploy their systems?
What I am missing?
Mitch
Hello Mitch -
I'm not certain I understand your issue. If you need to copy an entire database from SQL Server 2005 Express or MSDE to SQL Server 2005, you have many options. The first is that you can back up the database in question and restore it to the 2K5 server. I have done this many times and it works fine. You can also "detach" the database, copy the MDF and LDF files to the 2K5 server and "attach" it there using stored procedures or graphical methods. You can also use a wizard within SQL Server 2005 to transfer the database. Another method is to use the bcp program to copy out the data and import it into a SQL2K5 database. All of these methods have been tested and work correctly.
From your other statements it may be that you're trying to move a SQL Server 2005 Express database to SQL Server 2000. This is also possible, as long as you haven't used any of the extended features in 2005 on the source database. Set the database compatibility level to 80, and then use the database transfer wizard to transfer the database to SQL Server 2000, or use the bcp method.
Buck
|||Buck,
Thanks for the reply.
You are correct that I am trying to move from SQL Server 2005 Express to SQL Server 2005.
The reason the backup/restore approach doesn't work, is that the hosting company does not do restores for people. I can understand why. They can't spend 15 minutes everytime somebody wants to upload their SQL Server 2005 Express DB. And for whatever reason when I try to do the restore myself, I can't get it to access my backup file.
I do not understand the detach, reattach method, and doubt it would work from the host I am on.
The hosting company recommends using the SQL2K5 transfer wizard.
However as noted, I don't have SQL2K5.
I am currently in the process of installing the "trial version" of SQL2K5. To do this I first had to uninstall SQL 2005 Express. Now I am doing the SQL2K5 install, and it has taken over an hour and its not done yet.
I am hoping to figure out how to enable this $80.00 developers license, but while its mentioned here and there, there is no clear instructions on how exactly to buy such a thing.
By the way the reason that I don't want to go with the BCP program, is that I have about 50 tables and queries, and it seems that each table is a manual step.
Again thanks for the response, any advice would be appreciated.
Mitch
|||That is a little clearer. So the problem isn't with SQL Server, it's with the hosting company that won't restore the DB. If you're able to get to the SQL Server instance on your hosting service, you can just ask them to place the file for you and you can restore it yourself. If they won't do that, I'd investigate another hosting service.
The detach and attach method will have those same file copy and placement issues. It's better to use the backup and restore method if you can.
As far as the developer's license, you're not able to put that into production. You're only allowed to use that on your own machine for development purposes. If you're talking about installing it on your system, It may take a while to install, since it contains the Visual Studio-type interface for the BI Development Studio.
Buck
|||Mitch,
For deployment, take a look at "Distributing SQL Server Express Applications" in Books Online: http://msdn2.microsoft.com/en-us/library/ms165639(SQL.90).aspx
You don't have to uninstall SQL Server 2005 Express to install the Evaluation edition. SQL Server is a multi-instance product which means it can be installed side-by-side so long as the instance name is unique.
Cheers,
Dan
btw: SQL Server Express is SQL2K5.
