adminbd

Записи по mssql,oracle,windows и linux

Have a Question?

If you have any question you can ask below or enter what you are looking for!

Хранимая процедура для автоматизации создания архивной копии базы данных MS SQL Server и копирования ее в заранее указанное место

 [stextbox id=»info»]Хранимая процедура для автоматизации создания архивной копии базы данных MS SQL Server и копирования ее в заранее указанное место. После выполнения хранимки отправляется уведомление на email.[/stextbox]

Пример с указанием всех параметров

USE master
GO
EXEC dbo.BackupCopyDB
@db = '1citil'
,@b_path = 'c:\test\'
,@c_path = '\\rx6600\d$\test2\'
,@email = '123@mail'
,@alert = 1

Как сделать только бэкап

USE master
GO
EXEC dbo.BackupCopyDB
@db = '1citil'
,@b_path = 'c:\folder\'
,@email = 'mail@mail.ru'

Как сделать бэкап и скопировать его на другой сервер

USE master
GO
EXEC dbo.BackupCopyDB
@db = '1citil'
,@b_path = 'c:\folder\'
,@c_path = '\\ho-1c-app-2\d$\folder\'
,@email = 'mail@mail.ru'

Описание параметров хранимой процедуры

@db — Имя базы данных, которую необходимо за архивировать и скопировать. Обязательный параметр.
Примеры использования параметра: [stextbox id=»alert»]

  • Не правильно @db = »
  • Правильно @db = ‘zup_molniya’
  • Правильно @db = ‘prd1’

[/stextbox]

@b_path — Полный путь куда будет бэкапиться база данных,имя файла указывать не нужно. Не обязательный параметр,т.к есть значение по-умолчанию. Значение по умолчанию ‘E:\’
Примеры использования параметра: [stextbox id=»alert»]

  • Не правильно @b_path = ‘E:\новая папка\’
  • Правильно @b_path = ‘E:\backupfolder\’
  • Правильно @b_path = ‘D:\’

[/stextbox]

@c_path — Полный путь куда будет копироваться бэкап,имя файла указывать не нужно. Параметр нужно указывать, если необходимо произвести копирование файла бэкапа
Примеры использования параметра: [stextbox id=»alert»]

  • Не правильно @c_path = ‘E:\новая папка\’
  • Правильно @c_path = ‘E:\backupfolder\’
  • Правильно @c_path = ‘\\ho_report\backupfolder\’
  • Правильно @c_path = ‘D:\’

[/stextbox]

@email — Почтовый адрес на который будет отправлено письмо с уведомлением о выполнении хранимой процедуры. Не обязательный параметр,т.к есть значение по-умолчанию. Значение по умолчанию ‘event.messages@ctmol.ru’
Примеры использования параметра:

[stextbox id=»info»]Правильно @email= ‘r.mail@mail'[/stextbox]

@alert — флаг,если используется,то на почту, указанную в @email, приходят уведомления о ходе выполнения процедуры .Не обязательный параметр,т.к есть значение по-умолчанию True,@alert = 1
Примеры использования параметра:

[stextbox id=»info»]Правильно @alert= 0[/stextbox]

Пример работы хранимой процедуры

После выполнения хранимой процедуры на вкладке «Сообщения» в MS Management Studio будет показан примерно такой лог(кликабельно)

mj5higlgeue3hvlykrtuuw

Код хранимой процедуры

USE [master]
GO
/****** Объект:  StoredProcedure [dbo].[BackupCopyDB]    Дата сценария: 03/04/2014 09:55:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[BackupCopyDB] 	
 
	-- имя бд,которую будем бэкапить
	@db nvarchar(100)
 
	-- путь куда будет делаться наш файл бэкапа	
	-- если делаем бэкап локально,то достаточно указать путь "буква диска:\имя папки\"
	-- иначе пишем полный путь "\\имя сервера\путь до папки". Необходимо,чтобы у учетки SQL Agent'a  был доступ в указанную папку
	,@b_path nvarchar(100) = N'D:\'
 
	-- путь куда будет скопирован файл бэкапа 
	-- пишем полный путь "\\имя сервера\путь до папки". Необходимо,чтобы у учетки SQL Agent'a  был доступ в указанную папку
	,@c_path nvarchar(100) =N'S'
 
	-- email адрес, на который будут присылаться уведомления выполнения хранимой процедуры
	,@email nvarchar(100) = N'event.messages@ctmol.ru'
 
	-- флаг,если true то на почту будут приходить уведомления после каждого выполненного шага в хранимке
	-- иначе прийдет только уведомление об окончании выполнения хранимой процедуры
	,@alert bit = 1
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	-----------------------------------------------------
 
	-- путь до бэкапа
	declare @storegeplace varchar(260)
 
	-- перемення для команды cmd
	declare @var varchar(260)
 
	-- гарантия почти уникальности имени файла
	declare @seckey nvarchar(12)
 
	-- текущая дата формата ггггммдд
	declare @a nvarchar(8) 
 
	-- текущее время чч:мм
	declare @b nvarchar(5)
 
	-- два правых символа от @b
	declare @c nvarchar(2)
 
	-- два левых символа от @b
	declare @d nvarchar(2)
 
	-- flag
	declare @flag bit
 
	-- alert	
	declare @alert_desc nvarchar(100)	
 
	-----------------------------------------------------
	set @a = (select CONVERT(nvarchar(8), GETDATE(), 112)) 
	set @b = (select CONVERT(nvarchar(5), GETDATE(), 108)) 
	set @c = RIGHT(@b,2)
	set @d = LEFT(@b,2)
	-- получаем значение seckey
	set @seckey = @a +@d+@c
 
	-- указываем полный путь до файла бэкапа
	set @storegeplace = @b_path+@db+'_'+@seckey+'.bak' 
 
	-- указываем параметры для команды в cmd
	set @var = 'copy '+ @storegeplace + ' '+@c_path+@db + '_'+@seckey+'.bak'
 
	-- обнуляем flag
	set @flag = 0
 
	-----------------------------------------------------
 
	-- проверка входных параметров
	if (len(@db)<=0)
		begin
			print 'не указано имя базы данных'
			print ' '
		end
		else
 
			BEGIN
 
						print 'бэкап будет делаться сюда --> ' + @storegeplace
						print ' '
 
						-- alert #1
						if (@alert = 1)
								begin
											set @alert_desc = 'бэкап будет делаться сюда --> ' + @storegeplace + ' ('+ CONVERT(nvarchar(20), GETDATE(), 113)+')'
											EXECUTE msdb.dbo.sp_send_dbmail
												@profile_name  = 'atrans',        
												@recipients  = @email,
												@subject  = 'OK.Хранимая Процедура BackupCopyDB'
												,@body = @alert_desc
								end
						-- end alert #1
 
						begin try
								BACKUP DATABASE @db
								TO  DISK = @storegeplace
								WITH NOFORMAT, NOINIT,  NAME = @db, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
								print ' '
								print 'бэкап готов '
 
								-- alert #2
							if (@alert = 1)
								begin
									set @alert_desc = 'бэкап "'+@db+'" готов.'+' ('+CONVERT(nvarchar(20), GETDATE(), 113)+')'
									EXECUTE msdb.dbo.sp_send_dbmail
									@profile_name  = 'atrans',        
									@recipients  = @email,
									@subject  = 'OK.Хранимая Процедура BackupCopyDB'
									,@body = @alert_desc
								end
							-- end alert #2
 
							set @flag = 1
						end try
						BEGIN CATCH
							SELECT
								ERROR_NUMBER() AS ErrorNumber,
								ERROR_SEVERITY() AS ErrorSeverity,
								ERROR_STATE() AS ErrorState,
								ERROR_PROCEDURE() AS ErrorProcedure,
								ERROR_LINE() AS ErrorLine,
								ERROR_MESSAGE() AS ErrorMessage;
							print ' '
							print 'что-то пошло не так и бэкап не сделался'
							set @flag = 0
							EXECUTE msdb.dbo.sp_send_dbmail
								   @profile_name  = 'atrans',        
								   @recipients  = @email,
								   @subject  = 'ERROR.Хранимая Процедура BackupCopyDB'
						END CATCH;	
 
 
						if(len(@c_path)>1)						
							begin
 
						if (@flag = 1)
								begin
									begin try
										print ' '
										print 'бэкап копируется сюда --> '+@c_path+@db + '_'+@seckey+'.bak'
										print 'на почтовый адрес ' + @email + ' будет отправлено уведомление по окончанию копирования. enjoy :)'
 
										-- alert #3
										if (@alert = 1)
											begin
												set @alert_desc = 'бэкап копируется сюда --> '+@c_path+@db + '_'+@seckey+'.bak'+' ('+CONVERT(nvarchar(20), GETDATE(), 113)+')'
												EXECUTE msdb.dbo.sp_send_dbmail
												@profile_name  = 'atrans',        
												@recipients  = @email,
												@subject  = 'OK.Хранимая Процедура BackupCopyDB'
												,@body = @alert_desc
											end
										-- end alert #3
 
										EXEC master..xp_cmdshell @var
 
										set @alert_desc = 'OK.Хранимая Процедура BackupCopyDB выполнена'+' ('+CONVERT(nvarchar(20), GETDATE(), 113)+')'
										EXECUTE msdb.dbo.sp_send_dbmail
										   @profile_name  = 'atrans',        
										   @recipients  = @email,
										   @subject  = 'OK.Хранимая Процедура BackupCopyDB'
											,@body = @alert_desc 
									end try	
									BEGIN CATCH
										SELECT
											ERROR_NUMBER() AS ErrorNumber,
											ERROR_SEVERITY() AS ErrorSeverity,
											ERROR_STATE() AS ErrorState,
											ERROR_PROCEDURE() AS ErrorProcedure,
											ERROR_LINE() AS ErrorLine,
											ERROR_MESSAGE() AS ErrorMessage;
										print ' '
										print 'что-то пошло не так и бэкап не скопировался'
										EXECUTE msdb.dbo.sp_send_dbmail
										   @profile_name  = 'atrans',        
										   @recipients  = @email,
										   @subject  = 'ERROR.Хранимая Процедура BackupCopyDB'
									END CATCH;
								end	
							end	
		END;
 
END

 

Leave a Reply

Your email address will not be published. Required fields are marked *

You can use these HTML tags and attributes <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>