Link to home
Start Free TrialLog in
Avatar of okanagan
okanagan

asked on

Moving a SQL database from SQL 2000 to SQL 2008

Hi -

I am upgrading web servers (I am a small site designer).  Part of the upgrade is moving from a dedicated SQL 2000 server from one hosting company to a dedicated SQL 2008 server with another hosting company.  What is the best way to transfer the databases from one to the other while still retaining the ownership, roles and permissions necessary to make sure I don't have to rewrite a lot of stored procedures.

Any advice is welcome - I am attempting one of the moves tonight.

Thanks,

Rod
Avatar of Faizan Sarwar
Faizan Sarwar
Flag of United Kingdom of Great Britain and Northern Ireland image

have a look at

SQL Server Hosting Toolkit
This tool will help you generate the Schema and Data statements which you can execute on Remote Server
download free from this url
http://www.codeplex.com/sqlhost/Release/ProjectReleases.aspx?ReleaseId=2859

http://www.codeplex.com/sqlhost
First of all write or find a script to script out the logins. And script them out.
If you have any jobs then script then out too..
If you have any dts packages rebuild them as SSIS packages.
Detach the the database from SQL Server 2000.
Move the database files *.mdf, *.ldf to new location.
Attach the datebase files to the new server.
Create the logins from the scripts that you created.
Create the jobs from the scripts.
Deploy the SSIS packages.
Thats all.
Hope this help you.
ASKER CERTIFIED SOLUTION
Avatar of dportas
dportas

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial