{"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":"

Historia e m\u00ebparshme<\/h2>\n

Nj\u00eb her\u00eb, p\u00ebr t\u00eb riprodhuar nj\u00eb defekt, m\u00eb nevojitej nj\u00eb kopje rezerv\u00eb t\u00eb baz\u00ebs s\u00eb t\u00eb dh\u00ebnave t\u00eb prodhimit.<\/p>\n

P\u00ebr habin\u00eb time, u p\u00ebrballa me kufizimet e m\u00ebposhtme:<\/p>\n

    \n
  1. Kopja rezerv\u00eb e baz\u00ebs ishte b\u00ebr\u00eb n\u00eb versionin SQL Server 2016<\/i> dhe nuk ishte e kompatibilshme me SQL Server 2014<\/i>.<\/li>\n
  2. N\u00eb kompjuterin tim t\u00eb pun\u00ebs, sistemi operativ ishte Windows 7<\/i>, k\u00ebshtu q\u00eb nuk mund t\u00eb p\u00ebrdit\u00ebsoja SQL Server<\/i> n\u00eb versionin 2016<\/li>\n
  3. Produkti i mb\u00ebshtetur ishte pjes\u00eb e nj\u00eb sistemi m\u00eb t\u00eb madh me nj\u00eb arkitektur\u00eb legjendare t\u00eb lidhur ngusht\u00ebsisht dhe gjithashtu iu drejtohej produkteve dhe bazave t\u00eb tjera, prandaj implementimi i tij n\u00eb nj\u00eb stacion tjet\u00ebr mund t\u00eb zgjaste shum\u00eb koh\u00eb.<\/li>\n<\/ol>\n

    Duke marr\u00eb parasysh t\u00eb gjitha k\u00ebto, arrita n\u00eb p\u00ebrfundimin se ishte koha p\u00ebr zgjidhje t\u00eb passtandardizuara.<\/a><\/noindex><\/p>\n

    Rikuperimi i t\u00eb dh\u00ebnave nga kopja rezerv\u00eb<\/h2>\n

    Vendosa t\u00eb p\u00ebrdor nj\u00eb makina virtuale Oracle VM VirtualBox<\/a><\/noindex> me Windows 10 (mund t\u00eb marr\u00eb nj\u00eb imazh testues p\u00ebr shfletuesin Edge k\u00ebtu<\/a><\/noindex>). N\u00eb makin\u00ebn virtuale u instalua SQL Server 2016 dhe nga kopja rezerv\u00eb u rikuperua baza e t\u00eb dh\u00ebnave t\u00eb aplikacionit (instruksion<\/a><\/noindex>).<\/p>\n

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

    M\u00eb pas, ishin t\u00eb nevojshme disa hapa p\u00ebr t\u00eb mund\u00ebsuar aksesin 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 aksesin 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 (\u00ebsht\u00eb m\u00eb e leht\u00eb t\u00eb konfigurohet aksesin). Megjithat\u00eb, n\u00eb k\u00ebt\u00eb rast nuk duhet harruar t\u00eb aktivizohet n\u00eb pronat e SQL Server mund\u00ebsia e autentifikimit SQL.<\/li>\n
    3. N\u00eb cil\u00ebsimet e p\u00ebrdoruesit n\u00eb SQL Server, n\u00eb sked\u00ebn Vendosja e P\u00ebrdoruesve<\/i> p\u00ebrcaktoni rolin e p\u00ebrdoruesit p\u00ebr baz\u00ebn e rikuperuar db_securityadmin<\/i>.<\/li>\n<\/ol>\n

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

      N\u00eb fakt, transferimi i t\u00eb dh\u00ebnave p\u00ebrb\u00ebhet nga dy etapa:<\/p>\n

        \n
      1. Transferimi i skem\u00ebs s\u00eb t\u00eb dh\u00ebnave (tavolinat, pamjet, procedurat e ruajtura etj.)<\/li>\n
      2. Transferimi i t\u00eb dh\u00ebnave vet\u00eb<\/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 -> Generoni Skripta<\/i> p\u00ebr baz\u00ebn q\u00eb transferohet.<\/li>\n
        2. Zgjedhim objektet q\u00eb duam t\u00eb transferojm\u00eb ose e l\u00ebm\u00eb vler\u00ebn si\u00e7 \u00ebsht\u00eb (n\u00eb k\u00ebt\u00eb rast do t\u00eb krijohen skriptet p\u00ebr t\u00eb gjitha objektet e baz\u00ebs).<\/li>\n
        3. P\u00ebrcaktojm\u00eb cil\u00ebsimet p\u00ebr ruajtjen e skript\u00ebs. M\u00eb e leht\u00eb \u00ebsht\u00eb t\u00eb ruani skript\u00ebn n\u00eb nj\u00eb skedar t\u00eb vet\u00ebm n\u00eb 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

          Pasi t\u00eb ruhet skripta, mund t\u00eb ekzekutohet n\u00eb SQL Serverin origjinal (versionin e vjet\u00ebr) p\u00ebr t\u00eb krijuar baz\u00ebn e nevojshme.<\/p>\n

          Kujdes:<\/b> Pas vendosjes s\u00eb skriptit, \u00ebsht\u00eb e nevojshme t\u00eb kontrollohet p\u00ebrputhshm\u00ebria e konfigurimeve t\u00eb databaz\u00ebs nga backup dhe databaza e krijuar nga skripti. N\u00eb rastin tim, n\u00eb skript kishte munges\u00eb t\u00eb konfigurimit p\u00ebr COLLATE, q\u00eb shkaktonte d\u00ebshtim gjat\u00eb transferimit t\u00eb t\u00eb dh\u00ebnave dhe krijimin e v\u00ebshtir\u00ebsive n\u00eb krijimin e databaz\u00ebs me skriptin e p\u00ebrmir\u00ebsuar.<\/p>\n

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

          Para se t\u00eb transferoni t\u00eb dh\u00ebnat, \u00ebsht\u00eb e nevojshme t\u00eb \u00e7aktivizoni kontrollin e t\u00eb gjith\u00eb kufizimeve n\u00eb databaz\u00eb:<\/p>\n

          EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'<\/code><\/pre>\n
          Transferimi i t\u00eb dh\u00ebnave b\u00ebhet p\u00ebrmes asistentit t\u00eb importit t\u00eb t\u00eb dh\u00ebnave Tasks -> Import Data<\/i> n\u00eb SQL Server, ku ndodhet databaza e krijuar nga skripti:<\/p>\n
            \n
          1. Specifikoni konfigurimet e lidhjes me burimin (SQL Server 2016 n\u00eb nj\u00eb makin\u00eb virtuale). Un\u00eb p\u00ebrdora Data Source SQL Server Native Client<\/i> dhe autentifikimin SQL t\u00eb p\u00ebrmendur m\u00eb sip\u00ebr.<\/li>\n
          2. Specifikoni konfigurimet e lidhjes me vendin e destinacionit (SQL Server 2014 n\u00eb makin\u00eb mikprit\u00ebse).<\/li>\n
          3. Pastaj konfigurojm\u00eb hartimin. \u00cbsht\u00eb e nevojshme t\u00eb zgjidhni t\u00eb gjitha jo read-only<\/b> objektet (p\u00ebr shembull, nuk \u00ebsht\u00eb e nevojshme t\u00eb zgjidhen pamjet). Si mund\u00ebsi shtes\u00eb duhet zgjedhur \u00abLejo futjen n\u00eb kolona identity\u00bb<\/i>, n\u00ebse p\u00ebrdoren t\u00eb tilla.
            \nKujdes:<\/b> n\u00ebse, gjat\u00eb p\u00ebrpjekjes p\u00ebr t\u00eb zgjedhur disa tabela dhe p\u00ebr t'u dh\u00ebn\u00eb atyre pron\u00ebsin\u00eb \u00abLejo futjen n\u00eb kolona identity\u00bb<\/i> pronia tashm\u00eb ishte e vendosur m\u00eb par\u00eb p\u00ebr t\u00eb pakt\u00ebn nj\u00eb nga tabelat e zgjedhura, nj\u00eb dialog do t\u00eb sh\u00ebnoj\u00eb se prona tashm\u00eb \u00ebsht\u00eb vendosur p\u00ebr t\u00eb gjitha tabelat e zgjedhura. Ky fakt mund t\u00eb ngat\u00ebrronte dhe t\u00eb \u00e7onte n\u00eb gabime transferimi.<\/li>\n
          4. Fillojm\u00eb transferimin.<\/li>\n
          5. Rivendosim kontrollin e kufizimeve:\n
            EXEC sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT all'<\/code><\/pre>\n<\/li>\n<\/ol>\n
            N\u00ebse jan\u00eb shfaqur ndonj\u00eb gabim, kontrolloni konfigurimet, hiqni databaz\u00ebn e krijuar me gabime, krijoni at\u00eb p\u00ebrs\u00ebri nga skripti, b\u00ebni korrigjimet dhe p\u00ebrs\u00ebritni transferimin e t\u00eb dh\u00ebnave.<\/p>\n
            P\u00ebrfundim<\/h2>\n
            Kjo detyr\u00eb ndodhet mjaft rrall\u00eb dhe ndodh vet\u00ebm p\u00ebr shkak t\u00eb kufizimeve t\u00eb p\u00ebrmendura m\u00eb sip\u00ebr. Shpesh zgjidhja p\u00ebrfshin p\u00ebrdit\u00ebsimin e SQL Server ose lidhjen me nj\u00eb server t\u00eb larg\u00ebt, n\u00ebse arkitektura e aplikacionit e lejon k\u00ebt\u00eb. Megjithat\u00eb, askush nuk \u00ebsht\u00eb i mbrojtur nga kodi legacy dhe gabimet e zhvillimit t\u00eb dob\u00ebt. Shpresoj q\u00eb kjo udh\u00ebzues t\u00eb mos ju nevojitet, dhe n\u00ebse ndodh, 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