👵🏿 ☘️ 👨‍👩‍👧‍👦 एक डेटाबेस में अलग-अलग पृष्ठों को पुनर्स्थापित करें 🖍️ 👩🏼‍🎨 👨🏾‍🔧

प्रस्तावना

गेल शॉ के लेख "मदद करो, मेरा डेटाबेस भ्रष्ट है। अब क्या? ” , जो अनुवाद मैंने पिछले हफ्ते पोस्ट किया था, वह उत्तेजित हो गया, ऐसा लगता है, एक निश्चित रुचि, लेकिन, अफसोस, इसमें“ अभ्यास ”नहीं था। हां, यह कहता है कि डेटा को कैसे बचाया जाए, लेकिन इसके कोई उदाहरण नहीं हैं।
प्रारंभ में, मैं उसी लेखक का एक और अनुवाद करना चाहता था, लेकिन, सोचकर, मैंने एक पोस्ट "अपने दम पर" लिखने का फैसला किया, जैसे कि "उद्देश्यों पर आधारित"। जिन कारणों ने मुझे ऐसा करने के लिए प्रेरित किया, मैं पोस्ट के अंत में, नोट्स में वर्णन करूंगा।

SQL सर्वर में डेटाबेस रिकवरी

जैसा कि पिछले लेख में पहले ही उल्लेख किया गया है, यदि क्लस्टर इंडेक्स या हीप पेज क्षतिग्रस्त हैं, तो इन पृष्ठों पर मौजूद डेटा खो जाता है और उन्हें पुनर्स्थापित करने का एकमात्र विकल्प सीधे डेटाबेस को पुनर्स्थापित करना है।

SQL सर्वर कई डेटाबेस पुनर्प्राप्ति सुविधाएँ प्रदान करता है। सबसे पहले, यह पूरे डेटाबेस की बहाली है - इसमें बहुत समय लग सकता है (डेटाबेस के आकार और हार्ड ड्राइव की गति के आधार पर)। दूसरे, व्यक्तिगत फ़ाइल समूहों या फ़ाइलों की बहाली, यदि आपके डेटाबेस में कई फ़ाइल समूह (या, क्रमशः, फ़ाइलें) शामिल हैं। इस मामले में, बाकी को प्रभावित किए बिना डेटाबेस के केवल क्षतिग्रस्त हिस्सों को पुनर्स्थापित करना संभव है। इन दो प्रकार के डेटाबेस पुनर्प्राप्ति का उपयोग अक्सर किया जाता है और भविष्य में प्रभावित नहीं होगा।
तीसरा, SQL Server 2005 में व्यक्तिगत डेटाबेस पृष्ठों को पुनर्स्थापित करना संभव हो गया - इस मामले में केवल निर्दिष्ट पृष्ठों को बैकअप से पुनर्स्थापित किया जाएगा। इस तरह की वसूली विशेष रूप से प्रासंगिक होगी यदि DBCC CHECKDB किसी भारी तालिका में कई भ्रष्ट पृष्ठ "झूठ बोल" पाता है। इस तथ्य के कारण कि पूरी फ़ाइल को पुनर्स्थापित नहीं किया जाएगा, और पूरी तालिका भी नहीं, लेकिन केवल कुछ पृष्ठ - डाउनटाइम को काफी कम किया जा सकता है।

आवश्यकताएँ और सीमाएँ

रिकवरी मॉडल और लेन-देन लॉग बैकअप उपलब्धता

याद रखने वाली सबसे महत्वपूर्ण बात यह है कि अलग-अलग पृष्ठों को पुनर्स्थापित करने के लिए, डेटाबेस को पूर्ण (पूर्ण) रिकवरी मॉडल या अपूर्ण लॉगिंग (बल्क-लॉग) के साथ पुनर्प्राप्ति मॉडल का उपयोग करना चाहिए। यदि आपके डेटाबेस एक साधारण रिकवरी मॉडल में हैं, तो अब आप आगे नहीं पढ़ सकते हैं।
दूसरी आवश्यकता यह है कि लेन-देन लॉग के आपके पूर्ण बैकअप और बैकअप में लॉग्स ( लॉग चेन ) की एक अटूट श्रृंखला बननी चाहिए। यदि आप कभी भी TRUNCATE_ONLY (NO_LOG) कमांड के साथ BACKUP लॉग नहीं चलाते हैं और लेन-देन लॉग को कम करने के लिए एक साधारण पुनर्प्राप्ति मॉडल पर स्विच नहीं करते हैं, और आपके पास सभी पूर्ण लॉग बैकअप हैं, जिसमें अंतिम पूर्ण बैकअप है जिसमें कोई भ्रष्ट पृष्ठ नहीं है (इसमें शामिल हैं) सबसे पूर्ण बैकअप) - आपको पत्रिकाओं की श्रृंखला के बारे में चिंता करने की ज़रूरत नहीं है।
अपूर्ण लॉगिंग के साथ पुनर्प्राप्ति मॉडल में, सैद्धांतिक रूप से, व्यक्तिगत पृष्ठों की वसूली ठीक से काम करना चाहिए अगर ऊपर वर्णित शर्तों को पूरा किया जाता है और पुनर्स्थापित किए जा रहे पृष्ठों को न्यूनतम लॉगिंग के साथ किए गए संचालन द्वारा नहीं बदला जाता है।

SQL सर्वर संस्करण

पृष्ठ पुनर्प्राप्ति SQL सर्वर के किसी भी संस्करण में संभव है, लेकिन संस्करण एंटरप्राइज संस्करण और डेवलपर संस्करण के लिए क्षतिग्रस्त पृष्ठों को ऑन-लाइन, अर्थात पुनर्प्राप्त करना संभव है। आप पुनर्प्राप्ति के दौरान डेटाबेस तक पहुंच सकते हैं (और इसके अलावा, आप उस तालिका का भी उल्लेख कर सकते हैं, जिसके पृष्ठ वर्तमान में पुनर्स्थापित किए जा रहे हैं, यदि ये पृष्ठ स्वयं "प्रभावित" नहीं हैं - अन्यथा, अनुरोध विफल हो जाएगा)। एंटरप्राइज़ संस्करण के नीचे के संस्करणों के लिए, पृष्ठ पुनर्प्राप्ति ऑफ़-लाइन होती है और पुनर्प्राप्ति के दौरान डेटाबेस अनुपलब्ध हो जाता है।

क्षतिग्रस्त पृष्ठ प्रकार

इस घटना में कि इंडेक्स या डेटा पेज क्षतिग्रस्त हैं, एंटरप्राइज एडिशन में उनकी रिकवरी ऑनलाइन संभव है।
महत्वपूर्ण सिस्टम टेबल से जुड़े पेजों को पुनर्स्थापित किया जा सकता है, लेकिन डेटाबेस, जब पुनर्स्थापित किया जाता है, तो SQL सर्वर के किसी भी संस्करण में उपलब्ध नहीं होगा।
"प्लेसमेंट कार्ड" को "अलग से" बहाल नहीं किया जा सकता है। यदि GAM, SGAM, PFS, ML, DIFF पृष्ठ क्षतिग्रस्त हैं, तो आपको संपूर्ण डेटाबेस को पुनर्स्थापित करने की आवश्यकता है। केवल अपवाद IAM पृष्ठ हैं। यद्यपि वे "साइटमैप" का उल्लेख करते हैं, वे केवल एक तालिका का वर्णन करते हैं, पूरे डेटाबेस का नहीं, और उनकी वसूली संभव है।
डेटाबेस लोडिंग पेज (पहली डेटाबेस फ़ाइल में 9 वां पेज) और फ़ाइल हेडर पेज (प्रत्येक फ़ाइल में 0 पेज) को अलग से बहाल नहीं किया जा सकता है, अगर वे क्षतिग्रस्त हो जाते हैं, तो पूरे डेटाबेस को पुनर्स्थापित करना होगा।

असल में, वसूली

अब, आखिरकार, हम सिद्धांत से अभ्यास के लिए आगे बढ़ते हैं।
सबसे पहले, प्रशिक्षण के लिए, आपको एक क्षतिग्रस्त डेटाबेस की आवश्यकता है।

पोर्ट डी.बी.

प्रयोग के लिए, मैं AdventureWorks डेटाबेस का उपयोग करूंगा, जो SQL सर्वर के साथ आता है। यदि आपने इसे स्थापित नहीं किया है, यदि आप चाहें, तो आप इसे यहां डाउनलोड कर सकते हैं । मैं इसे पूर्ण पुनर्प्राप्ति मॉडल में स्थानांतरित करता हूं:

ALTER DATABASE AdventureWorks SET RECOVERY FULL

मुझे विश्वास है कि अभी तक इसमें कोई त्रुटि नहीं है:

 DBCC CHECKDB('AdventureWorks') WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY

और एक पूर्ण बैकअप बनाएँ:

 BACKUP DATABASE AdventureWorks TO DISK = 'D:\tmp\aw_backups\aw_full_ok1.bak'

इस डेटाबेस में, मैं एक क्रैश टेबल बनाता हूं।

 CREATE TABLE crash (txt varchar(1000))

हम यह जाँचने के लिए कि क्या होगा यदि कोई SQL सर्वर उसमें पाया जाता है, न कि वह डेटा जो उसने स्वयं वहाँ लिखा था, हम varchar प्रकार क्षेत्र को खराब कर देंगे।
इससे पहले कि आप कुछ बिगाड़ें, आपको इसे कुछ से भरने की जरूरत है। मैं बनाए गए तालिका में बाएं डेटा को हथौड़ा करता हूं।

 SET NOCOUNT ON DECLARE @i INT SET @i = 1 WHILE @i<100000 BEGIN INSERT INTO crash SELECT REPLICATE('a', 1000) SET @i = @i + 1 END SET NOCOUNT OFF

अब मैं लेन-देन लॉग का समर्थन कर रहा हूं:

 BACKUP LOG AdventureWorks TO DISK = 'D:\tmp\aw_backups\aw_log_ok1.trn'

अब डेटा को थोड़ा बदलते हैं:

तो, सब कुछ तैयार है। हम डेटाबेस को डिस्कनेक्ट करते हैं और एफएआर (या जो भी आपके लिए अधिक सुविधाजनक है) के साथ mdf- फाइल को खोलते हैं, उसमें स्ट्रिंग "zzzzzzz" देखें और कई "z" को मनमाने अक्षरों से बदलें:

अब जब डेटाबेस दूषित हो गया है, तो इसे कनेक्ट करें। और, हां, मुझे याद है कि पिछले लेख में स्पष्ट रूप से कहा गया था कि यह डेटाबेस को डिस्कनेक्ट / अटैच करने के लायक नहीं है। लेकिन हमारे मामले में यह बिल्कुल "सुरक्षित" है - "संदिग्ध" डेटाबेस में गिरावट नहीं होगी।

त्रुटियों की तलाश में

इसलिए, क्षतिग्रस्त डेटाबेस सफलतापूर्वक ऑपरेशन पर लौट आया है। अखंडता की जाँच करें फिर से चलाएँ:

 DBCC CHECKDB('AdventureWorks') WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY

नतीजतन, हम क्या इंतजार कर रहे थे ( क्षतिग्रस्त पृष्ठों की संख्या याद रखना सुनिश्चित करें! ):

Msg 8928, Level 16, State 1, Line 1
Object ID 1883153754, index ID 0, partition ID 72057594054246400, alloc unit ID 72057594061651968 (type In-row data): Page (1:20455) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 1883153754, index ID 0, partition ID 72057594054246400, alloc unit ID 72057594061651968 (type In-row data), page (1:20455). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 29493257 and -4.
CHECKDB found 0 allocation errors and 2 consistency errors in table 'crash' (object ID 1883153754).
CHECKDB found 0 allocation errors and 2 consistency errors in database 'AdventureWorks'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (AdventureWorks).

इस स्थिति में, हीप (इंडेक्स आईडी = 0) पर डेटा क्षतिग्रस्त हो जाता है, इसलिए SQL सर्वर इस डेटा को पुनर्प्राप्त नहीं कर पाएगा।
अब हमारे पास तीन विकल्प हैं:

डेटा हानि से निपटने और DBCC CHECKDB ('AdventureWorks', REPAIR_ALLOW_DATAOSOS) चलाएं
लेन-देन लॉग के सक्रिय भाग का बैकअप बनाएं और पूरे डेटाबेस को पुनर्स्थापित करें - परिणामस्वरूप, कोई डेटा हानि नहीं होगी, लेकिन इसमें लंबा समय लगेगा
लेन-देन लॉग के सक्रिय भाग का बैकअप बनाएं और केवल एक (!), क्षतिग्रस्त पृष्ठ को पुनर्स्थापित करें

दूसरे विकल्प के साथ, सब कुछ स्पष्ट होना चाहिए, लेकिन क्या होगा यदि आप DBCC CHECKDB शुरू करते हैं या व्यक्तिगत पृष्ठ कैसे बहाल किए जाते हैं - मैं आगे दिखाऊंगा।

क्षतिग्रस्त पृष्ठ पुनर्प्राप्त करें

सबसे पहले, हमें लेन-देन लॉग ( टेल बैकअप ) के अंतिम टुकड़े का बैकअप लेना होगा । उसी समय, यदि आपके पास एंटरप्राइज़ संस्करण है, तो आप NORECOVERY पैरामीटर को नहीं जोड़ सकते हैं, जो डेटाबेस को "पुनर्स्थापना" स्थिति में डाल देगा, क्योंकि यह संस्करण ऑन-लाइन पेज रिकवरी का समर्थन करता है। इसके अलावा, यदि आपके पास नियमित रूप से किए गए लेन-देन लॉग बैकअप हैं, तो एंटरप्राइज़ संस्करण में लॉग श्रृंखला को बाधित करने के लिए नहीं, तो आप COPY_ONLY बैकअप बना सकते हैं।
मैं ऑफ-लाइन पुनर्प्राप्ति के पथ का अनुसरण करता हूं और करता हूं:

 BACKUP LOG AdventureWorks TO DISK = 'D:\tmp\aw_backups\aw_log_fail3.trn' WITH NORECOVERY

अब, आप क्षतिग्रस्त पृष्ठ की मरम्मत कर सकते हैं। सबसे पहले, हम पूर्ण बैकअप (aw_full_ok1.bak) का उपयोग करते हैं:

 RESTORE DATABASE AdventureWorks PAGE = '1:20455' FROM DISK = 'D:\tmp\aw_backups\aw_full_ok1.bak' WITH NORECOVERY

परिणामस्वरूप, हमारे पास:

कृपया ध्यान दें कि NORECOVERY विकल्प का उपयोग करना आवश्यक है, क्योंकि हमें अभी भी इस पर लेन-देन लॉग वापस करना है।

 RESTORE LOG AdventureWorks FROM DISK = 'D:\tmp\aw_backups\aw_log_ok1.trn' WITH NORECOVERY

और

 RESTORE LOG AdventureWorks FROM DISK = 'D:\tmp\aw_backups\aw_log_fail3.trn' WITH RECOVERY

ऐसा लगता है कि सब कुछ ठीक चला, हमने DBCC CHECKDB और ...

रिकवरी सफल रही।
कृपया ध्यान दें कि डाउनटाइम इस तथ्य के कारण कम हो गया है कि हम पूर्ण बैकअप से पूरे डेटाबेस को पुनर्स्थापित नहीं करते हैं, लेकिन केवल क्षतिग्रस्त पृष्ठ (यदि मैंने संपूर्ण बैकअप को पुनर्स्थापित किया है, तो बैकअप 8.5 सेकंड में पुनर्प्राप्त हो जाएगा, लेकिन डेटाबेस जितना बड़ा होगा, उतना ही कम होगा) एक समय अंतर होगा)। ऑन लाइन रिकवरी का उपयोग करते हुए SQL सर्वर एंटरप्राइज एडिशन वाले भाग्यशाली लोग, लॉग बैकअप से रिकवरी पर समय भी बचाएंगे, और ऑफ-लाइन रिकवरी, अफसोस, लॉग को पूरी तरह से संसाधित किया जाएगा।
यह भी जोड़ने योग्य है कि SQL Server 2005, 2008, 2008 R2 में, एक पृष्ठ को पुनर्स्थापित करना केवल T-SQL का उपयोग करना संभव है; Denali में अब GUI के माध्यम से ऐसा करने की क्षमता है।

लेकिन क्या होगा अगर DBCC CHECKDB?

अगर मैं DBPA CHECKDB को REPAIR_ALLOW_DATA_LOSS पैरामीटर के साथ चलाऊं, तो मैं क्या करूंगा, यह जांचने का निर्णय लिया। सभी एक ही त्रुटि:

सबसे पहले, डेटाबेस को SINGLE_USER मोड में रखें:

 ALTER DATABASE AdventureWorks SET SINGLE_USER

और फिर, हम वसूली शुरू करते हैं:

 DBCC CHECKDB('AdventureWorks', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY

संक्षेप में:

Repair: The page (1:20455) has been deallocated from object ID 1883153754, index ID 0, partition ID 72057594054246400, alloc unit ID 72057594061651968 (type In-row data).

हाँ, SQL सर्वर ने "क्षतिग्रस्त" पृष्ठ को हटा दिया। हमने डेटाबेस को MULTI_USER मोड में रखा, ताकि यह सभी के लिए सुलभ हो जाए और जाँच करें कि क्या गायब है:

यह देखते हुए कि SQL सर्वर में पृष्ठ का आकार 8KB है, और उपयोगकर्ता डेटा के लिए थोड़ा कम उपलब्ध है - फिर सब कुछ स्वाभाविक है, तालिका 7 रिकॉर्ड द्वारा "खो वजन" (शुरुआत में 99999 थे)। चूंकि इस तालिका में कोई संकुल सूचकांक नहीं था, इसलिए डेटा यादृच्छिक क्रम में संग्रहीत किया जा सकता था, अर्थात। हमें यह भी पता नहीं चल पाया कि डेटा क्या खो जाएगा।

तो आखिरकार, अनुवाद क्यों नहीं?

तो क्यों यह अभी भी एक अनुवाद नहीं है, लेकिन एक पोस्ट "मकसद पर आधारित" है। तथ्य यह है कि, सार्वजनिक डोमेन में, गेल शॉ द्वारा कोई लेख "पृष्ठ पुनर्स्थापना" नहीं है। SQL Server MVP पुस्तक डीप डाइव्स vol.2 में ऐसा एक खंड है, जो बहुत मूर्त धन के लिए बेचता है (लेकिन, निश्चित रूप से, इंटरनेट पर आसानी से मिल जाता है) और मुझे यकीन नहीं है कि अनुवाद प्रकाशित करना उम है ... यह सही है।
सामान्य तौर पर, मैंने लेख पढ़ा, मुख्य बिंदुओं पर ध्यान दिया, और फिर मैंने स्वयं पाठ लिखा और रास्ते में, बहाली पर एक प्रयोग किया। मुझे उम्मीद है कि यह अनुभव किसी के लिए उपयोगी था।
और, सज्जनों, मुझे पूरी उम्मीद है कि यदि आप इस प्रयोग को दोहराने का निर्णय लेते हैं, तो आप बेहद सावधान रहेंगे (उदाहरण के लिए, आप उत्पादन सर्वर पर मुख्य डेटाबेस के साथ प्रयोग नहीं करेंगे)। याद रखें कि मैं आपके कार्यों के लिए कोई ज़िम्मेदारी नहीं उठाता।

एक डेटाबेस में अलग-अलग पृष्ठों को पुनर्स्थापित करें