بکاپی که آزموده نشده، بکاپ نیست: آزمون بازیابی در SQL Server

تقریباً هر تیمی که با SQL Server کار می‌کند یک جاب بکاپ دارد. جاب هر شب اجرا می‌شود، تیکش سبز است و فایل‌ها در یک پوشه روی هم انباشته می‌شوند. مشکل این است که سبز بودن جاب فقط یک چیز را ثابت می‌کند: SQL Server توانسته فایلی بنویسد. ثابت نمی‌کند که آن فایل برمی‌گردد، که زنجیرهٔ بکاپ‌ها کامل است، یا که دیتابیسِ برگشته سالم است.

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

سه نوع بکاپ و نقش هر کدام

SQL Server سه نوع بکاپ اصلی دارد و یک پلن درست معمولاً ترکیبی از هر سه است:

• فول (Full): کل دیتابیس به‌علاوهٔ بخشی از لاگ که برای سازگار ماندن لازم است. نقطهٔ شروع هر بازیابی است.
• دیفرنشیال (Differential): همهٔ extentهایی که از آخرین فول تغییر کرده‌اند. هر دیفرنشیال نسبت به آخرین فول تجمعی است، پس برای بازیابی فقط آخرین دیفرنشیال لازم است.
• لاگ ترنزکشن (Log): رکوردهای لاگ از آخرین بکاپ لاگ تا الان. همین بکاپ است که بازیابی تا یک لحظهٔ مشخص را ممکن می‌کند و همین بکاپ است که اجازه می‌دهد فضای لاگ دوباره استفاده شود.

یک الگوی رایج: فول هفتگی در پنجرهٔ کم‌باری، دیفرنشیال روزانه، و بکاپ لاگ هر ۱۵ دقیقه. عدد دقیق را دو سؤال تعیین می‌کند: چقدر داده می‌توانید از دست بدهید (RPO) و بازیابی چقدر می‌تواند طول بکشد (RTO). اگر کسب‌وکار نمی‌تواند بیش از ۱۵ دقیقه داده از دست بدهد، بکاپ لاگ ساعتی کافی نیست.

-- Full
BACKUP DATABASE Sales
  TO DISK = N'E:BackupSales_FULL_20260820.bak'
  WITH COMPRESSION, CHECKSUM, STATS = 10;

-- Differential
BACKUP DATABASE Sales
  TO DISK = N'E:BackupSales_DIFF_20260821.bak'
  WITH DIFFERENTIAL, COMPRESSION, CHECKSUM;

-- Log
BACKUP LOG Sales
  TO DISK = N'E:BackupSales_LOG_20260821_0915.trn'
  WITH COMPRESSION, CHECKSUM;

گزینهٔ CHECKSUM را جدی بگیرید: SQL Server هنگام بکاپ، checksum صفحه‌ها را بررسی می‌کند و اگر صفحهٔ خرابی ببیند، بکاپ با خطا متوقف می‌شود. بدون آن، ممکن است یک صفحهٔ خراب ماه‌ها بی‌صدا در بکاپ‌ها تکثیر شود.

مدل بازیابی: تصمیمی که پلن بکاپ را تعیین می‌کند

هر دیتابیس یک Recovery Model دارد و این تنظیم مشخص می‌کند بکاپ لاگ اصلاً معنا دارد یا نه:

مدل	بکاپ لاگ	بازیابی نقطه‌ای	کاربرد
SIMPLE	ندارد	نه؛ فقط تا آخرین فول/دیفرنشیال	دیتابیس‌های تست، گزارش‌گیری، داده‌ای که از جای دیگر بازسازی می‌شود
FULL	لازم است	بله	دیتابیس‌های عملیاتی
BULK_LOGGED	لازم است	محدود؛ نه داخل بازهٔ عملیات bulk	موقتاً هنگام بارگذاری‌های حجیم

خطای رایج این است که دیتابیس روی FULL است ولی کسی برایش بکاپ لاگ تعریف نکرده. نتیجه دو چیز است: بازیابی نقطه‌ای در عمل وجود ندارد، و فایل لاگ آن‌قدر بزرگ می‌شود تا دیسک پر شود. این سناریو را در نوشتهٔ لاگ ترنزکشن SQL Server پر شد مفصل باز کرده‌ایم. برای دیدن وضعیت فعلی:

SELECT d.name, d.recovery_model_desc,
       MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END) AS last_full,
       MAX(CASE WHEN b.type = 'I' THEN b.backup_finish_date END) AS last_diff,
       MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END) AS last_log
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset b ON b.database_name = d.name
WHERE d.database_id > 4
GROUP BY d.name, d.recovery_model_desc
ORDER BY d.name;

هر دیتابیسی که مدلش FULL است و ستون last_log خالی یا قدیمی دارد، همین حالا یک مشکل باز است.

زنجیرهٔ بکاپ و راه‌هایی که می‌شکند

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

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

جدول‌های msdb برای بازسازی زنجیره کافی‌اند. کوئری زیر بکاپ‌های یک دیتابیس را با بازهٔ LSN و نام فایل نشان می‌دهد؛ اگر first_lsn یک بکاپ لاگ با last_lsn قبلی جور نشود، زنجیره همان‌جا شکسته است:

SELECT b.type, b.backup_start_date, b.first_lsn, b.last_lsn,
       b.database_backup_lsn, b.is_copy_only, m.physical_device_name
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily m ON m.media_set_id = b.media_set_id
WHERE b.database_name = N'Sales'
  AND b.backup_start_date > DATEADD(DAY, -14, GETDATE())
ORDER BY b.backup_start_date;

RESTORE VERIFYONLY کافی نیست

بسیاری از پلن‌های نگه‌داری بعد از بکاپ یک RESTORE VERIFYONLY اجرا می‌کنند و خیال همه راحت می‌شود. این دستور بررسی می‌کند که مجموعهٔ بکاپ کامل و خواناست، سرآیندها درست‌اند و اگر بکاپ با CHECKSUM گرفته شده باشد، checksumها را دوباره حساب می‌کند. کار مفیدی است، ولی چند چیز را ثابت نمی‌کند:

• اینکه دیتابیس واقعاً برمی‌گردد و ریکاوری بدون خطا تمام می‌شود.
• اینکه داده‌ها از نظر منطقی سالم‌اند؛ خرابی‌ای که پیش از بکاپ در دیتابیس بوده، با خیال راحت در بکاپ هم هست.
• اینکه فضای دیسک، مسیرها و دسترسی‌های لازم برای بازیابی روی سرور مقصد وجود دارد.
• اینکه بازیابی در زمانی که کسب‌وکار تحمل می‌کند تمام می‌شود.

تنها آزمونی که به سؤال «بکاپ برمی‌گردد؟» جواب می‌دهد، برگرداندن آن است. VERIFYONLY آزمون خوانایی فایل است، نه آزمون بازیابی.

آزمون واقعی: بازگرداندن در دیتابیس موقت و CHECKDB

روش درست ساده است: بکاپ را با نامی دیگر و روی مسیری جدا برگردانید، روی آن DBCC CHECKDB بزنید، نتیجه و مدت زمان را ثبت کنید و دیتابیس موقت را پاک کنید. این کار را بهتر است روی سروری غیر از سرور تولیدی انجام دهید تا هم بار اضافه روی تولید نیاید و هم بازیابی روی سرور دیگر واقعاً آزموده شود.

-- 1) Logical file names inside the backup
RESTORE FILELISTONLY FROM DISK = N'E:BackupSales_FULL_20260820.bak';

-- 2) Restore full + diff + logs under a temporary name
RESTORE DATABASE Sales_RestoreTest
  FROM DISK = N'E:BackupSales_FULL_20260820.bak'
  WITH MOVE N'Sales'     TO N'T:RestoreTestSales_RT.mdf',
       MOVE N'Sales_log' TO N'T:RestoreTestSales_RT.ldf',
       NORECOVERY, CHECKSUM, STATS = 10;

RESTORE DATABASE Sales_RestoreTest
  FROM DISK = N'E:BackupSales_DIFF_20260821.bak'
  WITH NORECOVERY, CHECKSUM;

RESTORE LOG Sales_RestoreTest
  FROM DISK = N'E:BackupSales_LOG_20260821_0915.trn'
  WITH NORECOVERY, CHECKSUM;

RESTORE DATABASE Sales_RestoreTest WITH RECOVERY;

-- 3) Integrity check
DBCC CHECKDB (Sales_RestoreTest) WITH NO_INFOMSGS, ALL_ERRORMSGS;

-- 4) Clean up
DROP DATABASE Sales_RestoreTest;

برای آزمون بازیابی نقطه‌ای، در آخرین RESTORE LOG از STOPAT استفاده کنید و بعد با یک کوئری ساده بررسی کنید که آخرین رکوردهای مهم پیش از آن لحظه حاضرند. زمان کل این فرایند را هم یادداشت کنید؛ این همان RTO واقعی شماست، نه عددی که در سند نوشته شده.

هر چند وقت یک بار؟ دیتابیس‌های حیاتی را حداقل هفته‌ای یک بار، و بقیه را به نوبت. اگر ده‌ها دیتابیس دارید، یک چرخه بسازید که هر شب یکی دو دیتابیس را می‌آزماید تا در یک ماه همه پوشش داده شوند.

نگه‌داری و کپی بیرونی

بکاپی که روی همان سرور یا همان SAN دیتابیس است، در برابر بسیاری از اتفاق‌ها بی‌فایده است: خرابی آرایهٔ دیسک، حذف اشتباهی ماشین مجازی، و به‌خصوص باج‌افزار که اول سراغ همین پوشه‌ها می‌رود (در نشانه‌های اولیهٔ باج‌افزار روی ویندوز سرور دیده‌اید که پاک کردن راه برگشت جزو اولین قدم‌های مهاجم است). چند قاعدهٔ ساده:

• دست‌کم یک نسخه از بکاپ روی مقصدی بیرون از سرور دیتابیس، با حساب کاربری جدا که سرور دیتابیس نتواند فایل‌هایش را پاک کند.
• سیاست نگه‌داری بر اساس زنجیره، نه سن فایل: هیچ فولی را پاک نکنید تا وقتی دیفرنشیال یا لاگی به آن وابسته است.
• دست‌کم یک بکاپ قدیمی‌تر (مثلاً ماهانه) برای وقتی که خرابی یا حذف داده دیر کشف می‌شود.
• آزمون بازیابی گاهی از روی همان نسخهٔ بیرونی، چون کپی ناقص بیرونی هم رایج است.

خودکار کردن این چرخه با DBMug

همهٔ کارهای بالا را می‌شود با جاب‌های SQL Agent و چند اسکریپت ساخت، و اگر تیمتان DBA دارد احتمالاً همین کار را کرده است. مشکل معمولاً نگه‌داشتن آن است: اسکریپتی که بعد از اضافه شدن دیتابیس جدید به‌روز نشده یا جابی که شکست خورده و کسی نفهمیده.

دی‌بی‌ماگ همین چرخه را برای تیم‌های بی‌DBA انجام می‌دهد: پلن بکاپ فول، دیفرنشیال و لاگ را در پنجرهٔ کم‌باری با فشرده‌سازی، بررسی سلامت، سیاست نگه‌داری و کپی به مقصد بیرونی اجرا می‌کند؛ به‌صورت دوره‌ای یک بکاپ را در دیتابیس موقت برمی‌گرداند، CHECKDB می‌زند و پاکش می‌کند. برای بازیابی نقطه‌ای، زنجیرهٔ فول+دیفرنشیال+لاگ را برای لحظهٔ مورد نظر می‌سازد و پیش از اجرا وجود فایل‌ها را بررسی می‌کند، و بازنویسی دیتابیس تولیدی به تأیید صریح نام و یک بکاپ دم‌لاگ اجباری نیاز دارد. روی دیتابیس چیزی نصب نمی‌شود و حساب آزمون بازیابی از حساب پایش جداست. از SQL Server 2016 به بالا پشتیبانی می‌شود؛ جزئیات بیشتر در dbmug.ir.

پرسش‌های پرتکرار

آیا DBCC CHECKDB را روی خود دیتابیس تولیدی هم بزنیم؟

بله، اگر پنجرهٔ زمانی و منابعش را دارید. CHECKDB روی نسخهٔ برگشته خرابی‌ای را نشان می‌دهد که در بکاپ هست؛ اگر نتیجه تمیز باشد، تقریباً با اطمینان می‌شود گفت دیتابیس تولیدی هم در لحظهٔ بکاپ سالم بوده است. به همین دلیل خیلی از تیم‌ها CHECKDB را به‌طور کامل به سرور آزمون منتقل می‌کنند تا باری روی تولید نیاید.

بکاپ از روی ماشین مجازی (snapshot) جای بکاپ SQL را می‌گیرد؟

معمولاً نه. snapshot ماشین مجازی ممکن است در لحظه‌ای گرفته شود که دیتابیس در وضعیت سازگار نیست، و بازیابی نقطه‌ای هم نمی‌دهد. اگر از آن استفاده می‌کنید، مطمئن شوید با VSS و به‌صورت application-consistent گرفته می‌شود و زنجیرهٔ بکاپ لاگ SQL را نمی‌شکند.

اگر CHECKDB روی نسخهٔ برگشته خطا داد چه کنیم؟

اول روی دیتابیس تولیدی هم CHECKDB بزنید تا معلوم شود خرابی از خود دیتابیس است یا از فایل بکاپ. سراغ REPAIR_ALLOW_DATA_LOSS نروید؛ راه درست معمولاً بازگرداندن از آخرین بکاپ سالم و اعمال لاگ‌ها تا نزدیک‌ترین نقطه است، و هم‌زمان بررسی لاگ خطای سیستم و سلامت دیسک.

جمع‌بندی

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