Up-gradation of database with backup and restore

1. Script our logins on SQL 2000 Server using sp_help_revlogin.

2. Script out jobs and linked servers from SQL 2000 Server.

3. Stop all the application of database.

4. Start the database in single user mode
http://smarttechways.com/2014/09/04/start-sql-server-in-single-user-mode

5. Backup the database

Right click on database name, Go to backup, take full backup of database

6. Copy the backup file to SQL Server 2008 Machine

7. Restore the database on SQL Server 2008 Machine

8. Run the output of sp_help_revlogin on SQL 2008 to create logins.

9. Recreate SQL agent jobs and linked Server on new Server.

10. Change compatibility level on the restored databases to 100.

11. DBCC CHECKDB ('dbname') with all_errormsgs, no_infomsgs, data_purity for check the database consistency.

12. Run DBCC UPDATEUSAGE on the restored databases
DBCC UPDATEUSAGE('database_name') WITH COUNT_ROWS

13. Recompile all SP’s using sp_recompile 'Procedure Name'

14. Change the recovery model (if different from SQL 2000) to FULL.
If you change to FULL recovery model,
then MAKE SURE you do Transaction Log backups frequently.
This will help you to recover point-in-time as well as not bloat your T-Log.

15. In SQL 2005 and up, Database Mail was introduced. So you have to migrate from SQL Mail to Database Mail.

USE [master]
GO
sp_configure 'show advanced options',1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'Database Mail XPs',1
GO
RECONFIGURE
GO