Moving MSSQL data to a new server
Printed From: Web Wiz Forums
Category: Web Wiz Web App Support Forums
Forum Name: Web Wiz Forums
Forum Description: Support forum for Web Wiz Forums application.
URL: https://forums.webwiz.net/forum_posts.asp?TID=18634
Printed Date: 12 April 2026 at 6:10am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com
Topic: Moving MSSQL data to a new server
Posted By: Shaun
Subject: Moving MSSQL data to a new server
Date Posted: 07 March 2006 at 2:58am
|
Hi all - Hopefully someone can help me out here.
We have our own dedicated webserver running our Forum, with a separate shared server running our MSSQL DB etc. We've been having absolute crock of sh*te page load times for a few months now! On the advice I read in here, we've reduced our amount of Forum Sections (We did have 27 - Now down 16, of which 2 are Admin sections).. But we still get completely randomn page loads.. Can get 0.5 secs > 50 secs. Overall there has been an improvement though. The reason why we have the shared server for the MSSQL side of things, was to help out with the MSSQL License. Having just found out Microsoft have released Microsoft SQL Server 2005 Express Edition, I figure we can plop that on our own dedicated server and run the MSSQL stuff on it, so we have everything local. This would also rule out all our ideas about the current shared server, being pap and causing the slow page loads. Does that sound like a plan? So could anyone please take the time to knock up a "how to", on moving the data / tables from it's current server to our own. It would need to start with what I need to ask our Host to supply etc.. And follow on from there. Make it painfless please guys (with Mickey Mouse A - Z instructions).. I'm not the best Website Keeper. Many thanks, Shaun |
Replies:
Posted By: WebWiz-Bruce
Date Posted: 07 March 2006 at 11:48am
|
The problem with SQL 2005 Express is that DTS import/export tools don't work in it, which makes things tricky. Until I workout a solution to this issue I'm using MSDE 2000 for the forum on this site. The reason why is that with the DTS import/export tools I was able to move the database from SQL 2000 Enterprise straight across to MSDE. So far I have found no issues with using MSDE as the backend for the forum on this site which is very busy and is over 100Mb in size. If you find a simple way to move the database to SQL Express and beadle to backup SQL Express please let me know. |
Posted By: Shaun
Date Posted: 10 March 2006 at 12:16am
|
Cheers Borg.. Just checked mate (I didn't realise LOL).. But MSDE is a freebie as well.
So does this mean I can download MSDE onto our local web server and run the MSSQL stuff from there as well, hence avoiding the 2 servers that we have at the moment etc. Any tips on this idea please fella? Our forum is dying a slow death, I've tried everything that's come up on here and the members are getting seriously pissed. The only thing left to try, is to rule out any slow MSSQL issues, by having our MSSQL DB located on a seperate and shared server. Many thanks for your help, Shaun |
Posted By: WebWiz-Bruce
Date Posted: 10 March 2006 at 12:59pm
|
Having the SQL Server installed locally should sort out your issues. MSDE 2000 is the free version of SQL Server 2000. You install it in the same was as SQL 2005 Express, although if you can I would use SQL 2005 Express as it doesn't have the throttling limitations that MSDE has. Although both versions should sort out your problems. |
Posted By: Shaun
Date Posted: 13 March 2006 at 11:14pm
|
You've lost me now fella?
You originally said above, NOT to use 2005 Express? So next plan was MSDE, as you happily rate it and use it yourself etc. Anyways, any advice on what I need to do please - To get the MSSQL data that's currently on the shared server, onto our own dedicated server. The way I see it is: 1. Install MSDE on our server. 2. Get the data from our host - Can this just be a backup or do I need the data "detached"? 3. Import the tables / data into MSDE on our server. 4. Edit one of the .asp files (which one??) to point towards the new location of the MSSQL data. Borg, I really am a n00b here mate.. Looking for some guidance from you 
Cheers, Shaun |
Posted By: WebWiz-Bruce
Date Posted: 14 March 2006 at 2:51pm
|
I didn't say NOT to use SQL 2005 Express, what I said was that I'm not using it as I haven't found away yet for importing and exporting as DTS import/export tools don't work for it, but if you find away let me know. As soon as I find away to import and export data on Express 2005 I'm going to move straight over to it as it doesn't have the throttling that MSDE has. But for the moment use MSDE as you can, using Enterprise Manager use the DTS Import/Export Wizard built into Enterprise Manager to move your database from one SQL 2000 Server to another or between SQL Server and MSDE. |
Posted By: rx7tt
Date Posted: 15 March 2006 at 6:49am
|
Shaun,
I can be of assistance because I did exactly what your are asking. I moved my entire site and sql db from a shared server with shared sql to a dedicated with sql express. With both methods I do recommend a trial run. Attach method(Fairly simple): Copy the db files from the old server (eg. C:\...Microsoft SQL Server\MSSQL\Data) to the sql express folder on the new server (eg. C:\...\Microsoft SQL Server\MSSQL.1\MSSQL\Data) open the sql express manager and attach the db. PS In order to copy the files you will need to stop the db. Backup/Restore method(I prefer this way): Backup the db from the old server to C:\...Microsoft SQL Server\MSSQL\Backup copy the backup files to the new server in C:\...\Microsoft SQL Server\MSSQL.1\MSSQL\Backup. Open sql express manager and create a new db with the same name. Once that is done right click the db and go to tasks->restore->database. In the source section select "from device" then browse for your db and that should be it. Please let me know if you have any questions or problems with either. SQL Express will take some getting used to but after that it is very good. -Scott ------------- http://www.mobilegear.biz - Mobile Gear.biz -Ringtones, games, videos, links and more... http://www.mobilegear.biz/forum/ - Wireless Forums |
Posted By: WebWiz-Bruce
Date Posted: 15 March 2006 at 10:14am
| Thanks scott, this is also what I was looking for, as from what I read from Microsoft the DTS tools don't work in SQL 2005 Express. Now I know how to do this I will probably be upgrading to SQL 2005 Express very soon. |
Posted By: Shaun
Date Posted: 15 March 2006 at 10:59pm
|
Many thanks Scott (top car, top help)!
I'll probably get back in touch at some point LOL. Just one final question, which I've asked a few times but haven't had the answer as yet. Which file do I need to change (and how), to point the forum to the new SQL database and location etc? Cheers again, Shaun |
Posted By: WebWiz-Bruce
Date Posted: 16 March 2006 at 11:58am
| As far as I can recall, in version 7.x it should be 'admin/SQL_server_connection.asp' |
Posted By: rx7tt
Date Posted: 16 March 2006 at 1:25pm
Sorry I missed that as well. Change
Another cool thing that I'm not sure is possible with MSDE but works with SQL express is using Windows authentication for the forum instead of sql server authentication. Might be something to add to V8, as it would only add one line. -Scott ------------- http://www.mobilegear.biz - Mobile Gear.biz -Ringtones, games, videos, links and more... http://www.mobilegear.biz/forum/ - Wireless Forums |
Posted By: WebWiz-Bruce
Date Posted: 16 March 2006 at 1:39pm
| I do plan on having support for Windows Authentication in version 8 for db versions, but as I want to get version 8 out as soon as possible it will have to wait now till version 8.1 |
Posted By: Shaun
Date Posted: 17 March 2006 at 12:59am
|
Talk about weird?
I've looked at that file and in that exact location etc. BUT The details for our current MSSQL database are incorrect. It's been filled in with something different, definately not the MSSQL DB I've been using. Anywhere else I should be looking? |
Posted By: WebWiz-Bruce
Date Posted: 17 March 2006 at 11:54am
|
Not in a standard installation. It sounds like someone may have modified the code, have a look in both the common.asp files as this is where the connection string is used to connect to the database, someone may have modified the code in these files. |
Posted By: Ozoyo
Date Posted: 20 March 2006 at 9:18am
|
This is maddening... SO there is no import tool in SQL 2005  .I have installed 2005 express locally on the server but I can't import my SQL 2000 DB which is on a distant server. I have found a way to import a DB from MSDE to SQL 2005 (not tested though). But my main issue is that I can't install MSDE on my server (something about SA password), I have tried to change the password as told on the Microsoft site but no success...  .I have few questions: 1. Can you have SQL 2005 and MSDE on the same server? Is that why I have the password issue? 2. Does MSDE have an import wizzard that I can use to import my DB from the distant server? Thanks |
Posted By: WebWiz-Bruce
Date Posted: 20 March 2006 at 10:59am
|
1. Yes you can, I have SQL Express, SQL 2005 Developer Edition, and SQL 2000 Developer Edition all install on this computer at the moment, just installed under different instance names. 2. Enterprise Manager and the full SQL Server Management studio both come with DTS import export tools for moving SQL Databases, the only version that is not compatible with these tools in SQL 2005 Express which is limited. But you can detach and attach databases in SQL Express 2005 which would help in moving databases. |
Posted By: wistex
Date Posted: 14 April 2006 at 6:44pm
|
The only option I have is restoring the database. The host I am temporarily using (after having major problems with our now former host) doesn't even allow us Enterprise Manager access to the database! I can, however, make a backup file and download that. Thanks for letting me know about the backup/restore method. You are saving me hundreds of dollars in hosting fees and helping us with the performance issues we are having due to using a shared MS SQL Server. ------------- http://www.wistex.com" rel="nofollow - WisTex Solutions http://www.caribbeanchoice.com/forums" rel="nofollow - CaribbeanChoice Forums |