در این مطلب درباره آموزش پشتیبان (Backup) گیری خودکار از SQL Server صحبت کنیم. بکاپ گیری خودکار از بانک اطلاعاتی ، یکی از مهمترین نکات در آموزش SQL Server است که شما باید با آن آشنا باشید. اگر مقاله های قبلی من در خصوص Backup گیری از اکتیودایرکتوری را به خاطر داشته باشید یک قانون همیشگی در خصوص ادامه حیات شما در حوزه IT وجود دارد و آن قانون Backup است و Backup است و Backup . البته این جمله از خودم نیست و در واقع یک قانون در حوزه امنیت اطلاعات هست.امروزه تقریبا بیش از 90 درصد Database هایی که برای نرم افزارهای اتوماسیون ، مالی ، انبارداری و حتی سرورهای سازمانی مثل SharePoint یا Lync استفاده می شود چیزی به غیر از MSSQL Server نیست .
باید بدانید که بدون شک در مراحل زمانی مشخص و مرتب از Database های موجود در این سرور Backup بگیرید ، در زمان های قدیم ( یعنی زمانی که بنده اصلا کاری به کار SQL سرور نداشتم D: ) انجام عملیات Backup گیری از SQL سرور چندان آسان نبود و مستلزم اجرای یک سری دستورات و Query ها در SQL سرور بود. اما شخصی که تا کنون کاری به SQL سرور نداشته و به عنوان مدیر یک شبکه مسئولیت نگهداری از داده ها را دارد کار ساده ای با این نرم افزار نداشت .
در SQL سرور 2008 نسخه R2 که امروز می خواهیم در خصوص آن توضیح دهیم ، شرکت مایکروسافت یک روش بسیار بسیار ساده برای گرفتن Backup آن هم بصورت زمانبندی شده در اختیار مدیرهای شبکه قرار داده است که براحتی و تنها با چند کلیک می توانید از همه اطلاعات موجود در Database های خود backup بگیرید. در این مقاله شما بصورت گام به گام از کلیه اطلاعات موجود در Instance ای به نام ITPRO در MSSQL Server بصورت زمانبندی شده Backup میگیرید و این Backup ها را نیز بعد از مدتی باز هم بصورت مدت دار از دستگاه ذخیره سازی اطلاعات حذف می کنید تا فضای اضافه ای اشغال نکنند.
چگونه بصورت خودکار از Database های SQL بکاپ بگیریم ؟
خوب قبل از اینکه یک راست به سراغ فرآیند Backup گیری برویم باید مطمئن شوید که سرویس SQL Server Agent در حالت running قرار دارد در غیر اینصورت نمی توانید از این روش برای Backup گیری استفاده کنید. برای بررسی این مورد هم می توانید وارد کنسول Services.msc شوید و هم می توانید از طریق کنسول SQL Management Studio بعد از connect شدن به Instance موجود در SQL در قسمت Object Explorer بررسی کنید که آیا سرویس فعال و در حال اجرا است یا خیر ، اگر نبود براحتی بر روی سرویس مورد نظر راست کلیک کرده و مشابه تصویر پایین بر روی Start کلیک می کنیم.
از Object Explorer قسمت Management Node را باز کنید و گزینه Maintenance Plan را انتخاب کنید.توجه کنید که برای اینکه بتوانید یک برنامه Backup زمانبندی شده توسط Maintenance Plan ها داشته باشید باید دسترسی یا بهتر بگوییم role ای به نام SYSADMIN را داشته باشید ، البته من فرض را بر این میگیرم که شما دسترسی کامل را دارید زیرا هیچ مدیر سیستمی در ایران توانایی تحمل محدودیت را ندارد و در همه جا Admin می باشد ( باور کنید ، مدیر شبکه خود را مالک همه سیستم ها می داند چه به او ربطی داشته باشد و چه ربطی نداشته باشد ) به هر حال اگر نمی توانید Maintenance را مشابه آنچه در تصویر زیر مشاهده می کنید ببینید بررسی کنید که Permission های لازم را به این Instance داشته باشید.
مشابه آنچه در تصویر پایین می بینید بر روی Maintenance Plan راست کلیک کرده و گزینه new maintenance plan را انتخاب کنید.
در کادری که در پایین مشاهده می کنید یک اسم برای این maintenance plan ایجاد کنید ، اسم را متناسب با فرآیندی که قرار است در اینجا انجام شود تعیین کنید برای مثال بنده اسم را ITPRO Database Daily Backup گذاشته ام ، شما می توانید بر اساس نیاز خود هر اسمی که دوست دارید را انتخاب کنید فقط لطفا یک اسم با معنی ایجاد کنید نه هر چیزی که فقط کاراکتر باشد ( مورد داشتیم اسم Maintenance Mode به شکل Nazijoon وارد شده است ، به خداااا ( . اسم را ایجاد و بر روی OK کلیک کنید.
خوب شما الان در صفحه تنظیمات مربوط به Maintenance Plan هستید. به نقاطی که با علامت ویلبیلک ( VillBilak ) مشخص شده اند توجه کنید ، این دو قسمت مواردی هستند که شما باید تنظیمات Backup خود را انجام دهید. قسمتی که در گوشه بالای سمت راست در تصویر زیر مشخص شده است برای زمانبندی انجام عملیات خود استفاده می کنید ، اینکه در چه زمانی و چه روزهایی عملیات های Maintenance Plan انجام شوند. توجه کنید که زمانی برای انجام خود تعیین کنید که می دانید Database شما در حال استفاده نمی باشد ، هیچوقت در اوج ساعت کاری Database ها از آنها بصورت زمانبندی شده Backup نگیرید. اما قسمتی که در پایین و سمت چپ تصویر مشخص شده است اصل ماجرا یا همان Backup Maintenance Plan شما را ایجاد می کند. با توجه به اینکه می توان فقط برای همین یک قسمت چند مقاله نوشت ما بصورت جزئی تر وارد ماجرا نمی شویم و فقط به بررسی دو مورد از آنها که در آموزش ما استفاده می شود کفایت می کنیم.
بر روی آیکن تقویم یا همان Calendar که در تصویر بالا مشخص شده است دابل کلیک کنید. با اینکار یک کادر باز می شود که شما می توانید زمان های اجرای عملیات و همچنین وهله های زمانی کار را برای سیستم تعریف کنید. همیشه بر اساس نیاز و اهمیت کار سازمان تعاریف زمانبندی خود را انجام دهید. معمولا Backup ها سازمانی بصورت روزانه گرفته می شوند. باز هم تاکید می کنم زمانی را انتخاب کنید که می دانید Database شما Load کاری زیادی ندارد ، بنده معمولا شب ها یا ساعات اولیه بامداد را برای گرفتن backup انتخاب می کنم. زمانیکه کار خود را تمام کردید بر روی دکمه OK کلیک کنید.
از قسمت Maintenance Plans که در تصاویر بالا در گوشه سمت چپ پایین مشخص شده است گزینه backup database plan را انتخاب کنید و بوسیله Drag and Drop آنرا در کادر خالی روبرو بیندازید. این همان مجموعه دستوراتی است که عملیات Backup گیری را برای شما انجام می دهد. تصویر زیر دقیقا همان چیزی است که باید مشاهده کنید.
حالا بر روی backup database task ای که در کادر روبرو ایجاد شده است دابل کلیک کنید. با اینکار یک صفحه جدید باز می شود که شما می توانید تنظیمات مربوط به Database هایی که می خواهید Backup بگیرید را مشخص می کنید. در اینجاست که شما تعیین می کنید از چه Database هایی می خواهید Backup بگیرید ، در اینجاست که شما تعیین می کنید در کجای سیستم و در چه مسیری فایل های Backup قرار بگیرد و تمامی موارد مرتبط با backup گیری از دیتابیس ها در اینجا تعیین می شود. از قسمت بالایی این صفحه از منوی بازشوی Databases شما می توانید دقیقا Database مورد نظر خودتان را برای Backup گیری تعیین کنید ، همچنین در همین صفحه همانطور که در صفحه پایین مشاهده می کنید می توانید پسوند فایل های ذخیره شده و مسیر ذخیره را نیز تعیین کنید ، ترجیجا با توجه با ساختارهای شلخته ایرانی ( تعارف نداریم باور کنید ) Backup خود را بصورت All Databases تعریف کنید.
بعد از اینکه موارد بالا را انجام دادید بر روی OK کلیک کنید. تبریک صمیمانه ، تنظیمان مربوط به Backup گیری شما انجام شده است. فایل های Backup شما در زمان تعیین شده و در مسیر تعریف شده بصورت خودکار روزانه در ساعت 9 شب گرفته می شوند. نام فایل هایی که بعد از گرفتن Backup ایجاد می شود بر اساس تاریخی تعیین می شود که در آن Backup گرفته شده است و این کمک زیادی به بحث شناسایی دقیق داده های درست در زمان بازگردانی یا Recovery به مدیر سیستم می کند.
با توجه به اینکه Backup های شما بصورت متناوب و روزانه گرفته می شوند بعد از مدتی حجم اطلاعاتی که backup گرفته می شوند زیاد می شود بنابراین بهتر است برای این سیستم Backup تعریف کنید که بعد از یک وهله زمانی معین Backup های قبلی را از بین ببرد و حذف کند.. برای اینکه بتوانید اینکار را انجام دهید شما باید یک cleanup task از همان قسمتی که Maintenance Cleanup Taskرا انتخاب کردید را به شکل قبل در کادر روبرویی Drag and Drop کنید. با استفاده از روش زیر شما می توانید تنظیمات Maintenance Cleanup Task را نیز انجام دهید.
بر روی شیئ Maintenance Cleanup Task ای که در کادر روبرو انداخته اید دابل کلیک کنید تا تنظیمات آن را مشاهده کنید. شما باید مسیر Backup ای که گرفته شده است به همراه پسوند فایل ها و همچنین مدت زمانی که می خواهید Backup ها معتبر باشند را تعیین کنید. معمولا برای این قسمت مدت زمان یک ماه در نظر گرفته می شوند همانطور که در تصویر زیر نیز مشاهده می کنید.
زمانیکه بر روی تصویر بالا OK را کلیک کنید. می توانید مطمئن باشید که Backup های شما هر ماه یکبار گذشته را فراموش می کنند و به فکر آینده می افتند. امیدوارم هیچوقت در زندگی نیازی به بازگردانی Backup نداشته باشید اما به هر حال همیشه اصل Backup Backup Backup را فراموش نکنید ، امیدوارم مورد توجه شما قرار گرفته باشد. توسینسو باشید.
سلام ممنون از اطلاع رسانی شما استفاده کرم
با سلام و خسته نباشید خدمت استاد محمد نصیری و بقیه دوستان
استاد با اجازه شما من یک توضیحی و یک آموزشی برای ذخیره کردن فایل BACKUP به فرمت تاریخ شمسی در زیر می گذارم که بقیه دوستان استفاده کنند و لذت ببرند :
البته به صورت تاریخ میلادی هم می گذارم که بقیه دوستان استفاده کنند :
اول تاریخ شمسی را توضیح می دهم :
باید FUNCTION زیر را در دیتابیس MASTER ایجاد کنید در دیتابیس های دیگر هم می توانید ایجاد کنید :
USE [master]
GO
/****** Object: UserDefinedFunction [dbo].[IsLeapYear] Script Date: 08/25/2011 12:16:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Reza Rad
-- Create date: 8/25/2011
-- Description: IsLeapYear
-- =============================================
CREATE FUNCTION [dbo].[IsLeapYear]
(
@Year int
)
RETURNS bit
AS
BEGIN
-- Declare the return variable here
DECLARE @ResultVar bit
-- Add the T-SQL statements to compute the return value here
if @Year % 400 = 0
Begin
set @ResultVar=1
end
else if @Year % 100 = 0
Begin
set @ResultVar=0
end
else if @Year % 4 = 0
Begin
set @ResultVar=1
end
else
Begin
set @ResultVar=0
end
-- Return the result of the function
RETURN @ResultVar
END
GO
/****** Object: UserDefinedFunction [dbo].[NumberOfDaysInMonthGregorian] Script Date: 08/25/2011 12:16:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Reza Rad
-- Create date: 8/25/2011
-- Description: Fetch number of days in gregorian month
-- =============================================
CREATE FUNCTION [dbo].[NumberOfDaysInMonthGregorian]
(
@Year int
,@Month int
)
RETURNS int
AS
BEGIN
-- Declare the return variable here
DECLARE @ResultVar int
-- Add the T-SQL statements to compute the return value here
if(@Month<>2)
begin
set @ResultVar=30+((@Month + FLOOR(@Month/8)) % 2)
end
else
begin
if(dbo.IsLeapYear(@Year)=1)
begin
set @ResultVar=29
end
else
begin
set @ResultVar=28
end
end
-- Return the result of the function
RETURN @ResultVar
END
GO
/****** Object: UserDefinedFunction [dbo].[NumberOfDayInMonthPersian] Script Date: 08/25/2011 12:16:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Reza Rad
-- Create date: 8/25/2011
-- Description: Fetch number of days in Persian month
-- =============================================
CREATE FUNCTION [dbo].[NumberOfDayInMonthPersian]
(
@Year int,-- Gregorian Year
@Month int -- Presian Month
)
RETURNS int
AS
BEGIN
-- Declare the return variable here
DECLARE @ResultVar int
-- Add the T-SQL statements to compute the return value here
if(@Month<=6)
set @ResultVar=31
else
if(@Month=12)
if(dbo.IsLeapYear(@Year-1)=1)
set @ResultVar=30
else
set @ResultVar=29
else
set @ResultVar=30
-- Return the result of the function
RETURN @ResultVar
END
GO
/****** Object: UserDefinedFunction [dbo].[GregorianToPersian] Script Date: 08/25/2011 12:16:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Reza Rad
-- Create date: 8/25/2011
-- Description: Convert gregorian date in type of yyyy-MM-dd format to persian/shamsi date string
-- =============================================
CREATE FUNCTION [dbo].[GregorianToPersian]
(
@Date varchar(10)
)
RETURNS varchar(10)
AS
BEGIN
-- Declare the return variable here
DECLARE @ResultVar varchar(10)
-- Add the T-SQL statements to compute the return value here
declare @Year int
declare @Month int
declare @Day int
declare @PersianYear int
declare @PersianMonth int
declare @PersianDay int
declare @StartMonthGregorianDateInPersianCalendar int=10
declare @StartDayGregorianDateInPersianCalendar int=11
set @Year=convert(int,substring(@Date,1,4))
set @Month=convert(int,substring(@Date,6,2))
set @Day=convert(int,substring(@Date,9,2))
--print @year
--print @month
--print @day
declare @GregorianDayIndex int=0
if(dbo.IsLeapYear(@Year)=1)
set @StartDayGregorianDateInPersianCalendar=11
else
if(dbo.IsLeapYear(@Year-1)=1)
set @StartDayGregorianDateInPersianCalendar=12
else
set @StartDayGregorianDateInPersianCalendar=11
--print @StartDayGregorianDateInPersianCalendar
declare @m_index int=1
while @m_index<=@Month-1
begin
set @GregorianDayIndex=@GregorianDayIndex+dbo.NumberOfDaysInMonthGregorian(@Year,@m_index)
set @m_index=@m_index+1
end
set @GregorianDayIndex=@GregorianDayIndex+@Day
if(@GregorianDayIndex>=80)
begin
set @PersianYear=@Year-621
end
else
begin
set @PersianYear=@Year-622
end
declare @mdays int
declare @m int
declare @index int=@GregorianDayIndex
set @m_index=0
while 1=1
begin
if(@m_index<=2)
set @m=@StartMonthGregorianDateInPersianCalendar+@m_index
else
set @m=@m_index-2
--print 'm='+convert(varchar(max),@m )
set @mdays=dbo.NumberOfDayInMonthPersian(@Year,@m)
if(@m=@StartMonthGregorianDateInPersianCalendar)
set @mdays=@mdays-@StartDayGregorianDateInPersianCalendar+1
--print 'mday='+convert(varchar(max),@mdays)
--print 'index='+convert(varchar(max),@index)
--print 'm_index='+convert(varchar(max),@m_index)
if(@index<=@mdays)
begin
set @PersianMonth=@m
if(@m=@StartMonthGregorianDateInPersianCalendar)
set @PersianDay=@index+@StartDayGregorianDateInPersianCalendar-1
else
set @PersianDay=@index
break
end
else
begin
set @index=@index-@mdays
set @m_index=@m_index+1
end
end
set @ResultVar=
convert(varchar(4),@PersianYear)+'-'+
right('0'+convert(varchar(2),@PersianMonth),2)+'-'+
right('0'+convert(varchar(2),@PersianDay),2)
-- Return the result of the function
RETURN @ResultVar
END
GO
برای اجرای FUNCTION از کوئری زیر استفاده کنید :
select [dbo].[GregorianToPersian] ('2011-08-25')
و می بینید که به تاریخ شمسی تبدیل شده است.
حالا اگر بخواهیم که این FUNCTION را در BACKUP DATABASE بکار ببریم باید مثل زیر انجام دهید :
برای مثال فایلهای بکاپ ما به فرمت تاریخ شمسی ذخیره می شود حالا به کد زیر توجه کنید :
declare @filename nvarchar(200)
set @filename = N'c:\backup\' + 'AdventureWorks2017_' + [dbo].[GregorianToPersian] (convert(varchar(10),getdate(),121)) + '_' + CAST(DATEPART(HOUR ,GETDATE()) AS VARCHAR(10)) + '_' + CAST(DATEPART(MINUTE ,GETDATE()) AS VARCHAR(10)) + '_' + CAST(DATEPART(SECOND ,GETDATE()) AS VARCHAR(10))
BACKUP DATABASE [AdventureWorks2017] TO DISK = @filename WITH NOFORMAT, NOINIT, NAME = N'AdventureWorks2017-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
وقتی که شما هر بار کوئری بالا را اجرا کنید برای شما یک فایل جدید ایجاد می کند با تاریخ و ساعت و دقیقه و ثانیه ایجاد می کند البته به صورت تاریخ شمسی ایجاد می کند :
حالا اگر بخواهید فایلهای بکاپ خود را به صورت تاریخ میلادی ذخیره کنید باید به صورت زیر انجام دهید توجه بکنید :
declare @filename nvarchar(200)
set @filename = N'c:\backup\' + 'AdventureWorks2017_' + replace(convert(varchar(19),getdate(),121),':','')
BACKUP DATABASE [AdventureWorks2017] TO DISK = @filename WITH NOFORMAT, NOINIT, NAME = N'AdventureWorks2017-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
امیدوارم که مورد توجه شما دوستان قرار گرفته باشد
با تشکر
سلام مهندس من فایل بکاپی که با استفاده از این آموزش و در SQL server 2008 r2 ساخته شده رو در سیستم دیگری که همان ۲۰۰۸r2 نصب شده،ریستور میکنم ولی خطا میده:
Specified cast is not valid
خیر ، تاریخ شمسی رو پشتیبانی نمی کنه
مهندس امکانش هست در نام فایل بکاپی که ساخته میشه، تاریخ به صورت شمسی درج بشه؟
سلام وقت به خیر ، بله باید فعال باشه
سلام و وقت بخیر
مرحله تنظیم cleanup task گزینه ای هست با عنوان : include first level subfolders
اگر در مرحله تعریف بکاپ گیری گزینه : create a sub-directory for each database رو فعال کنیم تا برای هر دیتابیس یک پوشه بسازد و در داخل اون پوشه ها که ساب فولدر هستند، بکاپ ها رو ذخیره کنه اونموقع باید تیک گزینه include first level subfolders در قسمت cleanup task فعال بشه یا نه؟
لطفت سوالتون رو از طریق ارسال مطلب در جزیره مربوطه مطرح کنید با سپاس
با سلام و خسته نباشيد خدمت اساتيد محترم
من يه مشكلي با Backup گيري از ديتابيس هاي SQL سرور دارم و اون اينه كه من چند تا سرور مجازي مختلف دارم كه روي هر كدوم يه SQL سرور نصب شده و توي هر SQL سرور، چندين ديتا بيس وجود داره.
حالا من ميخوام يه سرور مجازي ديگه ايجاد كنم و روي اون يك SQL سرور نصب كنم و با استفاده از اون، از ديتابيس هاي SQL سرورهاي روي سرورهاي مجازي ديگه Backup بصورت اتوماتيك و دوره اي بگيرم.
اگه ممكنه راهنمايي كنيد. ممنون
با سلام و خسته نباشید خدمت استاد عزیزم جناب آقای مهندس محمد نصیری
جناب آقای مهندس خیلی عالی توضیح دادید انسان لذت می برد از آموزشهای خوب شما در این سایت ممنونم واقعا عالی هستند.