April 29, 2010 at 2:28 pm
We are planning to migrate our databases from SQL2000 Enterprise/Win2000 server to a new SQL2008 Standard/Win2008 R2 server environment. Based on some investigation it appears there is no direct path for us and we may need to migrate to SQL2005 as an intermediate step? It appears there are only a few options that will get us to our goal:
1. Copy Database Wizard function in SQL Server Management Studio.
2. Upgrade a Database Using Detach and Attach.
3. Upgrading the existing instance and all objects in place via the new SQL Server Version's Installer.
We also understand that SQL2000 DB can be migrated as Compatibility Level 80 and then Altered to 100 on the SQL2008 server so we don't forefit the new features.
Has anyone done this migration before and can share any lessons learned and what are the best resources to read and prepare for this move?
Thanky
April 29, 2010 at 10:26 pm
Are you moving to new hardware?
You can just backup the 2000 databases and restore on 2008.
You're going to want to do this multiple times and perform extensive testing on all of your applications before go live time.
April 30, 2010 at 1:50 am
1. Since you are migrating from SQL Server 2000 so there is high chance that you may have code that is incompatible with SQL Server 2008. For example:
=*, *=, usage of index hint etc so you should definitely run "Upgrade Advisor for sQL server 2008" and analyze result of this tool ensure no incompatible code there before you migrate. You may refer step by step article How To Analyze Incompatible Code in SQL Server 2000 and effort to make it compatible with SQL Server 2008
2. You can use either backup/restore method or detach/attach
3. Change database compatibility mode
4. Run updatestats
5. In my experience I have seen meta data issue so it is advisable to run dbcc checkcatalog to see if there is any
[font="Verdana"]--www.sqlvillage.com[/size][/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply