Moving MSSQL data to a new server

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
    

Edited by Shaun - 07 March 2006 at 2:59am

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

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

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