Geekly Articles each Day
📜 ⬆️ ⬇️

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

Prehistory


Once, in order to play a bug, I needed a backup of the production-base.

To my surprise, I encountered 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 work 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 databases, so its deployment to another station could take a very long time.

Given the above, I came to the conclusion that it is time to use crutches of non-standard solutions.
')

Data recovery from backup


I decided to use the Oracle VM VirtualBox virtual machine 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 from the backup ( instruction ).

Configure access to SQL Server in a virtual machine


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

  1. For a firewall, add a rule to skip requests on port 1433 .
  2. It is desirable that access to the server was not through windows-authentication, but through SQL by login and password (it is easier to configure access). However, in this case, you need to remember to include the possibility of SQL authentication in the properties of SQL Server.
  3. In the user settings on SQL Server on the User Mapping tab, specify the db_securityadmin user role 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. Data transfer

Data schema transfer


Perform the following operations:

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

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

Attention: after the script is executed, it is necessary to check the compliance of the settings of the database from the backup and the database created by the script. In my case, the script did not have a setting for COLLATE, which led to a crash when transferring data and dancing with a tambourine to re-create the database using an updated script.

Data transfer


Before transferring data, you should disable the check of all restrictions on the base:

EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'

Data transfer is performed using the Tasks Data Import Wizard -> Import Data to SQL Server, where the database created by the script is located:

  1. Specify the connection settings to the source (SQL Server 2016 on the virtual machine). I used the Data Source SQL Server Native Client and the above SQL authentication.
  2. Specify the connection settings to the destination (SQL Server 2014 on the host machine).
  3. Next, set up 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 such are used.
    Attention: if when trying to select several tables and set the 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 selected tables. This fact can be confusing and lead to transfer errors.
  4. We start the transfer.
  5. Restoring the constraint check:
     EXEC sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT all'

If there are any errors, 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. However, no one is insured against Legacy code and poor-quality hand curves . I hope that you will not need this instruction, and if you still need it, it will help save a lot of time and nerves. Thanks for attention!

List of used sources


Source: https://habr.com/ru/post/455934/

More articles:


All Articles