• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4828
  • Last Modified:

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
0
okanagan
Asked:
okanagan
1 Solution
 
sm394Commented:
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
0
 
tigin44Commented:
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.
0
 
dportasCommented:
BACKUP / RESTORE. Have you tested the database and all the applications under 2008? There are a number of breaking changes and other potential issues between 2000 and 2008.

You should also test the actual transfer and switch-over process before you attempt it for real.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now