Possible to restore a backup of SQL Server 2014 on SQL Server 2012?

sql sql-server sql-server-2012 compatibility sql-server-2014
RayofCommand picture RayofCommand · Jul 30, 2014 · Viewed 136.4k times · Source

I know that you can't (at least not easily) restore a SQL Server 2012 backup on SQL Server 2008. But how does it work for SQL Server 2014 to SQL Server 2012 ?

On database level there is the property to adjust the compatibility mode to any other SQL Server version.

How does this helps or work ? Will it only disallow the features from 2014?

To be honest I already tried to restore a backup, but 2012 didn't recognize the datafile, so I couldn't click ok Button to start the restore procedure.

Did I miss some important option ?

Answer

marc_s picture marc_s · Jul 30, 2014

You CANNOT do this - you cannot attach/detach or backup/restore a database from a newer version of SQL Server down to an older version - the internal file structures are just too different to support backwards compatibility. This is still true in SQL Server 2014 - you cannot restore a 2014 backup on anything other than another 2014 box (or something newer).

You can either get around this problem by

  • using the same version of SQL Server on all your machines - then you can easily backup/restore databases between instances

  • otherwise you can create the database scripts for both structure (tables, view, stored procedures etc.) and for contents (the actual data contained in the tables) either in SQL Server Management Studio (Tasks > Generate Scripts) or using a third-party tool

  • or you can use a third-party tool like Red-Gate's SQL Compare and SQL Data Compare to do "diffing" between your source and target, generate update scripts from those differences, and then execute those scripts on the target platform; this works across different SQL Server versions.

The compatibility mode setting just controls what T-SQL features are available to you - which can help to prevent accidentally using new features not available in other servers. But it does NOT change the internal file format for the .mdf files - this is NOT a solution for that particular problem - there is no solution for restoring a backup from a newer version of SQL Server on an older instance.

Related questions

Convert Date format into DD/MMM/YYYY format in SQL Server

I have a query in SQL, I have to get a date in a format of dd/mm/yy Example: 25/jun/2013. How can I convert it for SQL server?

Read More
Recover unsaved SQL query scripts

How to recover the unsaved scripts if the SSMS crashes / unsaved tab gets accidentally closed?

Read More
SQL Server IF EXISTS THEN 1 ELSE 2

Using Sql Server 2012. I have a stored procedure and part of it checks if a username is in a table. If it is, return a 1, if not, return a 2. This is my code: IF EXISTS (SELECT * FROM tblGLUserAccess WHERE GLUserName =…

Read More