?
Solved

Moving a SQL database from SQL 2000 to SQL 2008

Posted on 2008-10-30
3
Medium Priority
?
4,825 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 2000 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

770 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