Possible to Restore a Backup of SQL Server 2014 on SQL Server 2012

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

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.

SQL Server 2014 backup to 2012

To my knowledge, there are basically three two options for migrating a database to a lower version of SQL Server, without using 3rd party tools:

• Generate Scripts (not really suitable for large amounts of data)
• Custom Scripting and BCP or Import/Export Wizard
• SQL Server Integration Services (Transfer Database Task)

First option is not suitable in your case, as noted.

Second option is to simply script the structure of the database, and then use the Import/Export Wizard to copy the data, one table at a time. Note, that if you have foreign key constraints in your database, you might want to disable the constraints until after you have populated all your tables with data. This blog post explains in details how this can be done.

Third option uses the SISS Transfer Database Task which basically uses SMO to create the objects on the destination server and then transfers the data. This is the recommended way of migrating a database between SQL Server instances of different versions. SSIS requires that you have installed SQL Server Data Tools - Business Intelligence (SSDT-BI) for Visual Studio. Before SQL Server 2012, this was called Business Intelligence Development Studio (BIDS). You can download these here:

• Microsoft SQL Server Data Tools - Business Intelligence for Visual Studio 2013
• Microsoft SQL Server Data Tools - Business Intelligence for Visual Studio 2012
• If you don't have Visual Studio, SSDT-BI / BIDS is included in the SQL Server installation.

Can we restore database to SQL Server 2017 taken from SQL Server 2012?

Yes, SQL Server is backward compatible. You can restore a database from any previous version of SQL Server that was supported at the time of the release. For SQL Server 2017, you can therefore restore databases from SQL Server 2008 to 2017.

SQL Server, however, is not forward compatible. Once a database is restored on a more recent version of SQL Server you cannot create a back up of that database and restore it to an older version; even if the database had the correct compatibility setting. The upgrade process is one way.

Export SQL Server 2014 database (2008 compatible) in a backup file compatible with 2012

You have to script the SQL database creation, in SQL Server Management Studio :

right click on the DB -> Task -> Generate script

From there a series off dialog to choose what to generate with the SQL command creation and where, in clipboard or file (recommanded for big DB), with optionally the data as some Insert Into SQL command. To find the data option click on the "Advanced" button and search for "Type of data to script"

Possible to RESTORE older version MS-SQL database onto SQL server 2017 without it updating?

When the above runs I get: Database 'Polly' running the upgrade step
from version 782 to version 801. ... Database 'Polly' running the
upgrade step from version 868 to version 869.

Would like to see no upgrade steps, but only if database still usable.

This is not possible. Every version of SQL Server has its data and log files structure that differs between server versions. And if you restore or attach database from lower version db files are one-way updated to have a structure that the current version of SQL Server needs.

It's impossible to not upgrade because the current version of server needs that new structure for db files.

All speculations around compatibility level / read_only property will not help at all, current server will never run with files that are not of the structure it needs.

In case of readonly database its files will be upgrade but the database will remain readonly.

Compatibility level has nothing to do with database version (version of db files) at all. It just tell to server what version of query optimizer should be used, what legacy syntax can still be parsed, etc.

---

Related Topics