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:
I did small tests Database compatibility level remains the same.
1.Created dummy database on 2008r2
2.Restored it on 2012
So while restoring,i could see version being changed,but after restoring compatability level remained the same.So you need to alter database to 110 compatibility level to get benefits of SQL 2012
Database 'backupdb' running the upgrade step from version 699 to version 700.
Database 'backupdb' running the upgrade step from version 700 to version 701.
Database 'backupdb' running the upgrade step from version 701 to version 702.
Database 'backupdb' running the upgrade step from version 702 to version 703.
Database 'backupdb' running the upgrade step from version 703 to version 704.
Database 'backupdb' running the upgrade step from version 704 to version 705.
Database 'backupdb' running the upgrade step from version 705 to version 706.
From Paul Randal Q&A sessions,Nakul blogs,below are the details of version.
These file versions are the internal storage format, and have nothing to do with the compatibility level of the database
For second question,i would recommend running the database upgrade advisor,please see below link for more details
https://technet.microsoft.com/en-us/library/ms144256(v=sql.110).aspx
- 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)
- 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
