{"id":35306,"date":"2019-10-31T22:03:32","date_gmt":"2019-10-31T19:03:32","guid":{"rendered":"https:\/\/prohoster.info\/blog\/perenos-dannyh-bekapa-novoj-versii-ms-sql-server-na-bolee-staruyu-versiyu\/"},"modified":"2020-12-15T02:10:03","modified_gmt":"2020-12-15T00:10:03","slug":"perenos-dannyh-bekapa-novoj-versii-ms-sql-server-na-bolee-staruyu-versiyu","status":"publish","type":"post","link":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/perenos-dannyh-bekapa-novoj-versii-ms-sql-server-na-bolee-staruyu-versiyu","title":{"rendered":"Transferring backup data from a new version of MS SQL Server to an older version.","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Background<\/h2>\n

Once, to reproduce a bug, I needed a backup of the production database.<\/p>\n

To my surprise, I encountered the following limitations:<\/p>\n

    \n
  1. The database backup was made using version SQL Server 2016<\/i> and was not compatible with my SQL Server 2014<\/i>.<\/li>\n
  2. My work computer had Windows 7<\/i>, so I couldn\u2019t upgrade SQL Server<\/i> to version 2016.<\/li>\n
  3. The supported product was part of a larger system with a heavily interlinked legacy architecture and also accessed other products and databases, so deploying it on another station could take a very long time.<\/li>\n<\/ol>\n

    Given the above, I concluded that it was time for non-standard workaround solutions.<\/a><\/noindex><\/p>\n

    Data Recovery from Backup<\/h2>\n

    I decided to use a virtual machine Oracle VM VirtualBox<\/a><\/noindex> with Windows 10 (a test image can be obtained for the Edge browser from here<\/a><\/noindex>). SQL Server 2016 was installed on the virtual machine, and the application database was restored from the backup (the instruction<\/a><\/noindex>).<\/p>\n

    Setting Up Access to SQL Server on the Virtual Machine<\/h2>\n

    Next, it was necessary to take some steps to enable external access to SQL Server:<\/p>\n

      \n
    1. For the firewall, add a rule to allow requests on port 1433<\/i>.<\/li>\n
    2. Ideally, access to the server should not go through Windows authentication but via SQL using a login and password (it's easier to set up access). However, in this case, do not forget to enable SQL authentication in the properties of SQL Server.<\/li>\n
    3. In the SQL Server user settings on the User Mapping<\/i> tab, specify the user role for the restored database as db_securityadmin.<\/i>.<\/li>\n<\/ol>\n

      Data Transfer<\/h2>\n

      The actual data transfer consists of two stages:<\/p>\n

        \n
      1. Transferring the data schema (tables, views, stored procedures, etc.)<\/li>\n
      2. Transferring the actual data.<\/li>\n<\/ol>\n

        Transferring the Data Schema<\/h3>\n

        We perform the following operations:<\/p>\n

          \n
        1. Select Tasks -> Generate Scripts<\/i> for the database being transferred.<\/li>\n
        2. Select the objects needed for transfer or leave the default value (in this case, scripts for all database objects will be created).<\/li>\n
        3. Specify the settings for saving the script. It\u2019s most convenient to save the script in a single file in Unicode encoding. This way, if something goes wrong, there\u2019s no need to repeat all the steps.<\/li>\n<\/ol>\n

          After saving the script, it can be executed on the source SQL Server (the older version) to create the required database.<\/p>\n

          Attention:<\/b> After executing the script, it is necessary to check the compliance of the database settings from the backup with those from the database created by the script. In my case, the script was missing a setting for COLLATE, which led to failures during data transfer and required complicated workarounds to recreate the database using the augmented script.<\/p>\n

          Data Transfer<\/h3>\n

          Before transferring data, all constraints on the database must be disabled:<\/p>\n

          EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'<\/code><\/pre>\n
          Data transfer is carried out using the data import wizard. Tasks -> Import Data<\/i> to the SQL Server where the database created by the script is located:<\/p>\n
            \n
          1. We specify the connection settings to the source (SQL Server 2016 on a virtual machine). I used Data Source SQL Server Native Client<\/i> and the aforementioned SQL authentication.<\/li>\n
          2. We provide connection settings to the destination (SQL Server 2014 on the host machine).<\/li>\n
          3. Next, we configure mapping. It is necessary to select all not read-only<\/b> objects (for example, views do not need to be selected). As additional options, you should select \"Allow identity insert\"<\/i>, if such are used.
            \nAttention:<\/b> if when trying to highlight multiple tables and set their property \"Allow identity insert\"<\/i> the property was previously set for at least one of the selected tables, the dialog will indicate that the property has already been set for all highlighted tables. This fact can be confusing and lead to transfer errors.<\/li>\n
          4. We start the transfer.<\/li>\n
          5. We restore constraint checking:\n
            EXEC sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT all'<\/code><\/pre>\n<\/li>\n<\/ol>\n
            If any errors occur, we check the settings, delete the erroneous database, recreate it from the script, make corrections, and repeat the data transfer.<\/p>\n
            Conclusion<\/h2>\n
            This task is quite rare and occurs only due to the above-mentioned constraints. More often, the solution involves upgrading SQL Server or connecting to a remote server if the application's architecture allows it. However, no one is immune from legacy code and poorly executed development. I hope you won't need this guide, but if you do, it will help save a lot of time and nerves. Thank you for your attention!<\/p>\n
            List of sources used<\/h2>\n