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

Question

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

User · Answer

Sure it s possible    use Export Wizard in source option use SQL SERVER NATIVE CLIENT 11  later your source server ex 192 168 100 65 SQLEXPRESS next step select your new destination server ex 192 168 100 65 SQL2014  Just be sure to be using correct instance and connect each other  Just pay attention in Stored procs must be recompiled

User · Answer

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  gt  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

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

The answer is

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2012

Examples related to compatibility

Examples related to sql-server-2014

Tags