SQL Server یک سیستم مدیریت پایگاه داده رابطهای است که به طور گستردهای استفاده میشود. این سیستم برای ذخیره و بازیابی دادهها در بسیاری از سازمانهای بزرگ استفاده میشود. مایکروسافت SQL Server توصیه میکند که کاربران به طور دورهای از پایگاه داده پشتیبانگیری کنند تا از از دست رفتن یا فساد دادهها جلوگیری کنند. در این مقاله، فرآیند کامل پشتیبانگیری و بازیابی پایگاه داده در SQL Server 2017، ۲۰۱۶، ۲۰۱۴، ۲۰۱۲، ۲۰۰۸ و نسخههای دیگر را توضیح خواهیم داد.
MS SQL Server از سه فایل پشتیبانی میکند: فایل پایگاه داده اولیه (MDF)، فایل پایگاه داده ثانویه (NDF)، و فایل لاگ (LDF). در این راهنما، به صورت گامبهگام نحوه پشتیبانگیری و بازیابی پایگاه داده در SQL Server را با استفاده از SSMS و دستورات Transact-SQL نشان خواهیم داد. با دنبال کردن این راهنما، میتوانید به راحتی نحوه پشتیبانگیری از پایگاه داده در SQL Server را یاد بگیرید.
انواع پشتیبانگیری در SQL
به طور کلی، در SQL Server سه نوع پشتیبانگیری وجود دارد. به طور واضح، بهتر است که کاربران هر یک از این انواع را بررسی کنند تا بتوانند پایگاه داده SQL Server را به راحتی پشتیبانگیری و بازیابی کنند. این انواع عبارتند از:
پشتیبانگیری کامل (Full Backup): این نوع پشتیبانگیری شامل تمامی اطلاعات موجود در یک پایگاه داده است. هر جزئیات از ابتدا تا انتها در این پشتیبان ذخیره میشود. این نوع پشتیبانگیری نیازی به پشتیبانگیریهای قبلی ندارد و به طور معمول زمان زیادی میبرد زیرا همه چیز از ابتدا پشتیبانگیری میشود.
پشتیبانگیری تفاضلی (Differential Backup): همانطور که از نامش پیداست، این نوع پشتیبانگیری دادههایی را ذخیره میکند که تفاوت بین آخرین پشتیبانگیری و پایگاه داده فعلی است. با این حال، برای اعمال این نوع پشتیبانگیری، نیاز به یک پشتیبانگیری کامل از قبل دارید. این روش معمولاً سریعتر است و فضای کمتری را مصرف میکند، زیرا تنها به پر کردن شکافها میپردازد.
پشتیبانگیری از لاگ تراکنش (Transaction Log Backup): پشتیبانگیری از لاگ تراکنش به طور معمول تمام دادههای تراکنش را ذخیره میکند. میتوان گفت که این نوع پشتیبانگیری شامل فایلهای LDF است. اگرچه این فایلها پایگاه داده اصلی نیستند، اما همچنان بخش مهمی از پشتیبانگیری SQL به شمار میآیند.
نیازها و مزایای عملیات پشتیبانگیری و بازیابی پایگاه داده SQL Server
عملیات پشتیبانگیری و بازیابی در SQL Server اهمیت زیادی دارد و این فرآیندهای پیچیده برای کاربران ضروری هستند. قبل از پرداختن به مراحل این عملیات، بهتر است نگاهی به اهمیت آن داشته باشیم.
پشتیبانگیری از پایگاه داده به کاربران در موارد مختلفی کمک میکند، از جمله:
مدیریت بحران
خرابی سختافزار
فساد دادهها
مهاجرت دادهها
حمله ویروسی
علاوه بر نیازها، کاربران باید از مزایای این فرآیند نیز مطلع باشند. در ابتدا، پشتیبانگیری از پایگاه داده میتواند کاربران را در برابر تمام تهدیدات احتمالی محافظت کند. علاوه بر این، ایجاد پشتیبان همیشه بهترین روش برای بازیابی دادهها در صورت بروز مشکلات فساد دادهها است. همانطور که پیشتر ذکر شد، SQL Server همیشه توصیه میکند که کاربران پایگاه داده خود را پشتیبانگیری کنند و دو روش برای این کار توسط مایکروسافت ارائه شده است: اول، استفاده از SQL Server Management Studio (SSMS) و دوم، دستور T-SQL. اگر شما پشتیبان پایگاه داده خود را دارید، میتوانید به راحتی فایل .bak را در SQL Server بازیابی کنید. در کل، پشتیبانگیری و بازیابی بهترین گزینه برای محافظت از دادهها در برابر آسیبها است.
چگونه از پایگاه داده Microsoft SQL Server پشتیبانگیری کنیم؟
در این بخش، دو روش برای پشتیبانگیری از پایگاه داده SQL با استفاده از SSMS و T-SQL را توضیح خواهیم داد. مراحل زیر را دنبال کنید:
پشتیبانگیری از پایگاه داده SQL با استفاده از SQL Server Management Studio (SSMS)
مرحله ۱: SSMS را باز کرده و به SQL Server متصل شوید.
مرحله ۲: بخش Databases را گسترش دهید و پایگاه داده مورد نظر خود را انتخاب کنید.
مرحله ۳: روی پایگاه داده راستکلیک کرده و گزینههای Tasks و سپس Backup را انتخاب کنید.
مرحله ۴: در پنجره Back Up Database، نوع پشتیبانگیری (Backup Type) را روی Full (کامل) تنظیم کنید و در بخش Destination (مقصد)، گزینه Back up to: Disk (پشتیبانگیری روی دیسک) را انتخاب نمایید.
مرحله ۵: روی دکمه Remove (حذف) کلیک کنید.
مرحله ۶: روی دکمه Add (افزودن) کلیک کنید تا مقصد و نام فایل پشتیبان پایگاه داده را انتخاب نمایید.
مرحله ۷: پوشه مورد نظر برای ذخیره فایل پشتیبان را انتخاب کرده و نام فایل را با پسوند .bak وارد کنید.
مرحله ۸: برای پایان فرآیند پشتیبانگیری، روی OK کلیک کنید.
چگونه میتوان بهراحتی زمانبندی پشتیبانگیری از SQL Server را انجام داد؟
اغلب با شرایطی روبرو میشویم که نیاز به گرفتن پشتیبان داریم، اما در زمان مناسب در دسترس نیستیم. بنابراین، در چنین مواقعی، میتوانیم پشتیبانگیری را زمانبندی کنیم تا به صورت خودکار در زمان تعیینشده اجرا شود.
بله، امکان اجرای خودکار فرآیند پشتیبانگیری و بازیابی در SQL Server از طریق زمانبندی وجود دارد. در ادامه، مراحل زمانبندی را بررسی میکنیم:
مرحله ۱:
SSMS را باز کنید >> به منوی Script بروید >> گزینه Script Action to Job را انتخاب کنید.
مرحله ۲: در بخش Job جدید، به زبانه Schedule (زمانبندی) بروید و روی New کلیک کنید.
مرحله ۳: در این مرحله، یک Job برای پشتیبانگیری مطابق نیاز کاربر ایجاد کنید.
مرحله ۴: حالا با استفاده از دستور زیر، این Job را زمانبندی کنید:
USE msdb;
GO
EXEC msdb.dbo.sp_add_jobschedule
@job_name = ‘BackupDatabaseJob’,
@name = ‘BackupDatabaseSchedule’,
@freq_type = 4, — اجرای روزانه
@freq_interval = 1, — هر روز
@active_start_time = 0; — زمان شروع (نیمهشب)
GO
مرحله ۵: آخرین مرحله این است که Job زمانبندیشده را فعال کنید تا در زمان تعیینشده اجرا شود. دستور فعالسازی Job به شکل زیر است:
USE msdb;
GO
EXEC msdb.dbo.sp_update_job
@job_name = ‘BackupDatabaseJob’,
@enabled = 1;
GO
پشتیبانگیری از پایگاه داده SQL Server با Transact-SQL
ایجاد پشتیبان کامل (Full Backup) روی دیسک:
BACKUP DATABASE databasename
TO DISK = ‘filepath’
GO
ایجاد پشتیبان کامل در چند فایل دیسک مختلف:
BACKUP DATABASE AdventureWorks
TO DISK = ‘C:\AdventureWorks_1.BAK’,
DISK = ‘D:\AdventureWorks_2.BAK’,
DISK = ‘E:\AdventureWorks_3.BAK’
GO
این فرآیند کامل برای تهیه نسخه پشتیبان از پایگاه داده در SQL Server است. حال، بیایید فرآیند بازیابی فایل .bak در SQL Server را بررسی کنیم تا یاد بگیرید چگونه بهراحتی پشتیبان تهیه و بازیابی کنید.
چگونه بهجای Full Backup، یک Differential Backup ایجاد کنیم؟
اکنون ایجاد پشتیبان تفاضلی (Differential Backup) بهجای پشتیبان کامل بسیار ساده است. همچنین، اگر کاربران یک نسخه پشتیبان کامل داشته باشند اما آن بهروزرسانی نشده باشد، میتوانند از پشتیبان تفاضلی استفاده کنند. روند آن تقریباً مشابه پشتیبان کامل است با یک تغییر جزئی.
برای تهیه پشتیبان تفاضلی در SQL Server مراحل زیر را دنبال کنید:
مرحله ۱: مطمئن شوید که یک پشتیبان کامل از پایگاه داده موجود است.
مرحله ۲: روی پایگاه داده راستکلیک کنید >> گزینه Tasks >> سپس Back Up
مرحله ۳: در بخش Backup Type، بهجای گزینه Full، گزینه Differential را انتخاب کنید (طبق تصویر).
پشتیبانگیری و بازیابی SQL Server با SSMS با استفاده از فایل BAK
با استفاده از SSMS و دستور T-SQL، میتوانیم بهراحتی فایل پشتیبان با فرمت .bak را بازیابی کنیم. برای اینکه بدانید چگونه پایگاه داده SQL را از فایل پشتیبان بازیابی کنید، مراحل زیر را دنبال کنید:
مرحله ۱: SSMS (SQL Server Management Studio) را باز کرده و به پایگاه داده خود متصل شوید.
مرحله ۲: پایگاه داده مورد نظر را انتخاب کرده و راستکلیک کنید >> گزینه Tasks >> سپس Restore >> و بعد Database را انتخاب نمایید.
مرحله ۳: در پنجره Restore Database، در بخش Source for restore گزینه From device را انتخاب کنید و سپس روی دکمه Browse (…) کلیک کنید.
مرحله ۴: پنجره Specify Backup باز خواهد شد. در این پنجره، Backup media را روی File تنظیم کرده و برای ادامه روند پشتیبانگیری از پایگاه داده در SQL Server، روی دکمه Add کلیک کنید.
مرحله ۵: فایل پشتیبان موردنظر برای بازیابی را انتخاب کرده و روی OK کلیک کنید.
مرحله ۶: فایل .bak در پنجرهی “Restore Database” فهرست خواهد شد. روی OK کلیک کنید.
مرحله ۷: حالا از سمت چپ پنجره روی Options کلیک کرده، گزینههای موردنظر برای بازیابی و وضعیت بازیابی (Recovery State) را انتخاب کنید.
مرحله ۸. در پایان، روی OK کلیک کنید.
چگونه پایگاهداده را در SQL Server از طریق CMD بازیابی کنیم؟
حال که فایل پشتیبان را در پایگاهداده SQL خود داریم، زمان آن رسیده که به سراغ فرآیند بازیابی برویم. برای یادگیری نحوه بازیابی پایگاهداده در SQL Server از طریق CMD، دو گزینه پیشرو داریم: میتوان بازیابی کامل (Full Recovery) یا حالت بدون بازیابی (NORECOVERY) را انتخاب کرد. در ادامه هر دو را بررسی میکنیم.
بازیابی کامل پایگاهداده SQL
RESTORE DATABASE databasename
FROM DISK = ‘filepath’
GO
نحوه بازیابی پایگاهداده SQL با استفاده از NORECOVERY
RESTORE DATABASE databasename
FROM DISK = ‘filepath’ WITH NORECOVERY
GO
فرآیند پشتیبانگیری و بازیابی فایل .bak در SQL Server اکنون کامل شده است. حالا مشخص شد که چگونه میتوان فایل پشتیبان را ایجاد و آن را بازیابی کرد. گاهی اوقات، فایل پشتیبان SQL به دلیل حمله ویروسی، خاموش شدن ناگهانی سیستم و دلایل دیگر خراب میشود.
در چنین شرایطی، کاربران نمیتوانند با استفاده از فایل .bak، پایگاهداده را بازیابی کنند، زیرا ابزار داخلی برای این کار در SQL Server وجود ندارد. در بخش بعدی، راهحل تعمیر فایل پشتیبان خراب SQL را بررسی میکنیم تا فایل به راحتی قابل بازیابی باشد.
اجرای خودکار وظیفه پشتیبانگیری در SQL Server
همانطور که گفته شد، راهحل دستی برای بازیابی فایل پشتیبان خراب وجود ندارد. بنابراین برای تعمیر فایل .bak آسیبدیده، استفاده از نرمافزار SQL Backup Recovery بهترین راهحل است. این ابزار میتواند فایلهای پشتیبان با آسیبدیدگی بالا را بههمراه تمام اجزای پایگاهداده مانند جداول، ستونها، تریگرها و … تعمیر و بازیابی کند.
این ابزار امکان بازیابی و استخراج چندین فایل .bak را بهطور همزمان فراهم میکند. پس از بازیابی فایل پشتیبان آسیبدیده، میتوانید آن را به پایگاهداده SQL Server صادر کنید.
نکته: اگر فایلهای MDF و NDF شما خراب شدهاند و پشتیبانگیری ندارید، استفاده از ابزار SQL Recovery توصیه میشود تا بدون نیاز به فایل پشتیبان، پایگاهداده را بازیابی کنید.
نکات مهم برای بهبود فرآیند پشتیبانگیری و بازیابی در SQL Server
راهکارهای پیشرفته:
فشردهسازی پشتیبانها – با فشردهسازی فایلهای پشتیبان، فضای ذخیرهسازی را کاهش داده و مدیریت پایگاهداده را سادهتر کنید.
رمزگذاری پشتیبانها – برای حفاظت از دادههای حساس، رمزگذاری پشتیبانها الزامی است.
بازیابی در سرور دیگر – همیشه برنامه بازیابی فاجعه داشته باشید تا بتوانید پایگاهداده را در سروری دیگر بازیابی کنید.
بازیابی در زمان مشخص (Point-in-Time Recovery) – با استفاده از پشتیبانگیری لاگ، امکان بازگرداندن پایگاهداده به لحظهای خاص را فراهم کنید.
عیبیابی خطاها:
شکست پشتیبانگیری – راهکارهایی برای رفع خطاهای رایج در فرآیند پشتیبانگیری آماده داشته باشید.
خطاهای بازیابی – خطاهایی مثل خرابی منطقی یا خطای رسانه را پیشبینی و راهحلهایی برای آنها تدوین کنید.
گلوگاههای عملکردی – تکنیکهای بهینهسازی برای جلوگیری از افت عملکرد در فرآیند پشتیبانگیری و بازیابی را در نظر بگیرید.
بهترین روشها:
آزمایش منظم – پشتیبانها را بهطور منظم آزمایش کنید تا مطمئن شوید در زمان نیاز کار میکنند.
سیاست نگهداری (Retention Policy) – با درنظر گرفتن فضای ذخیرهسازی، سیاستی برای مدت نگهداری پشتیبانها تعیین کنید.
امنیت اطلاعات – لیستی از اقدامات امنیتی مثل رمزگذاری، دیوار آتش و … تهیه کنید.
اتوماسیون و زمانبندی:
وظایف Agent در SQL – نحوه ایجاد و زمانبندی وظایف Agent را برای پشتیبانگیری خودکار یاد بگیرید.
اسکریپتنویسی PowerShell – استفاده از اسکریپتهای پاورشل برای خودکارسازی پشتیبانگیری و بازیابی دادهها را بررسی کنید.
برای پشتیبانگیری و بازیابی پایگاه داده در SQL Server، ابتدا باید یک نسخه پشتیبان از دادههای خود تهیه کنید تا در صورت بروز هر گونه خرابی یا از دست رفتن دادهها، بتوانید آنها را بازگردانی کنید. SQL Server این امکان را فراهم میکند که از پایگاه دادهها پشتیبانگیری کنید و در صورت نیاز، با استفاده از فایلهای پشتیبان، دادهها را بازیابی کنید.
فرآیند پشتیبانگیری معمولاً شامل چند نوع پشتیبان است؛ از جمله پشتیبانگیری کامل (Full Backup) که تمام دادهها را ذخیره میکند، پشتیبانگیری تفاضلی (Differential Backup) که فقط تغییرات از آخرین پشتیبان را ذخیره میکند و پشتیبانگیری از لاگ تراکنشها (Transaction Log Backup) که تمامی تراکنشها را ذخیره میکند.
برای بازیابی پایگاه داده نیز، از ابزارهایی مانند SQL Server Management Studio (SSMS) یا دستورات T-SQL استفاده میشود. شما میتوانید فایلهای پشتیبان را به راحتی بازگردانی کنید و در صورت نیاز به بازیابی مرحلهای، از لاگهای تراکنش برای بازیابی دادهها به نقطه خاصی استفاده کنید.
این فرآیند به شما کمک میکند تا از دادههای خود محافظت کرده و در صورت بروز مشکل، به راحتی آنها را بازگردانی کنید.
