Пример реализации автоматизированного процесса резервного копирования и восстановления баз данных встроенными средствами / forpes.ru

Главная
Пример реализации автоматизированного процесса резервного копирования и восстановления баз данных встроенными средствами

Пример реализации автоматизированного процесса резервного копирования и восстановления баз данных встроенными средствами +10

01.12.2017 05:41

jobgemws 2 2600 Источник

Предисловие

В Интернете можно найти достаточно много примеров по созданию резервных копий баз данных, а также по их восстановлению. Приведем еще один пример встроенными средствами в MS SQL Server.

В данном примере будут собраны сразу несколько подходов-от проверки целостности базы данных перед созданием резервной копии до восстановления этой базы по уже созданной ранее резервной копии.

Решение

Сначала приведем общий алгоритм по созданию резервной копии:

1) Определяем для каких баз данных нужно сделать резервную копию
2) Проверяем каждую выбранную базу данных на целостность
3) Создаем для каждой выбранной базы данных резервную копию (полную или разностную (дифференциальную), или журнала транзакций)
4) Проверяем полученные резервные копии
5) Сжимаем журналы транзакций отработанных баз данных (по необходимости)

Далее приведем пример реализации данного выше алгоритма.

Для того, чтобы определить для каких баз данных необходимо делать резервные копии, создадим следующую таблицу:

Таблица настроек резервного копирования

USE [ИМЯ_БД]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [srv].[BackupSettings](
	[DBID] [int] NOT NULL,
	[FullPathBackup] [nvarchar](255) NOT NULL,
	[DiffPathBackup] [nvarchar](255) NULL,
	[LogPathBackup] [nvarchar](255) NULL,
	[InsertUTCDate] [datetime] NOT NULL,
 CONSTRAINT [PK_BackupSettings] PRIMARY KEY CLUSTERED 
(
	[DBID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
GO

ALTER TABLE [srv].[BackupSettings] ADD  CONSTRAINT [DF_BackupSettings_InsertUTCDate]  DEFAULT (getutcdate()) FOR [InsertUTCDate];
GO

В первом столбце указывается идентификатор базы данных, в FullPathBackup содержится полный путь для создания полных резервных копий (например, 'диск:\...\'), а в DiffPathBackup и LogPathBackup полные пути для создания разностных резервных копий и резервных копий журналов транзакций соответственно. Если столбец DiffPathBackup или LogPathBackup будет пустым, то база данных не будет участвовать в создании разностной резервной копии или резервной копии журнала транзакций соответственно.

Также можно создать представление на основе этой таблицы:

Представление для настроек резервного копирования

USE [ИМЯ_БД]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE view [srv].[vBackupSettings]
as
SELECT [DBID]
      ,DB_Name([DBID]) as [DBName]
	  ,[FullPathBackup]
      ,[DiffPathBackup]
      ,[LogPathBackup]
      ,[InsertUTCDate]
  FROM [srv].[BackupSettings];
GO

Данное представление дает возможность быстро увидеть какие базы данных участвуют в резервном копировании.

Теперь создадим представление, которое выводит информацию по файлам БД из системного представления sys.master_files:

Представление по файлам БД

USE [ИМЯ_БД]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE view [inf].[ServerDBFileInfo] as
SELECT  @@Servername AS Server ,
        File_id ,--Идентификатор файла в базе данных. Основное значение file_id всегда равно 1
        Type_desc ,--Описание типа файла
        Name as [FileName] ,--Логическое имя файла в базе данных
        LEFT(Physical_Name, 1) AS Drive ,--Метка тома, где располагается файл БД
        Physical_Name ,--Полное имя файла в операционной системе
        RIGHT(physical_name, 3) AS Ext ,--Расширение файла
        Size as CountPage, --Текущий размер файла в страницах по 8 КБ
		round((cast(Size*8 as float))/1024,3) as SizeMb, --Размер файла в МБ
		round((cast(Size*8 as float))/1024/1024,3) as SizeGb, --Размер файла в ГБ
        case when is_percent_growth=0 then Growth*8 else 0 end as Growth, --Прирост файла в страницах по 8 КБ
		case when is_percent_growth=0 then round((cast(Growth*8 as float))/1024,3) end as GrowthMb, --Прирост файла в МБ
		case when is_percent_growth=0 then round((cast(Growth*8 as float))/1024/1024,3) end as GrowthGb, --Прирост файла в ГБ
		case when is_percent_growth=1 then Growth else 0 end as GrowthPercent, --Прирост файла в целых процентах
		is_percent_growth, --Признак процентного приращения
		database_id,
		DB_Name(database_id) as [DB_Name],
		State,--состояние файла
		state_desc as StateDesc,--описание состояния файла
		is_media_read_only as IsMediaReadOnly,--файл находится на носителе только для чтения (0-и для записи)
		is_read_only as IsReadOnly,--файл помечен как файл только для чтения (0-и записи)
		is_sparse as IsSpace,--разреженный файл
		is_name_reserved as IsNameReserved,--1 - Имя удаленного файла, доступно для использования.
		--Необходимо получить резервную копию журнала, прежде чем удастся повторно использовать имя (аргументы name или physical_name) для нового имени файла
		--0 - Имя файла, недоступно для использовани
		create_lsn as CreateLsn,--Регистрационный номер транзакции в журнале (LSN), на котором создан файл
		drop_lsn as DropLsn,--Номер LSN, с которым файл удален
		read_only_lsn as ReadOnlyLsn,--Номер LSN, на котором файловая группа, содержащая файл, изменила тип с «для чтения и записи» на «только для чтения» (самое последнее изменение)
		read_write_lsn as ReadWriteLsn,--Номер LSN, на котором файловая группа, содержащая файл, изменила тип с «только для чтения» на «для чтения и записи» (самое последнее изменение)
		differential_base_lsn as DifferentialBaseLsn,--Основа для разностных резервных копий. Экстенты данных, измененных после того, как этот номер LSN будет включен в разностную резервную копию
		differential_base_guid as DifferentialBaseGuid,--Уникальный идентификатор базовой резервной копии, на которой будет основываться разностная резервная копия
		differential_base_time as DifferentialBaseTime,--Время, соответствующее differential_base_lsn
		redo_start_lsn as RedoStartLsn,--Номер LSN, с которого должен начаться следующий накат
		--Равно NULL, за исключением случаев, когда значение аргумента state = RESTORING или значение аргумента state = RECOVERY_PENDING
		redo_start_fork_guid as RedoStartForkGuid,--Уникальный идентификатор точки вилки восстановления.
		--Значение аргумента first_fork_guid следующей восстановленной резервной копии журнала должно соответствовать этому значению. Это отражает текущее состояние контейнера
		redo_target_lsn as RedoTargetLsn,--Номер LSN, на котором накат в режиме «в сети» по данному файлу может остановиться
		--Равно NULL, за исключением случаев, когда значение аргумента state = RESTORING или значение аргумента state = RECOVERY_PENDING
		redo_target_fork_guid as RedoTargetForkGuid,--Вилка восстановления, на которой может быть восстановлен контейнер. Используется в паре с redo_target_lsn
		backup_lsn as BackupLsn--Номер LSN самых новых данных или разностная резервная копия файла
FROM    sys.master_files--database_files;
GO

Для создания полных резервных копий реализуем хранимую процедуру:

Процедура по созданию полных резервных копий

USE [ИМЯ_БД]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [srv].[RunFullBackupDB]
	@ClearLog bit=1 --сокращать ли размер журнала транзакций 
AS
BEGIN
	/*
		Создание полной резервной копии БД с предварительной проверкой на целостность самой БД
	*/
	SET NOCOUNT ON;

    declare @dt datetime=getdate();
	declare @year int=YEAR(@dt);
	declare @month int=MONTH(@dt);
	declare @day int=DAY(@dt);
	declare @hour int=DatePart(hour, @dt);
	declare @minute int=DatePart(minute, @dt);
	declare @second int=DatePart(second, @dt);
	declare @pathBackup nvarchar(255);
	declare @pathstr nvarchar(255);
	declare @DBName nvarchar(255);
	declare @backupName nvarchar(255);
	declare @sql nvarchar(max);
	declare @backupSetId as int;
	declare @FileNameLog nvarchar(255);

	declare @tbllog table(
		[DBName] [nvarchar](255) NOT NULL,
		[FileNameLog] [nvarchar](255) NOT NULL
	);
	
	declare @tbl table (
		[DBName] [nvarchar](255) NOT NULL,
		[FullPathBackup] [nvarchar](255) NOT NULL
	);
	
	--получаем названия БД и полные пути для создания полных резервных копий
	insert into @tbl (
	           [DBName]
	           ,[FullPathBackup]
	)
	select		DB_NAME([DBID])
	           ,[FullPathBackup]
	from [srv].[BackupSettings];

	--получаем названия БД и полные названия файлов соответствующих журналов транзакций (т к у одной базы данных их может быть несколько)
	insert into @tbllog([DBName], [FileNameLog])
	select t.[DBName], tt.[FileName] as [FileNameLog]
	from @tbl as t
	inner join [inf].[ServerDBFileInfo] as tt on t.[DBName]=DB_NAME(tt.[database_id])
	where tt.[Type_desc]='LOG';
	
	--далее последовательно обрабатываем каждую полученную ранее БД
	while(exists(select top(1) 1 from @tbl))
	begin
		set @backupSetId=NULL;

		select top(1)
		@DBName=[DBName],
		@pathBackup=[FullPathBackup]
		from @tbl;
	
		set @backupName=@DBName+N'_Full_backup_'+cast(@year as nvarchar(255))+N'_'+cast(@month as nvarchar(255))+N'_'+cast(@day as nvarchar(255))--+N'_'
						--+cast(@hour as nvarchar(255))+N'_'+cast(@minute as nvarchar(255))+N'_'+cast(@second as nvarchar(255));
		set @pathstr=@pathBackup+@backupName+N'.bak';

		--осуществляем проверку на целостность БД
		set @sql=N'DBCC CHECKDB(N'+N''''+@DBName+N''''+N')  WITH NO_INFOMSGS';

		exec(@sql);
		
		--запускаем непосредственно процедуру резервного копирования
		set @sql=N'BACKUP DATABASE ['+@DBName+N'] TO DISK = N'+N''''+@pathstr+N''''+
				 N' WITH NOFORMAT, NOINIT, NAME = N'+N''''+@backupName+N''''+
				 N', CHECKSUM, STOP_ON_ERROR, SKIP, REWIND, COMPRESSION, STATS = 10;';
	
		exec(@sql);

		--проверяем созданную резервную копию БД
		select @backupSetId = position
		from msdb..backupset where database_name=@DBName
		and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=@DBName);

		set @sql=N'Ошибка верификации. Сведения о резервном копировании для базы данных "'+@DBName+'" не найдены.';

		if @backupSetId is null begin raiserror(@sql, 16, 1) end
		else
		begin
			set @sql=N'RESTORE VERIFYONLY FROM DISK = N'+''''+@pathstr+N''''+N' WITH FILE = '+cast(@backupSetId as nvarchar(255));

			exec(@sql);
		end

		--ужимаем журналы транзакций БД
		if(@ClearLog=1)
		begin
			while(exists(select top(1) 1 from @tbllog where [DBName]=@DBName))
			begin
				select top(1)
				@FileNameLog=FileNameLog
				from @tbllog
				where DBName=@DBName;
			
				set @sql=N'USE ['+@DBName+N'];'+N' DBCC SHRINKFILE (N'+N''''+@FileNameLog+N''''+N' , 0, TRUNCATEONLY)';

				exec(@sql);

				delete from @tbllog
				where FileNameLog=@FileNameLog
				and DBName=@DBName;
			end
		end
		
		delete from @tbl
		where [DBName]=@DBName;
	end
END
GO

По коду видно, что данная хранимая процедура сразу решает все оставшиеся пункты алгоритма по созданию полных резервных копий.

Аналогично реализуются хранимые процедуры по созданию разностных резервных копий и резервных копий журналов транзакций:

Процедура по созданию разностных резервных копий БД

USE [ИМЯ_БД]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [srv].[RunDiffBackupDB]
	@ClearLog bit=1 --сокращать ли размер журнала транзакций  
AS
BEGIN
	/*
		Создание разностной резервной копии БД
	*/
	SET NOCOUNT ON;

    declare @dt datetime=getdate();
	declare @year int=YEAR(@dt);
	declare @month int=MONTH(@dt);
	declare @day int=DAY(@dt);
	declare @hour int=DatePart(hour, @dt);
	declare @minute int=DatePart(minute, @dt);
	declare @second int=DatePart(second, @dt);
	declare @pathBackup nvarchar(255);
	declare @pathstr nvarchar(255);
	declare @DBName nvarchar(255);
	declare @backupName nvarchar(255);
	declare @sql nvarchar(max);
	declare @backupSetId as int;
	declare @FileNameLog nvarchar(255);
	
	declare @tbl table (
		[DBName] [nvarchar](255) NOT NULL,
		[DiffPathBackup] [nvarchar](255) NOT NULL
	);

	declare @tbllog table(
		[DBName] [nvarchar](255) NOT NULL,
		[FileNameLog] [nvarchar](255) NOT NULL
	);
	
	--получаем названия БД и полные пути для создания разностных резервных копий
	insert into @tbl (
	           [DBName]
	           ,[DiffPathBackup]
	)
	select		DB_NAME([DBID])
	           ,[DiffPathBackup]
	from [srv].[BackupSettings]
	where [DiffPathBackup] is not null;

	--получаем названия БД и полные названия файлов соответствующих журналов транзакций (т к у одной базы данных их может быть несколько)
	insert into @tbllog([DBName], [FileNameLog])
	select t.[DBName], tt.[FileName] as [FileNameLog]
	from @tbl as t
	inner join [inf].[ServerDBFileInfo] as tt on t.[DBName]=DB_NAME(tt.[database_id])
	where tt.[Type_desc]='LOG';
	
	--далее последовательно обрабатываем каждую полученную ранее БД
	while(exists(select top(1) 1 from @tbl))
	begin
		set @backupSetId=NULL;

		select top(1)
		@DBName=[DBName],
		@pathBackup=[DiffPathBackup]
		from @tbl;
	
		set @backupName=@DBName+N'_Diff_backup_'+cast(@year as nvarchar(255))+N'_'+cast(@month as nvarchar(255))+N'_'+cast(@day as nvarchar(255))+N'_'
						+cast(@hour as nvarchar(255))+N'_'+cast(@minute as nvarchar(255))+N'_'+cast(@second as nvarchar(255));
		set @pathstr=@pathBackup+@backupName+N'.bak';
		
		--осуществляем проверку на целостность БД
		set @sql=N'DBCC CHECKDB(N'+N''''+@DBName+N''''+N')  WITH NO_INFOMSGS';

		exec(@sql);
		
		--запускаем непосредственно процедуру резервного копирования
		set @sql=N'BACKUP DATABASE ['+@DBName+N'] TO DISK = N'+N''''+@pathstr+N''''+
				 N' WITH DIFFERENTIAL, NOFORMAT, NOINIT, NAME = N'+N''''+@backupName+N''''+
				 N', CHECKSUM, STOP_ON_ERROR, SKIP, REWIND, COMPRESSION, STATS = 10;';
	
		exec(@sql);

		--проверяем созданную резервную копию БД
		select @backupSetId = position
		from msdb..backupset where database_name=@DBName
		and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=@DBName);

		set @sql=N'Ошибка верификации. Сведения о резервном копировании для базы данных "'+@DBName+'" не найдены.';

		if @backupSetId is null begin raiserror(@sql, 16, 1) end
		else
		begin
			set @sql=N'RESTORE VERIFYONLY FROM DISK = N'+''''+@pathstr+N''''+N' WITH FILE = '+cast(@backupSetId as nvarchar(255));

			exec(@sql);
		end

		--ужимаем журналы транзакций БД
		if(@ClearLog=1)
		begin
			while(exists(select top(1) 1 from @tbllog where [DBName]=@DBName))
			begin
				select top(1)
				@FileNameLog=FileNameLog
				from @tbllog
				where DBName=@DBName;
			
				set @sql=N'USE ['+@DBName+N'];'+N' DBCC SHRINKFILE (N'+N''''+@FileNameLog+N''''+N' , 0, TRUNCATEONLY)';

				exec(@sql);

				delete from @tbllog
				where FileNameLog=@FileNameLog
				and DBName=@DBName;
			end
		end
		
		delete from @tbl
		where [DBName]=@DBName;
	end
END
GO

Т к проверка целостности БД – достаточно ресурсоемкая задача, то для повышения быстродействия можно не проверять на целостность БД перед созданием разностной резервной копии БД.

Процедура по созданию резервных копий журналов транзакций

USE [ИМЯ_БД]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [srv].[RunLogBackupDB]
	@ClearLog bit=1 --сокращать ли размер журнала транзакций 
AS
BEGIN
	/*
		Создание резервной копии журнала транзакции БД
	*/
	SET NOCOUNT ON;

    declare @dt datetime=getdate();
	declare @year int=YEAR(@dt);
	declare @month int=MONTH(@dt);
	declare @day int=DAY(@dt);
	declare @hour int=DatePart(hour, @dt);
	declare @minute int=DatePart(minute, @dt);
	declare @second int=DatePart(second, @dt);
	declare @pathBackup nvarchar(255);
	declare @pathstr nvarchar(255);
	declare @DBName nvarchar(255);
	declare @backupName nvarchar(255);
	declare @sql nvarchar(max);
	declare @backupSetId as int;
	declare @FileNameLog nvarchar(255);
	
	declare @tbl table (
		[DBName] [nvarchar](255) NOT NULL,
		[LogPathBackup] [nvarchar](255) NOT NULL
	);

	declare @tbllog table(
		[DBName] [nvarchar](255) NOT NULL,
		[FileNameLog] [nvarchar](255) NOT NULL
	);
	
	--получаем названия БД и полные пути для создания резервных копий журналов транзакций, у которых модель восстановления не простая (полная или с неполным протоколированием)
	--и за исключением системных БД
	insert into @tbl (
	           [DBName]
	           ,[LogPathBackup]
	)
	select		DB_NAME(b.[DBID])
	           ,b.[LogPathBackup]
	from [srv].[BackupSettings] as b
	inner join sys.databases as d on b.[DBID]=d.[database_id]
	where d.recovery_model<3
	and DB_NAME([DBID]) not in (
		N'master',
		N'tempdb',
		N'model',
		N'msdb',
		N'ReportServer',
		N'ReportServerTempDB'
	)
	and [LogPathBackup] is not null;

	--получаем названия БД и полные названия файлов соответствующих журналов транзакций (т к у одной базы данных их может быть несколько)
	insert into @tbllog([DBName], [FileNameLog])
	select t.[DBName], tt.[FileName] as [FileNameLog]
	from @tbl as t
	inner join [inf].[ServerDBFileInfo] as tt on t.[DBName]=DB_NAME(tt.[database_id])
	where tt.[Type_desc]='LOG';
	
	--далее последовательно обрабатываем каждую полученную ранее БД
	while(exists(select top(1) 1 from @tbl))
	begin
		set @backupSetId=NULL;

		select top(1)
		@DBName=[DBName],
		@pathBackup=[LogPathBackup]
		from @tbl;
	
		set @backupName=@DBName+N'_Log_backup_'+cast(@year as nvarchar(255))+N'_'+cast(@month as nvarchar(255))+N'_'+cast(@day as nvarchar(255))+N'_'
						+cast(@hour as nvarchar(255))+N'_'+cast(@minute as nvarchar(255))+N'_'+cast(@second as nvarchar(255));
		set @pathstr=@pathBackup+@backupName+N'.trn';
		
		--запускаем непосредственно процедуру резервного копирования
		set @sql=N'BACKUP LOG ['+@DBName+N'] TO DISK = N'+N''''+@pathstr+N''''+
				 N' WITH NOFORMAT, NOINIT, NAME = N'+N''''+@backupName+N''''+
				 N', CHECKSUM, STOP_ON_ERROR, SKIP, REWIND, COMPRESSION, STATS = 10;';
	
		exec(@sql);

		--проверяем созданную резервную копию журнала транзакций
		select @backupSetId = position
		from msdb..backupset where database_name=@DBName
		and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=@DBName);

		set @sql=N'Ошибка верификации. Сведения о резервном копировании для базы данных "'+@DBName+'" не найдены.';

		if @backupSetId is null begin raiserror(@sql, 16, 1) end
		else
		begin
			set @sql=N'RESTORE VERIFYONLY FROM DISK = N'+''''+@pathstr+N''''+N' WITH FILE = '+cast(@backupSetId as nvarchar(255));

			exec(@sql);
		end

		--ужимаем журналы транзакций БД
		if(@ClearLog=1)
		begin
			while(exists(select top(1) 1 from @tbllog where [DBName]=@DBName))
			begin
				select top(1)
				@FileNameLog=FileNameLog
				from @tbllog
				where DBName=@DBName;
			
				set @sql=N'USE ['+@DBName+N'];'+N' DBCC SHRINKFILE (N'+N''''+@FileNameLog+N''''+N' , 0, TRUNCATEONLY)';

				exec(@sql);

				delete from @tbllog
				where FileNameLog=@FileNameLog
				and DBName=@DBName;
			end
		end
		
		delete from @tbl
		where [DBName]=@DBName;
	end
END
GO

Т к резервная копия журнала транзакций обычно делается достаточно часто, а проверка целостности БД – достаточно ресурсоемкая задача, то обычно не проверяют целостность БД перед созданием резервной копии журнала транзакций.

Также необходимо помнить о том, что периодически нужно делать полные резервные копии БД master, msdb и model.

Для автоматизации процесса по созданию резервных копий, достаточно поместить вызов реализованных выше хранимых процедур в Планировщик заданий Windows, в задачи Агента или в любой другой доступный сервис.

Частоту вызовов по каждой хранимой процедуре необходимо подбирать индивидуально, исходя из пиков загрузки, периодов простоя, и т. д.

Наиболее общий подход:

1) Создание полной резервной копии 1 раз в день
2) Создание разностных резервных копий каждые 2-4 часа
3) Создание резервных копий журнала транзакций каждые 5-60 минут

Также важно помнить о том, что обычно базы данных участвуют в системе отказоустойчивости и быстрой доступности. И если в последнем используются резервные копии журналов транзакций, то важно не мешать этому процессу (т е нельзя допускать создания резервных копий журнала транзакций БД разными процессами, т к тогда будет потеряна последовательность для восстановления из этих резервных копий).

Здесь были приведены примеры последовательной обработки каждой БД. Но в производстве вполне возможно распараллелить обработку, делая несколько резервных копий одновременно. Это можно сделать разными способами. Например, вызовом следующей хранимой процедуры:

Процедура для асинхронного вызова запросов

USE [ИМЯ_БД]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [inf].[RunAsyncExecute]
(
	@sql nvarchar(max),
	@jobname nvarchar(57) = null,   
	@database nvarchar(128)= null,
	@owner nvarchar(128) = null
)
AS BEGIN
/*
	асинхронный вызов пакета через задания Агента
	RunAsyncExecute - asynchronous execution of T-SQL command or stored prodecure  
	2012 Antonin Foller, Motobit Software, www.motobit.com
	http://www.motobit.com/tips/detpg_async-execute-sql/  
*/  
    SET NOCOUNT ON;  
  
    declare @id uniqueidentifier;

    --Create unique job name if the name is not specified  
    if (@jobname is null) set @jobname= '';

    set @jobname = @jobname + '_async_' + convert(varchar(64),NEWID());
  
    if (@owner is null) set @owner = 'sa';
  
    --Create a new job, get job ID  
    execute msdb..sp_add_job @jobname, @owner_login_name=@owner, @job_id=@id OUTPUT;
  
    --Specify a job server for the job  
    execute msdb..sp_add_jobserver @job_id=@id;
  
    --Specify a first step of the job - the SQL command  
    --(@on_success_action = 3 ... Go to next step)  
    execute msdb..sp_add_jobstep @job_id=@id, @step_name='Step1', @command = @sql,   
        @database_name = @database, @on_success_action = 3;
  
    --Specify next step of the job - delete the job  
    declare @deletecommand varchar(200);

    set @deletecommand = 'execute msdb..sp_delete_job @job_name='''+@jobname+'''';

    execute msdb..sp_add_jobstep @job_id=@id, @step_name='Step2', @command = @deletecommand;
  
    --Start the job  
    execute msdb..sp_start_job @job_id=@id;
  
END  
GO

Здесь асинхронность достигается путем динамического создания заданий Агента с последующим их выполнением и удалением.

Теперь приведем общий алгоритм по восстановлению базы данных из созданных ранее резервных копий (в другой или тестовой среде):

1) Определяем какие базы данных нужно восстановить, а также место нахождения резервных копий
2) Восстанавливаем базы данных
3) Проверяем целостность восстановленных баз данных

Далее приведем пример реализации алгоритма по восстановлению базы данных из полной резервной копии. Для разностной процедура аналогичная, но сначала восстанавливается полная резервная копия, а затем разностная.

Для того, чтобы определить какие базы данных нужно восстановить, а также место нахождения резервных копий, создадим две таблицы:

Таблица настройки восстановления БД

USE [ИМЯ_БД]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [srv].[RestoreSettings](
	[DBName] [nvarchar](255) NOT NULL,
	[FullPathRestore] [nvarchar](255) NOT NULL,
	[DiffPathRestore] [nvarchar](255) NOT NULL,
	[LogPathRestore] [nvarchar](255) NOT NULL,
	[InsertUTCDate] [datetime] NOT NULL,
 CONSTRAINT [PK_RestoreSettings] PRIMARY KEY CLUSTERED 
(
	[DBName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
GO

ALTER TABLE [srv].[RestoreSettings] ADD  CONSTRAINT [DF_RestoreSettings_InsertUTCDate]  DEFAULT (getutcdate()) FOR [InsertUTCDate];
GO

Здесь назначения колонок аналогичны назначениям колонок для таблицы [srv].[BackupSettings] с той лишь разницей, что по полному пути будут не создаваться резервные копии, а браться существующие для восстановления.

Таблица файлов БД для восстановления

USE [ИМЯ_БД]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [srv].[RestoreSettingsDetail](
	[Row_GUID] [uniqueidentifier] NOT NULL,
	[DBName] [nvarchar](255) NOT NULL,
	[SourcePathRestore] [nvarchar](255) NOT NULL,
	TargetPathRestore [nvarchar](255) NOT NULL,
	[Ext] [nvarchar](255) NOT NULL,
	[InsertUTCDate] [datetime] NOT NULL,
 CONSTRAINT [PK_RestoreSettingsDetail] PRIMARY KEY CLUSTERED 
(
	[Row_GUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
GO

ALTER TABLE [srv].[RestoreSettingsDetail] ADD  CONSTRAINT [DF_RestoreSettingsDetail_Row_GUID]  DEFAULT (newid()) FOR [Row_GUID];
GO

ALTER TABLE [srv].[RestoreSettingsDetail] ADD  CONSTRAINT [DF_RestoreSettingsDetail_InsertUTCDate]  DEFAULT (getutcdate()) FOR [InsertUTCDate];
GO

Данная таблица нужна, чтобы определить полные названия файлов восстанавливаемой БД для последующего переноса (например, [SourcePathRestore]='Логическое имя файла' и [TargetPathRestore]= 'диск:\...\Физическое имя файла', а [Ext]= 'Расширение файла'.

На самом деле здесь можно определить логические имена файлов БД по следующему запросу:

Получение логических имен файлов БД

RESTORE FILELISTONLY 
FROM DISK ='диск:\...\резервная копия.BAK';

А получить информацию о резервных копиях, которые находятся в файле, можно следующим образом:

Получение информации о резервных копиях БД

RESTORE HEADERONLY
FROM DISK='диск:\...\резервная копия.BAK';

Теперь приведем пример реализации хранимой процедуры по восстановлению БД из полной резервной копии с последующей проверкой на целостность данных:

Процедура восстановления БД по полным резервным копиям

USE [ИМЯ_БД]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [srv].[RunFullRestoreDB]
AS
BEGIN
	/*
		Восстановление из полной резервной копии БД с последующей проверкой на целостность самой БД
	*/
	SET NOCOUNT ON;

    declare @dt datetime=DateAdd(day,-2,getdate());
	declare @year int=YEAR(@dt);
	declare @month int=MONTH(@dt);
	declare @day int=DAY(@dt);
	declare @hour int=DatePart(hour, @dt);
	declare @minute int=DatePart(minute, @dt);
	declare @second int=DatePart(second, @dt);
	declare @pathBackup nvarchar(255);
	declare @pathstr nvarchar(255);
	declare @DBName nvarchar(255);
	declare @backupName nvarchar(255);
	declare @sql nvarchar(max);
	declare @backupSetId as int;
	declare @FileNameLog nvarchar(255);
	declare @SourcePathRestore nvarchar(255);
	declare @TargetPathRestore nvarchar(255);
	declare @Ext nvarchar(255);
	
	declare @tbl table (
		[DBName] [nvarchar](255) NOT NULL,
		[FullPathRestore] [nvarchar](255) NOT NULL
	);

	declare @tbl_files table (
		[DBName] [nvarchar](255) NOT NULL,
		[SourcePathRestore] [nvarchar](255) NOT NULL,
		[TargetPathRestore] [nvarchar](255) NOT NULL,
		[Ext] [nvarchar](255) NOT NULL
	);
	
	--получаем список имен БД и полных путей к полным резервным копиям БД
	insert into @tbl (
	           [DBName]
	           ,[FullPathRestore]
	)
	select		[DBName]
	           ,[FullPathRestore]
	from [srv].[RestoreSettings];

	--получаем детальную информацию по тому, куда переносить файлы БД
	insert into @tbl_files (
	           [DBName]
	           ,[SourcePathRestore]
			   ,[TargetPathRestore]
			   ,[Ext]
	)
	select		[DBName]
	           ,[SourcePathRestore]
			   ,[TargetPathRestore]
			   ,[Ext]
	from [srv].[RestoreSettingsDetail];
	
	--обрабатываем каждую полученную БД
	while(exists(select top(1) 1 from @tbl))
	begin
		set @backupSetId=NULL;

		select top(1)
		@DBName=[DBName],
		@pathBackup=[FullPathRestore]
		from @tbl;
	
		set @backupName=@DBName+N'_Full_backup_'+cast(@year as nvarchar(255))+N'_'+cast(@month as nvarchar(255))+N'_'+cast(@day as nvarchar(255))--+N'_'
						--+cast(@hour as nvarchar(255))+N'_'+cast(@minute as nvarchar(255))+N'_'+cast(@second as nvarchar(255));
		set @pathstr=@pathBackup+@backupName+N'.bak';

		--формируем запрос на восстановление и вызываем его
		set @sql=N'RESTORE DATABASE ['+@DBName+N'_Restore] FROM DISK = N'+N''''+@pathstr+N''''+
				 N' WITH FILE = 1,';

		while(exists(select top(1) 1 from @tbl_files where [DBName]=@DBName))
		begin
			select top(1)
			@SourcePathRestore=[SourcePathRestore],
			@TargetPathRestore=[TargetPathRestore],
			@Ext=[Ext]
			from @tbl_files
			where [DBName]=@DBName;

			set @sql=@sql+N' MOVE N'+N''''+@SourcePathRestore+N''''+N' TO N'+N''''+@TargetPathRestore+N'_Restore.'+@Ext+N''''+N',';

			delete from @tbl_files
			where [DBName]=@DBName
			and [SourcePathRestore]=@SourcePathRestore
			and [Ext]=@Ext;
		end

		set @sql=@sql+N' NOUNLOAD,  REPLACE,  STATS = 5';

		exec(@sql);

		--проверяем на целотность БД
		set @sql=N'DBCC CHECKDB(N'+N''''+@DBName+'_Restore'+N''''+N')  WITH NO_INFOMSGS';
	
		exec(@sql);
		
		delete from @tbl
		where [DBName]=@DBName;
	end
END

Здесь, чтоб определить, какую полную резервную копию нужно восстанавливать, берётся имя файла, которое формируется следующим образом:
<название БД>_Full_backup_<год>_<номер_месяца_в_году>_<номер_дня_в_месяце>.bak

Для автоматизации процесса по восстановлению БД из полных резервных копий, достаточно поместить вызов реализованной выше хранимой процедуры в Планировщик заданий Windows, в задачи Агента или в любой другой доступный сервис.

Результат

В данной статье был рассмотрен пример реализации автоматизированного процесса резервного копирования на одном сервере с последующим восстановлением на другом сервере (например, тестовом).

Данный метод позволяет автоматизировать процесс создания резервных копий, проверить резервные копии методом восстановления, а также тонко настроить приведенные выше процессы.

Источники:

» Лекция 5: Полная модель восстановления
» Backup
» Restore
» Backupset
» CHECKDB
» SHRINKFILE
» sys.master_files
» sp_async_execute

Комментарии (2)

Vicking
01.12.2017 12:05
#10547246
+1
Спасибо за идею с асинхронным запуском через агента.
1. jobgemws Автор
  01.12.2017 12:06
  #10547254
  Почему-то так и знал, что это будет полезно, т к в свое время тоже долго намучался с асинхронным запуском, и Service Broker тогда увы не спас, хотя очень хорош для общения между экземплярами скуля и для работы с приллжениями