… Continuing from PART 1

Database check is a very crucial part of Database migration. Once the Database has been moved to SQL Server 2005/2008, it is time the perform other tasks on the database like Integrity check and Compatibility level changes.

SQL Server 2000 database is in 80 compatibility mode, where as the the databases for SQL server 2005 and 2008 are 90 and 100 modes.

What is Compatibility Level/Mode :  There are certain compatible features in versions of SQL Server on which the database will depend. Like the Maintenance plan might not backup the database in 70 compatibility Level etc.

Right Click on Database and click on properties, then under options, we can see the compatibility Level.

Just pick the new compatibility level (90) from the Drop down and click OK and we are done.

Using T-SQL :

USE [master]
GO
EXEC dbo.sp_dbcmptlevel @dbname=N’pubs’, @new_cmptlevel=90
GO

OR

sp_dbcmptlevel ‘pubs’,’90′

Check the Compatibility Level with :

select compatibility_level from sys.databases where name = ‘pubs’

After Checking the Compatibility level, run the DBCC CHECKDB on the Database to find any irregularities.

DBCC CHECKDB (PUBS) WITH ALL_ERRORMSGS

if Everything looks Good in the Results: Execute UPDATEUSAGE to correct the pages and row counts in the database.

Execute the DBCC UPDATEUSAGE(0) or DBCC UPDATEUSAGE (pubs)

Move the logins/users associated with the Database or Create New Login/users

If there are tons of users on the database, it would be wiser to move the logins using this article

After Creating/Moving the Logins/users, check for orphaned users with : Read here

and this Covers our Database Migration Topics. Now Wasn’t that FUN !