ProHoster > Blog > Administration > Transferring backup data from a new version of MS SQL Server to an older version

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

prehistory

Once, in order to reproduce a bug, I needed a backup of the production database.

To my surprise, I ran into the following limitations:

  1. The database backup was made on the version SQL Server 2016 and was not compatible with my SQL Server 2014.
  2. On my work computer, the OS used was Windows 7so I couldn't update SQL Server up to version 2016
  3. The supported product was part of a larger system with a tightly coupled legacy architecture and also spoke to other products and bases, so it could take a very long time to deploy it to another station.

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

Restoring data from a backup

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

Configuring access to SQL Server on a virtual machine

Next, it was necessary to take some steps to be able to access SQL Server from the outside:

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

Data transfer

Actually, the data transfer itself consists of two stages:

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

Data schema transfer

We perform the following operations:

  1. Choosing Tasks -> Generate Scripts for a portable base.
  2. Select the objects you need to transfer or leave the default value (in this case, scripts will be created for all database objects).
  3. Specify the settings for saving the script. It is most convenient to save the script in a single Unicode file. Then, in case of failure, you do not need to repeat all the steps again.

Once the script is saved, it can be run on the original SQL Server (old version) to create the required base.

Attention: After executing the script, you need to check the correspondence between the settings of the database 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 failure when transferring data and dancing with a tambourine to recreate the database using the supplemented script.

Data transfer

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

EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'

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

  1. Specify the connection settings to the source (SQL Server 2016 on a virtual machine). I used data source SQL Server Native Client and the aforementioned SQL authentication.
  2. Specify the connection settings for the destination (SQL Server 2014 on the host machine).
  3. Next, set up the mapping. All must be selected not read-only objects (for example, views do not need to be selected). As additional options, select "Allow insertion into identity columns"if such are used.
    Attention: if, when trying to select several tables and set their property "Allow insertion into identity columns" the property has already been set for at least one of the selected tables, the dialog will indicate that the property has already been set for all the selected tables. This fact can be confusing and lead to migration errors.
  4. We start the transfer.
  5. Restoring constraint checking: 
    EXEC sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT all'

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

Conclusion

This task is quite rare and occurs only because of 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 immune from legacy code and crooked hands of poor-quality development. I hope that you will not need this instruction, but if you still need it, it will help save a lot of time and nerves. Thank you for your attention!

List of sources used

Source: habr.com