High-version SQL server data backup is restored to low-version SQL Server Data Backup

High-version SQL server data backup is restored to low-version SQL Server Data Backup

Recently encountered a problem:

If you want to restore the data backed up by Sqlserver in a later version to an earlier version of SqlServer, you cannot restore the database directly in SqlServer. Therefore, after a series of questions, we can summarize the available methods.

First,You have to install the high version and the low version of SqlServer on your computer or the database where you can connect the high version of SqlServer (to facilitate subsequent data copying ),

Step 2,Open the database you want to back up in the database of the higher version, and delete the login name or user related to the database you backed up. Right-click Database> task> Generate script. In the "select object" step of generating the script, select "script options for compiling the entire database and all database objects ". Set the "Write use database script" and "schema qualified Object Name" options to False in the advanced options of "set script writing options" for the generated script, and select "server version script" as you the earlier version of the database. Generate scripts.

Step 3,Create a new database in your earlier database version (with a casual name) and open the SQL script. Delete all operations related to the original database name in the script, and divide the database into two parts from the first alter operation, and perform the first sub-Table creation operation on the new database.

Step 4,Right-click the newly created database, choose task> import data, set the data source to a database of the high version, and set the target to the current database. Copy one or more charts. Select all data tables, and then select "enable ID insertion" in "Edit ing. Import Data

Step 5,Finally, execute the SQL script in the second part to create an index for the database!

OK. In this later version, the SQL Server database is successfully restored in the earlier version of SQL Server.

Share the second case:

If you want to restore the data backed up by the SQL Server and later versions to the SQL Server R2, but you cannot restore the database directly in SQL Server, you can use the following methods to restore the data.

The SQL script generated through the later version is executed in the lower version, and the data can be retained.

Step: task-generate script-

Next-> advanced, select the database version and script data type as the Architecture and Data to retain the data.

Continue to the next step.

Finally, in the lower version of SQL database:

Create a database in the script, and then execute SQL to generate the table structure and data.

Restored successfully!

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.