Steps to Migrate SQL 2000 database to SQL 2005
- Detach database from SQL 2000
- Copy files to appropriate folders in SQL 2005
- Attach database in SQL 2005
- Change owner of the database to sa (if you logged in using your windows account you will be the owner of attached database).
Run this script with output format set to Text and copy the script and run to change the owner.
select 'USE '+name+ char(10) + 'GO' + char(10) + 'exec sp_changedbowner ''sa''' + char(10) + 'GO' + char(10)
from sys.databases
where database_id>4
- All migrated databases are set with compatibility level set to SQL 2000 (8.0), change the compatibility level by running this script to generate the script that changes the compatibility. Run the generated script from master database.
select 'EXEC dbo.sp_dbcmptlevel @dbname=N'''+name+''', @new_cmptlevel=90'
from sys.databases
where database_id>4
- Update statistics of all databases. Use the following script to generate script that updates the statistics.
select 'USE '+name+ char(10) + 'GO' + char(10) + 'EXEC sp_updatestats' + char(10) + 'GO' + char(10)
from sys.databases
where database_id>4
- Alter the database setting to change the default isolation level from READ COMMITTED to READ_COMMITTED_SNAPSHOT. Run the following script to generate script.
select 'alter database '+name+ ' set READ_COMMITTED_SNAPSHOT ON' + char(10) + 'GO' + char(10)
from sys.databases
where database_id>4
- Alter the database setting to change the default recovery option from PAGE_VERIFY – None to PAGE_VERIFY – CHECKSUM. Run the following script to generate script.
select 'alter database '+name+ ' set PAGE_VERIFY CHECKSUM' + char(10) + 'GO' + char(10)
from sys.databases
where database_id>4
- Fixed database users. Run sp_change_users_login ‘report’ on the database and see if any orphan users are there. If there are orphan users there, create logins for them from old server using sp_help_revlogin.
- If database has full text catalogs, create them on new/move them and create the job to populate them