3

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.
5
  • Consider that you can just copy datafiles and reattach them. In any case you will need to manually rise compatibility mode. For the steps after, take a look here: thomaslarock.com/2011/12/…
    – user_0
    Aug 13, 2015 at 9:01
  • 2
    @user_0 No, please don't advocate that risky method: blogs.sqlsentry.com/aaronbertrand/bad-habits-file-backups Aug 13, 2015 at 11:44
  • @AaronBertrand, I agree. I will never use mdf files as backup. But, somethimes, business says: database migration must last no longer than a couple of hours. So I detached and reattached files :-(
    – user_0
    Aug 13, 2015 at 12:03
  • @user_0 why didn't you already have backups to use? Why wouldn't you set up log shipping or mirroring, which can make your actual downtime seconds instead of the time it takes to copy all those files? There are many better answers. Detach/attach is the worst and most reckless way to do this, and it actually takes longer than a sane, planned approach. Aug 13, 2015 at 15:40
  • @AaronBertrand We used attached disk to new server, just seconds of activity. Plus was a 2000 to 2014 migration, with a bridge machine to raise compatibility. It will take long to discuss here, but I'd like to discuss this with you if possible.
    – user_0
    Aug 13, 2015 at 17:12

2 Answers 2

2
  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

2

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.