SQL Server High-version data backup restored to low version _mssql

Recently met a problem:

You want to restore the SQL Server backup data to a lower version of SQL Server, but it is not possible to restore the database directly in SQL Server, so after a series of consulting summarizes the available methods.

first of all, you have to have a high and a lower version of SQL Server that you will be working on your computer, or you can connect to the database where SQL Server's high version is located (easy to copy data later),

Step Two, open the database you need to back up in the high version database, and delete the login or user associated with the database you backed up. Right-click Database-> task-> generate script. Select script options for writing the entire database and all database objects in the Select object step of the build script. Set the "Write use DATABASE script" and "Schema qualified object name" option to False in the advanced selection of the build script's set scripting options and select Server version script as the version of your lower version of the database. Generate Scripts.

step Three, create a new database in your lower version of the database (name casually), and open the SQL script. Deletes all operations on the original database name in the script, divides the database from the first alter operation into two parts, and performs the first part of the new database-building operation.

Step Fourth, right-click the new Database-> task-> Import data, set the data source to a higher version of the database, the target is set to the current database. Then choose to copy one or more charts. Select all the data tables, and then in edit mappings, enable identity insertion. Import data

The fifth step, and finally execute the second part of the previous split SQL script, the database index!

Ok. To this high version of SQL Server database successfully restored in low version SQL Server

Share a second case:

You want to restore data from a Sqlserver2014 high backup to a lower version of SQLSERVER2008R2, but this cannot be directly restored in SQL Server, and can be restored successfully by the following methods.

SQL scripts are generated through a high version and are executed in a lower version, and the data can be retained.

Step: Task-"Generate script-"

Next-Advanced, select the database version and write script data types for schema and data, you can preserve the data.

Continue to the next step to finish.

Finally, in the lower version of the SQL database:

Create a new database in the script, and then implement the SQL generation table structure and data.

Perfect Restore Success!

The above is the entire content of this article, I hope to help you learn, but also hope that we support the cloud habitat community.