SQL Server - migration from sql server 2000 to sql server 2008

hi,

i want to migrate sql server database 2000 to sql server 2008.

Can you help me what to do?
I know there is a tool that can be use to migrate but I dont know the name of the tool.

Follow these links-

http://www.sqlmag.com/article/data-types/migrating-to-sql-server-2008-
and

here you will get document file.

http://www.google.com/url?sa=t&source=web&cd=3&ved=0CCoQFjAC&url=http%3A%2F%2Fdownload.microsoft.com%2Fdownload%2F2%2F0%2FB%2F20B90384-F3FE-4331-AA12-FD58E6AB66C2%2FSQL%2520Server%25202000%2520to%25202008%2520Upgrade%2520White%2520Paper.docx&ei=Lvq4TdifKoOEtgfXwuXeBA&usg=AFQjCNHREhaRPPZE_tjdjNENilWNKPNpIw&sig2=zj2MueRSlR8t2obP6AZ8Dg

The steps typically are (this is for moving SQL DB from SQL Server A to B, applies whether or not the SQL version is changing).

1. Shut down the FogBugz Maintenance Service on.

2. Shut down the old SQL Server service on A. If this cannot be done, set the FB DB on server A to READ ONLY and rename the FB DB, so nothing attaches to it accidentally.

3. Attach the db on SQL B

4. Check if any userid's or SID's in the DB need to be remapped. You may need to reset the dbowner or other user ids. See this kb: http://support.microsoft.com/kb/224071

5. Confirm that you can now log into to SQL B using SQL Management Studio (it is best to authenticate the same way FB will).

6. If you can auth OK, now set the connection string on the FB system to point to B. This is done on the FB web server. You can do this in the registry, in a location similar to this:

   HKEY_LOCAL_MACHINE\SOFTWARE\Fog Creek Software\FogBugz\C:/Program Files/FogBugz/website

   Note the sConnectionString element. That is the connection string.

7. Start the FogBugz Maintenance Service

8. Voila! FB is now running on the new DB server.

for more detail follow this link-
http://fogbugz.stackexchange.com/questions/1211/migrating-from-sql-server-2000-to-2008

Hi

Using this link...you can migrate.

refer the below link
http://blogs.msdn.com/b/blakhani/archive/2008/12/27/resources-migration-from-sql-2000-sql2005-to-sql-2008.aspx

hope it helps you...

hi deepak

here are some good links on this

http://msdn.microsoft.com/en-us/library/bb677622.aspx
http://blog.scalabilityexperts.com/2008/01/28/upgrade-sql-server-2000-to-2005-or-2008/

Hope this will help you

Hi ,
Follow these links-

http://

hi...

The "best" approach, (assuming you have available hardware and budget) is to install a fresh copy of Windows Server 2008 on new hardware, with SQL Server 2008 installed as a default instance. Then, you can use one of the methods below to get your database(s) upgraded to SQL Server 2008 format.

1. Take a full backup in SQL Server 2000\2005. Copy the backup files to the new server and restore from the backup. Change the compatibility level to 10.0 and run sp_updatestats.
2. Detach your SQL Server 2000\2005 database. Copy the data files and transaction log files to the new server and attach the database. Change the compatibility level to 10.0 and run sp_updatestats.
3. If you are running SQL Server 2005, you can use database mirroring to move the data with one brief 15-20 second outage. You must be using the full recovery model to do this. Take a full backup in SQL Server 2005. Copy the backup files to the new server and restore from the backup with no recovery. Replay any subsequent transaction log backups on the new server with no recovery. Enable database mirroring and let the mirror get synchronized. Modify your connection strings to include a failover partner. Failover from SQL Server 2005 to SQL Server 2008, and then remove the mirror and change your connection strings. Change the compatibility level to 10.0 and run sp_updatestats.

Methods 1 and 2 are easier, but require some downtime. Method 3 is quite a bit more complicated, but can be used to migrate with virtually no downtime. Of course with all of these methods, you will have to migrate your logins and SQL Agent jobs to the new server. Even though Microsoft puts a lot of effort and testing into various "upgrade in place" scenarios, I would personally never go that route.

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.


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:


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"



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 Article http://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%28SQL.80%29.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