SQL server Migration from 2008 to 2012

The project that I work at the present time has received the new SQL Server hardware,

So now is the time to upgrade from the SQL server 2008 to the 2012.

An upgrade, in new environment, refers to the process of moving from the SQL server version 2008 to the new version 2012.

There are two approaches for upgrading database:

1.      In position: The SQL Server is upgraded where it is currently installed

2.      Migration: A new environment is installed; the data is copied to it and configured with the existing data.

The approach which I am going to take is the second one, since I have a new server and I am going to do a fresh SQL Server 2012 installation. 

There is an article on MSDN that explains the SQL Server Database Engine Backward Compatibility. We must read it here. The upgrade of the databases can be implemented in the following steps:

1.      Backup the SQL Server 2008 databases and restored them in SQL 2012.

2.      Change each database compatibility level from 2008 to 2012 and update statistics.

ALTER DATABASE Database SET COMPATIBILITY_LEVEL = 110;

DECLARE @sql NVARCHAR(MAX)

set @sql=  N'';

SELECT @sql = CHAR(13) + CHAR(10) + 'UPDATE STATISTICS '

  + QUOTENAME(SCHEMA_NAME(schema_id))

  + '.' + QUOTENAME(name) + ' WITH FULLSCAN;'

  FROM sys.tables;

PRINT @sql;

EXEC sp_executesql @sql;

where [database] is the database to change the compatibility level

We can do it in a more compact way with

EXEC sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'

or go to the database properties and on the options select the Compatibility Level 110.

3.      Check the logical and physical integrity of all the objects in the upgraded databases:

DBCC CHECKDB([database]) WITH NO_INFOMSGS

Where [database] is the database to run the integrity checks NO_INFOMSGS option suppresses all informational messages.

If DBCC printed any error messages, we must fix them so that your database will work correctly.

The following script can be useful to check database compatibility level:

select name, compatibility_level , version_name =

CASE compatibility_level

    WHEN 65  THEN 'SQL Server 6.5'

    WHEN 70  THEN 'SQL Server 7.0'

    WHEN 80  THEN 'SQL Server 2000'

    WHEN 90  THEN 'SQL Server 2005'

    WHEN 100 THEN 'SQL Server 2008/R2'

    WHEN 110 THEN 'SQL Server 2012'

    WHEN 120 THEN 'SQL Server 2014'

END

from sys.databases order by compatibility_level