SQL Server Migration – Queries on Database Migration

sql serversql-server-2008sql-server-2012

I have a SQL Server 2008 database of size around 1 TB. I want to migrate it to SQL Server 2012. I have few queries:

  1. To migrate, I will take full backup of 2008 and restore it on 2012. I want to know if the compatibility version internally remains 2008 or changes to 2012?
  2. To leverage full potential of 2012 after migration, what considerations to take during migration. After migration all 2012 new features and engine improvements should be available for the migrated database.

Best Answer

  1. To migrate, I will take full backup of 2008 and restore it on 2012. I want to know if the compatibility version internally remains 2008 or changes to 2012?

Restore database backup on New SQL Server 2012 Server and then change compatibility level from 2008 to 2012 for each database.
Use one of the following methods.

i) Use script to change compatibility level:

USE [master]    
GO    
ALTER DATABASE [yourDatabaseName] SET COMPATIBILITY_LEVEL = 110

ii) Database Properties -> Options -> Compatibility Level -> SQL Server 2012 (110)

  1. To leverage full potential of 2012 after migration, what considerations to take during migration. After migration all 2012 new features and engine improvements should be available for the migrated database.

i) Compatibility Level change is important since it allows the usage of new SQL Server 2012 features.

ii) You must read SQL Server Database Engine Backward Compatibility article and ensure that you are not using any discontinued\deprecated feature that breaks SQL Server 2012 compatibility.

Note: You can run trace for deprecated calls

Related Question