The purpose of this post is to explain the minimum required for the migration of Microsoft SQL Server 2000 to Microsoft SQL Server 2008. You can use the same principles to migrate a Microsoft SQL Server 2005 to Microsoft SQL Server 2008.


1. SQL server 2008 upgrade advisor

1.1. Before migrating
Microsoft provides a tool called "Microsoft SQL Server 2008 Upgrade Advisor" to alert you of any changes in design between Microsoft SQL Server 2000/2005 and SQL Server 2008. It is strongly recommended to run this software before migration.

1.2. Download
You can download this tool from the link below:

“Download the Microsoft SQL Server 2008 Upgrade Advisor. Upgrade Advisor analyzes instances of SQL Server 2000 and SQL Server 2005 to help you prepare for upgrades to SQL Server 2008.”
http://www.microsoft.com/downloads/details.aspx?familyid=F5A6C5E9-4CD9-4E42-A21C-7291E7F0F852&displaylang=en 

After installation, a new tab appears in: Start>> All Programs >> Microsoft SQL server 2008 >> SQL Server 2008 Upgrade Advisor

1.3. Report before migration
Run “SQL Server 2008 Upgrade Advisor”.
Then click on “Launch Upgrade Advisor Analysis Wizard”.
Then click on "Detect". The tool will automatically select the components installed on your platform.
It is also interesting to give a trace profiler tool containing a representative of your business so that it detects all the elements that would longer supported or recommended in Microsoft SQL Server 2008.
migration SQL Server 2005 to SQL Server 2008

Then configure the connection to your SQL server 2000 instance. After a few minutes a report will be generated with warning or points on which you must bring your attention. These items may include Full Text Search, replication, objects that no longer exist or have been modified in the new version, plans to maintain ...
The tool will provide two other types of information:
1. Objects affected
2. Advice you can find a workaround or fix the problem.

Sample report provided by the tool:
migration SQL Server 2005 to SQL Server 2008

3. Migration with the database restore method

3.1. Restoring a database SQL server 2000
On your new instance Microsoft SQL Server 2008, connect to Management Studio 2008. Then click on the "Restore Database". Then follow the instructions.

RESTORE (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms186858.aspx

How to: Restore a Database Backup (SQL Server Management Studio)
http://msdn.microsoft.com/en-us/library/ms177429.aspx

3.2. Compatibility Level SQL 2000/ SQL 2008
If you restore your database in SQL Server 2000 SQL Server 2008, the level of compatibility will default mode "SQL Server 2000 (80).
To know the level of compatibility, Make a right click on the name of the database>> "Property"
Then in the dialog "Database Properties", click "Options"

migration SQL Server 2005 to SQL Server 2008

To enjoy all the new features in the new engine SQL server 2008, you must change the compatibility level to 100.

To know the differences between compatibility 80, 90 or 100, I invite you to read the following article
http://msdn.microsoft.com/en-us/library/bb510680.aspx

sp_dbcmptlevel (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms178653.aspx 


3.3. Transferring SQL Server logins and Windows
There are different ways to migrate your users
- SQL Server Intégration Services, with component « "transfer Login task ».
- SQL Server Management Studio, with “Copy Database Wizard”
- You can also draw on examples of script between SQL Server 2000 and 2005 KB Articlehttp://support.microsoft.com/kb/246133

3.4. SQL Server Agent jobs
You can migrate your SQL Server Agent jobs using Enterprise Manager 2000. You can find more detail in the documentation below:

How to script jobs using Transact-SQL (Enterprise Manager)
http://msdn.microsoft.com/en-us/library/aa177024(SQL.80).aspx

3.5. Other components 
You must also reconfigure the components such as SQL database Mail extended stored procedures, linked servers...

3.6. Update statistics
It is recommended that, after having committed or changed the compatibility mode to 100, execute the stored procedure: sp_updatestats

The procedure allows sp_updatestats system to recalculate the statistics and make an update for all the statistics on each table in your base data. To avoid errors related to the statistics of the previous version.

sp_updatestats (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms173804.aspx




Related Resources

Resources for Upgrading to SQL Server 2008
http://msdn.microsoft.com/en-us/library/cc936623.aspx

SQL Server 2008 Upgrade Technical Reference Guide
http://www.microsoft.com/downloads/details.aspx?FamilyID=66d3e6f5-6902-4fdd-af75-9975aea5bea7&displaylang=en

Michel Degremont | Microsoft EMEA
Product Support Services Developer - SQL Server Core Engineer |