Solved

Moving a SQL database from SQL 2000 to SQL 2008

Posted on 2008-10-30
3
4,824 Views
Last Modified: 2010-08-04
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
Comment
Question by:okanagan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 13

Expert Comment

by:sm394
ID: 22845602
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
 
LVL 26

Expert Comment

by:tigin44
ID: 22845649
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
 
LVL 22

Accepted Solution

by:
dportas earned 500 total points
ID: 22860594
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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question