تهیه Backup در SQL Server یک امر ضروری برای حفظ اطلاعات در دیتابیس است. بکاپ گیری در SQL Server به سه روش صورت می‌گیرد. برای انجام بهتر این کار لازم است تا با بخش‌های مختلف یک Backup set آشنایی پیدا کنید. همچنین لازم است تا بهترین روش بک آپ گیری در شرایط متفاوت را بدانید. در مقاله آشنایی با مفاهیم اولیه بکاپ گیری در sql server قصد داریم تا شما را با این موارد آشنا کنیم.

بکاپ ست (Backup set)

به خروجی هر دستور Backup، یک Backup set گفته می­‌شود. یک Backup set می­‌تواند از نوع Full، Differential و یا Log باشد. به‌عنوان مثال، خروجی دستور زیر یک Backup set از نوع Full است.

BACKUP DATABASE Northwind TO DISK = 'D:\Temp\NW.bak'

مدیا ست (Media set)

به محل ذخیره سازی یک Backup set ، Media set گفته می‌شود. درون یک Media set می‌تواند Backup set های مختلفی (هر نوع از backup از هر بانک اطلاعاتی) قرار بگیرد. به‌عنوان مثال، برای تهیه Backup در SQL Server دستور زیر، یک Media set با نام NW.bak ایجاد می‌کند و Backup set را در آن ذخیره می‌نماید. اما در صورتی که این Media set وجود داشته باشد، Backup set را در انتهای آن قرار می‌دهد.

BACKUP DATABASE Northwind TO DISK = 'D:\Temp\NW.bak'

درون یک Media set مطابق تصویر زیر است. Media Header که هنگام ایجاد Media ایجاد می­‌شود، یک یا چند Backup set که هر یک از آن‌ها برای خود یک Backup set Header دارند.

برای مشاهده اطلاعات مربوط به Media Header از دستور زیر استفاده نمایید.

RESTORE LABELONLY FROM DISK = 'D:\Temp\NW1.bak'

برای مشاهده اطلاعات مربوط  به Backup set های موجود در مدیا، از دستور زیر استفاده نمایید.

RESTORE HEADERONLY FROM DISK = 'D:\Temp\NW1.bak'

در صورتی که درون مدیا چندین Backup set وجود داشته باشد، می‌­توانید با دستور زیر Header مربوط به یک Backup set خاص را مشاهده نمایید. N شماره Backup set مورد نظر است.

RESTORE HEADERONLY FROM DISK = 'D:\Temp\NW1.bak'
WITH FILE = [N]

برای مشاهده فایل‌های درون یک Backup set در تهیه Backup در SQL Server از دستور زیر استفاده نمایید.

RESTORE FILELISTONLY FROM DISK = 'D:\Temp\NW1.bak'
WITH FILE = [N]

خانواده مدیا (Media Family)

به مجموعه‌ای از Media set های مرتبط با یکدیگر، Media Family گفته می‌شود. به‌عنوان مثال، دستور زیر، یک Backup set را درون دو Media set (NW1 و NW2) قرار می‌دهد که به مجموعه‌ی این دو Media set، Media Family گفته می‌شود. توجه داشته باشید که اگر هر یک از این Media Set ها را از دست بدهید، دیگر امکان بازیابی بانک اطلاعاتی از سایر Media Set ها برای تهیه Backup در SQL Server وجود ندارد. 

BACKUP DATABASE Northwind TO DISK = 'D:\Temp\NW1.bak',
DISK = 'D:\Temp\NW2.bak'

انواع بکاپ (Backup Types)

در Sql Server سه نوع Backup وجود دارد.

۱- Full Backup

فرض کنید که شما یک بانک اطلاعاتی دارید که فایل­‌های آن مطابق تصویر زیر است.

در این نوع از بکاپ برای تهیه Backup در SQL Server از کل دیتا فایل‌های بانک اطلاعاتی بکاپ تهیه می‌شود. از فایل Log نیز مطابق تصویر، از لحظه شروع Backup تا لحظه اتمام آن از Log Record های که در این بازه قرار گرفته‌اند، بکاپ گرفته می‌شود. دلیل بکاپ گیری از فایل Log به مبحث WAL(Write ahead log) باز می‌گردد. اگر در حین فرآیند بکاپ گیری، داده‌ای در یک Page تغییر کند، اطلاعات تغییر در فایل Log ذخیره می‌شود و حین فرآیند Restore، این مقدار از Log خوانده و در Data File مربوطه اعمال می‌گردد.

دستور ایجاد یک Full Backup به شکل زیر است.

BACKUP DATABASE [Database name] TO DISK = 'Media set path'

Differential Backup -2

این نوع از بکاپ گیری برای تهیه Backup در SQL Server به‌شدت به Full Backup قبل از خود وابسته است و تعریف دقیق آن عبارت است از ذخیره آخرین تغییرات به نسبت آخرین Full Backup.

برای بازیابی این نوع از بکاپ، باید حتماً Full Backup قبل از آن در دسترس باشد. دستور ایجاد Differential Backup به شکل زیر است.

BACKUP DATABASE [Database name] TO DISK = ['Media set path']
WITH DIFFERENTIAL

SQL Server با مراجعه به Page سیستمی DCM (Differential Changed Map) آخرین extent های تغییر یافته را شناسایی می­‌کند. DCM Page یک Page سیستمی است که آدرس extent های تغییر یافته را نسبت به آخرین Full Backup در خود ذخیره می­‌کند. داده‌های موجود در DCM Page پس از تهیه یک Full Backup حذف می‌شود.

Log Backup -3

در این نوع از بکاپ فقط از فایل Log ، Backup تهیه می‌شود. لازمه تهیه Backup در SQL Server از این نوع به شرح زیر است.

1. Recovery Model بانک اطلاعاتی حتماً باید Full یا Bulk Logged باشد.
2. یک Full Backup حتماً از بانک اطلاعاتی تهیه شده باشد.

برای بازیابی این نوع از بکاپ باید حتماً Full Backup و Log Backup های قبل از آن در دسترس باشد. دستور تهیه Backup در SQL Server از این نوع به شرح زیر است.

BACKUP LOG [Database name] TO DISK = 'Media set path'

گزینه های پشتیبان گیری (Backup options)

در ادامه Option های مربوط به فرآیند تهیه Backup در SQL Server آورده شده است.

FORMAT -1

زمانی که از FORMAT استفاده می‌­کنیم، اگر Media از قبل وجود داشته باشد، تمامی اطلاعات داخل مدیا اعم از Media header و تمامی بکاپ‌های موجود در آن پاک شده و Media از ابتدا ایجاد می‌­شود. این طور در نظر بگیرید که یک Media جدید برای شما ایجاد می­‌شود.

BACKUP DATABASE Northwind TO DISK = 'D:\Temp\NW.bak'
WITH FORMAT

INIT -2

زمانی که از INIT استفاده می­‌کنیم، اگر Media از قبل وجود داشته باشد، تمامی بکاپ‌های موجود در آن پاک می‌­شود؛ اما توجه داشته باشید که اطلاعات مربوط به Media header تغییر نخواهد کرد.

BACKUP DATABASE Northwind TO DISK = 'D:\Temp\NW.bak'
WITH INIT

۳- COPY_ONLY

زمانی که شما برای تهیه Backup در SQL Server فرآیند Backup گیری Job تهیه کرده‌اید، اگر بخواهید یک Backup خارج از برنامه Job تهیه کنید، برای اینکه زنجیره‌ی بکاپ‌های گرفته شده توسط Job خراب نشود (به‌عنوان مثال، DCM Page ریست نشود) باید از این Option استفاده نمایید.

BACKUP DATABASE Northwind TO DISK = 'D:\Temp\NW.bak'
WITH COPY_ONLY

۴- COMPRESSION

زمانی که شما از این Option در فرآیند بکاپ‌گیری استفاده نمایید، باعث می‌شود تا حجم فایل Backup کاهش یابد. همچنین باعث افزایش سرعت فرآیند بکاپ‌گیری می‌شود.

BACKUP DATABASE Northwind TO DISK = 'D:\Temp\NW.bak'
WITH COMPRESSION

۵- NAME

اگر بخواهید برای Backup set خود یک نام تعیین کنید، از این Option استفاده نمایید.

BACKUP DATABASE Northwind TO DISK = 'D:\Temp\NW.bak'
WITH NAME = 'نام بکاپ'

۶- DESCRIPTION

اگر بخواهید برای Backup set خود توضیحات بنویسید، از این Option استفاده نمایید.

BACKUP DATABASE Northwind TO DISK = 'D:\Temp\NW.bak'
WITH DESCRIPTION = 'توضیحات'

۷- MEDIANAME

اگر بخواهید برای Media set خود یک نام تعیین کنید، از این Option استفاده نمایید. توجه داشته باشید که از این Option فقط در دو حالت می‌توان استفاده کرد.

• وقتی Media set وجود ندارد.
• وقتی با Option: FORMAT فرآیند تهیه Backup در SQL Server انجام می‌شود.

BACKUP DATABASE Northwind TO DISK = 'D:\Temp\NW.bak'
WITH MEDIANAME = N'نام مدیا', FORMAT

۸- MEDIADESCRIPTION

اگر بخواهید برای Media set خود یک توضیحات تعیین کنید از این Option استفاده نمایید. این Option تمامی شروط MEDIANAME را برای تهیه Backup در SQL Server شامل می‌شود.

BACKUP DATABASE Northwind TO DISK = 'D:\Temp\NW.bak'
WITH MEDIADESCRIPTION = N'توضیحات مدیا ست'

۹- EXPIREDATE

این Option باعث می‌شود که Backup set شما زمانی که دوباره یک بکاپ جدید در همان مدیا قرار می‌دهید و از INIT هم استفاده می‌کنید، overwrite نشود. 

BACKUP DATABASE Northwind TO DISK = N'D:\Temp\NW.bak'
WITH EXPIREDATE = '2021-08-19 15:00:00'

اگر شما دوباره یک بکاپ در همان مدیا با Option: INIT قرار دهید، با خطای زیر مواجه می‌شوید.

BACKUP DATABASE Northwind TO DISK = N'D:\Temp\NW.bak'
WITH INIT

Error:

Msg 4030, Level 16, State 1, Line 5

The medium on device ‘D:\Temp\NW.bak’ expires on Aug 19 2021 12:00:00:000AM and cannot be overwritten.

برای رفع این مشکل هم می‌توانید از Option: SKIP استفاده نمایید. یکم عجیبه، قبول دارم!

BACKUP DATABASE Northwind TO DISK = N'D:\Temp\NW.bak'
WITH INIT, SKIP

RETAINDAYS -10

این option دقیقاً مشابه EXPIREDATE عمل می‌کند؛ با این تفاوت که به جای تاریخ، تعداد روز را تعیین می‌کنید که Backup set شما، قابلیت Overwrite ازطریق INIT را نداشته باشد.

BACKUP DATABASE Northwind TO DISK = N'D:\Temp\NW.bak'
WITH RETAINDAYS = 1

۱۱-  CHECKSUM

زمانی که از این Option استفاده می‌کنید، Checksum تمامی Page های محاسبه می‌شود و با مقدار ذخیره شده آن، مقایسه می‌شود. در کل، اگر خطای IO در بانک اطلاعاتی وجود داشته باشد، فرآیند تهیه Backup در SQL Server متوقف می‌شود و خطای IO به شما نشان داده می‌شود. همچنین یک Checksum هم برای کل فایل بکاپ هم تهیه و در فایل Backup ذخیره می‌کند. توصیه می‌شود که حتماً از این Option در بکاپ‌های خود استفاده نمایید.

BACKUP DATABASE Northwind TO DISK = N'D:\Temp\NW.bak'
WITH CHECKSUM

۱۲-  STOP_ON_ERROR

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

BACKUP DATABASE Northwind TO DISK = N'D:\Temp\NW.bak'
WITH CHECKSUM, STOP_ON_ERROR

۱۳-  CONTINUE_AFTER_ERROR

این Option باعث می‌شود که اگر حین تهیه Backup در SQL Server خطا رخ دهد، فرآیند بکاپ ادامه یابد.

BACKUP DATABASE Northwind TO DISK = N'D:\Temp\NW.bak'
WITH CHECKSUM, CONTINUE_AFTER_ERROR

از این Option هنگامی که بانک در حالت Suspected قرار گرفته است و شما می‌خواهید یک Tail log backup تهیه نمایید، استفاده کنید.

۱۴-  STATS

این Option درصد پیشرفت فرآیند را در تب Message نمایش می‌دهد. در این مثال، درصد پیشرفت با گام ۵% نمایش داده می‌شود.

BACKUP DATABASE Northwind TO DISK = N'D:\Temp\NW.bak'
WITH STATS = 5

Mirrored media set

اگر قصد این را دارید که هنگامی که تهیه Backup در SQL Server تمام می‌شود، دو فایل بکاپ ایجاد شود و می‌توانید از MIRROR استفاده نمایید.

BACKUP DATABASE Northwind TO DISK = N'D:\Temp\NW1.bak',
DISK = N'D:\Temp\NW2.bak'
MIRROR TO DISK = N'E:\Temp\NW1.bak',
DISK = N'E:\Temp\NW2.bak'
WITH STATS = 1, FORMAT

به دستور بالا توجه کنید. یک Media family از بانک Northwind در مسیر D:\Temp و یک Media Family کاملاً مشابه در مسیر E:\Temp ایجاد می‌نماید. در این سناریو، شما یک Backup از فایل Backup خود تهیه کرده‌اید.

تهیه Backup در SQL Server به این شیوه‌های گفته شده به شما کمک می‌کند تا بتوانید بهترین روش بکاپ گیری را باتوجه به شرایط خود انتخاب و اطلاعات خود را حفظ کنید.

برای آموزش تهیه نسخه پشتیبان از دیتابیس و باز گردانی فایل Backup در SQL Server می توانید این مطلب را مطالعه نمایید.