نحوه بکاپ گیری و بازگردانی یا ریستور دیتابیس‌ها در SQL Server

دسته بندی : بلاگ بینا
تاریخ انتشار : فوریه 9, 2020

فهرست مطالب این مقاله:

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

در نرم افزار SQL Server به دو روش به صورت دستی و به صورت خودکار امکان پشتیبان‌گیری از دیتابیس وجود دارد.
در ادامه به شرح جزئیات در خصوص نحوه انجام بکاپ‌گیری ( backup ) به این دو روش پرداخته می‌شود.

بکاپ‌ گیری از SQL به صورت دستی (Manual)

عملیات بکاپ‌ گیری به صورت دستی را می‌توان به دو روش زیر انجام داد:

روش اول backup در SQL Server

1. در ابتدا پنجره SQL Server Management Studio را باز کرده و در قسمت Object Explorer با ورود به بخش databases، بر روی دیتابیس موردنظر کلیک راست کرده و از منوی Task گزینه Backup را انتخاب کنید.

2. با توجه به موارد مشخص شده در تصویر زیر نوع بکاپ را مشخص کنید و سپس گزینه Add را انتخاب کنید

نکته قابل توجه اینکه جهت بکاپ ‌گیری از Transaction Logها و محتوای Files and Groups می‌بایست Recovery Model دیتابیس مورد نظر، در حالت Full قرار داشته باشد.

جهت تغییر Recovery Model، بر روی دیتابیس موردنظر Properties گرفته و در تب Option گزینه Recovery Model را تغییر می‌دهیم.

3. در ادامه می‌بایست محل ذخیره و نام بکاپ backup را مشخص کرد.

4. توجه داشته باشید فایل بکاپ را حتما می‌بایست با پسوند bak ذخیره نمایید. در ادامه بر روی دکمه Ok کلیک نمایید.

5. درصورتیکه بکاپ ‌گیری با موفقیت انجام شود صفحه ی زیر را مشاهده خواهید کرد:

روش دوم بکاپ گیری از طریق T-SQL

در این روش می‌توان از طریق T-SQL عملیات بکاپ‌ گیری را انجام داد.

در ادامه روش بکا‌پ‌ گیری به روش T-SQL برای انواع روش های بکاپ‌ گیری شرح داده می‌شود.

بکاپ گیری Full Backup به روش T-SQL

1. وارد کنسول SQL Server Management Studio شوید و روی گزینه New Query در بالای صفحه کلیک کنید.

2. دستور زیر را وارد کرده و روی گزینه Execute کلیک کنید (دستور زیر دیتابیس BinaDB را در پوشه :c با نام BinaDB-Backup-Test.BAK در حالت Full Backup ذخیره می‌کند).

BACKUP DATABASE BinaDB TO DISK = ‘C:BinaDB-Backup-Test.BAK’
GO

توجه داشته باشید پس از اجرای Query، پیغام “Query Executed Successfully” که بیانگر اجرای موفقیت آمیز دستور بر روی دیتابیس می‌باشد، نمایش داده شود.

بکاپ گیری Differential backup به روش T-SQL

1. مانند قبل، وارد کنسول SQL Server Management Studio شوید و روی گزینه New Query در بالای صفحه کلیک کنید.

2. دستور زیر را وارد کرده و روی گزینه Execute کلیک کنید
(دستور زیر دیتابیس BinaDB را در پوشه :c با نام BinaDB-Backup-Test.DIF به روش Differential Backup ذخیره می‌کند).

BACKUP DATABASE BinaDB TO DISK = ‘C:BinaDB-Backup-Test.DIF’ WITH DFFERENTIAL
GO

بکاپ گیری File Backup به روش T-SQL

در رابطه با ارائه توضیحات در خصوص این روش بکاپ ‌گیری، برای مثال دیتابیسی با نام BinaDB ایجاد کرده‌ایم که این دیتابیس دارای دو Data File با نام‌های “BinaDB” و “BinaDB2” و یک Log File است.
مراحل زیر نشان می‌دهد چگونه می‌توان از هر File به صورت جداگانه بکاپ تهیه کرد.

BACKUP DATABASE BinaDB FILE = ‘BinaDB1’
TO DISK = ‘C:BinaDB_BinaDB1.FIL’
GO
BACKUP DATABASE BinaDB FILE = ‘BinaDB2’
TO DISK = ‘C:BinaDB_BinaDB2.FIL’
GO

بکاپ گیری FileGroup Backup به روش T-SQL

1. وارد کنسول SQL Server Management Studio شوید و روی گزینه New Query در بالای صفحه کلیک کنید.

2. دستور زیر را وارد کرده و روی گزینه Execute کلیک کنید (دستور زیر FileGroup موجود با نام ReadOnly را از دیتابیس BinaDB در پوشه :C را با نام BinaDB_ReadOnly.FLG به روش FileGroup Backup ذخیره می‌کند).

BACKUP DATABASE BinaDB FILEGROUP = ‘ReadOnly’
TO DISK = ‘C:BinaDB_ReadOnly.FLG’
GO

بکاپ گیری Transaction Log Backup به روش T-SQL

1. وارد کنسول SQL Server Management Studio شوید و روی گزینه New Query در بالای صفحه کلیک کنید.

2. دستور زیر را وارد کرده و روی گزینه Execute کلیک کنید

(دستور زیر Transaction Logهای دیتابیس BinaDB را در پوشه :C با نام BinaDB-TRNBackup-Test.TRN ذخیره می‌کند).

BACKUP LOG BinaDB TO DISK = ‘C:BinaDB-TRNBackup-Test.TRN’
GO

بکاپ گیری Partial Backup

به روش T-SQL

در رابطه با ارائه توضیحات در خصوص این روش بکاپ‌ گیری، برای مثال دیتابیسی با نام BinaDB ساخته ایم که سه Data File و یک Log File دارد. دو تا از Data Fileها به صورت Primary FileGroup و یکی از آنها به صورت Read-Only FileGroup می‌باشد. دستور زیر نشان می‌دهد چگونه می‌توان یک بکاپ جزئی ایجاد کرد.

ایجاد Full Partial Backup

همانند روش‌های قبل، بر روی New Query کلیک کنید و پس از وارد کردن دستورات زیر بر روی Execute کلیک کنید.

BACKUP DATABASE BinaDB READ_WRITE_FILEGROUPS
TO DISK = ‘C:BinaDB_Partial.BAK’
GO

ایجاد Differential partial Backup

در این روش نیز مانند روش‌های قبل، بر روی New Query کلیک کنید و پس از وارد کردن دستورات زیر بر روی Execute کلیک کنید.

BACKUP DATABASE BinaDB READ_WRITE_FILEGROUPS
TO DISK = ‘C:BinaDB_Partial.DIF’
WITH DIFFERENTIAL
GO

بکاپ ‌گیری از SQL به صورت خودکار (Maintenance Plan)

1. برای استفاده از این روش بکاپ‌ گیری می‌بایست سرویس SQL Server Agent در حالت Running قرار داشته باشد. لذا برای بررسی اینکه سرویس مذکور در حالت Running قرار دارد یا خیر، هم می‌توان وارد کنسول Services.msc شد و وضعیت سرویس را بررسی کرد (در صورتی که سرویس Stop بود آن را Start کنید) و هم می توانید از طریق کنسول SQL Server Management Studio پس از اتصال به Instance موردنظر، در منوی Object Explorer مشاهده کرد که آیا سرویس فعال و در حال اجرا است یا خیر؟

2. پس از اینکه اطمینان حاصل کردید سرویس SQL Server Agent در حال اجرا می‌باشد، از منوی Object Explorer گزینه Management را انتخاب و در زیرمجموعه آن گزینه Maintenance Plan را انتخاب کنید و با کلیک راست بر روی آن وارد Maintenance Plan Wizard شوید.

3. هنگامی که پنجره Pop-Up مربوط به Maintenance Plan Wizard باز شد، در اولین صفحه، گزینه Next انتخاب می‌کنیم.

4. در صفحه بعد با انتخاب نام Plan و ارائه توضیحات در قسمت Description، گزینه Change را جهت انجام تنظیمات Backup Plan انتخاب کنید.

5. تنظیمات موردنظر را (شامل تنظیمات زمانی و نوع Plan و شرایط وقوع آن) جهت ایجاد برنامه بکاپ وارد کنید.

6. در مرحله بعد، نوع عملیات Full یا Differential یا Transaction Log را انتخاب کنید.

7. پس از آن از قسمت (Database(s، دیتابیس (های) موردنظر جهت اجرای Plan را انتخاب کنید و محل ذخیره فایل‌های Backup را مشخص کنید.

8. در نهایت تمامی تنظیمات انجام شده را مشاهده خواهید کرد که با فشردن دکمه Finish عملیات نهایی شده و Plan موردنظر ایجاد می‌گردد.

روش بازگردانی (Restore) اطلاعات یا ریستور دیتابیس

جهت بازگردانی اطلاعات از یک Database که قبلا از آن Backup تهیه شده است به روش زیر عمل کنید:

1. پس از اتصال به کنسول SQL Server Management Studio روی دیتابیس کلیک راست کرده
و از منوی Task گزینه Restore و سپس Databases را انتخاب کنید.

2. از قسمت Source For Restore گزینه From Device را انتخاب کرده و فایل Backupی که پیش‌تر تهیه شده است را انتخاب کنیم.

3. جهت جایگزین (Replace) شدن Database از منوی Options گزینه Overwrite the existing database را انتخاب کنید.

لازم به ذکر است در نسخه SQL Server 2014 و بالاتر، جهت مشخص کردن محل ذخیره‌ی جدید برای فایل‌های دیتابیس، از تب Files بر روی گزینه Relocate all files to folder کلیک کنید و محل ذخیره‌ای جدید برای Data File Folderها و Log File Folderها تعیین کنید.

نکات قابل توجه در Backup SQL server

پیشنهاد می‌شود به دلیل حجم بالای تراکنش‌ها در SQL Server، زمان انجام عملیات بکاپ خودکار، خارج از ساعات کاری باشد، برای مثال ساعات پایانی روز.

پیشنهاد می‌شود فایل‌های بکاپ بر روی سرور و یا دیسک دیگری ذخیره شود.
تا در صورت بروز هرگونه اشکال در سرور SQL، فایل‌های بکاپ از بین نرود.

جهت انجام عملیات بکاپ ‌گیری، یوزر می‌بایست دسترسی Sysadmin را به عنوان Server Roles و دسترسی‌های db_owner و db_backupoperator را به عنوان Database Roles داشته باشد. بدین منظور در نرم‌افزار SQL Server Management Studio پس از اتصال به Instance به مسیر Security و سپس Login رفته و با کلیک راست بر روی یوزر موردنظر گزینه Properties را انتخاب مکنید و تنظیمات را مطابق زیر اعمال نمایید.