Moving the database to an older version of MS SQL Server

You have a MS SQL Server database that you need to transfer to another physical computer. You have already made a backup and happily proceed to recovery. But it turns out that an older version of MS SQL Server is installed on the computer where you need to transfer the database. Stack Overflow assures you that everything is bad. But is it really?


Of course, moving a database from a newer version to an old one is not a classic and not the most correct work scenario. But often databases are created such that they support all newer versions of SQL, starting with some, for example, from 2008 R2, because direct compatibility with MS SQL is more than excellent. And, for example, your client has already set himself MS SQL 2016, and you have MS SQL 2014 on your test server for development. And you want to deploy a client base for yourself to figure out where his data is confused.

Microsoft disowned the problem - they say they have no backward compatibility, and that's that. A backup created in a newer server cannot be restored on an older server. Yes, they have DTS-type tools, database copying, export-import, etc. But they are so inconvenient and cumbersome that a normal transfer of a large database with a variety of tables is not very convenient to do with them. In any case, personally I did not succeed.

Yes, you can generate SQL scripts for the entire database, including data. But imagine, you have a lot of blob-fields with big data in your database, and in general the size of the entire database is 500+ GB. Imagine how much such a script will take, how long it will be generated and executed.

So, the task is to exactly recreate the database (structure and data) from the new version of MS SQL server in the older version. I came up with a fairly simple solution that I want to share. Of course, this solution has a significant number of restrictions, but in my opinion it is better than DTS and scripts.

The number one limitation is that you need access through both MS SQL Management Studio to both old and new servers. If this is not possible, then it should be possible on the machine from where you need to transfer the database, install the SQL version to which you need to transfer the database, to transfer the database first to this version locally, and then drag it through a backup or directly via * df database files (via Detach / Attach) to a new machine (the version of SQL Server will already be the same in this case).

Another limitation is that you will need a database schema script (all objects, including tables, indexes, collections, stored procedures, triggers, etc.) without data, and the instructions for creating Foreign Key Constraints should be in this script at the very end, separate from the script for creating the tables themselves.

I will describe briefly the data transfer algorithm itself. All actions are performed in the Management Studio session connected to the server to which the base should be transferred.

1) On the new server we create an empty database with the same files and file groups as the portable database.

2) Using the database schema script, we create all database objects (tables, indexes, views, triggers, stored procedures and functions), but without creating Foreign Key Constraints. You cannot create FK at this stage, since they will interfere with the insertion of data.

3) We connect the database from which we will transfer the data as Linked Server, so that it can be used in queries to the new database to access the old database.

EXEC sp_addlinkedserver     
   @server=N'LinkedServerAlias',   
   @srvproduct=N'',  
   @provider=N'SQLNCLI',   
   @datasrc=N'LinkedServerHost\LinkedServerName';  
EXEC sp_addlinkedsrvlogin 'LinkedServerUser', 'false', null, 'RealUser', 'RealUserPassword';

4) Since the database structures are the same; we use the sp_msforeachtable built-in stored procedure that allows you to execute a query on each database table to generate a script for transferring data from the old database to a new one through a query

INSERTINTO ? 
  SELECT * FROM ?

Instead of the question mark, sp_msforeachtable substitutes the name of each table and executes the query several times (once for each table).

Here I came across the largest number of rakes.

a) Problem number one is that for tables with IDENTITY fields, you need to call:

SET IDENTITY_INSERT ON; 
--INSERT INTO ... (сама вставка);SET IDENTITY_INSERT OFF;

b) Problem number two is that it is impossible to make this call on tables that do not have IDENTITY fields, so you need to dynamically determine whether an IDENITY column is in the table or not.

This can be done with such a query:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
WHERE (TABLE_NAME='SomeTable') AND (COLUMNPROPERTY(object_id('dbo.SomeTable'), COLUMN_NAME, 'IsIdentity') = 1)

c) Problem number three is that, as it turned out, in the IDENITY_INSERT ON mode you cannot do

INSERTINTO ... SELECT * FROM ...

, and you need to list specific fields.

You can enumerate the fields of a table into a row with such a query:

SELECTSUBSTRING(
    (SELECT', ' + QUOTENAME(COLUMN_NAME)
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'SomeTable'ORDERBY ORDINAL_POSITION
        FORXMLpath('')),
    3,
    200000);

4) Generate the insert script for all tables:


5) Execute the generated data transfer script

6) Execute the script to create all the Foreign Key Constraints (now you can).

7) Done! You moved the database from the new SQL server to the old one, even though it was considered impossible. Moreover, the transfer is carried out only one and a half times slower than the speed of data transmission over the network, i.e. pretty fast.

8) We clean up after ourselves (we disconnect Linked Server):

EXEC sp_droplinkedsrvlogin 'LinkedServerUser', null;
sp_dropserver 'LinkedServerAlias';

Method limitations

1) By a similar method it will not be possible to transfer tables in which there are columns with the XML type.
Surely there are many other restrictions, because In the database that I transferred in a similar way, many possibilities of the SQL server were not used. You can write about restrictions in the comments, and I will add an article to them.

Thanks for attention! I hope someone will help.