Migrate SQL 2000 to SQL 2005

1. Database
Make database backups the first
1.1 Direct upgrade
 --Database instance of SQL Server 2000 with SP3 or later can be upgraded directly to SQL 2005.
 --Using upgrade advisor to prepare for upgrades. it will suggest what should be done before and after upgrade. If you are scanning instances of Reporting Services, you must install Upgrade Advisor on the report server.otherwise, it can be installed anywhere that can run it.
1.2 Co-existence upgrade
 -- install a separate SQL Server 2005 box and restore the database backup to the new box. This requires more work to do, but it's safer since the original environment is still there.

2. Logins and Users

http://support.microsoft.com/kb/246133/

1. Run the following script on the source SQL Server.
   
   

   USE master 
   GO 
   IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL 
   DROP PROCEDURE sp_hexadecimal 
   GO 
   CREATE PROCEDURE sp_hexadecimal 
   @binvalue varbinary(256), 
   @hexvalue varchar(256) OUTPUT 
   AS 
   DECLARE @charvalue varchar(256) 
   DECLARE @i int 
   DECLARE @length int 
   DECLARE @hexstring char(16) 
   SELECT @charvalue = '0x' 
   SELECT @i = 1 
   SELECT @length = DATALENGTH (@binvalue) 
   SELECT @hexstring = '0123456789ABCDEF' 
   WHILE (@i <= @length) 
   BEGIN 
   DECLARE @tempint int 
   DECLARE @firstint int 
   DECLARE @secondint int 
   SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1)) 
   SELECT @firstint = FLOOR(@tempint/16) 
   SELECT @secondint = @tempint - (@firstint*16) 
   SELECT @charvalue = @charvalue + 
   SUBSTRING(@hexstring, @firstint+1, 1) + 
   SUBSTRING(@hexstring, @secondint+1, 1) 
   SELECT @i = @i + 1 
   END 
   SELECT @hexvalue = @charvalue 
   GO 
   
   IF OBJECT_ID ('sp_help_revlogin_2000_to_2005') IS NOT NULL 
   DROP PROCEDURE sp_help_revlogin_2000_to_2005 
   GO 
   CREATE PROCEDURE sp_help_revlogin_2000_to_2005 
   
   @login_name sysname = NULL, 
   @include_db bit = 0, 
   @include_role bit = 0 
   
   AS 
   DECLARE @name sysname 
   DECLARE @xstatus int 
   DECLARE @binpwd varbinary (256) 
   DECLARE @dfltdb varchar (256) 
   DECLARE @txtpwd sysname 
   DECLARE @tmpstr varchar (256) 
   DECLARE @SID_varbinary varbinary(85) 
   DECLARE @SID_string varchar(256) 
   
   IF (@login_name IS NULL) 
   DECLARE login_curs CURSOR STATIC FOR 
   SELECT sid, [name], xstatus, password, isnull(db_name(dbid), 'master') 
   FROM master.dbo.sysxlogins 
   WHERE srvid IS NULL AND 
   [name] <> 'sa' 
   ELSE 
   DECLARE login_curs CURSOR FOR 
   SELECT sid, [name], xstatus, password, isnull(db_name(dbid), 'master') 
   FROM master.dbo.sysxlogins 
   WHERE srvid IS NULL AND 
   [name] = @login_name 
   
   OPEN login_curs 
   
   FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb 
   
   IF (@@fetch_status = -1) 
   BEGIN 
   PRINT 'No login(s) found.' 
   CLOSE login_curs 
   DEALLOCATE login_curs 
   RETURN -1 
   END 
   
   SET @tmpstr = '/* sp_help_revlogin script ' 
   PRINT @tmpstr 
   SET @tmpstr = '** Generated ' 
   + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */' 
   PRINT @tmpstr 
   PRINT '' 
   PRINT '' 
   PRINT '' 
   PRINT '/***** CREATE LOGINS *****/' 
   
   WHILE @@fetch_status = 0 
   BEGIN 
   PRINT '' 
   SET @tmpstr = '-- Login: ' + @name 
   PRINT @tmpstr 
   
   IF (@xstatus & 4) = 4 
   BEGIN -- NT authenticated account/group 
   IF (@xstatus & 1) = 1 
   BEGIN -- NT login is denied access 
   SET @tmpstr = '' --'EXEC master..sp_denylogin ''' + @name + '''' 
   PRINT @tmpstr 
   END 
   ELSE 
   BEGIN -- NT login has access 
   SET @tmpstr = 'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE [name] = ''' + @name + ''')' 
   PRINT @tmpstr 
   SET @tmpstr = CHAR(9) + 'CREATE LOGIN [' + @name + '] FROM WINDOWS' 
   PRINT @tmpstr 
   END 
   END 
   ELSE 
   BEGIN -- SQL Server authentication 
   EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT 
   
   IF (@binpwd IS NOT NULL) 
   BEGIN -- Non-null password 
   EXEC sp_hexadecimal @binpwd, @txtpwd OUT 
   SET @tmpstr = 'CREATE LOGIN [' + @name + '] WITH PASSWORD=' + @txtpwd + ' HASHED' 
   END 
   ELSE 
   BEGIN -- Null password 
   SET @tmpstr = 'CREATE LOGIN [' + @name + '] WITH PASSWORD=''''' 
   END 
   
   SET @tmpstr = @tmpstr + ', CHECK_POLICY=OFF, SID=' + @SID_string 
   PRINT @tmpstr 
   END 
   
   FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb 
   END 
   
   IF @include_db = 1 
   BEGIN 
   PRINT '' 
   PRINT '' 
   PRINT '' 
   PRINT '/***** SET DEFAULT DATABASES *****/' 
   
   FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb 
   
   WHILE @@fetch_status = 0 
   BEGIN 
   PRINT '' 
   SET @tmpstr = '-- Login: ' + @name 
   PRINT @tmpstr 
   
   SET @tmpstr = 'ALTER LOGIN [' + @name + '] WITH DEFAULT_DATABASE=[' + @dfltdb + ']' 
   PRINT @tmpstr 
   
   FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb 
   END 
   END 
   
   IF @include_role = 1 
   BEGIN 
   PRINT '' 
   PRINT '' 
   PRINT '' 
   PRINT '/***** SET SERVER ROLES *****/' 
   
   FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb 
   
   WHILE @@fetch_status = 0 
   BEGIN 
   PRINT '' 
   SET @tmpstr = '-- Login: ' + @name 
   PRINT @tmpstr 
   
   IF @xstatus &16 = 16 -- sysadmin 
   BEGIN 
   SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''sysadmin''' 
   PRINT @tmpstr 
   END 
   
   IF @xstatus &32 = 32 -- securityadmin 
   BEGIN 
   SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''securityadmin''' 
   PRINT @tmpstr 
   END 
   
   IF @xstatus &64 = 64 -- serveradmin 
   BEGIN 
   SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''serveradmin''' 
   PRINT @tmpstr 
   END 
   
   IF @xstatus &128 = 128 -- setupadmin 
   BEGIN 
   SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''setupadmin''' 
   PRINT @tmpstr 
   END 
   
   IF @xstatus &256 = 256 --processadmin 
   BEGIN 
   SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''processadmin''' 
   PRINT @tmpstr 
   END 
   
   IF @xstatus &512 = 512 -- diskadmin 
   BEGIN 
   SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''diskadmin''' 
   PRINT @tmpstr 
   END 
   
   IF @xstatus &1024 = 1024 -- dbcreator 
   BEGIN 
   SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''dbcreator''' 
   PRINT @tmpstr 
   END 
   
   IF @xstatus &4096 = 4096 -- bulkadmin 
   BEGIN 
   SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''bulkadmin''' 
   PRINT @tmpstr 
   END 
   
   FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb 
   END 
   END 
   
   CLOSE login_curs 
   DEALLOCATE login_curs 
   RETURN 0 
   GO
   
   exec sp_help_revlogin_2000_to_2005 @login_name=NULL, @include_db=1, @include_role=1
   GO

2. Save the output, and then paste and run the output in SQL Server Management Studio on the destination SQL Server 2005.

3. Discontinued functionality or feature
    syslocks -->sys.dm_tran_locks
    isql -->sqlcmd
    old style of outer join *= and =* should be replaced by JOIN syntaxt
    Others are here: http://msdn.microsoft.com/en-us/library/ms144262%28v=sql.90%29.aspx

4. Deprecated features
    try to use new syntax for deprecated features.
    backup transaction --> backup log
    setuser --> execute as
    sp_add_server --> sp_add_linked_server
    restore database with dbo_only --> restore database with restricted_user
    Check the full list here: http://msdn.microsoft.com/en-us/library/ms143729%28v=sql.90%29.aspx

5. Breaking changes must be dealt with
    http://msdn.microsoft.com/en-us/library/ms143179%28v=sql.90%29.aspx

6. Compatibility level

    Set the new database to still compatible with SQL 2000 until systems have been fully tested.

7. Convert to new and more efficient syntax
  pivot, unpivot (T_SQL case when in sql 2000)
  OUTPUT for insert/update
  top @n with variable
  APPLY to easily integrate with table-valued UDFs.
  Ranking functions. thinking about row_number() for web page pagenation.
  Error handling, try,,,catch...
  CTE, thinking to rewrite the direved queries.
  and Recursive queries with CTE,