SQL Server Administration

[Reza_Yarahmadi]

هدف از Backup گيري ، ايجاد امكان بازيابي دیتابیس در زمان خرابي و از دست دادن دیتابیس مي باشد. يكي از اهداف ديگر Backup گيري ، جابجا كردن دیتابیس مي باشد كه اين روش ، روش پرهزينه ايي براي انتقال دیتابیس مي باشد .در عمل Backup گيري عملي است كه در آن با استفاده از توابعي ويژه دیتابیس را به File تبديل مي كند. اين فايل با ساختاري كاملا منحصر به فرد و قابل شناسايي توسط SQL Server ايجاد مي شود.
در واقع Backup مي گيريم براي بازسازي دیتابیس وقتي كه

1. دیتابیس خراب شود.
2. سخت افزاري خراب شود.
3. اشكالاتي از بابت كار كاربران پيش مي آيد.

Backup تهيه شده بايستي قابل اتكا و حتي الامكان با كمترين هزينه براي كاربر تهيه شود لذا تحليل و تهيه يك برنامه مدون براي Backup گيري از ملزمات اين كاراست.

شناخت فاكتورهاي مهم براي Backup گيري مناسب
براي تهيه Backup هاي بهينه از دیتابیس ها شناخت فاكتورهاي اساسي در نحوه Backup گيري بسيار موثر است . اين فاكتورها عبارتند از :

1. نرخ بروز رساني اطلاعات در هر روز
2. سخت افزار لازم براي تهيه Backup
3. اهميت بازيابي اطلاعات از Database Recovery) Backup)

در واقع بايستي عمل Backup گيري در شرايطي انجام شود كه هزينه backup گيري تا حد ممكن كم شود.


Recovery Model
رفتار هاي مختلفي كه SQL Server در مورد نگهداري Log هاي دیتابیس اجرا مي كند را Recovery Model گوييم اين حالات عبارتند از :

1. Full
2. Simple
3. Bulk-logged

Full Recovery Model
در اين مدل تمامي تراكنشها در Log file ثبت ميشود .اين حالت باعث افزايش حجم Log File و در نتيجه كندي در ثبت اطلاعات را پيش مي آورد.ولي در اين حالت امكان بازيابي دیتابیس از طريق Log file مقدور مي باشد .


Simple Recovery Model
در اين مدل تمامي تراكنشهاي دیتابیس فقط براي حفظ Database Consistency ثبت مي شود و از نگهداري Log ها Historical خودداري مي شود .به عبارت ديگر تمامي تراكنشهاي Commit شده از Truncate ، Log file مي شوند. استفاده از Log در دیتابیس مهمترين عامل براي باز سازي دیتابیس در زمان خرابي آن به حساب مي آيد. لذا ضرورت استفاده از Log در SQL Server از ملزمات مي باشد .

نكته:

1. در زماني كه دیتابیس در Recovery model = Simple باشد . امكان تهيه Transaction Log Backup مقدور نمي باشد و با Backup گيري از آن اندازه آن به اندازي ابتدايي خود ميرسد (Shrink) مي شود.
2. براي حفظ امنيت دیتابیس بايستي از امكان Transaction Log Backup استفاده كرد. از اين رو استفاده از Recovery model = Full را پيشنهاد مي نماييم .

Bulk-logged :
در اين مدل تمامي تراكنش ها روي دیتابیس Log مي شود مگر آندسته از Logهايي كه از نتيجه كارهاي حجيم مانند Import Data و Index Creation ... توليد مي شود.
براي تغيير Recovery Model مي توان بصورت زير عمل كرد:

ضمیمه 69802

انواع Backup ها
انواع Backup هاي موجود در SQL Server به شرح زير است :

1. Full Backup
2. Deferential Backup
3. File \ File Group Backup
4. Transaction Log Backup

قبل از معرفي انواع Backup بايستي بدانيم كه هر دیتابیس شامل 2 دسته اطلاعات است. يكي اطلاعات كاربر (User Data) و ديگري اطلاعات مربوط به ساختار هاي دیتابیسSystem Data) .

Full Backup - Complete Backup
در اين نوع Backup از تمام اشياي دیتابیس و Data ها مي توانيد Backup بگيريد. اين Backup كاملترين نوع Backup بوده و با Restore آن تمامي اطلاعات دیتابیس بازسازي مي شود .
اين Backup همواره نسبت به زمان ايجاد دیتابیس ، ايجاد مي شود . در نتيجه اندازه اين نوع از Backup با افزايش حجم Database نيز زياد مي شود. استفاده از اين نوع Backup بسيار مطمئن مي باشد ولي بايستي طوري از آن استفاده شود تا اندازه فايل Backup دردسر ساز نشود.

Deferential Backup
دراين مدل Backup از تغييرات Data ها نسبت به آخرين Full Backup ، Backup تهيه مي شود. استفاده از اين مدل Backup از محدوديت هاي خاصي برخوردار است و بايستي براي حفظ امنيت اطلاعات طبق زمانبندي و بطور منظم Backup تهيه شود.به عنوان مثال
شنبه ساعت 8:00 يك Backup Full تهيه مي شود و در طي روز 3 Backup از نوع Deferential گرفته مي شود. اين مدل در طي هفته تكرار مي شود ولی Backup از نوع Full فقط هفته ايي يكبار گرفته مي شود. آخرين Deferential Backup روز 3 شنبه شامل تغييرات دیتابیس از آخرين Full Backup يعني Backup روز شنبه مي باشد..
اين Backup سريعتر و در اندازه كمتر گرفته مي شود و زمان بازيابي كمتري را صرف مي كند.

Transaction Log Backup
يكي از مهمترين انواع Backup براي نگهداري از دیتابیس هايي است كه بين 2 Full Backup تغييرات زيادي دارند.
در اين مدل Backup از تمامي Log Entry هاي غير فعال در فايل Log ، يك Backup تهيه مي شود. ارزش اين Backup در آن است كه در صورت خرابي دیتابیس مي توان دیتابیس را از روي آنها باز سازي كرد.

تعاريف پايه ايي براي Backup
براي تشريح موضوع بايستي ابتدا تعاريف زير را بيان نماييم :

Backup Set
مجموعه ايي از Backup ها روي يك يا چند Backup Media و يا تركيبي از چندين Backup Media را يك Backup Set گوييم.

Media Set
تركيبي از چندين Backup Media مانند Disk و Tape را گوييم.

Backup Media
سخت افزار هاي مورد استفاده در Backup گيري را گوييم.

Media Family
زمانيكه از چندين Backup Media براي تهيه Backup استفاده مي نماييم به آن Media Family مي گوييم. معمولا از اين كار براي ايجاد Mirror Backup و يا توزيع Backup يك دیتابیس روي چندين Media مورد استفاده قرار مي گيرد .در اين رابطه بايستي ملاحظاتي را در نظر بگيريم:

• در حالت Backup گيري چنانچه از چندين Media استفاده نماييد تمامي زير مجموعه هاي Backup ها بايستي در Backup Device ها شركت كنند.

Backup Device
شيي از SQL Server مي باشد كه وظيفه مديريت Backup هاي يك يا چندين دیتابیس را برعهده دارد. در واقع اين شيي مديريت Backup هاي تهيه شده و مديريت Backup Media را برعهده دارد. براي ايجاد اين شيي به صورت زير عمل مي كنيم در سمت Server Object ، عنوان Backup Device را انتخاب مي نماييم :

ضمیمه 69801

با انتخاب اين گزينه مي توان يك Device را انتخاب كرد. مزاياي استفاده از Backup Device عبارتند از :

1. Device Backup شامل يك فايل مي باشد و تمامي Backup هاي ايجاد شده در يك فايل نگهداري مي شود.
2. همواره كاربر با يك فايل به عنوان Backup روبرو است ولي بايد اندازه فايل Backup را كنترل كرده زيرا Backup ها در اين فايل بصورت تصاعدي افزايش حجم پيدا مي كند.

ضمیمه 69800

همانطور كه در شكل فوق ملاحظه مي فرماييد مجموعهايي از Backup ها در يك Device قرار دارد و در زمان Restore كردن اين امكان وجود دارد تا با انتخاب هركدام از Backup هاي اين مجموعه به دیتابیس آن دسترسي پيدا كزد.

ادامه دارد...

[Reza_Yarahmadi]

تهيه Backup
براي تهيه Backup بصورت زير عمل كنيد.
پس از ايجاد يك Backup Device از مسیر زير استفاده كرده و Backup را تهيه نماييد.

ضمیمه 69826

در واقع مطلب ذكر شده ، ميسر اصلي براي تهيه Backup مي باشد ولي مسير كوتاه براي تهيه Backup بصورت زير است
روی دیتابیسی که قصد Backup گیری آن را دارید راست کلیک کرده و از منوی Tasks گزینه Back Up را انتخاب نمایید.

ضمیمه 69827

1 - Database : نام دیتابیسي كه مي خواهيد از آن Backup تهيه نماييد
2 - Recovery Model : Recovery Model دیتابیسي كه از آن Backup خواهيد گرفت
3 - Backup Type : در اينجا يكي از انواع Backup را مشخص مي كند

• Full
• Deferential
• Transaction Log

نكته:
اگر امكان Backup از نوع Transaction Log و يا Deferential ميسر نباشد Recovery Model ، دیتابیس از نوع Simple مي باشد.

4 - اين قسمت مشخص می کند كه از دیتابیس Backup بگيريد يا از قسمتي از آن به عنوان File / File Group بک آپ بگیرید.
5 - Backup Set Name : در اين قسمت مي توانيم نامي براي Backup خود انتخاب نماييد. توصيه مي شود براي مشخص شدن Backup ها از اين امكان استفاده نماييم.
6 - Backup Set Description : براي Backup مي توانيد شرح مختصري بنويسيد كه به عنوان تاريخچه Backup در دیتابیس MSDB نگهداري مي شود.
7 - Backup Set Expire Date : اين امكان خوبي براي Backup مي باشد. در زمان اعتبار Backup امكان Overwrite شدن روي Backup File وجود ندارد مگر آنكه كاربر Backup Set Name را كه در قسمت Option مشخص مي شود را بداند. مدت اعتبار Backup به دو صورت مشخص ميشود :

• بصورت زمان مشخص از لحظه تهيه Backup
• در زمان مقرر شده

8 - Destination : همان موقعيت Backup گيري مي باشد . كه همانطور كه در شكل مشخص است Disk يا Tape مي باشد .
چنانچه از Device استفاده شده باشد ، Device به عنوان پيش فرض براي Backup گيري انتخاب شده است در غير اين صورت با استفاده از Add مي توانيم Device يا ساير Backup Device ها را براي آن مشخص نماييد.

ضمیمه 69828

1 - مي توان يك مسير فيزيكي را به عنوان Path به آن معرفي کرد.
2 - از ساير Device هاي موجود براي Backup استفاده کرد.
در قسمت 1 مي توان علاوه بر Path ، از URL نيز استفاده نمود. براي استفاده از اين امكان حتما بايستي تنظيمات لازم را انجام داد. در قسمتهاي آينده بيشتر به آن مي پردازيم.

در قسمت Option موارد زير مشاهده مي شود

ضمیمه 69829

1 - در اين قسمت مديريت Backup Media Set را مي توان انجام داد
2 - در اين حالت مي توان به Backup Set موجود يك Backup اضافه كرد .
3 - در اين حالت مي توان روي Backup Set موجود Overwrite كرد. به عبارت ديگر با انتخاب اين آيتم Backup گرفته شده روي Backup Set قديمي كاملا Overwrite شده و اطلاعات قديم آن از بين مي رود.
4 - در اين آيتم مي توان نامي را انتخاب كرد كه در واقع اين نام كلمه ايي براي مشخص كردن اعتبار Backup مي باشد.
5 - اين آيتم صحت Backup گرفته شده را مورد بررسي قرار مي دهد. در صورت وجود اشكال در دیتابیس Backup گرفته شده اشكال دارد و امكان Restore كردن آنرا كم مي كند. توصيه مي شود كه براي Backup هاي گرفته شده اين آيتم را انتخاب نماييد. انتخاب اين آيتم براي دیتابیس هاي بزرگ توصيه مي شود. ضمنا اين آيتم زمان Backup گيري را كمي افزايش مي دهد.
6 - Checksum به مجموعه كنترل هايي اطلاق مي شود كه در طي انجام كاري بايستي وجود داشته باشد تا در زمان رخداد خطايي از پيشرفت آن كار جلوگيري كند .در SQL Server عمل Checksum براي 3 مورد زير انجام مي شود .

1. براي Torn page
2. براي Log Block
3. Backup گيري

در SQL Server اين امكان وجود دارد تا با انجام Checksum در عمل Backup گيري ، از Backup تهيه شده بيشتر اطمينان حاصل كرده و در واقع Backup مطمئن تري داشته باشيم. عمل Checksum براي Backup ، قبل از آنكه Page هاي Backup روي Backup Medial نوشته شود ، در Level- Page مورد بررسي قرار داده مي شود و چنانچه مشكلي مشاهده شود عمل Backup گيري را متوقف مي كند.
استفاده از اين عمل همانند Verifying Backup مطمئن تري را مي سازد. انتخاب اين مورد نيز براي دیتابیس هاي بزرگ توصيه مي شود.
در زمان Backup گيري اين امكان وجود دارد تا با وجود خطا عمل Backup گيري با موفقيت انجام شود.(گزینه Continue on error)
7 - در SQL Server براي مديريت Log هاي دیتابیس شرايطي جديد را در اختيار كاربر قرار مي دهد. يكي از اين امكانات Backup گرفتن از Transaction Log هاي خاتمه يافته مي باشد.
با كمك Backup از تراكنش ها مي توان مجموعه ايي مطمئن تر از Backup ها را براي نگهداري دیتابیس فراهم آورد. يا با كمك اين آيتم Log هاي خانمه يافته را از Log File خارج نمود.
عمل شماره 7 امكان حذف Log هاي خاتمه يافته را مي دهد. از اين آيتم در مواقعي كه Log File بزرگ شده باشد و از آنها Backup ايي به عنوان تاريخچه احتياج نباشد مي توان استفاده كرد. توجه كنيد اين امكان اندازه Log File را كوچك نمي كند بلكه اطلاعات بلا استفاده در Log File را از Log File خالي مي كند.
8 - معمولا در زمان خرابي دیتابیس مي توان در شرايطي خاص دیتابیس را بازيابي كرد .اين امكان در نسخه هاي قبلي SQL Server بصورت دستي بايستي صورت مي گرفت و معمولا از طريق Transaction Log Backup ها به سختي مي توانست دیتابیس رابازسازي كند ولي در نسخه 2005 امكانی قرار دارد كه در زمان بازيابي دیتابیس از طريق Transaction Log هم از دیتابیس يك Transaction Log Backup گرفته شود و دیتابیس را در وضعيتي قرار دهد تا بتوان از طريق Backup هاي قبلي و Transaction Log فعلي دیتابیس رابازسازي كرد ولي بايد توجه داشته باشيد Recovery Model براي دیتابیس دقيقا در استفاده از اين امكان موثر است به عبارت ديگر در زمان Recovery Model = Simple امكان تهيه Transaction Log Backup مقدور نيست. اين قسمت را در Restore بيشتر توضيح مي دهيم
9 - در اين قسمت مديريت Tape انجام مي شود.

1. پس از انجام Tape ، Backup را Load كند یا نه.
2. پس از اتمام Tape ، Backup را به ابتداي نوار آورد يا خير.

(عکسهایی که در این پست قرار داده شده به دلیل عدم دسترسی به 2005 از 2008 گرفته شده. ولی موارد توضیح داده شده همگی در 2005 وجود دارند)
ادامه دارد...

[Reza_Yarahmadi]

Backup Mirroring (Backup Family)
Backup يك دیتابیس بعنوان مهمترين و قابل اتكاترين شيي براي بازيابي اطلاعات يك پايكاه داده مي باشد لذا در امر Backup گيري از دیتابیس بايستي طوري عمل شود تا با حداقل خطا بتوان دیتابیس را بازسازي كنيم. يكي از راه هاي رسيدن به اين مهم استفاده از Mirrored Media Set مي باشد. مفهوم آن بصورت زير است:

ضمیمه 69878

Media Family 1 , Medial Family 2 بعنوان Backup Media و همانطور كه در شكل مي بينيد 4 عدد Tape بعنوان Backup Media وجود دارد.
Mirror 2 يك كپي مشابه از Mirror 1 مي باشد. به عبارت ديگر زمانيكه از دیتابیس يك Backup و Mirror آن تهيه مي شود. 2 Backup بصورت مستقل توليد مي كند و براي Restore آنها هر كدام بصورت مستقل از هم قابل Restore شدن مي باشند.
Mirror مي تواند روي Disk , Tape گرفته شود. در زمان Backup / Restore وجود Mirror الزامی نمي باشد .
براي اين كار مي توان بصورت زير عمل كرد :

Backup Database MyDB

To Disk  = 'c:\sss.bak'

mirror to disk = 'c:\ssss.bak'

      with format

روش Backup گيري با URL
در بسياري از مواقع لازم مي شود تا Backup را روي Backup Media خارج از سیستم Server گرفته شود. از اين رو ضرورت وجود امكان تهيه Backup روي يك Media در شبكه لازم مي شود. براي استفاده از اين امكان بايستي نكاتي را در نظر بگيريم كه به شرح زير مي باشد :

1. Startup Account مربوط به سرويس SQL Server را با Administrator Domain و در صورت Workgroup با كاربر Administrator كامپيوتر مبدا تنظيم شده باشد.
2. در كامپيوتر مقصد Folder با مجوز Write براي كاربري كه Service را Start كرده است ، ايجاد شود.
3. در قسمت معرفي مسير Backup گيري از عبارات ComputerName\ShaeredFolder\...) URL) استفاده شود.

Partial Backup
در SQL Server 2005 نوعي جديد از Backup گيري ايجاد شده است كه با كمك آن مي توان شبيه به يك Full Backup از اشياي زير Backup تهيه مي كند:

1. تمامي Data ها در Primary File Group
2. تمامي اطلاعات در Read – Write File Group
3. تمامي اطلاعات از فايلهاي Read – Only

ضمیمه 69879

نكته :
يك Partial Backup از دیتابیس Read-only فقط از گروه Backup ، Primary تهيه مي كند.
اين نوع Backup در Backup گيري از دیتابیس در مدل Simple بيشتر مورد استفاده قرار مي گيرد هر چند كه در ساير مدل ها نيز اين امكان وجود دارد.

Backup گيري با دستور T-SQL

 
BACKUP DATABASE { database_name | @database_name_var } 

TO < backup_device > [ ,...n ] 

[ [ MIRROR TO < backup_device > [ ,...n ] ] [ ...next-mirror ] ] 

[ WITH 

     [ BLOCKSIZE = { blocksize | @blocksize_variable } ] 

     [ [ , ] { CHECKSUM | NO_CHECKSUM } ]

     [ [ , ] { STOP_ON_ERROR | CONTINUE_AFTER_ERROR } ]

     [ [ , ] DIFFERENTIAL ] 

     [ [ , ] PASSWORD = { password | @password_variable } ] 

     [ [ , ] { Init | NOINIT  } ] 

]

 

مثلا:

BACKUP DATABASE MyDB

TO DISK='c:\MyFolder\BackupDB.bak

برخي از كارها را نمي توان با كمك ابزار گرافيكي Backup گيري انجام داد و بايستي با استفاده از دستورات اين كارها را انجام داد. اين كارها در مجموعه Switch هاي دستور مي با شد كه در اينجا تعدادي از Switch هاي مهم را مورد بررسي قرار مي دهيم :

CHECKSUM | NO_CHECKSUM
CheckSum برای Backup محاسبه شود یا نه.( در زمان Restore کردن ، در نظر گرفتن CheckSum اختیاری است.)

STOP_ON_ERROR | CONTINUE_AFTER_ERROR
در زمان Backup گيري چنانچه Error رخ دهد بايستي كار Backup گيري ادامه داشته باشد و يا متوقف شود.

DIFFERENTIAL
انجام Backup روش deferential با اين Switch امكان پذير است.

{‌PASSWORD = {password‌
مي توان براي Backup گرفته شده Password قرار داد و در زمان Restore با ارائه Password دیتابیس بازيابي شود.

INIT | NOINIT
Init امكان Overwrite كردن روي دیتابیس موجود را فراهم مي آورد و NOINIT عمل عكس را انجام مي دهد.

ادامه دارد ...