{"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\/sq\/blog\/administrirovanie\/perenos-dannyh-bekapa-novoj-versii-ms-sql-server-na-bolee-staruyu-versiyu","title":{"rendered":"Transferimi i t\u00eb dh\u00ebnave t\u00eb backup-it t\u00eb versionit t\u00eb ri t\u00eb MS SQL Server n\u00eb nj\u00eb version m\u00eb t\u00eb vjet\u00ebr","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Pas historia<\/h2>\n

Nja nj\u00eb her\u00eb, p\u00ebr t\u00eb riprodhuar nj\u00eb defekt, m\u00eb nevojitej nj\u00eb backup i baz\u00ebs s\u00eb t\u00eb dh\u00ebnave production.<\/p>\n

P\u00ebr habin\u00eb time, u p\u00ebrballa me k\u00ebto kufizime:<\/p>\n

    \n
  1. Backup-i i baz\u00ebs ishte b\u00ebr\u00eb n\u00eb versionin SQL Server 2016<\/i> dhe nuk ishte i p\u00ebrputhsh\u00ebm me versionin tim SQL Server 2014<\/i>.<\/li>\n
  2. N\u00eb kompjuterin tim t\u00eb pun\u00ebs ishte e instaluar Windows 7<\/i>, ndaj nuk mund t\u00eb azhurnoja SQL Server<\/i> n\u00eb versionin 2016<\/li>\n
  3. Produkti p\u00ebrkrahur ishte pjes\u00eb e nj\u00eb sistemi m\u00eb t\u00eb madh me nj\u00eb arkitektur\u00eb legaci t\u00eb fort\u00eb dhe gjithashtu i drejtohej produkteve dhe bazave t\u00eb tjera, ndaj implementimi i tij n\u00eb nj\u00eb stacion tjet\u00ebr mund t\u00eb k\u00ebrkonte shum\u00eb koh\u00eb.<\/li>\n<\/ol>\n

    Duke pasur parasysh k\u00ebt\u00eb, un\u00eb arrita n\u00eb p\u00ebrfundimin se ishte koha p\u00ebr disa zgjidhje jo standarde.<\/a><\/noindex><\/p>\n

    Rind\u00ebrtimi i t\u00eb dh\u00ebnave nga backup-i<\/h2>\n

    Un\u00eb vendosa t\u00eb p\u00ebrdor nj\u00eb makin\u00eb virtuale Oracle VM VirtualBox<\/a><\/noindex> me Windows 10 (mund t\u00eb merrni nj\u00eb imazh prov\u00eb p\u00ebr shfletuesin Edge k\u00ebtu<\/a><\/noindex>). N\u00eb makin\u00ebn virtuale u instalua SQL Server 2016 dhe nga backup-i u rikuperua baza e t\u00eb dh\u00ebnave t\u00eb aplikacionit (udh\u00ebzimi<\/a><\/noindex>).<\/p>\n

    Konfigurimi i aksesit n\u00eb SQL Server n\u00eb makin\u00ebn virtuale<\/h2>\n

    Pastaj, ishte e nevojshme t\u00eb nd\u00ebrmerreshin disa hapa p\u00ebr t\u00eb mund\u00ebsuar qasjen n\u00eb SQL Server nga jasht\u00eb:<\/p>\n

      \n
    1. P\u00ebr firewall-in, shtoni nj\u00eb rregull p\u00ebr t\u00eb lejuar k\u00ebrkesat n\u00eb portin 1433<\/i>.<\/li>\n
    2. Preferohet q\u00eb qasja n\u00eb server t\u00eb b\u00ebhet jo p\u00ebrmes autentifikimit t\u00eb Windows, por p\u00ebrmes SQL me emrin e p\u00ebrdoruesit dhe fjal\u00ebkalimin (m\u00eb e leht\u00eb p\u00ebr t'u konfiguruar qasja). Megjithat\u00eb, n\u00eb k\u00ebt\u00eb rast, mos harroni t\u00eb aktivizoni mund\u00ebsin\u00eb e autentifikimit SQL n\u00eb pron\u00ebsit\u00eb e SQL Server.<\/li>\n
    3. N\u00eb cil\u00ebsimet e p\u00ebrdoruesit n\u00eb SQL Server n\u00eb sked\u00ebn User Mapping<\/i> specifikoni p\u00ebr baz\u00ebn e rikuperuar rolin e p\u00ebrdoruesit db_securityadmin<\/i>.<\/li>\n<\/ol>\n

      Transferimi i t\u00eb dh\u00ebnave<\/h2>\n

      Transferimi i t\u00eb dh\u00ebnave p\u00ebrfshin dy etapa:<\/p>\n

        \n
      1. Transferimi i skem\u00ebs s\u00eb t\u00eb dh\u00ebnave (tabela, pamje, procedura t\u00eb ruajtura, etj.)<\/li>\n
      2. Transferimi i t\u00eb dh\u00ebnave t\u00eb v\u00ebrteta<\/li>\n<\/ol>\n

        Transferimi i skem\u00ebs s\u00eb t\u00eb dh\u00ebnave<\/h3>\n

        Kryejm\u00eb operacionet e m\u00ebposhtme:<\/p>\n

          \n
        1. Zgjidhni Tasks -> Generate Scripts<\/i> p\u00ebr baz\u00ebn e t\u00eb dh\u00ebnave q\u00eb do t\u00eb transferohet.<\/li>\n
        2. Zgjidhni objektet e nevojshme p\u00ebr transferim ose lini vler\u00ebn e parazgjedhur (n\u00eb k\u00ebt\u00eb rast do t\u00eb krijohen skriptet p\u00ebr t\u00eb gjith\u00eb objektet e baz\u00ebs).<\/li>\n
        3. Specifikoni cil\u00ebsimet p\u00ebr ruajtjen e skriptit. E p\u00ebrshtatshme \u00ebsht\u00eb t\u00eb ruani skriptin n\u00eb nj\u00eb skedar t\u00eb vet\u00ebm me kodimin Unicode. K\u00ebshtu, n\u00eb rast d\u00ebshtimi, nuk do t\u00eb nevojitet t\u00eb p\u00ebrs\u00ebrisni t\u00eb gjitha hapat.<\/li>\n<\/ol>\n

          Pas ruajtjes s\u00eb skriptit, ai mund t\u00eb ekzekutohet n\u00eb SQL Server-in origjinal (versionin m\u00eb t\u00eb vjet\u00ebr) p\u00ebr t\u00eb krijuar baz\u00ebn e nevojshme.<\/p>\n

          Kujdes:<\/b> pas ekzekutimit t\u00eb skriptit, \u00ebsht\u00eb e nevojshme t\u00eb kontrolloni p\u00ebrputhshm\u00ebrin\u00eb e cil\u00ebsimeve t\u00eb baz\u00ebs nga backup-i dhe baz\u00ebs s\u00eb krijuar nga skripti. N\u00eb rastin tim, n\u00eb skript mungonte cil\u00ebsimi p\u00ebr COLLATE, i cili \u00e7onte n\u00eb d\u00ebshtim gjat\u00eb transferimit t\u00eb t\u00eb dh\u00ebnave dhe k\u00ebrkonte rregullime t\u00eb menj\u00ebhershme p\u00ebr t\u00eb rib\u00ebr\u00eb baz\u00ebn p\u00ebrmes nj\u00eb skripti t\u00eb p\u00ebrmir\u00ebsuar.<\/p>\n

          Transferimi i t\u00eb dh\u00ebnave<\/h3>\n

          Para transferimit t\u00eb t\u00eb dh\u00ebnave, \u00ebsht\u00eb e nevojshme t\u00eb \u00e7aktivizoni verifikimin e t\u00eb gjitha kufizimeve n\u00eb baz\u00eb:<\/p>\n

          EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'<\/code><\/pre>\n
          Transferimi i t\u00eb dh\u00ebnave b\u00ebhet p\u00ebrmes asistenc\u00ebs s\u00eb importit t\u00eb t\u00eb dh\u00ebnave Tasks -> Import Data<\/i> n\u00eb SQL Server, ku ndodhet baza e krijuar nga skripti:<\/p>\n
            \n
          1. Specifikoni cil\u00ebsimet e lidhjes me burimin (SQL Server 2016 n\u00eb makin\u00ebn virtuale). Un\u00eb p\u00ebrdora Data Source SQL Server Native Client<\/i> dhe autentifikimin SQL t\u00eb p\u00ebrmendur m\u00eb par\u00eb.<\/li>\n
          2. Specifikoni cil\u00ebsimet e lidhjes me destinacionin (SQL Server 2014 n\u00eb makin\u00eb e hostit).<\/li>\n
          3. Pastaj konfiguroni mapping-un. Duhet t\u00eb zgjidhen t\u00eb gjitha jo read-only<\/b> objektet (p\u00ebr shembull, pamjet nuk duhet t\u00eb zgjidhen). Si opsione shtes\u00eb, duhen zgjedhur \u00abLejo inserimin n\u00eb kolona identity\u00bb<\/i>, n\u00ebse t\u00eb tilla p\u00ebrdoren.
            \nKujdes:<\/b> n\u00ebse, gjat\u00eb p\u00ebrpjekjes p\u00ebr t\u00eb zgjedhur disa tabela dhe t'u japim atyre k\u00ebt\u00eb pron\u00ebsi \u00abLejo inserimin n\u00eb kolona identity\u00bb<\/i> pron\u00ebsia ishte tashm\u00eb e vendosur p\u00ebr t\u00eb pakt\u00ebn nj\u00eb nga tabelat e zgjedhura, n\u00eb dialog do t\u00eb sh\u00ebnohet se pron\u00ebsia \u00ebsht\u00eb vendosur p\u00ebr t\u00eb gjitha tabelat e zgjedhura. Ky fakt mund t\u00eb ngat\u00ebrroj\u00eb dhe t\u00eb \u00e7oj\u00eb n\u00eb gabime t\u00eb transferimit.<\/li>\n
          4. Nisim transferimin.<\/li>\n
          5. Rind\u00ebrtojm\u00eb verifikimin e kufizimeve:\n
            EXEC sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT all'<\/code><\/pre>\n<\/li>\n<\/ol>\n
            N\u00ebse ndodhin ndonj\u00eb gabim, kontrolloni cil\u00ebsimet, fshini baz\u00ebn e krijuar me gabime, krijoni p\u00ebrs\u00ebri at\u00eb nga skripti, b\u00ebni rregullimet dhe p\u00ebrs\u00ebrisni transferimin e t\u00eb dh\u00ebnave.<\/p>\n
            P\u00ebrfundimi<\/h2>\n
            Kjo detyr\u00eb ndodhet mjaft rrall\u00eb dhe shfaqet vet\u00ebm p\u00ebr shkak t\u00eb kufizimeve t\u00eb lartp\u00ebrmendura. N\u00eb shumic\u00ebn e rasteve, zgjidhja konsiston n\u00eb azhurnimin e SQL Server-it ose n\u00eb lidhjen me nj\u00eb server t\u00eb larg\u00ebt, n\u00ebse arkitektura e aplikacionit e lejon. Megjithat\u00eb, askush nuk \u00ebsht\u00eb i mbrojtur nga kodi legaci dhe gabimet nga zhvillimi i dob\u00ebt. Shpresoj q\u00eb ky udh\u00ebzues t\u00eb mos ju nevojitet, dhe n\u00ebse e b\u00ebni k\u00ebshtu, do t'ju ndihmoj\u00eb t\u00eb kurseni shum\u00eb koh\u00eb dhe nerva. Faleminderit p\u00ebr v\u00ebmendjen!<\/p>\n
            Lista e burimeve t\u00eb p\u00ebrdorura<\/h2>\n