تازه سازی پایگاه داده SQL Server به طور خودکار

کاری که میخواهیم انجام دهیم این است که هر روز عصر پایگاه داده Test / Development را از روی یک کپی از بانک اطلاعاتی محیط production بازیابی کنیم تا توسعه دهندگان هر روز صبح داده های تازه ای برای کار داشته باشند که آنها بتوانند از آخرین نسخه پشتیبان که قبل از نیمه شب روز گذشته اجرا شده، استفاده کنند . بنابراین ، در پایان ، داده ها هرگز قدیمی تر از یک روز نخواهند بود. برای این کار بسیار ساده ، تصمیم داریم یک اسکریپت SQL را بسازیم و آن را تحت یک جاب در SQL Agent قرار دهیم.

اسکریپت SQL زیر به یک دایرکتوری منبع در فایل سیستم که در آن نسخه پشتیبان تهیه می شود ، هدایت می شود و آخرین نسخه پشتیبان تهیه شده را به صورت کامل در یک پایگاه داده مقصد بازیابی می کند که یک متغیر درون اسکریپت است. ما آن را برای نشان دادن محل متغیرهایی که نیاز به بروزرسانی دارند اضافه کرده ایم.

اسکریپت نسبتاً ساده است. شما باید اسکریپت زیر را با متغیرهای خود برای مکان فایل تغییر دهید. ابتدا به پوشه پشتیبان برای آخرین فایل پشتیبان نگاه می کند. سپس یک جدول موقت برای قرار دادن نام پرونده ها ایجاد می کند. سپس آخرین فایل پشتیبان را برای استفاده از جدول موقت انتخاب می کند. سرانجام ، با استفاده از آخرین فایل پشتیبان ذکر شده در جدول موقت ، بازیابی را انجام می دهد و آن را از پوشه سیستم فایل پشتیبان می گیرد. علاوه بر این ، هربار که job اجرا میشود برای ثبت فعالیت job ، یک audit log ایجاد کردیم که در صورت عدم موفقیت در کار برای عیب یابی ، از آن استفاده کنیم.  همچنین notificationها را فعال کرده ایم تا در صورت ناموفق بودن کار برای ما از طریق ایمیل ارسال شود. 

این اسکریپت را در یک پنجره جستجوی جدید کپی و جایگذاری کنید و متغیرهای مشخص شده را بجای * * تغییر دهید.

–get the last backup file name and path

 Drop table #BackUpFilesAvailable

 Declare @LatestBackupFile varChar(255)

Declare @BackUpFolder varchar(255)

set @LatestBackupFile = null

set @BackUpFolder = ‘/**/ C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS2019\MSSQL\Backup\’

create table #BackUpFilesAvailable (

FileName varchar(255),

levels int,

files int)

–get all the files and folders in the backup folder and

 put them in temporary table

insert into #BackUpFilesAvailable exec xp_dirtree @BackUpFolder,0,1

–get the latest backup file name

select top 1 @LatestBackupFile = @BackUpFolder + FileName

from #BackUpFilesAvailable where Filename

like ‘%.bak’ order by filename desc

–execute the restore

RESTORE DATABASE /**/ [TestDevelopmentDB] FROM 

DISK = @LatestBackupFile WITH

/**/

MOVE N’ProductionDB’ TO N’C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS2019\MSSQL\DATA\TestDevelopmentDB.mdf’, 

MOVE N’ProductionDB_log’ TO N’C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS2019\MSSQL\DATA\TestDevelopmentDB_log.ldf’, REPLACE

اسکریپت را اجرا کنید. اگر همه متغیرها به درستی تغییر کرده باشند ، در پنجره پیام ها در زیر پنجره جستجو یک تأیید مشاهده خواهید کرد.

Agent Job

 کار را با قرار دادن آن در یک جاب SQL Server Agent خودکار کرده و برنامه ریزی کنید تا در زمان مشخصی اجرا شود. برای افزودن آن به یک جاب ایجنت ، SQL Server Management Studio را باز کرده و در آبجکت اکسپلورر SQL Server Agent  را بازکنید. توجه داشته باشید SQL Agent باید در حال اجرا باشد وگرنه این شاخه غیرفعال می شود.سپس بر روی Jobs کلیک راست کرده و New Job را انتخاب کرده و نام آن را انتخاب کنید. توجه داشته باشید که  مالک به عنوان کاربر وارد شده به طور پیش فرض برای شما انتخاب میشود. من معمولاً این مورد را به SA یا یک کاربر با اختیارات SA تغییر می دهم اما این به شما و بیزنس تان بستگی دارد.

حال به تب steps بروید. روی دکمه New در قسمت پایین صفحه کلیک کنید. وقتی صفحه جدید باز شد ، نامی برای step مشخص کنید ، اسکریپت اصلاح شده را از بالا جایگذاری کنید سپس بر روی Parse کلیک کنید تا اسکریپت آزمایش شود. اگر خطایی وجود ندارد ، روی Advanced در فهرست چپ کلیک کنید. پس از گزارش موفقیت ok را زده، ازآن خارج شوید.

بررسی شکست یا موفقیت جاب

میخواهیم در صورت بروز مشکل گزارش خرابی و شکست در یک فایل متنی در فایل سیستم ثبت شود و لاگ بیاندازد. بنابراین قسمت Output file  را همانطور که در زیر آورده ایم کامل کنید.

علاوه بر این سابقه حسابرسی ، ما همچنین می خواهیم یک اعلان ایمیل در مورد job failure را اضافه کنیم تا در صورت بروز چنین مواردی به ما اطلاع داده شود. برای انجام این کار ، روی صفحه نمایش بالا روی ok کلیک کنید و سپس از فهرست سمت چپ روی notifications کلیک کنید.

ایمیل را بررسی کنید و گیرنده را اضافه کنید. لطفاً توجه داشته باشید که شما باید dbMail را فعال و پیکربندی کنید تا بتوانید این کار را انجام دهید و همچنین اپراتورها را بر روی سرور خود نصب کنید. ما نمی خواهیم نحوه پیکربندی dbMail یا افزودن اپراتورها را بررسی کنیم زیرا آنها از دیگر منابع به راحتی در دسترس هستند. پس از تکمیل اعلان ، بر روی Schedules در فهرست سمت چپ کلیک کنید و سپس روی new کلیک کنید.

برنامه اجرای خود را برای جاب restore اضافه کنید. به عنوان مثال در اینجا ، ما یک کار روزانه را برنامه ریزی کرده ایم که ساعت 4:30 صبح اجرا می شود و بدون تاریخ پایان است.