Importing an mssql high-version database into a lower version, suitable for data transfer between different versions.
Published: 2018-11-14
Applicable scenario: transferring data from a higher-version database to a lower version. Due to sqlserver backward compatibility but not forward compatibility, higher-version data cannot be restored using bak, nor can it be attached. It can only be imported via sql. Two methods are introduced below.
Because many lower-version databases have stopped being updated, it is strongly recommended to import into a higher version using the following method. After the import is complete, perform a backup, and you can obtain a higher-version database bak. In the future, you can directly restore from bak in the higher version.
Restoring from a lower version to a higher version is compatible. Generally, use a bak file to restore, or attach an mdf file. Click to view the tutorial. If it fails, you can refer to the following methods.
Method 1: Import using Navicat Premium software.
Navicat Premium is a powerful sql connection tool that supports connecting to many databases such as mysql, sqlserver, sqlite, etc. This article does not provide a separate download address; users need to download it from the Internet themselves.
1. Open the software, click Connection, and select sql server.
Add the connection information for the old and new servers. The connection name can be arbitrary. Click Confirm to add.
To distinguish them, we name them "New Database" and "Old Database" respectively.
2. Data export.
Directly double-click "Old Database" to connect to the database. Double-click to expand step by step. Generally, tables are under dbo, and you can see the database tables.
Right-click dbo -- Dump sql file -- Structure and Data Choose a directory to save, wait for execution to complete. Seeing Finished Successfully means it completed successfully.
3. Data import
Double-click to connect to the new server, double-click the database to open it, then right-click the database -- Run sql file
Select the sql file just exported, then start execution.
After the import is complete, check whether the database tables and views are normal.
If there are errors, you can check and handle them based on the errors, or directly refer to Method 2 below and try it.
Method 2: sqlserver import/export function. (Views cannot be migrated very well; if there are views, use Method 1)
Condition: sqlserver manager is installed on the local machine, and you have sqlserver management permissions.
1. Log in to the original database management, right-click the database to be restored -- Tasks -- Export Data.
2. Fill in the local database login information and select windows authentication. If it is a database elsewhere, select sqlserver authentication and fill in the database address, account, and password.
3. Click Next, fill in the target server database login information, and select sqlserver authentication.
As shown in the following figure, continue to the next step until completion.
The import is now complete.
There may be errors; you can check and handle them based on the errors. For example, if you encounter the following error
Solution:
In the Import/Export Wizard, on the "Select Source Tables and Views" page, after checking the tables to import/export, click Edit Mappings, and select “Enable identity insert".