{"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\/hu\/blog\/administrirovanie\/perenos-dannyh-bekapa-novoj-versii-ms-sql-server-na-bolee-staruyu-versiyu","title":{"rendered":"A mentett adatok \u00e1tvitele az \u00faj MS SQL Server verzi\u00f3r\u00f3l egy r\u00e9gebbi verzi\u00f3ra","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

H\u00e1tt\u00e9r<\/h2>\n

Egyszer azzal a c\u00e9llal, hogy reproduk\u00e1ljam a hib\u00e1t, sz\u00fcks\u00e9gem volt egy production adatb\u00e1zis ment\u00e9s\u00e9re.<\/p>\n

Meglepet\u00e9semre a k\u00f6vetkez\u0151 korl\u00e1toz\u00e1sokba \u00fctk\u00f6ztem:<\/p>\n

    \n
  1. Az adatb\u00e1zis ment\u00e9se a k\u00f6vetkez\u0151 verzi\u00f3n k\u00e9sz\u00fclt: SQL Server 2016<\/i> \u00e9s nem volt kompatibilis a k\u00f6vetkez\u0151 verzi\u00f3val: SQL Server 2014<\/i>.<\/li>\n
  2. A munkahelyi sz\u00e1m\u00edt\u00f3g\u00e9pemen az oper\u00e1ci\u00f3s rendszer: Windows 7<\/i>, ez\u00e9rt nem tudtam friss\u00edteni SQL Server<\/i> 2016-os verzi\u00f3ra<\/li>\n
  3. A t\u00e1mogatott term\u00e9k egy nagyobb rendszer r\u00e9sze volt, amely szorosan kapcsol\u00f3d\u00f3 legaci architekt\u00far\u00e1val rendelkezett, \u00e9s m\u00e1s term\u00e9kekhez \u00e9s adatb\u00e1zisokhoz is kapcsol\u00f3dott, ez\u00e9rt a m\u00e1sik \u00e1llom\u00e1sra val\u00f3 telep\u00edt\u00e9se sok id\u0151t vehetett ig\u00e9nybe.<\/li>\n<\/ol>\n

    Figyelembe v\u00e9ve a fenti t\u00e9nyez\u0151ket, arra a k\u00f6vetkeztet\u00e9sre jutottam, hogy itt az ideje a nem standard megold\u00e1soknak.<\/a><\/noindex><\/p>\n

    Adatok helyre\u00e1ll\u00edt\u00e1sa a ment\u00e9sb\u0151l<\/h2>\n

    Elhat\u00e1roztam, hogy virtu\u00e1lis g\u00e9pet haszn\u00e1lok Oracle VM VirtualBox<\/a><\/noindex> Windows 10-tel (lehet tesztk\u00e9pet venni az Edge b\u00f6ng\u00e9sz\u0151h\u00f6z innen<\/a><\/noindex>). A virtu\u00e1lis g\u00e9pre SQL Server 2016 ker\u00fclt telep\u00edt\u00e9sre, \u00e9s a ment\u00e9sb\u0151l vissza lett \u00e1ll\u00edtva az alkalmaz\u00e1s adatb\u00e1zisa (utas\u00edt\u00e1s<\/a><\/noindex>).<\/p>\n

    Hozz\u00e1f\u00e9r\u00e9s be\u00e1ll\u00edt\u00e1sa az SQL Serverhez a virtu\u00e1lis g\u00e9pen<\/h2>\n

    Ezut\u00e1n n\u00e9h\u00e1ny l\u00e9p\u00e9st kellett tenni a k\u00fcls\u0151 hozz\u00e1f\u00e9r\u00e9s lehet\u0151v\u00e9 t\u00e9tel\u00e9hez az SQL Serverhez:<\/p>\n

      \n
    1. A t\u0171zfalhoz szab\u00e1lyt hozz\u00e1adni, hogy engedje \u00e1t a lek\u00e9rdez\u00e9seket a porton 1433<\/i>.<\/li>\n
    2. Azt javaslom, hogy a hozz\u00e1f\u00e9r\u00e9s az SQL bel\u00e9p\u00e9ssel \u00e9s jelsz\u00f3val t\u00f6rt\u00e9njen, ne pedig Windows hiteles\u00edt\u00e9ssel (egyszer\u0171bb be\u00e1ll\u00edtani a hozz\u00e1f\u00e9r\u00e9st). Azonban ebben az esetben ne felejts\u00fck el enged\u00e9lyezni az SQL hiteles\u00edt\u00e9st az SQL Server tulajdons\u00e1gai k\u00f6z\u00f6tt.<\/li>\n
    3. Az SQL Server felhaszn\u00e1l\u00f3i be\u00e1ll\u00edt\u00e1saiban, a User Mapping<\/i> f\u00fcl\u00f6n meg kell adni a vissza\u00e1ll\u00edtott adatb\u00e1zishoz a felhaszn\u00e1l\u00f3i szerepk\u00f6rt: db_securityadmin<\/i>.<\/li>\n<\/ol>\n

      Adatok \u00e1tvitele<\/h2>\n

      Az adatok \u00e1tvitele val\u00f3j\u00e1ban k\u00e9t l\u00e9p\u00e9sb\u0151l \u00e1ll:<\/p>\n

        \n
      1. Az adatarchitekt\u00fara \u00e1tvitele (t\u00e1bl\u00e1k, n\u00e9zetek, t\u00e1rolt elj\u00e1r\u00e1sok stb.)<\/li>\n
      2. Az adatok t\u00e9nyleges \u00e1tvitele<\/li>\n<\/ol>\n

        Az adatarchitekt\u00fara \u00e1tvitele<\/h3>\n

        A k\u00f6vetkez\u0151 m\u0171veleteket hajtjuk v\u00e9gre:<\/p>\n

          \n
        1. Kiv\u00e1laszt\u00e1s Tasks -> Generate Scripts<\/i> a m\u00e1soland\u00f3 adatb\u00e1zishoz.<\/li>\n
        2. V\u00e1lassza ki a m\u00e1solni k\u00edv\u00e1nt objektumokat vagy hagyja az alap\u00e9rtelmezett \u00e9rt\u00e9ket (ebben az esetben az \u00f6sszes adatb\u00e1zis objektumra vonatkoz\u00f3 szkript k\u00e9sz\u00fcl).<\/li>\n
        3. \u00c1ll\u00edtsa be a szkript ment\u00e9s\u00e9nek be\u00e1ll\u00edt\u00e1sait. A legpraktikusabb, ha a szkript egyetlen f\u00e1jlba van mentve Unicode k\u00f3dol\u00e1sban. \u00cdgy hiba eset\u00e9n nem kell \u00fajra v\u00e9gighaladni az \u00f6sszes l\u00e9p\u00e9sen.<\/li>\n<\/ol>\n

          A szkript ment\u00e9se ut\u00e1n azt v\u00e9gre lehet hajtani az eredeti SQL Serveren (r\u00e9gebbi verzi\u00f3), hogy l\u00e9trej\u00f6jj\u00f6n a k\u00edv\u00e1nt adatb\u00e1zis.<\/p>\n

          Figyelem:<\/b> A script must be executed to verify the alignment of the backup database settings with the database created by the script. In my case, the script lacked a setting for COLLATE, resulting in data transfer failures and complicated procedures to recreate the database using an enhanced script.<\/p>\n

          Adatok \u00e1tvitele<\/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> on the SQL Server where the database created by the script is located:<\/p>\n
            \n
          1. 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. Specify the connection settings to the destination (SQL Server 2014 on the host machine).<\/li>\n
          3. Then we configure the mapping. All must be selected not read-only<\/b> objects (e.g., views do not need to be selected). For additional options, select \"Allow inserts into identity columns\"<\/i>, if such are used.
            \nFigyelem:<\/b> if, when attempting to select multiple tables and set their property \"Allow inserts into identity columns\"<\/i> the property was previously 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 misleading and lead to transfer errors.<\/li>\n
          4. We start the transfer.<\/li>\n
          5. Restoring constraint checks:\n
            EXEC sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT all'<\/code><\/pre>\n<\/li>\n<\/ol>\n
            If any errors occur, check the settings, delete the created database with errors, recreate it from the script, make corrections, and repeat the data transfer.<\/p>\n
            Z\u00e1r\u00e1s<\/h2>\n
            This task is quite rare and arises only due to the aforementioned constraints. Most often, solutions involve upgrading SQL Server or connecting to a remote server, if the application's architecture allows it. However, no one is safe from legacy code and poorly done development. I hope you won't need this guide, but if you do, I hope it saves you a lot of time and nerves. Thank you for your attention!<\/p>\n
            List of sources used<\/h2>\n