امروز می خواهیم مباحث و مسائل مربوط به تهیه پشتیبان و و بازیابی پایگاه های داده در SQL Server را با هم یاد بگیریم. تا این لحظه جای این سرویس و مباحث مربوط به مدیریت پایگاه داده های SQL Server در وب سایت انجمن تخصصی فناوری اطلاعات ایران کمرنگ بود امیدوار هستم در انتهای این سری مقالات پر رنگ شدن این موضوع مهم را در وب سایت itpro شاهد باشیم.
خوب قبل از اینکه به سراغ بحث اصلی در مورد SQL Server Backup ها بپردازیم یک موضوع است که باید بصورت کاملا شفاف و واضح در ذهن داشته باشید ، وجود یک Backup کامل و آزمایش شده و یک طرح بازگردانی درست برای Database های SQL Server در مجموعه ای که در آن قرار دارید اگر مهمترین موضوع نباشد یکی از مهمترین کارهای شما به عنوان یک DBA می باشد.
تصور کنید به عنوان یک DBA ( Database Administrator ) از Database های SQL Server های نرم افزار مالی ، نرم افزار اتوماسیون اداری ، نرم افزار نیروی انسانی ، نرم افزار شیرپوینت و مدیریت مستندات و ... به درستی Backup نگرفته اید ، خدای ناکرده بر اثر یک تصادف اطلاعات موجود در این Database ها دچار اختلال می شود و شما مجبور می شوید اطلاعات را بازگردانی کنید .
اما متوجه می شوید که Backup ها به درستی گرفته نشده اند ، تست نشده اند و قابل بازگردانی نیستند ، اگر مدیر شما باشم شما را بیچاره می کنم ، اما در این لحظه ساده ترین کاری که با شما انجام می شود اخراج است. نکته قابل تامل در خصوص Backup این است که شما بتوانید Backup را بازگردانی کنید ، Backup ای که قابل Restore نباشد به درد لای جرز می خورد .اگر شما یک برنامه بازیابی از حادثه یا Disaster Recovery Plan نداشته باشید حتما برای خود چنین برنامه ای ، حداقل در سطح کاری خودتان تدوین کنید و به آن عمل کنید ، به این برنامه به عنوان بیمه کاری خود نگاه کنید.
اگر بخواهیم بصورت ریز به ریز در خصوص انواع روش های Backup گیری SQL سرور صحبت کنیم چندین مقاله می توانیم بنویسیم اما در این مقاله صرفا به معرفی روشهای مختلف و کلیات آنها خواهیم پرداخت و در خصوص ریز و جزئیات روش ها در مقالات بعدی توضیحاتی خواهیم داد ، بصورت کلی در SQL Server ما می توانیم هشت روش مختلف Backup گیری داشته باشیم که به شکل زیر می باشند :
معمولترین روش Backup گیری از SQL سرور روش Full یا Complete می باشد به این روش Database Backup هم گفته می شود. در این نوع Backup گیری از کلیه Database های موجود بر روی Instance شما به همراه همگی Transaction Log های موجود در آن Backup گرفته می شود و با این روش شما براحتی می توانید اطلاعات خود را Recover یا بازیابی کنید. این روش ساده ترین روش بازیابی اطلاعات می باشد زیرا تمامی اطلاعات به یکباره بازیابی می شوند.
یکی دیگر از روش های معمول Backup گیری از SQL Server به روش Differential معروف است.اگر کمی با مفاهیم Backup در ویندوز آشنایی داشته باشید این نوع روش برای شما بسیار راحت می باشد ، در این روش فقط از تغییراتی که در Database های شما از آخرین Backup گرفته شده انجام شده است Backup گرفته می شود ، طبیعی است که اگر از Database های شما تاکنون Backup گرفته نشده باشد در اولین باری که بصورت Differential بکاپ بگیرید تمامی اطلاعات Database ها Backup گرفته می شوند.
در فایل های ویندوز یک بیت به نام Archive Bit وجود دارد که تغییر کردن یک فایل را به شما نشان می دهد ، همین مفهوم در SQL Server به عنوان extent شناخته می شود ، یک extent شامل هشت قسمت 8 کیلوبایتی می شود که مجموعه 64 کیلوبایت داده را تشکیل می دهد.
هر بار که اطلاعاتی در SQL سرور وارد می شود و یا تغییری انجام می شود یک Flag ایجاد می شود که به SQL سرور می گوید یک Differential Backup ایجاد شده است و باید اطلاعات موجود در extent ها در آن موجود باشد ، زمانیکه شما یک Full Backup می گیرید این Flag ها خاموش می شوند.
پس همانطور که عنوان هم کردیم اگر شما یک Full Backup بگیرید و سپس یک Differential Backup بگیرید محتویات موجود در Differential Backup شما فقط شامل اطلاعات تغییر کرده بعد از Full Backup می باشد که در واقع همان اطلاعات Extent ها هستند.
توجه کنید که برخلاف ساختار Backup گیری ویندوز شما اگر چندین بار هم از اطلاعات موجود در SQL سرور بصورت Differential بکاپ بگیرید در نهایت همه تغییراتی که از ابتدای آخرین Full Backup بر روی Database انجام شده اند Backup گرفته خواهند شد.
زمانیکه می خواهید Backup ای از SQL سرور خود را بازیابی کنید کافیست ضمن بازگردانی آخرین Full Backup فقط اطلاعات موجود در آخرین Differential Backup را نیز بازیابی کنید تا اطلاعات کامل بازیابی شود. در این حالت سایر Differential Backup های موجود نادیده گرفته می شوند.
توجه کنید که اگر Database شما در مدل ریکاوری Simple یا Simple Recovery Model قرار دارد ، شما همچنان قابلیت استفاده از Full Backup و Differential Backup را دارید . اگر Database های شما از مدل ریکاوری Full یا Bulk-Logged استفاده می کنند ( در خصوص Recovery Model ها در مقاله بعدی توضیحاتی خواهیم داد) شما می توانید همچنان از Differential Backup ها استفاده کنید در این حالت شما باید توجه کنید که تعداد Transaction Log بکاپ هایی که می بایست بازگردانی شوند را نیز درست تعیین کنید.
با توجه به اینکه در Differential Backup تمامی Extent هایی که تا آخرین لحظه Full Backup شما از سیستم گرفته شده اند Backup گرفته می شوند ، در زمان بازیابی اطلاعات شما ابتدا باید Full Backup خود را بازیابی کنید و آخرین Differential Backup را به همراه آخرین Transaction Log Backup ای که بعد از Differential Backup آخر گرفته شده است بایستی بازیابی کنید. با اینکار شما تعداد فایل هایی که باید بازیابی شوند را به حداقل می رسانید.
اگر Recovery Model پایگاه داده های شما در حالت Full یا Bulk-Logged قرار داشته باشد بنابراین شما می توانید از Transaction Log های خود نیز Backup بگیرید. اگر شما در ساختار خود Transaction Log Backup را دیده باشید و به همراه آن Full Backup نیز داشته باشید قادر خواهید بود چیزی شبیه به Restore Point ویندوز را برای SQL سرور ایجاد کنید بدین معنا که اگر شخصی بصورت تصادفی کلیه اطلاعات موجود در Database های شما را حذف کند ، شما می توانید با استفاده از این Backup ها اطلاعات را به حالت عملیاتی قبل از حذف اطلاعات بازیابی کنید.
نکته منفی که در خصوص Log Backup ها وجود دارد این است که اگر Recovery Model شما به حالت Bulk-Logged قرار گرفته باشد شما برای بازیابی مجبور هستید کل Transaction Log های موجود را بازیابی کنید. Log Backup ها در واقع همان Transaction Log Backup ها هم هستند ، این نوع Backup به شما اجازه می دهد که بتوانید از بخش فعال Transaction Log ها Backup بگیرید.
در اینصورت زمانیکه شما از اطلاعات خود یک Full یا Differential Backup می گیرید ، Transaction Log Backup تمامی اطلاعاتی که بعد از گرفته این Backup ها ایجاد شده اند را نیز Backup می گیرد. زمانیکه دستور گرفتن Transaction Log Backup صادر شد فضایی که توسط Transaction Log ها اشغال شده بود آزاد و می توان از آن برای سایر فرآیند های سیستم استفاده کرد اما اگر شما Transaction Log Backup نگیرید حجم این Log ها همینطور اضافه خواهد شد و رشد خواهد کرد.
یکی دیگر از گزینه هایی که برای Backup گیری از SQL سرور وجود دارد به File Backup معروف است. این روش به شما امکان این را می دهد که بتوانید به جای اینکه کل Database را Backup بگیرید هر فایل را بصورت مستقل Backup بگیرید. البته این روش زمانی صادق است که شما چندین فایل Data در Database خود ساخته باشید. یکی از دلایل مهم استفاده از این روش Backup گیری زمانی است که شما یک Database دارید که دارای چندین فایل با حجم های زیاد است و می خواهید هر کدام از این فایل ها را بصورت جداگانه و مستقل Backup بگیرید.
توجه کنید که در بیشتر موارد شما فقط یک فایل در Database خود دارید و این روش معمولا به کار شما نمی آید ، اگر فرض کنید که ما در Database همین وب سایت انجمن تخصصی فناوری اطلاعات ایران یا tosinso.com دارای چندین فایل در هر Database بودیم و نیاز به Backup گیری از هر کدام بصورت مجزا را داشتیم از این روش استفاده می کردیم. این نوع Backup معمولا در محیط های Enterprise ای انجام می شود که واقعا حجم عظیمی از اطلاعات در آن وجود دارد.
شما می توانید برای فایل های موجود در Database خود filegroup درست کنید ، علاوه بر اینکه شما می توانید با استفاده از روش قبلی که File Backup بود از فایل های مجزای موجود در Database خود Backup بگیرید می توانید از این filegroup ها که به تعریف فارسی گروه فایل ها می باشند نیز Backup بگیرید. پ
بصورت پیشفرض در یک Database یک عدد filegroup اصلی یا PRIMARY filegroup وجود دارد که به فایل Data ای که ایجاد شده است گره می خورد.همانطور که اشاره کردیم شما می توانید برای خود filegroup های جدید ایجاد کنید و فایل های data ی خود را در این filegroup ها دسته بندی کنید.
در بسیاری از موارد شما فقط همین PRIMARY filegroup را در ساختار خود دارید بنابراین همانند بحث filegroup قبلی این مورد نیز کاربرد چندانی در database های شما ندارد. تاکید کردیم که شما می توانید از هر filegroup ها بصورت جداگانه و مستقل Backup بگیرید ، بزرگترین مزیتی که filegroup Backup ها نسبت به file Backup ها دارند
این است که شما می توانید filegroup های خود را بصورت فقط خواندنی یا Read Only بکاپ بگیرید این بدین معناست که نمی توان این اطلاعات را دستکاری کرد و صرفا می توان آن را خواند. شما می توانید به جای اینکه از همه Database های خود Backup بگیرید تنها در وهله های زمانی معین از نسخه های خواندنی و نوشتنی یا Read Write مربوط به filegroup ها Backup بگیرید.
زمانیکه شما از روش Copy Only Backup برای Backup گیری Database ها استفاده می کنید تقریبا Backup ای شبیه به Full Backup گرفته اید که در آن تمامی اطلاعات موجود در Database ها و Log ها و سایر موارد مرتبط وجود دارد ، اما این سئوال پیش می آید که واقعا تفاوت Full و Copy در چه چیزی است ؟ تفاوت در این است که در صورتیکه شما از Database های خود Full Backup بگیرید Plan بکاپ گیری Differential و تغییراتی که در Database ها انجام شده است تغییر می کند.
اما در حالت Copy Only هیج تغییری در Backup Plan شما پیش نمی آید و صرفا یک کپی با تمام مشخصات از اطلاعات برداشت می شود ، در اصطلاح فنی Copy Only Backup ها Log Chain یا ذنجیره Log های شما را که برای برنامه ریزی Backup های دیگر استفاده می شود را تغییر نمی دهند.
معمولا اینگونه backup ها بصورت دستی و برای موراد بررسی و تحلیلی استفاده می شود اما به هر حال بد نیست هر چند وقت یکبار بصورت دستی اینکار را انجام دهید. هرگاه از Copy Backup یاد کردید به فکر Snapshot های VMware بیوفتید که دقیقا چنین روش Backup گیری هستند.
از این نوع روش Backup گیری به عنوان Database Mirroring نیز یاد می شود ، در این روش همانطور که از نامش پیداست که به معنی بکاپ گیری آینه ای است دقیقا چیزی شبیه به ساختار RAID Level 1 را شاهد هستیم اما در سطح نرم افزار ، یدین صورت که از این نوع روش Backup گیری بیشتر برای بالابردن دسترسی پذیری یا Availability پایگاه های داده استفاده می شود ، شما در این روش نیازمند حداقل دو سرور مجزا هستید که بتوانند Database های خود را با همدیگر Synchronize کنند.
روش Backup گیری Mirroring بر اساس هر Database انجام می شود و در اصطاح فنی به آن Per Database Basis گفته می شود ، توجه کنید که این روش صرفا با Full Recovery Model کار می کند. در این روش Backup گیری ، بعد از راه اندازی سرویس اگر هر اطلاعاتی در Database ها ثبت شود بلافاصله با Database دیگر که بر روی سرور دیگری قرار دارد Replicate و یکپارچه سازی می شود.
روش Backup گیری به صورت Partial یا جزئی ( قسمتی ، بخشی ) برای اولین بار در SQL Server 2005 معرفی شد. شما با این روش می توانید PRIMARY filegroup بعلاوه Read Write filegroup ها و همچنین هر فایل مشخص دیگری را به یکباره Backup بگیرید.
این روش زمانی کاربردی است که شما در Database های خود Read Only Filegroup دارید و نمی خواهید در هر لحظه همه Database های موجود را Backup بگیرید.نتیجهPartial Backup ها می تواند بصورت Full و هم Differential هم باشد.توجه کنید که شما از این روش برای Backup گیری Transaction Log ها نمی توانید استفاده کنید.اگر filegroup ای از حالت Read Only به حالت Read Write تغییر پیدا کند می تواند در Partial Backup آینده استفاده شود اما بر عکس این مورد اتفاق نمی افتد.
خوب در این مقاله در خصوص انواع روش های Backup گیری از اطلاعات موجود در SQL سرور صحبت کردیم اما هنوز شما با مفاهیمی مثل Transaction Log ها و Recovery Model ها آشنا نشدید ، برای مطالعه بیشتر در خصوص اینه Transaction Log ها چه هستند می توانید به نکته بنده با عنوان منظور از Transaction Log در SQL سرور چیست ؟ در این خصوص از همین لینک استفاده کنید اما در خصوص Recovery Model ها به امید خدا در قسمت بعدی توضیحاتی را ارائه خواهیم کرد.
در قسمت قبلی در خصوص روش های مختلف Backup گیری در SQL سرور صحبت کردیم ، اگر دقت کرده باشید متوجه می شوید که در هر قسمت از انواع Backup اشاره ای به این نکته داشتیم که فلان نوع Backup فقط در فلان Recovery Model قابل استفاده است و برعکس ، اما در مورد Recovery Model و وافعیت آن صحبتی نکردیم. در این قسمت قصد داریم انواع Recovery Model های موجود در SQL سرور را با هم بررسی کنیم.
در ابتدای کار باید بدانیم اصلا Recovery Model چیست ؟ ترجمه این کلمات به شکل مدل بازیابی می باشد و دقیقا کاری است که باید انجام دهد. در SQL Server ما سه نوع Recovery Model مختلف داریم که روش مدیریت Log File ها و آماده سازی آنها برای بروز حوادث احتمالی را مشخص می کنند. بنابراین وجود یک Recovery Model مناسب ( و البته استفاده درست از آن ) باعث آسودگی خاطر شما در زمان بروز مشکل برای Database ها خواهد بود.
هر یک از این Recovery Model ها که در ادامه در خصوص آنها صحبت خواهیم کرد برای اینکه یک تعادل درست بین استفاده بهینه از فضای هارد دیسک و همچنین ارائه امکانات مختلف در مواقع بروز حادثه ارائه کنند روش های مختلفی را استفاده می کنند.
در واقع بیشتر تفاوت در نتیجه کار هر یک از این Recovery Model ها در اندازه Transaction Log هایی است که ایجاد می کنند. توجه کنید که ممکن است Recovery Model ها را به عنوان Disaster Recovery Model هم نام ببرند که کاملا درست می باشد، بصورت کلی در SQL سرور سه نوع Recovery Model به شرح زیر وجود دارد :
همانطور که از نام این روش بازیابی مشخص است ، ساده است . هدف اصلی این روش بازیابی این است که SQL Server حداقل اندازه اطلاعات را در Transaction Log های خود داشته باشد. SQL Server در این حالت هرگاه که Database ها به یک Transaction Checkpoint برسند اطلاعات موجود در Transaction Log ها را حذف می کند که در اصطلاح به اینکار Truncate کردن می گوییم ، با اینکار هیچ Log ای برای عملیات و اهداف Disaster Recovery وجود نخواهد داشت.
در Database هایی که از حالت ریکاوری Simple Model استفاده می کنند شما فقط می توانید Backup های Full و Differential را بازیابی کنید. در حالت Simple Recovery Model شما قابلیت بازیابی اطلاعات به یک نقطه زمانی خاص را ندارید و فقط می توانید اطلاعات را بصورت کامل با استفاده از Full یا Differential Backup به زمانی برگردانید که این Backup ها گرفته شده اند. بنابراین شما در چنین روش ریکاوری ای اطلاعاتی که بعد از گرفته شدن آخرین Full یا Differential Backup گرفته شده است را بعد از بروز حادثه ، نمی توانید بازیابی کنید و این اطلاعات از بین می رود.
همانطور که اشاره کردیم با خالی شدن Transaction Log از اطلاعات در این حالت Recovery طبیعی است که فضای بیشتری در هارد دیسک شما برای انجام فرآیند های دیگر باز خواهد شد. اگر شما در طرح بازیابی از حادثه خود گزینه ای برای بازیابی اطلاعات یه یک زمان خاص و تاریخ خاص ندارید می توانید از این روش استفاده کنید ، با توجه به اینکه Transaction Log های شما در این روش معمولا خالی می شوند مدیریت ساده تری بر روی آنها نیاز است و از این بابت برای Admin های تنبل مناسب است. ( البته Admin ها همه خوبن فقط من بدم D: )
همانند روش قبلی در این مدل ریکاوری اسم مدل بیانگر شیوه کاری آن است. با استفاده از این مدل SQL Server تا زمانیکه از Transaction Log ها Backup گرفته نشود آنها را خالی نمی کند. در این حالت شما به عنوان مدیر Database های وب سایتی مثل توسینسو ( یعنی اگر کپی می کنی اشکال نداره اما حق تالیف رو رعایت کن دوست من ) قادر خواهید بود برنامه بازیابی از حادثه خود را بر اساس یک برنامه ترکیبی از Full و Differential Backup ها به همراه Transaction Log ها طراحی کنید .
در صورتیکه خدای نکرده زبانم لال Database های شما دچار مشکل شدند ، شما با استفاده از Full Recovery Model بیشترین انعطاف پذیری را برای بازیابی اطلاعات و Database های خود خواهید داشت. علاوه بر اینکه در این حالت تمامی تغییراتی که در Transaction Log ها ایجاد شده است حفظ می شود شما قادر خواهید بود
در هنگام بازیابی اطلاعات ، آنها را به زمان و تاریخ دلخواهی که تعیین کرده اید بازیابی کنید. برای مثال اگر مشکلی در ساعت 5 و 36 دقیقه برای Database های شما پیش آمده باشد ، شما با استفاده از این روش می توانید اطلاعات را به زمان 5 و 35 دقیقه یعنی درست زمان قبل از حادثه بازیابی کنید و حداقل از دست رفتن اطلاعات را داشته باشید.
در این حالت Transaction Log ها زمانی حذف خواهند شد که یا از آنها Full یا Differential و یا Transaction Log Backup گرفته باشید. استفاده از این نوع روش ریکاوری برای استفاده از سایر امکانات SQL سرور مانند Log Shipping ، Database Mirroring و Transaction Log Backup ها الزامی است
زیرا به ما امکان استفاده از قابلیت نقطه زمانی بازیابی یا Point In Time Recovery ( می دونم نباید ترجمه می شد اما بالاخره محض دور هم بودن ترجمش کردیم ) می دهد. در مواقعی که شما نمی توانید تحمل Data Loss زیادی را در مجموعه داشته باشید Point In Time Recovery یک عصای دست درست و حسابی است. وهله های زمانی که شما از Transaction Log های خود Backup می گیرید تعیین کنند میزان اطلاعاتی است که شما در مواقع بروز حادثه می توانید بازیابی کنید.
این نوع مدل ریکاوری برای اهداف خاصی در نظر گرفته شده است و بیشتر شبیه به مدل ریکاوری Full Recovery Model کار می کند. تنها تفاوت و در واقع اصلیترین تفاوت بین این مدل و مدل Full در روشی است که این مدل برای مدیریت عملیات های گسترده تغییرات در اطلاعات انجام می دهد. Bulk-logged Recovery در چنین مواقعی برای ثبت اطلاعات و عملیات ها در Transaction Log ها از روشی به نام Minimal Logging استفاده می کنید.
با استفاده از این روش سرعت پردازشی بسیار بالا می رود و زمان پردازش ها طبیعتا کاهش پیدا می کند اما شما دیگر قادر به استفاده از قابلیت Point In Time Restore نخواهید بود. در این مدل حجم فایل Transaction Log نسبت به Full Recovery Model بسیار کمتر خواهد شد.
مایکروسافت پیشنهاد می کند که از Bulk-logged Recovery Model بصورت مقطعی و مورد استفاده کنید و آن را به عنوان یک راهکار دائمی در نظر نداشته باشید. اگر نیاز سازمان شما به گونه ای بود که نیاز داشتید از Full Logging به Minimal Logging سویچ کنید ، دقت کنید زمانی اینکار را انجام دهید که کاربران شما در حال بروزرسانی یا ثبت اطلاعات جدید در Database ها نباشند ، زیرا ممکن است مقداری از اطلاعات در این فرآیند تبدیل از بین برود. توسینسو باشید.