Migration from SQL 2000 to SQL 2008

I am needing to migrate SQL 2000 to 2008 and have only found guides for 2000>2005.
Is it possible to do a GUI backup then restore to 2008? I do not mind that the logins will be lost.
Larry KiterlingAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
Yep, you can go directly to 2008 - the steps should be pretty much the same as 2005.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David Johnson, CD, MVPOwnerCommented:
you can do an inplace upgrade
Larry KiterlingAuthor Commented:
Moving to 2008 and 2008 is not supported on 2003.

Will this be a backup and restore through the GUI?
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Kyle AbrahamsSenior .Net DeveloperCommented:
Asking questions on your own question?
Larry KiterlingAuthor Commented:
The first statement was towards David Johnson. Second question was me trying to pull some better answers ;p

There doesn't seem to be one out there. Unless its EXACTLY the same as going from 2000 to 2005, I would like some references.
Paul JacksonSoftware EngineerCommented:
You can't go direct from 2000 to 2008 because you can't restore a backup from 2000 in 2008.
When I did the same (going from 2000 to 2008) I first migrated to 2005 and then made sure the app still worked with sql 2005 and fixed anything that didn't. Than did the migration to Sql Server 2008.
David Johnson, CD, MVPOwnerCommented:
Your quite correct there is no upgrade scenario from SQL2K to SQL2K8 .. Why you don't upgrade to 2K14
Vitor MontalvãoMSSQL Senior EngineerCommented:
I am needing to migrate SQL 2000 to 2008 and have only found guides for 2000>2005.
You can use the same process to migrate from MSSQL 2000 to 2008 or 2008R2. There's always a 2 versions backward compatibilities in Microsoft products.
Larry KiterlingAuthor Commented:
Can someone please verify? These answers are lacking :(

SQL 2014 isn't compatible with our vendor.
Kyle AbrahamsSenior .Net DeveloperCommented:
Your question is  "Is it possible to do a GUI backup then restore to 2008?"

The answer to that question is "yes."  What else do you seek?
Paul JacksonSoftware EngineerCommented:
You can take a GUI backup in 2000, restore it to 2005.
At this stage I would carry out some app tests to make sure everything is still working.
Then you can take a GUI backup in 2005 and restore to 2008.

Not sure what is unclear about this please be more specific if you need a specific question answered.

To be clear you can not take a backup from sql 2000 and restore it in sql 2008 despite what other "experts" have said.
I have carried out this procedure myself and had to use sql 2005 as a go between.
Larry KiterlingAuthor Commented:
Kyle, you stated "Yep, you can go directly to 2008 - the steps should be pretty much the same as 2005."
while Paul is stating you have to go through 2005 first.

Vitor states "You can use the same process to migrate from MSSQL 2000 to 2008 or 2008R2. There's always a 2 versions backward compatibilities in Microsoft products."

Best answer yet I suppose. I wanted to verify since there didn't seem to be a consensus yet.
Kyle AbrahamsSenior .Net DeveloperCommented:

I've gone right from 2000 to 2008 . . . I guess the caveat being you have to be on 2k SP4 for 2008 to allow it.
Paul JacksonSoftware EngineerCommented:
I was restoring to SQL Server 2008 not 2008 R2, so maybe they've changed it since then.
I can only speak from experience and not what is posted in a Microsoft forum.
Paul JacksonSoftware EngineerCommented:
We should probably ask what operating system your SQL Server 2000 is hosted on?
Do you plan to restore to an instance of SQL Server 2008 R2 on the same server or is it a different server with a different operating system.

If the SQL Server 2008 r2 instance is on another server then you should be able to restore directly from 2000. i.e. a Side-by-side installation

What may not be possible is an inplace upgrade but again this seems to depend on what operating system the SQL Server 2000 instance is installed on.

Good article worth a read here:
Larry KiterlingAuthor Commented:
I'm on a 2003 server and restoring on a 2008r2
Kyle AbrahamsSenior .Net DeveloperCommented:
Can you attempt do a restore of one of the databases?
Anthony PerkinsCommented:
Perhaps I am stating the obvious, but have you considered using the Microsoft SQL Server 2008 Upgrade Advisor.  You may find the information there a tad more, how can I put it, official.
Here is the description:
Microsoft SQL Server 2008 Upgrade Advisor analyzes instances of SQL Server 2000 and SQL Server 2005 in preparation for upgrading to SQL Server 2008. Upgrade Advisor identifies feature and configuration changes that might affect your upgrade, and it provides links to documentation that describes each identified issue and how to resolve it.

You also may want to read Upgrading to SQL Server 2008

And specifically focus on the matrix covered in Version and Edition Upgrades to see what is supported and what is not.

But as someone has already pointed out, there is nothing stopping you from attempting to restore a SQL Server 2000 backup to a SQL Server 2008 server and you would get your answer faster and more reliably than on this site.  But that is when the fun starts.  If you think that the versions are fully backward compatible than you may be in for a shock:  They are not.  Successfully restoring a 2000 database to 2008 is the least of your problems.

And of course I am making the assumption you are upgrading to SQL Server 2008 and not SQL Server 2008 R2.  If it is SQL Server 2008 R2 then you may have to get creative.
Vitor MontalvãoMSSQL Senior EngineerCommented:
To be clear you can not take a backup from sql 2000 and restore it in sql 2008 despite what other "experts" have said.
Don't judge others by your "bad" experience.
I, myself, did dozens of migrations from SQL Server 2000 to 2008 and 2008R2 in one shot. No need for MSSQL 2005. It's a waste of time.
Larry KiterlingAuthor Commented:
Sql2000 currently has no service packs installed. Would I need to install SP4? If so, do I have to install 1/2/3 prior or can I go straight to 4?
Paul JacksonSoftware EngineerCommented:
Yes you would definitely need to install SP4 and you can go straight to it as all service packs are cumulative.

@Vitor - don't judge others by your good experiences, it very much depends on whether you are doing an in-place upgrade or a side by side migration and what operating system the existing instance of sql server is on which of these is achievable.
Larry KiterlingAuthor Commented:
So I'm able to go directly from a Windows 2003 Sql2000 to a Windows 2008R2 Sql2008 as long as I have the SQL2000 on SP4? This is a great conversation since I'm sure every IT companies are migrating out of 2003 to 2008/2012 :)
Paul JacksonSoftware EngineerCommented:
Yes the consensus is that you can do that. Some of the confusion I think is because a lot of the experts seem to think you were doing an in-place upgrade of the SQL Server 2000 instance to SQL Server 2008. Where as you seem to be doing a migration i.e. taking a backup from a SQL server 2000 instance and restoring it to a SQL Server 2008 instance on a different server.
Larry KiterlingAuthor Commented:
Thank you. I will be attempting this sometimes this week and will let you guys know.
Vitor MontalvãoMSSQL Senior EngineerCommented:
There's no issues for in-place ugrade. The only thing that you need is to have MSSQL 2000 with SP4 installed. With that you can perform an in-place upgrade or a side-by-side upgrade to MSSQL 2005, 2008 or 2008R2. All in one shot, so no need for extra steps.
Just don't forget to run the Upgrade Advisor that comes in MSSQL 2008 media. The Upgrade Advisor will check and report for any incompatibility issue on your MSSQL 2000 databases.
Larry KiterlingAuthor Commented:
Its not an inplace upgrade. Does this guide look about right for side by side?

Vitor MontalvãoMSSQL Senior EngineerCommented:
I know that isn't an in-place upgrade. Just want to comment that there's no issues with the SQL Server 2008 if you would do it.

The guide is for side-by-side migration as you can confirm by the first step:
"1. Install a separate copy of SQL Server 2014 (...)"
Kyle AbrahamsSenior .Net DeveloperCommented:
Yeah, without the 2014 part.  You would need to install it as a separate instance, and only one can be the default instance.

Note if you're going to be upgrading to SP4 I would recommend doing backups of your dbs before hand.

If you're already at SP4 just take the backups, and then restore to the 2008 instance.
Larry KiterlingAuthor Commented:
I'll be doing this in a few days. Hold on tight.
Kyle AbrahamsSenior .Net DeveloperCommented:
Any issues?
Larry KiterlingAuthor Commented:
After the upgrade on 2000 sql to SP4, I was able to backup and restore with no issues. Thanks for the help bros. You guys just got me a raise and promotion for getting it done in one shot...jk.
Larry KiterlingAuthor Commented:
Kyle be killing the SQL game.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.