June 13, 2019 at 11:11

Transfer backup data of a new version of MS SQL Server to an older version

Background


Once, to reproduce a bug, I needed a backup of the production base.

To my surprise, I came across the following limitations:

  1. The database backup was made on the version of SQL Server 2016 and was not compatible with my SQL Server 2014 .
  2. On my working computer, Windows 7 was used as the OS , so I could not upgrade SQL Server to version 2016
  3. The supported product was part of a larger system with a heavily connected Legacy architecture and also turned to other products and bases, so deploying it to another station could take a very long time.

Given the above, I came to the conclusion that the time has come for crutches of non-standard solutions.

Data recovery from backup


I decided to use the Oracle VM VirtualBox with Windows 10 (you can take a test image for the Edge browser from here ). SQL Server 2016 was installed on the virtual machine and the application database was restored on it from the backup ( instructions ).

Configure Access to SQL Server in a Virtual Machine


Further, it was necessary to take some steps to make it possible to access SQL Server from the outside:

  1. For a firewall, add a rule to skip requests to port 1433 .
  2. It is desirable that access to the server does not go through windows authentication, but through SQL with a username and password (it’s easier to configure access). However, in this case, you must remember to enable SQL authentication in the properties of SQL Server.
  3. In the user settings on SQL Server, on the User Mapping tab, specify the user role db_securityadmin for the restored database .

Data transfer


Actually the data transfer itself consists of two stages:

  1. Data schema transfer (tables, views, stored procedures, etc.)
  2. Transfer data itself

Data schema migration


We perform the following operations:

  1. Select Tasks -> Generate Scripts for the portable database.
  2. We select the objects necessary for transferring or leave the default value (in this case, scripts will be created for all objects in the database).
  3. Specify settings for saving the script. It is most convenient to save the script into a single file encoded in Unicode. Then, in the event of a failure, it is not necessary to repeat all the steps again.

After saving the script, it can be executed on the original SQL Server (old version) to create the required database.

Attention: after running the script, you need to check the compliance of the database settings from the backup and the database created by the script. In my case, there was no setting for COLLATE in the script, which led to a crash when transferring data and dancing with a tambourine to recreate the database using an augmented script.

Data transfer


Before transferring data, you must disable the check of all restrictions based on:

EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'

We transfer data using the Tasks -> Import Data data import wizard on SQL Server, where the database created by the script is located:

  1. Specify the source connection settings (SQL Server 2016 on the virtual machine). I used Data Source SQL Server Native Client and the aforementioned SQL authentication.
  2. Specify the settings for connecting to the destination (SQL Server 2014 on the host machine).
  3. Next, configure the mapping. You must select all non-read-only objects (for example, you do not need to select views). As additional options, select "Allow insertion into identity columns" , if used.
    Attention: if, when you try to select several tables and set the “Allow insertion into identity-columns” property on them, the property has already been set for at least one of the selected tables, the dialog will indicate that the property is already set for all selected tables. This fact can be confusing and lead to transfer errors.
  4. We start the transfer.
  5. Restore constraint check:
    EXEC sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT all'

If any errors occur, check the settings, delete the database created with the errors, re-create it from the script, make corrections and repeat the data transfer.

Conclusion


This task is quite rare and arises only due to the above limitations. The most common solution is to upgrade SQL Server or connect to a remote server if the application architecture allows it. However, no one is safe from the legacy code and crooked hands of poor-quality development. I hope you don’t need this instruction, but if you still need it, it will help to save a lot of time and nerves. Thanks for attention!

List of sources used


Tags:

Also popular now: