How to downgrade a database from a higher version to a lower version

Microsoft SQL Server is an ideal choice for many enterprises. New versions are usually faster and more powerful, and we can't wait to upgrade old database versions to new versions. This is understandable and usually smooth, back up the database in the old version, and restore it in the new version of the database. for Microsoft, compatibility is usually not a problem, but sometimes we need to go back to the lower version, for example, if the customer only has a database of a lower version, it is usually unrealistic to back up the database of a higher version and restore it with a lower version. It is difficult to downgrade the database easily.
At this time, we naturally thought that the script can be used to create tables, views, stored procedures, functions, and other functions, but data cannot be imported, if there are hundreds of tables in the database, manual data import is a dream. For this reason, I use a piece of code to implement the automatic import function. The specific steps are as follows:
1. Create a link to a later version of the database in a lower version of the database. If you have never used a link to the server, click Baidu, which is very simple.
2. use Management studio of SQL server to connect to the database of a later version, and configure the options in "SQL Server Object Resource Manager" → "compile script, remove "include descriptive Header"/"Write USE <database> script"/"write if not exists clause", check "write index script ", note: select the version corresponding to the database of the lower version for "writing scripts for the server version". Other options can be set according to the actual situation.
3. Create a database in a lower-version database with the same name as in the higher version.
4. go back to the High Version database, select all user tables (Press F7 to open the details of the object Resource Manager), right-click the table, and choose create script. We are delighted to find that, the created script is divided into two parts: the first part is the creation of the table structure, the latter part is to add constraints/associations for each table and other information, we first copy the previous part, paste it to the database of earlier versions for execution. In this way, the table structure is created.
5. Execute the following script in the earlier version database to import data from the later version:
-- Set the name of the connected database
Declare @ linkdb nvarchar (100)
Select @ linkdb = '[192.168.1.102, 3000]. [station_center]'
-- Create a table variable to store all user table names
Declare @ TB table (tablename nvarchar (50 ))
Insert @ TB
Select [name] int from sysobjects where xtype = 'U' order by [name]

-- Define the SQL statement to be executed, the name of the table for the current operation, and whether the table has an ID field
Declare @ SQL nvarchar (3000), @ tbname nvarchar (50), @ isisidentity bit

-- Import data to each table one by one through a cursor
Declare scu_ticks cursor local forward_only read_only static
For select tablename from @ TB
Open scu_ticks

Fetch from scu_ticks into @ tbname
While (@ fetch_status = 0)
Begin
-- Check that a field is identified. If yes, disable it first.
If exists (select *
From syscolumns a inner join sysobjects D on
A. ID = D. id and d. XTYPE = 'U' and d. NAME <> 'dtproperties'
Where d. NAME = @ tbName and columnproperty (A. ID, A. NAME, 'isidentity ') = 1
)
BEGIN
SET @ IsISIDENTITY = 1
END
ELSE
BEGIN
SET @ IsISIDENTITY = 0
END

SELECT @ SQL ='
Truncate TABLE '+ @ tbName
IF @ IsISIDENTITY = 1
-- With an ID field, temporarily disable it
BEGIN
SET @ SQL = @ SQL +'
SET IDENTITY_INSERT '+ @ tbName +' ON
'
END
-- Import data from a linked Database
SET @ SQL = @ SQL +'
Insert into '+ @ tbName +'
Select * from '+ @ LinkDB +'. dbo. '+ @ tbName
IF @ IsISIDENTITY = 1
-- Restore the ID
BEGIN
SET @ SQL = @ SQL +'
SET IDENTITY_INSERT '+ @ tbName +' OFF
'
End
Exec (@ SQL) -- execute the combined SQL statement
Fetch next from scu_ticks into @ tbname
End
Close scu_ticks
Deallocate scu_ticks

6. Now that there is data in the table, copy the second half of the script produced in step 1 and execute it. In this way, the Association/constraint between tables will exist.
7. Finally, create the view, function, stored procedure, and other content using scripts.
In fact, what we really need to solve is how to automatically import data. Others are implemented by automatically generated scripts.