64

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 ?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
RayofCommand
  • 4,054
  • 17
  • 56
  • 92
  • possible duplicate of [How to restore SQL Server 2014 backup in SQL Server 2008](http://stackoverflow.com/questions/17743956/how-to-restore-sql-server-2014-backup-in-sql-server-2008) – Vulcronos Jul 30 '14 at 17:04
  • 4
    i saw the question for 2014 to 2008 of course, but I thought this is different – RayofCommand Jul 30 '14 at 17:50

2 Answers2

82

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.

xgMz
  • 3,334
  • 2
  • 30
  • 23
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 4
    Is it safe to assume same is true for 2016 vs 2014? I mean 2016 backup not being compatible for restore on 2014 server, while the 2016 server can restore a backup from the 2014 server. – cusman Jan 26 '17 at 16:16
32

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

Jordan Medina
  • 329
  • 3
  • 2
  • 10
    technically not 'restoring a backup' but it does accomplish the goal of moving data from server[new] to server[old]. – Cos Callis Oct 31 '15 at 19:31
  • 2
    Nice "workaround" for dev environments. I wanted to move a DB from 2016 to 2014 and did not want to upgrade the other instance or spend too much time migrating the data. This trick was perfect. – Sébastien Sevrin Aug 31 '16 at 09:42
  • 2
    To find the Wizard, click on the Database in SSMS, select Tasks, then Export Data. – Greg Gum Oct 18 '16 at 17:46
  • 2
    Expanding on this a bit... When I just used the export data, I lost all my indexes, constraints, etc. I had luck generating the scripts for all the objects in the 2014 database, then running those on the 2012 server. I then disabled all the constraints, and ran export data to copy all the data over to the new db shell. Just remember to enable identity insert from the options in the export data wizard. Also remember to enable the constraints after. – ScottLenart May 19 '17 at 20:24
  • -- Disable constraints EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL" GO --- Enable constraints EXEC sp_MSforeachtable @command1="ALTER TABLE ? ENABLE TRIGGER ALL" GO EXEC sp_MSforeachtable @command1="ALTER TABLE ? CHECK CONSTRAINT ALL" GO – ScottLenart May 19 '17 at 20:26
  • @ScottLenart You rock! This is the only method that would transfer a database correctly from SQL Server 2016 to SQL Server 2012. I had tried a regular export with Identity but to no avail.. only creating a database from generated scripts and then exporting with identities into it worked! A side note, I did not have to disable constraints. – Chockomonkey Jul 30 '20 at 20:59