Can we restore SQL Server 2008R2 backup on SQL Server 2014?

Question

Currently, one of our product running on the SQL Server 2008R2.

For new installation client want to go for SQL Serer 2014 because at this location Microsoft is not selling licence for the SQL Server 2008R2.

Answer 1

In SQL Server 2014, you can restore a user database from a database backup that was created by using SQL Server 2005 or a later version. However, backups of master, model and msdb that were created by using SQL Server 2005 through SQL Server 2012 cannot be restored by SQL Server 2014. Also, backups created in SQL Server 2014 cannot be restored by any earlier version of SQL Server.

Source

• vishal: Does that mean that I can directly import backup and restore it. Do you know maximum size of database that 2014 suporting

Answer 2

Yes.

Wen you restore database backup that created with older version of SQL Server in to new version, SQL Server automatically updated your database in new version. But don't change compatibility level of your database. You can change Compatibility level of your database after restore complete by following query :

ALTER DATABASE test SET COMPATIBILITY_LEVEL = 120

• vishal: Does that mean that I can directly import backup and restore it. Do you know maximumsize of database that 2014 suporting
• mehdi lotfi: Yes. you can directly backup from SQL Server2008R2 and restore into SQL Server 2014 without any limitation.
• vishal: Right now, we are running on the Window SERVER 2008 R2 64 Bit OS - Service Pack 1. Is SQLSERVER2014 compatible with that?
• mehdi lotfi: @vishal. Yes. you only must restore your backup. I Upgrade all on my database on my company from SQL Server 2008R2 and SQL Server2012 to SQL Server 2014 with backup and restore (about 45 database).
• Marc Roussel: @mehdi lotfi Does changing to 120 after restoring from 2008 R2 to 2014 gives some kind of performance gain ? Because actually it is slower than when the database was in 2008 R2

Answer 3

In SQL Server 2014 SP1 there are breaking changes to the restoreheader definition (3 new fields added). If you use it, you should have a look at this thread: breaking changes to SQL Server 2014 SP1 - restoreheader