راهنمای جامع بکاپ SQL Server: از انتخاب مدل بازیابی تا آزمون واقعی

بیشتر تیم‌هایی که با آن‌ها کار کرده‌ام، بکاپ داشتند. مشکل جای دیگری بود: هیچ‌کس نمی‌دانست آن بکاپ‌ها اگر لازم شوند، چه چیزی را برمی‌گردانند و چقدر داده از دست می‌رود. «بکاپ داریم» یک جملهٔ آرام‌بخش است که تا روز حادثه آزموده نمی‌شود.

این راهنما همان چیزی است که باید یک بار، با آرامش و پیش از حادثه، بخوانید و تصمیم‌هایش را بگیرید: چه مدل بازیابی‌ای مناسب شماست، چه ترکیبی از فول و دیفرنشیال و لاگ لازم دارید، و چطور مطمئن شوید آن فایل‌ها واقعاً برمی‌گردند.

اول یک عدد: چند دقیقه داده می‌توانید از دست بدهید؟

هر تصمیم دیگری در این مقاله به همین یک عدد وصل است. اگر جواب «هیچ» است، دارید اشتباه فکر می‌کنید؛ هیچ سیستمی صفر مطلق نمی‌دهد. جواب واقعی چیزی شبیه این است: «اگر ۱۵ دقیقه از سفارش‌های امروز گم شود، می‌توانیم از روی لاگ اپلیکیشن بازسازی کنیم» یا «اگر یک ساعت از داده‌های انبار گم شود، فاجعه است».

این عدد در ادبیات فنی RPO نام دارد و تعیین می‌کند هر چند دقیقه باید بکاپ لاگ بگیرید. عدد دوم، RTO، می‌گوید چند ساعت می‌توانید سرویس را پایین نگه دارید و تعیین می‌کند ساختار بکاپ‌تان چقدر باید سریع برگردد. این دو را جداگانه در مقالهٔ RPO و RTO باز کرده‌ام؛ اینجا فقط یادآوری می‌کنم که بی این دو عدد، هر بحثی دربارهٔ «بکاپ خوب» سلیقه‌ای است.

مدل بازیابی: مهم‌ترین تصمیمی که اغلب تصادفی گرفته می‌شود

SQL Server سه مدل بازیابی دارد و انتخابش تعیین می‌کند اصلاً چه بکاپ‌هایی ممکن‌اند.

SIMPLE

لاگ ترنزکشن پس از هر checkpoint خودش را بازیافت می‌کند، پس لاگ بی‌مهار رشد نمی‌کند — و بکاپ لاگ ممکن نیست. یعنی فقط می‌توانید به لحظهٔ آخرین فول یا دیفرنشیال برگردید. اگر فول شما هر شب ساعت ۲ گرفته می‌شود و سرور ساعت ۱۷ می‌سوزد، ۱۵ ساعت کار از دست رفته است. برای دیتابیس‌های گزارشی و انبار داده‌ای که از منبع دیگری بازسازی می‌شوند انتخاب درستی است؛ برای دیتابیس تراکنشی تقریباً هرگز.

FULL

هر تغییر در لاگ می‌ماند تا بکاپ لاگ گرفته شود. این مدل به شما بازیابی نقطه‌ای می‌دهد: می‌توانید به ۱۴:۳۷ همان روز برگردید، یعنی یک لحظه پیش از آن DELETE بی‌شرط. قیمتش این است که باید بکاپ لاگ بگیرید؛ وگرنه لاگ رشد می‌کند تا دیسک پر شود و دیتابیس از نوشتن بازبماند.

این رایج‌ترین خرابی‌ای است که در سرورهای مشتریان دیده‌ام: مدل روی FULL است (پیش‌فرض بسیاری از نصب‌ها)، ولی هیچ‌کس بکاپ لاگ نمی‌گیرد. تیم فقط می‌داند «فایل لاگ ۴۰۰ گیگ شده» و هر چند ماه یک بار آن را کوچک می‌کند. در واقع آن سرور نه مزیت مدل FULL را دارد و نه راحتی SIMPLE را؛ فقط هزینه‌اش را می‌دهد.

BULK_LOGGED

حالت میانی برای عملیات انبوه. در بازه‌هایی که عملیات حجیم (مثل بازسازی ایندکس یا درج انبوه) انجام می‌دهید، لاگ کم‌تر می‌نویسد. ولی در همان بازه بازیابی نقطه‌ای را از دست می‌دهید. ابزار مفیدی است برای پنجره‌های مشخص، نه برای همیشه.

برای دیدن وضعیت فعلی:

SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM   sys.databases
ORDER  BY name;

ستون سوم را جدی بگیرید. اگر روی LOG_BACKUP ایستاده، یعنی SQL Server منتظر بکاپ لاگ است تا فضای لاگ را آزاد کند — و شما آن را نمی‌گیرید.

سه نوع بکاپ و کاری که هر کدام می‌کند

نکتهٔ کلیدی که خیلی‌ها اشتباه می‌فهمند: دیفرنشیال از آخرین فول حساب می‌شود، نه از آخرین دیفرنشیال. پس دیفرنشیال‌ها در طول هفته بزرگ‌تر و بزرگ‌تر می‌شوند و روزی که فول بعدی گرفته شود از صفر شروع می‌کنند. به همین دلیل گرفتن یک فول دستی «فقط برای اطمینان» وسط هفته، زنجیرهٔ دیفرنشیال شما را جابه‌جا می‌کند. اگر ناچارید چنین کاری بکنید، از COPY_ONLY استفاده کنید تا زنجیره دست‌نخورده بماند:

BACKUP DATABASE [Sales]
TO DISK = N'E:\backup\Sales_adhoc.bak'
WITH COPY_ONLY, COMPRESSION, CHECKSUM, INIT;

یک زمان‌بندی که در عمل کار می‌کند

این نقطهٔ شروع خوبی برای یک دیتابیس تراکنشی متوسط است:

• فول: هفته‌ای یک بار، در کم‌بارترین شب.
• دیفرنشیال: هر شب.
• لاگ: هر ۱۵ دقیقه — یا هر چند دقیقه‌ای که RPO شما می‌گوید.

چرا فول هفتگی و نه شبانه؟ چون فول روی دیتابیس چند صد گیگی، I/O سنگینی است و اگر شب‌ها هم کار دارید مزاحم می‌شود. دیفرنشیال شبانه همان نتیجه را با هزینهٔ کم‌تر می‌دهد. ولی اگر دیتابیس کوچک است (زیر ۵۰ گیگ)، فول شبانه ساده‌تر است و سادگی خودش یک ارزش عملیاتی است: زنجیرهٔ کوتاه‌تر، خطای انسانی کم‌تر.

سه گزینه‌ای که در هر دستور بکاپ باید باشند:

• CHECKSUM — هنگام نوشتن، صحت صفحه‌ها بررسی می‌شود. بی این، می‌توانید ماه‌ها بکاپِ دیتابیسِ خراب بگیرید و ندانید.
• COMPRESSION — معمولاً دو تا چهار برابر کوچک‌تر و سریع‌تر، چون I/O کم‌تری می‌نویسد. کمی CPU می‌خورد؛ تقریباً همیشه معاملهٔ خوبی است.
• INIT یا نام فایل یکتا — وگرنه بکاپ‌ها به یک فایل الحاق می‌شوند و فایلی دارید که هر روز بزرگ‌تر می‌شود و کسی نمی‌داند چه چیزهایی درونش است.

کجا بنویسیم: مهم‌تر از خود زمان‌بندی

بکاپی که روی همان دیسکِ دیتابیس نوشته می‌شود، در برابر خرابی دیسک محافظتی ندارد. بکاپی که روی همان سرور است، در برابر سوختن سرور محافظتی ندارد. و بکاپی که روی یک شیر شبکه‌ای است که با همان حساب ویندوزی دسترسی دارد، در برابر باج‌گیر محافظتی ندارد — چون باج‌گیر با همان حساب همه‌جا را رمز می‌کند.

قاعدهٔ عملی: یک نسخهٔ محلی برای بازیابی سریع، و یک نسخه در جایی که با اعتبارنامهٔ دیگری نوشته می‌شود. لازم نیست پیچیده باشد؛ لازم است جدا باشد.

و حالا بخشی که تقریباً همه نادیده می‌گیرند

تا اینجا دربارهٔ گرفتن بکاپ حرف زدیم. اما بکاپ گرفتن نیم کار است. بکاپی که آزموده نشده، بکاپ نیست؛ یک فرض است.

چیزهایی که فقط در آزمون بازیابی معلوم می‌شوند و در هیچ لاگ موفقیتی پیدا نیستند:

• فایل بکاپ نوشته شده ولی روی دیسکی که دیگر وجود ندارد یا مسیرش عوض شده.
• زنجیرهٔ لاگ جایی شکسته — کسی یک بکاپ دستی گرفته یا مدل بازیابی عوض شده.
• دیتابیس از قبل خرابی صفحه داشته و بکاپ‌ها همان خرابی را وفادارانه کپی کرده‌اند.
• بازگرداندن ۴ ساعت طول می‌کشد، در حالی که RTO شما ۱ ساعت است.
• بکاپ رمزنگاری‌شده است و کلیدش روی همان سروری بوده که سوخته.

حداقلِ قابل قبول این است: به‌صورت دوره‌ای، یک بکاپ را در یک دیتابیس موقت و با نام دیگر برگردانید، DBCC CHECKDB بزنید، مدت کار را ثبت کنید و بعد پاکش کنید.

RESTORE DATABASE [Sales_drill]
FROM DISK = N'E:\backup\Sales_full.bak'
WITH MOVE N'Sales'     TO N'T:\drill\Sales_drill.mdf',
     MOVE N'Sales_log' TO N'T:\drill\Sales_drill.ldf',
     RECOVERY, STATS = 5;

DBCC CHECKDB([Sales_drill]) WITH NO_INFOMSGS, ALL_ERRORMSGS;

DROP DATABASE [Sales_drill];

دو نکتهٔ عملی: نام دیگر و مسیر دیگر بدهید تا به دیتابیس تولیدی نخورد، و این کار را روی سرور تولیدی در ساعت اوج انجام ندهید — CHECKDB سبک نیست.

خطاهایی که بیشتر از همه دیده‌ام

• مدل FULL بی بکاپ لاگ. شایع‌ترین. هم لاگ را باد می‌کند و هم توهم بازیابی نقطه‌ای می‌دهد.
• کوچک کردن دوره‌ای فایل لاگ. علامت را پاک می‌کند، بیماری را نه. لاگ دوباره رشد می‌کند و این رشد مکرر، فایل را قطعه‌قطعه و کندش می‌کند.
• بکاپ روی همان دیسک داده. در برابر تنها خرابی‌ای که واقعاً محتمل است بی‌فایده.
• اعتماد به «جاب سبز است». جاب موفق یعنی فایل نوشته شد، نه اینکه قابل بازگرداندن است.
• نبود بکاپ از master و msdb. روز بازسازی سرور، همهٔ لاگین‌ها، جاب‌ها و تاریخچه آنجاست.
• رمز بکاپ در همان سرور. بکاپ رمزشده‌ای که کلیدش رفته، فایل بی‌معنی است.

چک‌لیست: همین امروز

1. مدل بازیابی هر دیتابیس را ببینید و برای هر کدام تصمیم آگاهانه بگیرید.
2. برای دیتابیس‌های FULL، بکاپ لاگ با فاصلهٔ متناسب RPO بگذارید.
3. log_reuse_wait_desc را چک کنید؛ هر چیزی جز NOTHING یعنی چیزی فضای لاگ را گرو گرفته.
4. مطمئن شوید بکاپ‌ها جای دیگری هم کپی می‌شوند.
5. یک بکاپ را امروز در دیتابیس موقت برگردانید و مدتش را ثبت کنید. عدد را با RTO مقایسه کنید.
6. تاریخ آخرین فول موفق هر دیتابیس را جایی ببینید که اگر یک هفته قدیمی شد، خودش خبر بدهد.

بند آخر همان کاری است که DBMug انجام می‌دهد: تاریخچهٔ بکاپ را از msdb می‌خواند، دیتابیسی که فول تازه ندارد را علامت می‌زند، پلن فول و دیفرنشیال و لاگ را در پنجرهٔ کم‌باری اجرا می‌کند، و به‌صورت دوره‌ای یک بکاپ را در دیتابیس موقت برمی‌گرداند و CHECKDB می‌زند. اما حتی اگر این کار را دستی انجام می‌دهید، چک‌لیست بالا همان چیزی است که باید برایش عدد داشته باشید.