Solved

Moving a SQL database from SQL 2000 to SQL 2008

Posted on 2008-10-30
3
4,820 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
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Updating ms sql with special characters 8 41
CROSS APPLY 4 45
Writing SQL Select Query result to a text file 12 27
Replace Dates in query 14 18
Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
In this article I will describe the Backup & Restore 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.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

895 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now