patternsqlMinor
Queries on database migration
Viewed 0 times
databasequeriesmigration
Problem
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:
- 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?
- 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.
Solution
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
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
http://beyondrelational.com/modules/2/blogs/77/Posts/19759/0311-sql-server-database-files-upgraded-from-version-nnnn-to-mmm-why-databases-cannot-be-restored-ba.aspx
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
http://beyondrelational.com/modules/2/blogs/77/Posts/19759/0311-sql-server-database-files-upgraded-from-version-nnnn-to-mmm-why-databases-cannot-be-restored-ba.aspx
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
http://beyondrelational.com/modules/2/blogs/77/Posts/19759/0311-sql-server-database-files-upgraded-from-version-nnnn-to-mmm-why-databases-cannot-be-restored-ba.aspx
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
http://beyondrelational.com/modules/2/blogs/77/Posts/19759/0311-sql-server-database-files-upgraded-from-version-nnnn-to-mmm-why-databases-cannot-be-restored-ba.aspx
Code Snippets
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.Context
StackExchange Database Administrators Q#110870, answer score: 2
Revisions (0)
No revisions yet.