הגדרת גיבוי MS SQL Server (מלא/דיפרנציאלי/יומן)

הגדרת גיבוי MS SQL Server (מלא/דיפרנציאלי/יומן)

הגורם השכיח ביותר לאובדן נתונים ב-SQL Server אינו תקלת חומרה או תוכנת כופר. זהו היעדר אסטרטגיית גיבוי מאומתת: גיבויים נלקחו, אך איש לא בדק שחזור, וברגע הקריטי הגיבוי התקין האחרון היה מלפני שלושה שבועות. או שגיבוי יומן העסקאות נכשל בשקט מכיוון שהמאגרים היו במצב שחזור SIMPLE, וכל גיבויי היומן נכשלו עם This command is not supported with simple recovery model. נתקלנו במצבים כאלה עשרות פעמים — לכן בנינו תהליך שמבטל את ההפתעות הללו. לצוות שלנו ניסיון של שנים ב-SQL Server והוא ביצע למעלה מ-50 פרויקטים הכוללים הגדרת גיבוי ושחזור.

שלושת סוגי הגיבוי ומתי להשתמש בכל אחד

גיבוי מלא לוכד את כל המאגר. הוא עשוי להימשך שעות במאגרים גדולים. הוא הבסיס לגיבויים דיפרנציאליים ולגיבויי יומן.

גיבוי דיפרנציאלי כולל רק עמודים ששונו מאז הגיבוי המלא האחרון. הוא גדל עם הזמן: אם חלף שבוע מאז הגיבוי המלא, הגיבוי הדיפרנציאלי עשוי להיות כמעט גדול כמוהו. הוא מזרז את השחזור בהשוואה להפעלה מחדש של כל שרשרת היומן.

גיבוי יומן עסקאות מאחסן רשומות יומן עסקאות מאז הגיבוי האחרון. הוא דורש שמצב השחזור יהיה FULL או BULK_LOGGED. הוא מאפשר שחזור לנקודת זמן מדויקת.

אסטרטגיה אופיינית למאגרי OLTP בייצור:

• גיבוי מלא: פעם בשבוע (ליל ראשון)
• גיבוי דיפרנציאלי: פעם ביום (בימי חול בשעה 02:00)
• גיבוי יומן: כל 15–30 דקות

עם אסטרטגיה זו, אובדן הנתונים המרבי במקרה של אסון הוא 15–30 דקות.

מדוע גיבויי יומן עסקאות חשובים

ללא גיבויי יומן, השחזור אפשרי רק עד הגיבוי המלא או הדיפרנציאלי האחרון, כלומר אובדן נתונים של מספר שעות או ימים. עם גיבויי יומן, ניתן לחזור לכל שנייה בתוך המרווח. זה קריטי במיוחד עבור מערכות פיננסיות ו-ERP שבהן כל עסקה חשובה. אם המאגר במצב שחזור SIMPLE, גיבוי יומן בלתי אפשרי — יש לעבור ל-FULL. זה דורש זמן השבתה, אך משתלם.

הגדרה באמצעות SQL Server Agent Jobs (T-SQL)

תוכניות תחזוקה גרפיות (GUI) שבריריות וקשות לניהול עם git. הגישה הנכונה היא סקריפטים של T-SQL ב-Agent Jobs.

-- Пример job для полного бэкапа с компрессией
DECLARE @BackupPath NVARCHAR(500) = 'D:\SQLBackup\Full\AppDB_' + CONVERT(NVARCHAR(20), GETDATE(), 112) + '_' + REPLACE(CONVERT(NVARCHAR(8), GETDATE(), 108), ':', '') + '.bak';
BACKUP DATABASE [AppDB] TO DISK = @BackupPath WITH COMPRESSION, CHECKSUM, STATS = 10, DESCRIPTION = 'Weekly Full Backup';
-- Проверка целостности после бэкапа
RESTORE VERIFYONLY FROM DISK = @BackupPath WITH CHECKSUM;

-- Transaction Log backup
DECLARE @LogPath NVARCHAR(500) = 'D:\SQLBackup\Log\AppDB_log_' + CONVERT(NVARCHAR(20), GETDATE(), 112) + '_' + REPLACE(CONVERT(NVARCHAR(8), GETDATE(), 108), ':', '') + '.trn';
BACKUP LOG [AppDB] TO DISK = @LogPath WITH COMPRESSION, CHECKSUM, STATS = 5;

האפשרות -- Пример job для полного бэкапа с компрессией DECLARE @BackupPath NVARCHAR(500) = 'D:\SQLBackup\Full\AppDB_' + CONVERT(NVARCHAR(20), GETDATE(), 112) + '_' + REPLACE(CONVERT(NVARCHAR(8), GETDATE(), 108), ':', '') + '.bak'; BACKUP DATABASE [AppDB] TO DISK = @BackupPath WITH COMPRESSION, CHECKSUM, STATS = 10, DESCRIPTION = 'Weekly Full Backup'; -- Проверка целостности после бэкапа RESTORE VERIFYONLY FROM DISK = @BackupPath WITH CHECKSUM; מוסיפה checksum לגיבוי — אם עמוד פגום, זה מתגלה במהלך הגיבוי, לא במהלך השחזור במשבר.

פתרון התחזוקה של Ola Hallengren

עבור רוב הפרויקטים, עדיף להשתמש בפתרון התחזוקה של Ola Hallengren (חינמי, דה-פקטו סטנדרט בקהילה) במקום לכתוב סקריפטים מותאמים אישית. זה מקצר את זמן התחזוקה ביותר מפי 5 — אין צורך לתקן פתרונות משלך. הוא תומך ב-Always On ומדלג אוטומטית על גיבוי יומן עבור מאגרי SIMPLE.

-- Пример вызова для полного бэкапа всех пользовательских баз
EXECUTE dbo.DatabaseBackup
    @Databases = 'USER_DATABASES',
    @Directory = 'D:\SQLBackup',
    @BackupType = 'FULL',
    @Compress = 'Y',
    @CheckSum = 'Y',
    @Verify = 'Y',
    @CleanupTime = 168, -- удалять файлы старше 168 часов (7 дней)
    @LogToTable = 'Y';

-- Log backup с очисткой старых файлов
EXECUTE dbo.DatabaseBackup
    @Databases = 'USER_DATABASES',
    @Directory = 'D:\SQLBackup',
    @BackupType = 'LOG',
    @Compress = 'Y',
    @CheckSum = 'Y',
    @CleanupTime = 48,
    @LogToTable = 'Y';

הפתרון מדלג אוטומטית על גיבוי יומן עבור מאגרי SIMPLE, מטפל ב-Always On AG (מבצע גיבוי על העותק המועדף), וכותב היסטוריה לטבלה לניטור.

באיזו תדירות כדאי לבדוק שחזור?

גיבוי ללא אימות אינו גיבוי. לפחות אחת לרבעון, יש לשחזר בפועל מאגר מגיבוי לשרת בדיקה. אנו כוללים שחזור בדיקה מתוכנן בכל פרויקט ומכשירים את הצוות שלך לבצע זאת באופן עצמאי. זו הדרך היחידה להיות בטוחים שבמצב קריטי הנתונים יהיו זמינים.

-- Восстановление с точкой во времени
RESTORE DATABASE [AppDB_Test] FROM DISK = 'D:\SQLBackup\Full\AppDB_YYYYMMDD_HHMMSS.bak' WITH NORECOVERY, REPLACE, MOVE 'AppDB' TO 'E:\TestData\AppDB_Test.mdf', MOVE 'AppDB_log' TO 'E:\TestData\AppDB_Test_log.ldf';
-- Применяем differential
RESTORE DATABASE [AppDB_Test] FROM DISK = 'D:\SQLBackup\Diff\AppDB_diff_YYYYMMDD_HHMMSS.bak' WITH NORECOVERY;
-- Применяем log бэкапы до нужной точки
RESTORE LOG [AppDB_Test] FROM DISK = 'D:\SQLBackup\Log\AppDB_log_YYYYMMDD_HHMMSS.trn' WITH NORECOVERY;
RESTORE LOG [AppDB_Test] FROM DISK = 'D:\SQLBackup\Log\AppDB_log_YYYYMMDD_HHMMSS.trn' WITH RECOVERY, STOPAT = 'YYYY-MM-DD HH:MM:SS'; -- точная точка восстановления

אחסון גיבוי ושכפול

גיבויים מקומיים הם רק קו ההגנה הראשון. להגנה מפני אובדן שרת, יש צורך בעותק חיצוני:

• SQL Server Backup to URL (Azure Blob Storage) — מובנה מ-SQL Server 2012
• Robocopy/rsync לשרת קבצים נפרד או NAS
• פתרונות צד שלישי: Veeam Backup & Replication, Commvault, Veritas

כלל 3-2-1: שלושה עותקים, שני אמצעי אחסון שונים, אחד מחוץ לאתר.

מה כלול בהגדרת גיבוי?

אנו מציעים שירות מקיף ומלא:

• ביקורת על התשתית הקיימת ומצב השחזור
• פיתוח אסטרטגיית גיבוי תוך התחשבות ב-RPO/RTO
• יצירה והגדרה של SQL Agent Jobs (T-SQL) או הטמעת Ola Hallengren
• הגדרת התראות (אימייל, Telegram) על כשלי גיבוי
• הגדרת שכפול גיבוי לאחסון חיצוני
• תיעוד של הליך השחזור
• הכשרת הצוות שלך (הדגמה חיה של שעתיים)
• אחריות לפעולה תקינה של הגיבוי למשך חודש לאחר ההגדרה

אנו בשוק כבר שנים וביצענו למעלה מ-50 פרויקטים של SQL Server. צור קשר כדי לקבל ייעוץ והערכה לפרויקט שלך תוך יום עסקים אחד.