بابک رستمی

آموزش گام به گام و جامع بکاپ گیری از SQL Server بصورت تصویری

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

دوره های شبکه، برنامه نویسی، مجازی سازی، امنیت، نفوذ و ... با برترین های ایران
برای مشاهده تخفیف های ویژه امروز کلیک کنید
سرفصل های این مطلب
  1. مدل های بازیابی SQL Server
  2. مدل بازیابی کامل در پایگاه داده (SQL Server Full Recovery Model)
  3. توضیح
  4. مدل بازیابی ساده در SQL Server
  5. شرح
  6. چگونه می توان این مدل بازیابی ساده را در T-SQL تنظیم نماییم
  7. چگونه می توان این مورد را در SQL Server Management Studio تنظیم نمود
  8. مدل بازیابی Bulk-Logged (گزارش گیری دسته ای) درSQL Server
  9. توضیح
  10. نحوه تنظیم bulk-logged recovery model با استفاده از T-Sql
  11. نحوه تنظیم در SQL Server Management studio
  12. انواع پشتیبان گیری ها در SQL Server
  13. 1. Full backups چیست؟
  14. T-SQL
  15. SQL Server Management Studio
  16. 2. Transaction Log Backups چیست؟
  17. T-SQL
  18. SQL Server Management Studio
  1. انتخاب نمودن یک مدل بازیابی صحیح
  2. چه گزینه هایی برای پشتیبانی گرفتن در دسترس می باشد.
  3. چگونه با محیط دستوری T-SQL و SQL Server Management Studio می توان پشتیبانی گرفت.

اگر شما در SQL Server تازه کار هستید شما باید یک مروری بر هر یک از این موارد داشته باشید تا با گزینه های موجود و مراحل مورد نیاز برای بازگرداندن اطلاعات آگاه شوید.

مدل های بازیابی SQL Server

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

  1. Full
  2. Simple
  3. Bulk-logged

هر پایگاه داده می تواند تنها یک مدل بازیابی را داشته باشد، اما هر کدام از پایگاه داده های شما می توانند مدل بازیابی متفاوتی داشته باشند، که این هم بستگی به پردازش ها و نیازهای پشتیبانی و بازیابی مد نظر شما می باشد. تنها استثنایی که وجود داره اینکه پایگاه داده TempDB تنها از مدل بازیابی Simple استفاده می کند.همچنین، مدل بازیابی پایگاه داده هر زمان می تونه تغییر کند، اما این کار زنجیره پشتیبان گیری شما را تحت تاثیر قرار می دهد، پس این یک تمرین خوب هست که یک پشتیبانی گیری کامل بعد از تغییر مدل بازیابی خود صادر کنید.مدل بازیابی می تواند با استفاده از T-SQL یا SQL Management Studio تغییر کند. به مثال زیر توجه فرمایید.استفاده از T-SQL برای تغییر یک بازیابی به حالت کامل برای پایگاه داده AdventureWorks بدین صورت می باشد.

وب سایت توسینسو

و استفاده از SSMS برای تغییر مدل بازیابی برای پایگاه داده AdventureWorks

وب سایت توسینسو

مدل بازیابی کامل در پایگاه داده (SQL Server Full Recovery Model)

مدل بازیابی “Full” به SQL Server فرمان می ده که تمام داده تراکنش را داخل گزارش تراکنش نگه دارد تا زمانی که گزارش تراکنش پشتیبانی دیگری رخ دهد یا گزارش تراکنش تخلیه شود. راهی که این عملیات را انجام می دهد بدین صورت می باشد که تمام تراکنش هایی که که قرار هست در SQL Server انجام در ابتدا در گزارش تراکنش وارد می شوند بعد داده در فایل اطلاعاتی مورد نظر قرار می گیرد. این کار کمک به SQL Server می کند تا در هر مرحله از این فرایند که با خطا یا لغو تراکنش یا هر دلیل دیگری رو به رو شد بتواند به حالت قبل از اعمال تراکنش برگردد. بنابراین در مدل بازیابی "Full" تا زمانی که تمام تراکنش ها توسط کاربر ذخیره نشوند امکان این موجود دارد که به زمان درست قبل از اعمال تراکنش در پایگاه داده برگشت.

توضیح

مدل بازیابی کامل (full recovery model) یک مدل بازیابی کامل می باشد که به شما اجازه بازیابی همه ی داده هایتان به هر نقطه در زمان، تا وقتی که فایل های پشتیبانی قابل استفاده باشند را می دهد. با این مدل همه ی عملیات ها به طور کامل گزارش می شوند به عبارتی شما می توانید پایگاه داده خود را به هر نقطه ای بازیابی کنید. علاوه بر این، اگر پایگاه داده بر مدل بازیابی کامل تنظیم شده باشد آنگاه شما باید به مسئله پشتیبان گیری ها از گزارشات تراکنش رسیدگی کنید در غیر این صورت گزارش تراکنش تا ابد رشد خواهد کرد.در زیر بعضی از دلایل که ممکن است شما از این نوع مدل بازیابی استفاده کنید را بیان می کنیم:

  1. داده حساس هست و نباید از دست برود.
  2. باید این توانایی وجود داشته باشه که در هر لحظه از زمان بتوان بازیابی نمود.
  3. شما از database mirroring استفاده می کنید.( database mirroring جدیدا دیگر در SQL Server استفاده نمی شود و منسوخ شده است )

انواع پشتیبانی هایی که شما می توانید وقتی داده داخل مدل بازیابی کامل هست اجرا کنید:

  1. Complete backups
  2. Differential backups
  3. File and/or FileGroup backups
  4. Partial backups
  5. Copy-only backups
  6. Transaction log backups

اما نحوه ی درج مدل بازیابی کامل با استفاده از T-Sql:

ALTER DATABASE dbName SET RECOVERY recoveryOption
GO

مثال: تغییر پایگاه داده AdventureWorks به مدل بازیابی کامل

ALTER DATABASE AdventureWorks SET RECOVERY FULL
GO

و حالا نحوه انجام همین تنظیم در SQL Server Management Studio

  1. بر روی نام پایگاه داده خود راست کلیک کرده و بر روی عبارت Properties کلیک نمایید.
  2. به صفحه Options رفته.
  3. در مقابل عبارت Recovery model روی عبارت Full کلیک نمایید تا انتخاب شود.
  4. بر روی دکمه OK کلیک کنید تا ذخیره شود.
SQL Server Full Recovery

مدل بازیابی ساده در SQL Server

مدل بازیابی “Simple” به شما امکان یک پشتیبان گیری ساده که می تواند برای جایگذین کردن تمام پایگاه داده شما در مواقع بروز شکست و یا اگر شما نیاز به بازگرداندن پایگاه داده خود در یک سرور دیگر باشید استفاده شود. با این مدل بازیابی شما توانایی انجام پشتیبان گیری های کامل (یک کپی کامل) یا پشتیبانگیری های تفاضلی یا همان differential backups (هر تغییری از آخرین پشتیبان گیری کامل) را دارید. با استفاده از این مدل بازیابی در صورت هر گونه شکست به آخرین پشتیبانی کامل گرفته شده بر می گردد.

شرح

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

  1. داده شما حیاتی نمی باشد و به آسانی دوباره تولید می شود.
  2. پایگاه داده فقط برای تست یا توسعه استفاده می شود.
  3. داده ثابت هست و تغییر نمی کند.
  4. از دست رفتن تراکنش ها تا اخرین پشتیبان گرفته شده مشکلی ایجاد نکند.
  5. داده از جای دیگری مشتق شده و به راحتی می تواد دوباره آن را تولید کرد.

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

  1. Complete backups
  2. Differential backups
  3. File and/or Filegroup backups
  4. Partial backups
  5. Copy-Only backups

چگونه می توان این مدل بازیابی ساده را در T-SQL تنظیم نماییم

ALTER DATABASE dbName SET RECOVERY recoveryOption
GO

مثال: تغییر پایگاه داده AdventureWorks به مدل بازیابی ساده.

ALTER DATABASE AdventureWorks SET RECOVERY SIMPLE
GO

چگونه می توان این مورد را در SQL Server Management Studio تنظیم نمود

  1. بر روی پایگاه داده خود کلیک راست کنید و بر رو به گزینه Properties کلیک نمایید.
  2. در صفحه Options وارد می شوید.
  3. در مقابل عبارت Recovery Model عبارت Simple را انتخاب می کنید.
  4. در نهایت بر روی OK کلیک نمایید.


SQL Server Management Studio

مدل بازیابی Bulk-Logged (گزارش گیری دسته ای) درSQL Server

مدل بازیابی Bulk-logged دقیقا کاری رو انجام می ده که در اسمش اومده. در این مدل برخی عملیات های دسته ای مثلا BULK INSERT, CREATE INDEX, SELECT INTO و غیره را انجام می دهد و با این تفاوت که یک گزارش گیری کامل (fully logged) انجام نمی شود و این امر سبب می شود که فضای کمتری در گزارش تراکنش ایجاد شود.

توضیح

فایده استفاده از بازیابی "Bulk-logged" این می باشد که اگر از عملیات های دسته ای انجام دهید گزارش های تراکنش شما حجم زیادی ایجاد نمی کند. اگر هیچ عملیات دسته ای در این بازیابی اجرا نشود این مدل ریکاوری همانند مدل ریکوری کامل کار خواهد کرد. یک نکته ی دیگری هم که وجود داره اینکه اگر شما از این مدل برای بازیابی استفاده کنید شما باید از گزارش تراکنش، پشتیبان هایی بگیرید در غیر این صورت گزارش تراکنش مدام بزرگ بزرگتر خواهد شد.در زیر دلایل استفاده از این نوع مدل بازیابی را بیان کردیم:

  1. داده حساس می باشد، اما شما نمی خواهید عملیات دسته ای بزرگ را گزارش گیری کنید.
  2. عملیات های دسته ای در زمان های مختلف بر خلاف فرایند معمول انجام شود.
  3. شما هنوز قادر به بازیابی به موقع به یک نقطه را داشته باشید.

نوع پشتیبانی که شما می توانید در مدل بازیابی Bulk-logged انجام دهید:

  1. Complete backups
  2. Differential backups
  3. File and/or Filegroup backups
  4. Partial backups
  5. Copy-Only backups
  6. Transaction log backups

نحوه تنظیم bulk-logged recovery model با استفاده از T-Sql

ALTER DATABASE dbName SET RECOVERY recoveryOption
GO

مثال از این مدل در پایگاه داده AdventureWorks بدین صورت می باشد.

ALTER DATABASE AdventureWorks SET RECOVERY BULK_LOGGED
GO

نحوه تنظیم در SQL Server Management studio

  1. بر روی پایگاه داده خود کلیک راست کنید و بر رو به گزینه Properties کلیک نمایید.
  2. در صفحه Options وارد می شوید.
  3. در مقابل عبارت Recovery Model عبارت Bulk-logged را انتخاب می کنید.
  4. در نهایت بر روی OK کلیک نمایید.
SQL Server Management studio

انواع پشتیبان گیری ها در SQL Server

SQL Server گزینه های مختلفی برای پشتیبانگیری در اختیار ما قرار می دهد. در مباحث قبلی به موضوع مدل های بازیابی پرداختیم. بیان کردیم که چه نوع از پشتیباگیری ها بر پایه مدل بازیابی پایگاه داده می توانند انجام شوند. در این بخش و بخش های بعدی در مورد هر کدام از این گزینه های پشتیبانی و نحوه ی اعمال انها با استفاده از T-SQL و SSMS خواهیم پرداخت. انواع مختلف پشتیبانی که شما در اختیار داری عبارتند از :

  1. Full backups
  2. Differential backups
  3. File backups
  4. Filegroup backups
  5. Partial backups
  6. Copy-Only backups
  7. Mirror backups
  8. Transaction log backups

1. Full backups چیست؟

یک نوع مرسوم پشتیبان گیری ، پشتیبانگیری کامل یا full backups می باشد. این نوع پشتیبانگیری یک پشتیبانگیری کامل از تمام پایگاه داده شما و هچنین گزارش تراکنش ها می باشد، پس پایگاه داده قابل بازیابی می باشد. از این لحاظ که این پشتیبان گیری شامل تمام داده های ما می باشد،پس اجازه می دهد که به ساده ترین حالت پایگاه داده را ترمیم نماییم. برای انجام این پشتیبان گیری می توان از T-SQL یا SSMS استفاده کرد. به مثال زیر توجه نمایید:

T-SQL

BACKUP DATABASE AdventureWorks TO DISK = 'C:\AdventureWorks.BAK'
GO

SQL Server Management Studio

  1. بر روی پایگاه داده خود راست کلیک نمایید.
  2. در بخش Task به قسمت BackUp کلیک می کنیم.
  3. در پنجره باز شده در بخش Destination بر روی Disk تنظیم می کنیم.
  4. و بر روی دکمه Add کلیک می کنیم تا مسیر فایل پشتیبان را قرار دهیم مثلا "C:\AdventureWorks.BAK" و بعد Ok می نماییم.
  5. در پایان دوباره OK می کنیم تا فایل پشتیبان ایجاد شود.
Full backups

2. Transaction Log Backups چیست؟

اگر پایگاه داده شما به صورت بازیابی Full یا Bulked-logged باشد آنگاه می توانید از پشتیبانی Transaction Log استفاده کنید. با استفاده از این نوع پشتیبانی شما قادر هستید در هر زمان بازیابی داشته باشید، پس اگر به صورت تصادفی اطلاعات داخل پایگاه داده را حذف کردید می توانید پایگاه داده را از لحظه قبل از این اتفاق برگردانید. فقط یک نکته را مراقب باشید اگر پایگاه داده شما به صورت مدل بازیابی Bulk-logged تنظیم شده باشد و یک bulk operation اتفاق افتاده باشد، انگاه شما نیاز دارید که تمام گزارش تراکنش را بازیابی نمایید.

یک پشتیبان transaction log به شما اجازه می دهد که از بخش فعال در transaction log پشتیبان بگیرید. پس بعد از یک پشتیبان گیری Full یا Differential موفق، transaction log backup هر تراکنش بعدی را پشتیبان گیری می کند. بعد از اینکه transaction log backup به نتیجه رسید، انگاه فضای داخل transaction log برای پردازش های بعدی دوباره قابل استفاده می باشد. یک transaction log backup می تواند توسط T-SQL یا SSMS اعمال شود. به مثال زیر به منظور ایجاد transaction log backup دقت نمایید.ساخت یک transaction log backup درپایگاه داده AdventureWorks به صورت زیر می باشد

T-SQL

BACKUP LOG AdventureWorks TO DISK = 'C:\AdventureWorks.TRN'
GO

SQL Server Management Studio

  1. کلیک راست روی نام پایگاه داده مد نظر شما
  2. در بخش Tasks > Backup
  3. نوع پشتیبانی Transaction Log را انتخاب کنید.
  4. بر روی دکمه Add کلیک نمایید و مسیر فایل پشتیبانی را تعیین نمایید مثلا C:\AdventureWorks.TRN و بعد بر روی Ok کلیک نمایید.
  5. در انتها بار دیگر بر روی OK کلک نمایید تا فایل پشتیبان ایجاد شود.
Transaction Log Backups
0 3
بابک رستمی
بابک رستمی

بابک رستمی. کارشناس ارشد کامپیوتر نرم افزار. C# Programming، Asp.Net، SQL Server, Web Designning And Web Programmer

نظرات