ProHoster > Ibhulogi > Ulawulo > Ukudlulisela idatha yogcino ukusuka kuguqulelo olutsha lwe-MS SQL Server ukuya kuguqulelo oludala

Ukudlulisela idatha yogcino ukusuka kuguqulelo olutsha lwe-MS SQL Server ukuya kuguqulelo oludala

ukubuzwa

Kanye, ukuvelisa i-bug, bendifuna i-backup yedatha yemveliso.

Okwandimangalisayo kukuba, ndiye ndadibana nale mida ilandelayo:

  1. Ugcino lwedatha lwenziwe kwinguqulelo SQL Server 2016 kwaye yayingahambelani neyam SQL Server 2014.
  2. Kwikhompyuter yam yomsebenzi yayiyi-OS 7 Windowsngoko andikwazanga ukuhlaziya SQL Server ukuya kuthi ga kwinguqulelo ka-2016
  3. Imveliso exhaswayo yayiyinxalenye yenkqubo enkulu enelifa elidityaniswe ngokuqinileyo kwaye ifikelele kwezinye iimveliso kunye neziseko, ngoko kunokuthatha ixesha elide kakhulu ukuthunyelwa kwesinye isikhululo.

Ukuqwalasela oku ngasentla, ndafikelela kwisigqibo sokuba ixesha lifikile leentonga zezisombululo ezingezona umgangatho.

Ukubuyisela idatha kwi-backup

Ndagqiba ekubeni ndisebenzise umatshini wenyani I-Oracle VM VirtualBox nge Windows 10 (ungathatha umfanekiso wovavanyo kwi-browser ye-Edge kusuka apha). Iseva yeSQL ka-2016 yafakwa kumatshini wenyani kwaye idatabase yesicelo yabuyiselwa kugcino (ncwadana).

Ukumisela ukufikelela kwi-SQL Server kumatshini wenyani

Okulandelayo, bekuyimfuneko ukuthatha amanyathelo ukwenza kube nokwenzeka ukufikelela kwi-SQL Server ngaphandle:

  1. Kwi-firewall, yongeza umthetho ukuvumela izicelo zezibuko ukuba zidlule 1433.
  2. Kuyacetyiswa ukuba ukufikelela kwiseva akwenzeki ngokuqinisekiswa kweWindows, kodwa nge-SQL usebenzisa i-login kunye negama lokugqitha (kulula ukuqwalasela ukufikelela). Nangona kunjalo, kulo mzekelo, kufuneka ukhumbule ukwenza uqinisekiso lwe-SQL kwiipropati ze-SQL Server.
  3. Kwizicwangciso zomsebenzisi kwi-SQL Server kwithebhu Imephu yomsebenzisi cacisa indima yomsebenzisi kwisiseko sedatha esibuyiselweyo db_securityadmin.

Ukudluliselwa kwedatha

Ngokwenyani, ugqithiso lwedatha ngokwalo lunamanqanaba amabini:

  1. Ukudluliselwa kwe-schema yedatha (iitafile, iimbono, iinkqubo ezigciniweyo, njl.njl.)
  2. Ukudlulisela idatha ngokwayo

Ukugqithiselwa kweSikimu seDatha

Senza le misebenzi ilandelayo:

  1. Khetha Imisebenzi -> Velisa izikripthi kwisiseko esiphathwayo.
  2. Khetha izinto ozifunayo ukuhambisa okanye ukushiya ixabiso elingagqibekanga (kule meko, izikripthi ziya kudalelwa zonke izinto zesiseko sedata).
  3. Cacisa useto logcino lweskripthi. Eyona ndlela iluncedo kukugcina iskripthi kwifayile enye kwikhowudi ye-Unicode. Emva koko, ukuba kukho ukungaphumeleli, akuyi kufuneka uphinde onke amanyathelo kwakhona.

Nje ukuba iskripthi sigciniwe, sinokuphunyezwa kumthombo we-SQL Server (uguqulelo oludala) ukwenza isiseko sedatha esifunekayo.

Isexwayiso: Emva kokuphumeza iskripthi, kufuneka ukhangele ukuhambelana kwezicwangciso zedatha ukusuka kwi-backup kunye ne-database eyenziwe ngeskripthi. Kwimeko yam, iskripthi sasingenaso isicwangciso se-COLLATE, ekhokelele ekungaphumelelini xa uhambisa idatha kunye nokudanisa ngetamborini ukwenza kwakhona isiseko sedatha usebenzisa iskripthi esongeziweyo.

Ukudluliselwa kwedatha

Ngaphambi kokudlulisela idatha, kufuneka ukhubaze ukujonga zonke izithintelo kwisiseko sedatha:

EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'

Ugqithiso lwedatha lwenziwa kusetyenziswa iWizard yokuNgenisa iDatha Imisebenzi -> Thatha ngaphandle Idatha kumncedisi we SQL, apho idatabase eyenziwe siscript ibekwe khona:

  1. Cacisa izicwangciso zoqhagamshelwano kumthombo (SQL Server 2016 kumatshini wenyani). Ndisebenzise uMthombo weDatha SQL Server Native Client kunye noqinisekiso lweSQL olukhankanywe ngasentla.
  2. Sicacisa izicwangciso zoqhagamshelo kwindawo ekuyiwa kuyo (SQL Server 2014 kumatshini wokusingatha).
  3. Okulandelayo siseta imephu. Kufuneka ukhethe zonke hayi ukufunda kuphela izinto (umzekelo, iimboniselo akufuneki zikhethwe). Iinketho ezongezelelweyo kufuneka zikhethwe "Vumela ukufakwa kwiikholamu zesazisi", ukuba zisetyenziswa.
    Isexwayiso: Ukuba uzama ukukhetha iitafile ezininzi kwaye unike ipropathi kubo "Vumela ukufakwa kwiikholamu zesazisi" ipropathi sele imiselwe noko inye yeetafile ezikhethiweyo, incoko yababini izakubonisa ukuba impahla sele imiselwe zonke iitafile ezikhethiweyo. Le nyaniso inokubhida kwaye ikhokelele kwiimpazamo zokudlulisa.
  4. Masiqale udluliselo.
  5. Ukubuyisela ujongo lwesithintelo: 
    EXEC sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT all'

Ukuba kukho naziphi na iimpazamo ezenzekayo, sijonga izicwangciso, cima i-database eyenziwe ngeempazamo, siyenze kwakhona kwiskripthi, yenza izilungiso kwaye uphinde udlulise idatha.

isiphelo

Lo msebenzi unqabile kwaye uvela kuphela ngenxa yemida engentla. Isisombululo esiqhelekileyo kukuphucula i-SQL Server okanye ukudibanisa kwiseva ekude ukuba i-architecture yesicelo iyakuvumela. Nangona kunjalo, akukho mntu ukhuselekile kwikhowudi yelifa kunye nezandla ezigwenxa zophuhliso olukumgangatho ophantsi. Ndiyathemba ukuba awuzukuyidinga le miyalelo, kwaye ukuba uyayifuna, iya kukunceda wonge ixesha elininzi kunye nemithambo-luvo. Enkosi ngosinaka kwakho!

Uluhlu lwemithombo esetyenzisiweyo

umthombo: www.habr.com