1. Nkag siab txog MDF File Corruption
MDF cov ntaub ntawv yog lub hauv paus ntawm SQL Servercov ntaub ntawv cia mechanism. Cov khoom tseem ceeb no xav tau kev tshawb nrhiav sai thiab txoj hauv kev zoo rov qab thaum lawv tau corrupted kom txo cov ntaub ntawv poob. Cia peb kawm txog MDF cov ntaub ntawv thiab yuav ua li cas kho cov ntaub ntawv corrupted MDF.
1.1 Dab tsi yog MDF cov ntaub ntawv hauv SQL Server?
MDF (Master Database File) ua cov ntaub ntawv tseem ceeb hauv SQL Server uas tuav tag nrho cov neeg siv cov ntaub ntawv xws li cov ntxhuav, indexes, cov txheej txheem khaws cia, saib, thiab lwm yam khoom siv database. Cov ntaub ntawv no muaj ob lub schema thiab cov ntaub ntawv tiag tiag, uas ua rau nws yog most ib feem tseem ceeb ntawm ib qho twg SQL Server database. Lub .mdf txuas ntxiv txheeb xyuas cov ntaub ntawv tseem ceeb no uas ua haujlwm ua ke nrog cov ntaub ntawv teev npe (.ldf). Cov ntaub ntawv teev tseg no taug qab cov ntaub ntawv hloov pauv xav tau rau kev ua haujlwm rov qab.
1.2 Feem ntau ua rau MDF cov ntaub ntawv tsis raug
Koj cov ntaub ntawv MDF tuaj yeem cuam tshuam vim ntau qhov laj thawj:
- Fais fab tuag los yog tsis tsim nyog SQL Server shutdowns
- Hardware tsis ua hauj lwm, tshwj xeeb tshaj yog cov teeb meem cia subsystem
- Tsis zoo sectors ntawm lub cia tsav
- Malware lossis kab mob tawm tsam ntawm cov tshuab tsis muaj kev tiv thaiv
- Cov ntaub ntawv kaw lus yuam kev uas cuam tshuam rau kev khaws cov ntaub ntawv
- Software kab hauv SQL Server nws tus kheej
Kev sib tsoo hauv lub sijhawm ua haujlwm hauv database tuaj yeem ua rau muaj kev pheej hmoo ntawm kev noj nyiaj txiag ntau vim tias lawv tuaj yeem nres cov haujlwm tseem ceeb.
1.3 Cov lus qhia muaj txiaj ntsig los tiv thaiv MDF cov ntaub ntawv tsis raug
Nov yog qhov koj tuaj yeem tiv thaiv koj cov ntaub ntawv MDF los ntawm kev noj nyiaj txiag:
- Tsim cov thaub qab tsis tu ncua thiab xyuas lawv cov kev ncaj ncees tau txais
- Siv CHECKSUM raws li nplooj ntawv Tshawb Fawb xaiv rau txhua qhov chaw khaws ntaub ntawv
- Khiav BACKUP DATABASE cov lus txib nrog CHECKSUM
- Tau txais UPS kom tsis txhob raug kaw tam sim ntawd
- Tshawb xyuas qhov chaw disk feem ntau los tiv thaiv teeb meem cia
- Hloov tshiab SQL Server thiab lub operating system nrog kev ruaj ntseg thaj ua rau thaj
- Khiav DBCC CHECKDB tsis tu ncua ntawm txhua lub databases
1.4 Cov cim qhia ntawm cov ntaub ntawv tsis raug MDF
Kev tshawb nrhiav ceev ntawm MDF cov ntaub ntawv kev noj nyiaj txiag tuaj yeem txuag koj ntau lub sijhawm rov qab. Hauv qab no yog qee cov cim qhia ntawm MDF cov ntaub ntawv kev noj nyiaj txiag:
- Cov lus yuam kev thaum koj sim txuas lossis nkag mus rau hauv cov ntaub ntawv
- Database tshwm sim hauv SUSPECT lossis RECOVERY_PENDING hom
- I/O yuam kev hauv SQL Server log, tshwj xeeb tshaj yog yuam kev 823, 824, los yog 825
- Kev faib ua yuam kev ntawm GAM/SGAM/IAM/PFS nplooj ntawv.
- Kev sib raug zoo yuam kev thaum ua haujlwm database
- Sudden performance teeb meem
Hauv qab no yog qee cov lus yuam kev thaum MDF cov ntaub ntawv tsis raug:
Rov qab tsis tau cov ntaub ntawv rau qhov kev thov no. (Microsoft.SqlServer.Management.Sdk.Sfc) xxx.mdf tsis yog thawj cov ntaub ntawv database. (Microsoft SQL Server, Yuam kev: 5171)
Txuas database ua tsis tau tejyam rau Server 'xxx'. (Microsoft.SqlServer.Smo) Lub header rau cov ntaub ntawv 'xxx.mdf' tsis yog ib tug siv tau database ntaub ntawv header. FILE SIZE cov cuab yeej tsis raug. (Micosoft SQL Server, Yuam kev: 5172)
SQL Server kuaj pom qhov ua kom tsis raug raws li I / O yuam kev: tsis yog kev sau nqi (kev cia siab: 0x2abc3894; tseeb: 0x2ebe208e). Nws tshwm sim thaum nyeem ntawv ntawm nplooj ntawv (1: 1) hauv database ID 12 ntawm kev suav 0x00000000002000 hauv cov ntaub ntawv 'xxx.mdf'. Cov lus qhia ntxiv hauv SQL Server log cav yuam kev lossis cov txheej xwm kaw lus yuav qhia ntau yam. Qhov no yog qhov ua yuam kev loj heev uas cuam tshuam rau database kev ncaj ncees thiab yuav tsum raug kho tam sim. Ua kom tiav qhov kev txheeb xyuas tag nrho cov ntaub ntawv (DBCC CHECKDB). Qhov kev ua yuam kev no tuaj yeem tshwm sim los ntawm ntau yam; rau cov lus qhia ntxiv, saib SQL Server Phau Ntawv Online (Microsoft SQL Server, Yuam Kev: 824)
SQL Server kuaj pom qhov tsis sib xws raws li I/O yuam kev: torn nplooj (xav kos npe: 0x########; kos npe tiag tiag: 0x#########).
2. Phau Ntawv Qhia Dawb Txoj Kev Kho MDF Cov Ntaub Ntawv
SQL ServerCov txheej txheem ib txwm muaj pub dawb txoj hauv kev los kho MDF cov ntaub ntawv kev noj nyiaj txiag. Cov kev qhia phau ntawv no tuaj yeem khaws cov ntaub ntawv khaws tseg uas koj nkag tsis tau, tab sis lawv xav tau kev ua kom zoo.
2.1 Restore los ntawm ib tug tsis ntev los no thaub qab
Lub most txhim khu kev qha rov qab txoj kev starts nrog rov qab los ntawm kev thaub qab tsis ntev los no. Txoj kev no hla dhau kev noj nyiaj txiag los ntawm kev rov qab mus rau lub xeev noj qab haus huv. Koj tuaj yeem siv qhov RESTORE DATABASE cov lus txib uas tsim nyog NTAWM DISK tsis coj koj cov ntaub ntawv rov qab mus rau nws lub xeev ua ntej kev noj nyiaj txiag. Koj cov ntaub ntawv kev ncaj ncees nyob twj ywm tsis muaj kev cuam tshuam kev sib raug zoo ntawm cov khoom siv database.
2.2 Siv DBCC CHECKDB nrog kev xaiv kho
DBCC CHECKDB muab cov peev xwm kho tau rau koj yog tias kev rov kho dua tsis yog ib qho kev xaiv:
DBCC CHECKDB (database_name, REPAIR_ALLOW_DATA_LOSS)
WITH ALL_ERRORMSGS, NO_INFOMSGS;
Cov lus txib muaj peb theem kho:
- REPAIR_FAST: Khaws cov syntax rau rov qab sib raug zoo nkaus xwb; tsis kho
- REPAIR_REBUILD: Ua kho tsis muaj cov ntaub ntawv poob
- REPAIR_ALLOW_DATA_LOSS: Kho tag nrho cov lus qhia yuam kev, tab sis tej zaum yuav txi qee cov ntaub ntawv
Peb muaj ib tug Cov lus qhia dav dav ntawm DBCC CHECKDB uas npog tag nrho cov kev xaiv thiab cov xwm txheej (Microsoft cov ntaub ntawv raug cai kuj muaj no).
2.3 Kev pheej hmoo ntawm kev siv REPAIR_ALLOW_DATA_LOSS
Kev kho cov kev xaiv tuaj nrog kev pheej hmoo. REPAIR_ALLOW_DATA_LOSS tej zaum yuav tshem tawm cov nplooj ntawv tsis ncaj ncees lawm, uas ua rau cov ntaub ntawv ploj mus tas li. Nws kuj tsis quav ntsej txog cov kev txwv tseem ceeb txawv teb chaws, uas tuaj yeem rhuav tshem kev sib raug zoo ntawm cov rooj. Yog li ntawd,
- Ua ntej kho, koj yuav tsum rov qab tau koj cov ntaub ntawv tsis raug MDF manually.
- Tom qab kev kho, koj yuav tsum txheeb xyuas qhov txwv tsis pub siv DBCC CHECKCONSTRAINTS txhawm rau nrhiav kev lag luam qhov tsis zoo.
2.4 Siv DBCC CHECKTABLE
DBCC CHECKTABLE muab rau koj tartau txais txoj hauv kev rau kev noj nyiaj txiag hauv cov lus tshwj xeeb:
DBCC CHECKTABLE (table_name, REPAIR_ALLOW_DATA_LOSS);
Cov lus txib no saib ntawm ib lub rooj tsis yog tag nrho cov ntaub ntawv. Nws khiav nrawm dua thiab muaj kev pheej hmoo tsawg dua thaum koj paub tias lub rooj twg muaj teeb meem.
2.5 Teem database rau thaum muaj xwm ceev thiab SINGLE_USER hom
Cov ntaub ntawv muaj kev puas tsuaj loj heev yuav xav tau hom xwm txheej kub ntxhov raws li qhov kev xaiv kawg:
ALTER DATABASE [DatabaseName] SET EMERGENCY;
ALTER DATABASE [DatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC CHECKDB ([DatabaseName], REPAIR_ALLOW_DATA_LOSS);
Cov neeg tswj hwm cov ntaub ntawv tau txais kev nyeem nkaus xwb los ntawm hom xwm ceev yog tias kev nkag mus tsis tau. Cov txheej txheem rov tsim kho cov ntaub ntawv sib pauv, tab sis koj poob kev sib koom ua ke thiab rhuav tshem RESTORE saw. Thaub koj cov ntaub ntawv database ua ntej sim kho xwm txheej ceev kom koj cov kev xaiv rov qab qhib.
2.6 "Hack Attach" Txoj Kev
Yog tias koj tsis tuaj yeem xa cov ntaub ntawv MDF puas rau hauv SQL Server, koj tuaj yeem sim "hack attach" txoj kev:
- Tsim ib tug dummy database nrog tib lub npe raws li koj puas database
- Siv lub dummy database offline
- Tshem tawm cov ntaub ntawv dummy database
- Luam koj cov ntaub ntawv MDF puas rau hauv qhov chaw dummy cov ntaub ntawv
- Nqa cov ntaub ntawv hauv online
Txoj kev no yuav tsis kho kev noj nyiaj txiag tab sis pab SQL Server paub cov ntaub ntawv thiab pab kho cov lus txib.
2.7 Txhim kho lub corrupted Master Database
Master database kev noj nyiaj txiag xav tau cov kauj ruam no:
- Luam the corrupted master.mdf ua tus neeg siv database rau lwm tus neeg rau zaub mov.
- Siv cov txheej txheem phau ntawv los yog DataNumen SQL Recovery rov qab cov khoom ntawm nws.
- Export cov khoom recovered rau hauv ib tsab ntawv.
- Rebuild tus tswv database
ntawm tus thawj server.
- Khiav cov ntawv tsim nyob rau hauv kauj ruam 3 los tsim cov neeg siv thiab cov khoom.
3. Siv DataNumen SQL Recovery Kho cov ntaub ntawv tsis raug MDF
Yog tias cov kev qhia saum toj no tsis ua tiav, koj tuaj yeem sim cov cuab yeej tshaj lij, xws li DataNumen SQL Recovery, uas pab koj kho cov ntaub ntawv puas MDF uas SQL Server's built-in cov cuab yeej ua tsis tau. Lub software siv advanced algorithms kom tau cov most cov ntaub ntawv tau los ntawm corrupted ntaub ntawv thiab muab koj ntau txoj kev rov qab.
3.1 Kho ib daim ntawv
Cov txheej txheem kho cov ntaub ntawv ib leeg yog ncaj nraim thiab yooj yim ua raws li:
- Kaw cov kev pab cuam uas yuav hloov tau cov ntaub ntawv MDF.
- Start DataNumen SQL Recovery.
- Xaiv cov ntaub ntawv MDF puas los ntawm kev ntaus nws lub npe lossis nyem lub pob tshawb.
- suav nrog cov ntaub ntawv NDF ntsig txog yog tias koj xav tau.
- Xaiv qhov chaw database hom lossis cia lub kaw lus ntes nws:
- Xaiv qhov chaw koj xav kom txuag tau cov ntaub ntawv rov qab.
- Nyem rau “Start Rov qab" khawm, lub software yuav txuas rau koj SQL Server piv txwv nrog koj lub npe server thiab cov ntsiab lus nkag.
- Tom qab ntawd lub software yuav kho koj cov ntaub ntawv nrog nws cov qib siab rov qab algorithms.
Koj yuav pom ib qho kev nce qib uas qhia tias kev kho yuav mus li cas. Tom qab nws ua tiav, koj tuaj yeem tshawb xyuas koj cov ntaub ntawv rov qab hauv SQL Server Tswj Studio.
3.2 Kho ib pawg ntawm cov ntaub ntawv
Tuam txhab uas muag nrog ob peb corrupted databases yuav txuag lub sij hawm nrog batch kho. Koj tuaj yeem ntxiv tag nrho koj cov ntaub ntawv MDF puas rau hauv kab, teeb tsa koj cov kev nyiam tso zis, thiab start rov. Qhov no feature ua hauj lwm zoo nyob rau hauv kev lag luam ib puag ncig uas yuav tsum tau kho systemic database teeb meem.
Cov kauj ruam ntxaws:
- Mus rau "Batch Recovery" tab.
- Nyem "Ntxiv cov ntaub ntawv" ntxiv ntau yam SQL Server MDF cov ntaub ntawv yuav tsum tau kho.
- Koj tuaj yeem nyem "Nrhiav Cov Ntaub Ntawv" txhawm rau nrhiav cov ntaub ntawv yuav tsum tau kho ntawm lub computer hauv zos.
- Nyem rau “Start Rov qab" khawm
- Tag nrho cov ntaub ntawv PST hauv daim ntawv yuav raug kho ib qho los ntawm ib qho.
3.3 Rov qab los ntawm Hard Drive, Disk Duab lossis cov ntaub ntawv thaub qab
DataNumen SQL Recovery ua ntau tshaj li kev rov qab los. Nws tuaj yeem tau txais SQL cov ntaub ntawv los ntawm:
- VMWare VMDK virtual tshuab disk cov ntaub ntawv
- Virtual PC VHD cov ntaub ntawv
- ISO duab cov ntaub ntawv
- Windows NTBackup files (.bkf)
- Acronis True Image files (.tib)
- Norton Gh Ibost files (.gho, .v2i)
Qhov kev hloov pauv no yog txoj hauv kev zoo kom tau txais koj cov ntaub ntawv rov qab rau ntau yam xwm txheej, xws li:
- Koj rho tawm cov database hauv SQL Server.
- Koj format lub hard drive.
- Hard drive tsis ua hauj lwm.
- Lub virtual disk hauv VMWare lossis Virtual PC yog corrupted lossis puas thiab koj khaws cov ntaub ntawv rau nws.
- Cov ntaub ntawv thaub qab ntawm cov xov xwm thaub qab yog corrupt lossis puas thiab koj tsis tuaj yeem rov qab tau cov ntaub ntawv database los ntawm nws.
- Cov ntaub ntawv disk duab tsis raug lossis puas thiab koj tsis tuaj yeem rov qab tau koj cov ntaub ntawv MDF los ntawm nws.
Hauv cov xwm txheej saum toj no, koj tseem tuaj yeem rov qab tau SQL Server cov ntaub ntawv los ntawm lub hard drive, disk duab lossis cov ntaub ntawv thaub qab ncaj qha.
Yog tias koj muaj cov duab disk lossis cov ntaub ntawv thaub qab ntawm tes, koj tuaj yeem ua raws li hauv qab no:
- Nyem "…" khawm xaiv cov ntaub ntawv qhov chaw.
- Hauv "Qhib Cov Ntaub Ntawv" dialog, xaiv "Tag nrho Cov Ntaub Ntawv (*.*)" ua cov lim.
- Xaiv cov duab disk lossis cov ntaub ntawv thaub qab ua cov ntaub ntawv los kho.
- Teem cov ntawv tso tawm cov ntaub ntawv ruaj khov npe, xws li E_Drive_fixed.mdf.
Yog tias koj xav rov qab los ntawm lub hard drive ncaj qha, koj tuaj yeem siv DataNumen Disk Image los tsim ib daim duab disk cov ntaub ntawv ntawm lub hard drive li cov ntaub ntawv hauv DataNumen SQL Recovery:
- Xaiv lub hard drive lossis disk.
- Teem lub tso zis duab cov ntaub ntawv npe.
- Nyem “Start Cloning" khawm los tsim cov duab disk los ntawm lub hard drive / disk.
3.4 Rov qab Deleted Cov Ntaub Ntawv thiab Cov Rooj
Yog tias koj rho tawm qee cov ntaub ntawv hauv ib lub rooj, lossis rho tawm qee cov ntxhuav hauv cov ntaub ntawv los ntawm kev yuam kev, koj tuaj yeem rov qab tau lawv ntawm DataNumen SQL Recovery.
Txog cov ntaub ntawv tsis tseem ceeb, lawv yuav tsis tshwm nyob rau hauv qhov kev txiav txim zoo ib yam li ua ntej lawv yuav raug tshem tawm, yog li tom qab rov ua haujlwm dua, koj yuav tau siv cov nqe lus SQL los nrhiav cov ntaub ntawv tsis sau tseg no.
Rau lub rooj tsis tau tawm, yog tias lawv cov npe tsis tuaj yeem rov qab tau zoo, ces lawv yuav tau lub npe zoo li "Recovered_Table1", "Recovered_Table2", thiab lwm yam ...
3.5 Rov qab los ntawm Ransomware lossis Virus
Cov txheej txheem txheej txheem feem ntau tsis kho databases encrypted los ntawm malware. DataNumen SQL Recovery exploits tshwj xeeb algorithms kom tau cov ntaub ntawv los ntawm ransomware-encrypted MDF cov ntaub ntawv yam tsis tau them tus neeg tawm tsam. Lub software tuaj yeem rov qab tau cov ntaub ntawv txawm tias tom qab encryption puas lawm lawv cov headers lossis cov qauv sab hauv.
3.6 Kho cov ntaub ntawv rov qab
Yog tias MDF cov ntaub ntawv rov qab los ntawm DataNumen Data Recovery (los yog lwm cov ntaub ntawv rov qab cov cuab yeej) tsis tuaj yeem txuas rau hauv SQL Server, cov ntaub ntawv no tseem yuav muaj qee yam kev noj nyiaj txiag. Hauv qhov xwm txheej zoo li no, siv DataNumen SQL Recovery kho lawv dua, kom ntseeg tau tias lawv cov ntaub ntawv nkag tau rau hauv SQL Server.
3.7 Rov qab los ntawm tempdb.mdf
thaum SQL Server tab tom khiav, nws yuav khaws txhua lub sijhawmrary cov ntaub ntawv, suav nrog txhua nturary ntxhuav thiab khaws cov txheej txheem, rau hauv kev siv davrary database hu ua tempdb.mdf. Thaum cov ntaub ntawv kev puas tsuaj tshwm sim, yog tias koj tsis tuaj yeem rov qab xav tau cov ntaub ntawv los ntawm mdf thiab cov ntaub ntawv ndf txuam nrog, koj tseem tuaj yeem rov qab tau koj cov ntaub ntawv los ntawm tempdb.mdf, los ntawm kev siv DataNumen SQL Recovery, raws li nram no:
- Tsis txhob SQL Server Cov ntaub ntawv pabcuam pabcuam.
- Siv lub Windows nrhiav ua haujlwm los tshawb rau tempdb.mdf file ntawm lub computer uas lub SQL Server Piv txwv li yog ntsia.
- Tom qab koj pom cov tempdb.mdf, koj tuaj yeem xaiv nws ua cov ntaub ntawv los ntawm kev rov qab los, thiab siv DataNumen SQL Recovery kom rov qab tau nws.
3.8 Cov ntaub ntawv ua piv txwv
SQL Server version | Corrupt MDF cov ntaub ntawv | MDF ntawv tsau los ntawm DataNumen SQL Recovery |
SQL Server 2014 | Yuam Kev1_4.mdf | Yuam Kev1_4_fixed.mdf |
SQL Server 2014 | Yuam Kev2_4.mdf | Yuam Kev2_4_fixed.mdf |
SQL Server 2014 | Yuam Kev4_4.mdf | Yuam Kev4_4_fixed.mdf |
SQL Server 2014 | Yuam Kev5_4.mdf | Yuam Kev5_4_fixed.mdf |
4. Kev Pabcuam Rov Qab Online
Cov kev pabcuam online muab txoj hauv kev sib txawv rau koj los kho SQL databases thaum rub tawm thiab txhim kho cov software rov qab ua tsis tau lossis yooj yim. Cov kev pabcuam online no pab kho cov ntaub ntawv tsis raug MDF yam tsis xav tau kev paub tob tob.
4.1 Cov txheej txheem rov qab yooj yim
Online SQL cov kev pabcuam rov qab ua raws li cov txheej txheem yooj yim-rau-siv:
- Upload cov ntaub ntawv corrupted - Koj cov ntaub ntawv MDF puas raug xa mus rau qhov chaw muab kev pabcuam server los ntawm kev sib txuas encrypted
- Kev ua thiab kev tsom xam - Cov kev pabcuam scans koj cov ntaub ntawv nrog cov algorithms tshwj xeeb thiab txheeb xyuas cov ntaub ntawv rov qab tau
- Saib ua ntej cov txiaj ntsig rov qab - Koj tuaj yeem saib ua ntej cov khoom khaws cia rov qab tau ua ntej cog lus kom rov qab tau tag nrho
- Download tau kho cov ntaub ntawv - Cov ntaub ntawv kho cov ntaub ntawv lossis SQL scripts muaj tom qab ua tiav
Cov kev pabcuam huab no ua haujlwm nrog SQL Server cov ntaub ntawv database xws li .mdf, .ndf, thiab .ldf. Cov ntaub ntawv loj thiab kev noj nyiaj txiag hnyav txiav txim siab lub sijhawm ua haujlwm, uas nyob ntawm vib nas this mus rau feeb.
4.2 Pros thiab Cons
Qhov zoo ntawm kev rov qab online:
- Koj tsis tas yuav nruab software - zoo meej rau cov cheeb tsam txwv lossis cov neeg siv tsis muaj cai tswj hwm
- Ua haujlwm ntawm txhua qhov kev ua haujlwm suav nrog macOS thiab Linux
- Npaj siv yam tsis tau rub tawm cov pob software loj
- Tus neeg siv-phooj ywg interface suits cov neeg uas muaj kev txawj ntse
- Koj tuaj yeem pom cov ntaub ntawv rov qab tau ua ntej yuav
Kev txwv kom xav txog:
- Uploading rhiab cov ntaub ntawv database rau lwm tus neeg servers ua rau muaj kev txhawj xeeb txog kev ceev ntiag tug
- Cov ntaub ntawv loj tshaj plaws muaj kev txwv
- Tsawg dua kev xaiv kho dua li cov cuab yeej offline
- Rov qab ceev yog nyob ntawm tus neeg rau zaub mov kev ua haujlwm thiab kev thauj khoom
- Demo versions qhia tsuas yog qee cov ntaub ntawv rov qab mus txog thaum yuav khoom
Cov cuab yeej rov qab offline muab rau koj tswj ntau dua thiab kev ceev ntiag tug tab sis xav tau kev tshaj lij ntxiv. Most cov kev pabcuam hauv online cia koj sim lawv cov txiaj ntsig nrog cov demos dawb. Cov demos rov qab cov ntaub ntawv piv txwv thiab khij lwm yam khoom rov qab nrog cov chaw tso khoom.
Qhov kev xaiv zoo tshaj plaws ntawm online thiab offline rov qab los rau koj cov kev xav tau. Lub sij hawm siab, kev txawj ntse, cov ntaub ntawv rhiab heev, thiab hom MDF cov ntaub ntawv kev noj nyiaj txiag tag nrho ua lub luag haujlwm tseem ceeb hauv qhov kev txiav txim siab no.
5. Ntau txoj kev daws teeb meem
Thaum cov txheej txheem tau hais hauv ntu dhau los ua haujlwm most MDF cov ntaub ntawv kev noj nyiaj txiag teeb meem, cov ntaub ntawv rov qab thaj chaw muaj cov kev daws teeb meem tshwj xeeb ntxiv. Peb tau soj ntsuam ntau txoj hauv kev thiab muab tso ua ke daim ntawv no qhia txog most txoj kev siv tau zoo uas ua kom tiav cov txheej txheem kho tseem ceeb.
6. Tom qab Kho: Siv tau thiab ruaj ntseg koj cov ntaub ntawv
Kho koj cov ntaub ntawv corrupted MDF daws tsuas yog ib nrab ntawm qhov teeb meem. Koj cov ntaub ntawv yuav tsum tau ua pov thawj txoj cai thiab kev nyab xeeb tom qab ua tiav rov qab los tswj cov ntaub ntawv ncaj ncees thiab nres cov teeb meem yav tom ntej.
6.1 Txheeb xyuas cov ntaub ntawv rov qab ncaj ncees
Daim ntawv txheeb xyuas kom meej tias txhua cov ntaub ntawv nyob twj ywm tom qab kho. DBCC CHECKDB cov lus txib nrog DATA_PURITY scans rau qhov tsis raug:
DBCC CHECKDB (database_name) WITH DATA_PURITY, NO_INFOMSGS;
Cov lus txib no qhia cov kab ke qhov tseem ceeb uas tej zaum yuav siv tsis tau lossis tsis muaj ntau yam rau lawv hom ntaub ntawv. Nco ntsoov tias koj yuav tsum tau kho tej yam tsis raug manually vim kho cov kev xaiv kho tsis tau lawv.
DBCC CHECKCONSTRAINTS pab kom paub tseeb tias kev lag luam logic nyob twj ywm tsis zoo vim kev kho cov haujlwm tsis kuaj lossis tswj cov kev txwv tseem ceeb txawv teb chaws.
6.2 Rebuild Indexs thiab Constraints
Kev ua tau zoo ntawm kev ua tau zoo dhau los ua cov kauj ruam tom ntej thaum cov ntaub ntawv kev ncaj ncees txheeb xyuas tawm. Kev kho cov hauj lwm feem ntau tawm indexes fragmented, uas xav tau ceev ceev:
ALTER INDEX ALL ON table_name REBUILD;
Pab pawg tseem ceeb yuav tsum rov tsim kho qhov tshwj xeeb siv cov ntsuas ntsuas ua ntej kom rov ua haujlwm sai ntawm cov tshuab tseem ceeb. Qhov no tsim tshiab, zoo hosted indexes tias boost cov lus teb lub sij hawm.
6.3 Thaub qab Kho Database
Tsim ib daim ntawv thaub qab tag nrho ntawm koj cov ntaub ntawv kho tshiab tam sim ntawd:
BACKUP DATABASE database_name
TO DISK = 'path\backup_file.bak'
WITH CHECKSUM, FORMAT;
Qhov kev xaiv CHECKSUM txheeb xyuas cov kev ntseeg ruaj khov thaum lub sijhawm ua haujlwm. Ntsuam xyuas cov thaub qab ntawm cov neeg rau zaub mov kuaj tom qab kom paub tseeb tias nws ua haujlwm zoo ua ntej siv nws rau kev tsim rov qab.
6.4 Teeb Kev Saib Xyuas thiab Kev Ceeb Toom
Kev saib xyuas zoo pab tshawb xyuas cov teeb meem ua ntej lawv ua tsis ncaj cov ntaub ntawv. Teeb tsa kev ceeb toom rau cov ntsuas tseem ceeb suav nrog:
- CPU feem pua (tus pib: 90%)
- Cov ntaub ntawv qhov chaw siv feem pua (them pib: 95%)
- Kev siv tus neeg ua haujlwm (tus pib: 60%)
- Deadlocks (txhua yam tshwm sim)
- System yuam kev hauv kev sib txuas sim
Azure Monitor los yog SQL Server Management Studio tuaj yeem teeb tsa cov lus ceeb toom no. Cov lus ceeb toom tseem ceeb nyob twj ywm kom txog thaum daws tau, uas tiv thaiv kev ceeb toom cua daj cua dub thaum muaj teeb meem tsis tu ncua.
Kev kuaj xyuas kev ncaj ncees txhua lub lim tiam siv DBCC CHECKDB yuav tsum khiav ntawm cov ntaub ntawv tseem ceeb kom ntes tau teeb meem ntxov.
7. Cov Lus Nug
Database kev noj nyiaj txiag tsim ntau cov lus nug los ntawm cov neeg siv. Cia peb saib qee cov lus nug txog kev kho cov ntaub ntawv MDF hauv SQL Server.
7.1 Kuv puas tuaj yeem rov qab tau cov ntaub ntawv MDF yam tsis muaj cov ntaub ntawv LDF?
Teb: Yog, nrog cov cuab yeej tshaj lij xws li DataNumen SQL Recovery, koj tuaj yeem rov qab tau cov ntaub ntawv MDF yam tsis muaj cov ntaub ntawv LDF.
7.2 Kuv puas yuav tsum siv cov cuab yeej kho thib peb lossis cov txheej txheem tsim?
Teb: Built-in txoj kev tsim nyog rau thawj zaug txij li thaum lawv muaj kev txhawb nqa. Txawm li cas los xij, cov cuab yeej thib peb tuaj yeem rov qab tau cov ntaub ntawv ntau dua hauv cov teeb meem kev noj nyiaj txiag hnyav. Qhov kev xaiv zoo tshaj yog nyob ntawm:
- Cov ntaub ntawv tseem ceeb
- Thaub qab muaj
- Sijhawm txwv
- Kev txwv nyiaj txiag
- Risk kam rau ua cov ntaub ntawv poob
8. xaus
SQL Server database kev noj nyiaj txiag xav tau kev tshawb nrhiav sai thiab txoj hauv kev zoo rov qab los txo cov ntaub ntawv poob. MDF cov ntaub ntawv kev noj nyiaj txiag pab koj ua ceev ceev thaum muaj teeb meem tshwm sim. Koj muaj ntau txoj kev rov qab los, los ntawm built-in SQL Server cov cuab yeej tshwj xeeb rau cov kev daws teeb meem thib peb xws li DataNumen SQL Recovery cuab tam. Hauv qab no yog cov ntsiab lus ntawm tsab xov xwm no:
Kev tiv thaiv ua haujlwm zoo dua li kho. Kev saib xyuas cov ntaub ntawv tsis tu ncua, cov txheej txheem thaub qab kom raug, thiab kev saib xyuas kev noj qab haus huv ntawm disk tau txo qis kev pheej hmoo kev noj nyiaj txiag. Lub kaw lus tseem yuav tsum tau ua pov thawj cov ntaub ntawv rov qab tau zoo los ntawm kev kuaj xyuas kev ncaj ncees thiab rov tsim cov indexes kom cov ntaub ntawv txhim khu kev qha tom qab kho.