Автоматизируем "перезаливку" баз (Скрипты для SQL-Server - Часть 1)

08.06.18

Предлагаю вашему вниманию несколько SQL-скриптов (для MS SQL-Server) для автоматизации процесса резервного копирования и восстановления баз данных. Скрипты для восстановления позволяют "перезаливать" базы данных, т. е. восстанавливать одну базу из копий другой базы данных. Все скрипты формируют лог для вывода и при необходимости могут отправлять результат работы по электронной почте.

Скачать файл

ВНИМАНИЕ: Файлы из Базы знаний - это исходный код разработки. Это примеры решения задач, шаблоны, заготовки, "строительные материалы" для учетной системы. Файлы ориентированы на специалистов 1С, которые могут разобраться в коде и оптимизировать программу для запуска в базе данных. Гарантии работоспособности нет. Возврата нет. Технической поддержки нет.

Наименование		По подписке [?]	Купить один файл
Все скрипты в архиве (часть 1) .zip 10,23Kb 47	47	Скачать (1 SM)	Купить за 1 850 руб.

Видео в формате вебинара с подробным разбором всех скриптов:

Ну и, непосредственно, сами разобранные в вебинаре скрипты под спойлерами ниже. Все переменные собраны в соответствующем блоке в начале скриптов. Для отправки электронной почты используется предварительно настроенный профиль электронной почты компоненты DataBase Mail. О настройке компоненты можно прочитать, например, здесь. Все скрипты много раз опробованы в бою и протестированы на версиях MS SQL 2008, 2012, 2016.

Скрипт для создания резервной копии указанной базы данных:

-------------------------------------------
-- НАСТРАИВАЕМЫЕ ПАРАМЕТРЫ
-- Имя базы данных для резервной копии
DECLARE @DBName as nvarchar(40) = 'WorkBase'
-- Каталог для резервной копии
DECLARE @Path as nvarchar(400) = 'E:\Backup_SQL'
-- Тип резервного копирования:
--		0 - Полная резервная копия с флагом "Только резервное копирование"
--		1 - Полная резервная копия
--		2 - Разностная резервная копия
--		3 - Копия журнала транзакций
DECLARE @Type as int = 1
-- Сжимать резервные копии:
--		0 - Не сжимать или по умолчанию
--		1 - Сжимать
DECLARE @Compression as int = 0
-- Имя почтового профиля, для отправки электонной почты									
DECLARE @profile_name as nvarchar(100) = 'Main'
-- Получатели сообщений электронной почты, разделенные знаком ";"				
DECLARE @recipients as nvarchar(500) = 'admin@mydomen.com'

-------------------------------------------
-- СЛУЖЕБНЫЕ ПЕРЕМЕННЫЕ
DECLARE @SQLString NVARCHAR(4000)
DECLARE @subject as NVARCHAR(100) = ''
DECLARE @finalmassage as NVARCHAR(1000) = ''

-------------------------------------------
-- ТЕЛО СКРИПТА
use master

-- Формируем строку для исполнения
IF @Type = 3 SET @SQLString = 
	N'BACKUP LOG [' + @DBName + ']
	TO DISK = N''' + @Path + '\\' + @DBName + '_' + Replace(CONVERT(nvarchar, GETDATE(), 126),':','-') + '.trn'' '
ELSE SET @SQLString = 
	N'BACKUP DATABASE [' + @DBName + ']
	TO DISK = N''' + @Path + '\\' + @DBName + '_' + Replace(CONVERT(nvarchar, GETDATE(), 126),':','-') + '.bak'' '
set @SQLString = @SQLString +		  
	'WITH NOFORMAT, NOINIT,
	SKIP, NOREWIND, NOUNLOAD, STATS = 10'
IF @Compression = 1 SET @SQLString = @SQLString + ', COMPRESSION'
IF @Type = 0 SET @SQLString = @SQLString + ', COPY_ONLY'
IF @Type = 2 SET @SQLString = @SQLString + ', DIFFERENTIAL'

-- Выводим и выполняем полученную инструкцию
PRINT @SQLString
BEGIN TRY 
	EXEC sp_executesql @SQLString
END TRY
BEGIN CATCH  
	-- Ошбика выполнения операции
	SET @subject = 'ОШИБКА Создания резервной копии базы ' + @DBName
	SET @finalmassage = 'Ошибка создания резервной копии базы ' + @DBName + ' в каталог ' + @Path + CHAR(13) + CHAR(13)
		+ 'Код ошибки: ' + CAST(ERROR_NUMBER() as nvarchar(10)) + CHAR(13) + CHAR(13)
		+ 'Текст ошибки: ' + ERROR_MESSAGE()  + CHAR(13) + CHAR(13)
		+ 'Текст T-SQL:' + CHAR(13) + @SQLString  
END CATCH;

-- Если ошибок не было, сформируем текст сообщения
IF @subject = ''
BEGIN
	-- Успешное выполнение всех операций
	SET @subject = 'Успешное создание резервной копии базы ' + @DBName
	SET @finalmassage = 'Успешное создание резревной копии базы ' + @DBName + ' в каталог ' + @Path
END

-- Если задан профиль электронной почты, отправим сообщение
IF @profile_name <> ''
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = @profile_name,
    @recipients = @recipients,
    @body = @finalmassage,
    @subject = @subject;

-- Выводим сообщение о результате
SELECT
	@subject as subject,
	@finalmassage as finalmassage

GO

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

-------------------------------------------
-- НАСТРАИВАЕМЫЕ ПАРАМЕТРЫ
-- Условие для выборки, '%' - все базы данных 
DECLARE @namelike varchar(100) = 'Work%'
-- Каталог для резервной копии
DECLARE @Path as nvarchar(400) = 'E:\Backup_SQL'
-- Тип резервного копирования:
--		0 - Полная резервная копия с флагом "Только резервное копирование"
--		1 - Полная резервная копия
--		2 - Разностная резервная копия
--		3 - Копия журнала транзакций
DECLARE @Type as int = 0
-- Сжимать резервные копии:
--		0 - Не сжимать или по умолчанию
--		1 - Сжимать
DECLARE @Compression as int = 0
-- Имя почтового профиля, для отправки электонной почты									
DECLARE @profilename as nvarchar(100) = 'ОсновнойПрофиль'
-- Получатели сообщений электронной почты, разделенные знаком ";"				
DECLARE @recipients as nvarchar(500) = 'admin@mydomen.com'

-------------------------------------------
-- СЛУЖЕБНЫЕ ПЕРЕМЕННЫЕ
DECLARE @SQLString NVARCHAR(4000)
DECLARE @DBName varchar(100)
DECLARE @subdir NVARCHAR(400) = ''
DECLARE @subject as NVARCHAR(100) = ''
DECLARE @finalmassage as NVARCHAR(1000) = ''

-------------------------------------------
-- ТЕЛО СКРИПТА
use master

-- Отбоерем базы для выполнения операций
DECLARE DBcursor CURSOR FOR 
(
	SELECT d.name as DatabaseName 
	FROM sys.databases d
	WHERE d.name <> 'tempdb'
		AND d.name <> 'master'
		AND d.name <> 'model'
		AND d.name <> 'msdb'
		AND d.state_desc = 'ONLINE' -- база должна быть в сети
		AND d.name like @namelike -- база должна содержать указанное слово  
)

-- Цикл по всем базам, попавшим в выборку
OPEN DBcursor
FETCH NEXT FROM DBcursor INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN

	-- Создаем вложенный каталог с именем базы
	SET @subdir = @Path + '\\' + @DBName
	BEGIN TRY 
		EXEC master.dbo.xp_create_subdir @subdir 
	END TRY
	BEGIN CATCH
		-- Ошбика выполнения операции
		SET @finalmassage = @finalmassage + 'Ошибка создания каталога: ' + @subdir + CHAR(13) + CHAR(13)
			+ 'Код ошибки: ' + CAST(ERROR_NUMBER() as nvarchar(10)) + CHAR(13) + CHAR(13)
			+ 'Текст ошибки: ' + ERROR_MESSAGE()  + CHAR(13) + CHAR(13)
			+ 'Текст T-SQL:' + CHAR(13) + @SQLString + CHAR(13) + CHAR(13) 
		SET @subdir = '' 
	END CATCH;
	
	IF @subdir <> ''
	BEGIN
		
		-- Формируем строку для исполнения
		IF @Type = 3 SET @SQLString = 
			N'BACKUP LOG [' + @DBName + ']
			TO DISK = N''' + @subdir + '\\' + @DBName + '_' + Replace(CONVERT(nvarchar, GETDATE(), 126),':','-') + '.trn'' '
		ELSE SET @SQLString = 
			N'BACKUP DATABASE [' + @DBName + ']
			TO DISK = N''' + @subdir + '\\' + @DBName + '_' + Replace(CONVERT(nvarchar, GETDATE(), 126),':','-') + '.bak'' '
		set @SQLString = @SQLString +		  
			'WITH NOFORMAT, NOINIT,
			SKIP, NOREWIND, NOUNLOAD, STATS = 10'
		IF @Compression = 1 SET @SQLString = @SQLString + ', COMPRESSION'
		IF @Type = 0 SET @SQLString = @SQLString + ', COPY_ONLY'
		IF @Type = 2 SET @SQLString = @SQLString + ', DIFFERENTIAL'

		-- Выводим и выполняем полученную инструкцию
		PRINT @SQLString
		BEGIN TRY
			EXEC sp_executesql @SQLString
		END TRY
		BEGIN CATCH  
			-- Ошбика выполнения операции
			SET @finalmassage = @finalmassage + 'Ошибка создания резервной копии базы ' + @DBName + ' в каталог ' + @subdir + CHAR(13) + CHAR(13)
				+ 'Код ошибки: ' + CAST(ERROR_NUMBER() as nvarchar(10)) + CHAR(13) + CHAR(13)
				+ 'Текст ошибки: ' + ERROR_MESSAGE()  + CHAR(13) + CHAR(13)
				+ 'Текст T-SQL:' + CHAR(13) + @SQLString + CHAR(13) + CHAR(13)  
		END CATCH;
	END
	
	-- Следующий элемент цикла
    FETCH NEXT FROM DBcursor 
    INTO @DBName

END
CLOSE DBcursor;
DEALLOCATE DBcursor;

-- Формируем сообщение об успешном или не успешном выполнении операций
IF @finalmassage = ''
BEGIN
	-- Успешное выполнение всех операций
	SET @subject = 'Успешное создание резервных копий баз данных '
	SET @finalmassage = 'Успешное создание резервных копий всех баз данных '
END
ELSE
	-- Были ошибки
	SET @subject = 'БЫЛИ ОШИБКИ при создании резервных копий баз данных '

-- Если задан профиль электронной почты, отправим сообщение
IF @profilename <> ''
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = @profilename,
    @recipients = @recipients,
    @body = @finalmassage,
    @subject = @subject;

-- Выводим сообщение о результате
SELECT
	@subject as subject, 
	@finalmassage as finalmassage 

GO

Скрипт для восстановления указанной базы данных из созданной резервной копии другой базы данных:

-------------------------------------------
-- НАСТРАИВАЕМЫЕ ПЕРЕМЕННЫЕ
-- База данных назначения
DECLARE @DBName_To as nvarchar(40) = 'TestBase'
-- База данных источник						
DECLARE @DBName_From as nvarchar(40) = 'WorkBase'
-- Каталог для резервной копии
DECLARE @Path as nvarchar(400) = 'E:\Backup_SQL'
-- Имя почтового профиля, для отправки электонной почты									
DECLARE @profile_name as nvarchar(100) = 'ОсновнойПрофиль'
-- Получатели сообщений электронной почты, разделенные знаком ";"				
DECLARE @recipients as nvarchar(500) = 'admin@mydomen.com'

-------------------------------------------
-- СЛУЖЕБНЫЕ ПЕРЕМЕННЫЕ	
DECLARE @SQLString NVARCHAR(4000)
DECLARE @backupfile NVARCHAR(500)
DECLARE @physicalName NVARCHAR(500), @logicalName NVARCHAR(500)
DECLARE @out as int = 0
DECLARE @subject as NVARCHAR(100) = ''
DECLARE @finalmassage as NVARCHAR(1000) = ''

-------------------------------------------
-- ТЕЛО СКРИПТА
use master

-- 1. Создаем резервную копию с флагом "Только резервное копирование"
-- Формируем строку для исполнения
SET @backupfile = @Path + '\\' + @DBName_From + '_' + Replace(CONVERT(nvarchar, GETDATE(), 126),':','-') + '.bak'
SET @SQLString = 
	N'BACKUP DATABASE [' + @DBName_From + ']
	TO DISK = N''' + @backupfile + '''  
	WITH NOFORMAT, NOINIT,
	SKIP, NOREWIND, NOUNLOAD, STATS = 10, COPY_ONLY'

-- Выводим и выполняем полученную инструкцию
PRINT @SQLString
BEGIN TRY 
	EXEC sp_executesql @SQLString
END TRY
BEGIN CATCH  
	-- Ошбика выполнения операции
	SET @subject = 'ОШИБКА Создания резервной копии базы ' + @DBName_From
	SET @finalmassage = 'Ошибка создания резервной копии базы ' + @DBName_From + ' в каталог ' + @Path + CHAR(13) + CHAR(13)
		+ 'Код ошибки: ' + CAST(ERROR_NUMBER() as nvarchar(10)) + CHAR(13) + CHAR(13)
		+ 'Текст ошибки: ' + ERROR_MESSAGE()  + CHAR(13) + CHAR(13)
		+ 'Текст T-SQL:' + CHAR(13) + @SQLString  
END CATCH;

-- 2. Загружаем полученный файл резервной копии
IF @subject = ''
BEGIN
	
	-- Формируем строку для исполнения	
	SET @SQLString = 
	N'RESTORE DATABASE [' + @DBName_To + ']
	FROM DISK = N''' + @backupfile + '''   
	WITH  
	FILE = 1,'

	-- Переименуем файлы базы данных на исходные
	-- Новый цикл по всем файлам базы данных
	DECLARE fnc CURSOR LOCAL FAST_FORWARD FOR 
	(
		SELECT
			t_From.name,
			t_To.physical_name
		FROM sys.master_files as t_To 
			join sys.master_files as t_From 
			on t_To.file_id = t_From.file_id
		WHERE t_To.database_id = DB_ID(@DBName_To) 
			and t_From.database_id = DB_ID(@DBName_From)
	)
	OPEN fnc;
	FETCH fnc INTO @logicalName, @physicalName;
	WHILE @@FETCH_STATUS=0
		BEGIN
			SET @SQLString = @SQLString + '
			MOVE N''' + @logicalName + ''' TO N''' + @physicalName + ''','
			FETCH fnc INTO @logicalName, @physicalName;
		END;
	CLOSE fnc;
	DEALLOCATE fnc;

	SET @SQLString = @SQLString + '
	RECOVERY,
	REPLACE,
	STATS = 5'

	-- Выводим и выполняем полученную инструкцию
	PRINT @SQLString
	BEGIN TRY 
		EXEC sp_executesql @SQLString
	END TRY
	BEGIN CATCH  
		-- Ошбика выполнения операции
		SET @subject = 'ОШИБКА ВОССТАНОВЛЕНИЯ базы данных ' + @DBName_To
			SET @finalmassage = 'Ошибка восстановления полной резервной копии для базы данных ' + @DBName_To + CHAR(13) + CHAR(13)
			+ 'Код ошибки: ' + CAST(ERROR_NUMBER() as nvarchar(10)) + CHAR(13) + CHAR(13)
			+ 'Текст ошибки: ' + ERROR_MESSAGE()  + CHAR(13) + CHAR(13)
			+ 'Текст T-SQL:' + CHAR(13) + @SQLString  
	END CATCH;
END

-- 3. Переводим базу в простую модель восстановления
IF @subject = '2'
BEGIN	
	
	-- Формируем строку для исполнения
	SET @SQLString = 'ALTER DATABASE ' + @DBName_To + ' SET RECOVERY SIMPLE;'
	
	-- Выводим и выполняем полученную инструкцию
	PRINT @SQLString
	BEGIN TRY 
		EXEC sp_executesql @SQLString
	END TRY
	BEGIN CATCH  
		-- Ошбика выполнения операции
		SET @subject = 'ОШИБКА ВОССТАНОВЛЕНИЯ базы данных ' + @DBName_To
		SET @finalmassage = 'Ошибка перевода в простую модель восстановления базы данных ' + @DBName_To + CHAR(13) + CHAR(13)
			+ 'Код ошибки: ' + CAST(ERROR_NUMBER() as nvarchar(10)) + CHAR(13) + CHAR(13)
			+ 'Текст ошибки: ' + ERROR_MESSAGE()  + CHAR(13) + CHAR(13)
			+ 'Текст T-SQL:' + CHAR(13) + @SQLString  
	END CATCH;
END

-- 4. Запускаем сжатие базы данных
IF @subject = '2'
BEGIN

	-- Формируем строку для исполнения
	SET @SQLString = 'DBCC SHRINKDATABASE(N''' + @DBName_To + ''');'
					
	-- Выводим и выполняем полученную инструкцию
	PRINT @SQLString
	BEGIN TRY 
		EXEC sp_executesql @SQLString
	END TRY
	BEGIN CATCH  
		-- Ошбика выполнения операции
		SET @subject = 'ОШИБКА ВОССТАНОВЛЕНИЯ базы данных ' + @DBName_To
		SET @finalmassage = 'Ошибка сжатия базы данных ' + @DBName_To + CHAR(13) + CHAR(13)
			+ 'Код ошибки: ' + CAST(ERROR_NUMBER() as nvarchar(10)) + CHAR(13) + CHAR(13)
			+ 'Текст ошибки: ' + ERROR_MESSAGE()  + CHAR(13) + CHAR(13)
			+ 'Текст T-SQL:' + CHAR(13) + @SQLString  
	END CATCH;
END	

-- 5. Если файл был создан, удалим файл резервной копии
BEGIN TRY
	EXEC master.dbo.xp_fileexist @backupfile, @out out
	IF @out = 1 EXEC master.dbo.xp_delete_file 0, @backupfile
END TRY
BEGIN CATCH  
	-- Ошбика выполнения операции
	SET @subject = 'ОШИБКА ВОССТАНОВЛЕНИЯ базы данных ' + @DBName_To
	SET @finalmassage = 'Ошибка удаления файла резервной копии ' + @backupfile + CHAR(13) + CHAR(13)
		+ 'Код ошибки: ' + CAST(ERROR_NUMBER() as nvarchar(10)) + CHAR(13) + CHAR(13)
		+ 'Текст ошибки: ' + ERROR_MESSAGE()  + CHAR(13) + CHAR(13)
		+ 'Текст T-SQL:' + CHAR(13) + 'master.dbo.xp_delete_file 0, ' + @backupfile  
END CATCH;
	
-- Если ошибок не было, сформируем текст сообщения
IF @subject = ''
BEGIN
	-- Успешное выполнение всех операций
	SET @subject = 'Успешное восстановление базы данных ' + @DBName_To
	SET @finalmassage = 'Успешное восстановление базы данных ' + @DBName_To + ' из резервной копии базы данных ' + @DBName_From
END

-- 6. Если задан профиль электронной почты, отправим сообщение
IF @profile_name <> ''
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = @profile_name,
    @recipients = @recipients,
    @body = @finalmassage,
    @subject = @subject;

-- Выводим сообщение о результате
SELECT
	@subject as subject,
	@finalmassage as finalmassage

GO

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

-------------------------------------------
-- НАСТРАИВАЕМЫЕ ПЕРЕМЕННЫЕ
-- База данных назначения
DECLARE @DBName_To as nvarchar(40) = 'TestBase'
-- База данных источник						
DECLARE @DBName_From as nvarchar(40) = 'WorkBase'
-- Дата, на котороую собирается цепочка файлов резервных копий, в формате '20160315 12:00:00'							
DECLARE @BackupTime as datetime = GETDATE()
-- Имя почтового профиля, для отправки электонной почты									
DECLARE @profile_name as nvarchar(100) = 'ОсновнойПрофиль'
-- Получатели сообщений электронной почты, разделенные знаком ";"				
DECLARE @recipients as nvarchar(500) = 'admin@mydomen.com'

-------------------------------------------
-- СЛУЖЕБНЫЕ ПЕРЕМЕННЫЕ	
DECLARE @SQLString NVARCHAR(4000)
DECLARE @backupfile NVARCHAR(500)
DECLARE @physicalName NVARCHAR(500), @logicalName NVARCHAR(500)
DECLARE @error as int
DECLARE @subject as NVARCHAR(100)
DECLARE @finalmassage as NVARCHAR(1000)

-------------------------------------------
-- ТЕЛО СКРИПТА
use master

-- Удалим временные таблицы, если вдруг они есть
IF OBJECT_ID('tempdb.dbo.#BackupFiles') IS NOT NULL DROP TABLE #BackupFiles
IF OBJECT_ID('tempdb.dbo.#FullBackup') IS NOT NULL DROP TABLE #FullBackup
IF OBJECT_ID('tempdb.dbo.#DiffBackup') IS NOT NULL DROP TABLE #DiffBackup
IF OBJECT_ID('tempdb.dbo.#LogBackup') IS NOT NULL DROP TABLE #LogBackup
IF OBJECT_ID('tempdb.dbo.#BackupFilesFinal') IS NOT NULL DROP TABLE #BackupFilesFinal

-- Соберем данные о всех сдаланных раннее бэкапах
SELECT
	backupset.backup_start_date,
	backupset.backup_set_uuid,
	backupset.differential_base_guid,
	backupset.[type] as btype,
	backupmediafamily.physical_device_name
INTO #BackupFiles	
FROM msdb.dbo.backupset AS backupset
    INNER JOIN msdb.dbo.backupmediafamily AS backupmediafamily 
	ON backupset.media_set_id = backupmediafamily.media_set_id
WHERE backupset.database_name = @DBName_From 
	and backupset.backup_start_date < @BackupTime
	and backupset.is_copy_only = 0 -- флаг "Только резервное копирование"
	and backupset.is_snapshot = 0 -- флаг "Не snapshot"
	and (backupset.description is null or backupset.description not like 'Image-level backup') -- Защита от Veeam Backup & Replication
	and device_type <> 7
ORDER BY 
	backupset.backup_start_date DESC

-- Найдем последний полный бэкап
SELECT TOP 1
	BackupFiles.backup_start_date,
	BackupFiles.physical_device_name,
	BackupFiles.backup_set_uuid	
INTO #FullBackup	 
FROM #BackupFiles AS BackupFiles
WHERE btype = 'D'
ORDER BY backup_start_date DESC

-- Найдем последний разностный бэкап
SELECT TOP 1
	BackupFiles.backup_start_date,
	BackupFiles.physical_device_name
INTO #DiffBackup	 
FROM #BackupFiles AS BackupFiles
	INNER JOIN #FullBackup AS FullBackup
	ON BackupFiles.differential_base_guid = FullBackup.backup_set_uuid
WHERE BackupFiles.btype = 'I'
ORDER BY BackupFiles.backup_start_date DESC

-- Соберем бэкапы журналов транзакций
SELECT
	BackupFiles.backup_start_date,
	BackupFiles.physical_device_name
INTO #LogBackup	
FROM #BackupFiles AS BackupFiles
	INNER JOIN
	(
		SELECT MAX(table_backup_start_date.backup_start_date) AS backup_start_date
		FROM 
		(
			SELECT backup_start_date
			FROM #FullBackup
			UNION ALL
			SELECT backup_start_date
			FROM #DiffBackup
		) AS table_backup_start_date
	) AS table_lsn
	ON BackupFiles.backup_start_date > table_lsn.backup_start_date
WHERE BackupFiles.btype = 'L'

-- Инициируем цикл по объединению всех трех таблиц
SELECT physical_device_name
INTO #BackupFilesFinal
FROM 
(
	SELECT
		backup_start_date,
		physical_device_name
	FROM #FullBackup
	UNION ALL
	SELECT
		backup_start_date,
		physical_device_name
	FROM #DiffBackup
	UNION ALL
	SELECT
		backup_start_date,
		physical_device_name
	FROM #LogBackup
) AS T 
ORDER BY backup_start_date

-- Соберем файлы в цикл
DECLARE bkf CURSOR LOCAL FAST_FORWARD FOR 
(
	SELECT physical_device_name
	FROM #BackupFilesFinal
);

-- Начало цикла
OPEN bkf;

-- Прочитаем первый элемент цикла, им может быть только полная резервная копия
FETCH bkf INTO @backupfile;
IF @@FETCH_STATUS<>0
	-- Если получить элемент не удалось, то полная резерная копия не найдена
	BEGIN
		SET @subject = 'ОШИБКА ВОССТАНОВЛЕНИЯ базы данных ' + @DBName_To
		SET @finalmassage = 'Не найдена полная резервная копия для базы данных ' + @DBName_From
	END
ELSE
	BEGIN

	--Далее загружаем все файлы резервных копий в 3 этапа:

	-- 1. Загружаем полный бэкап
	SET @SQLString = 
	N'RESTORE DATABASE [' + @DBName_To + ']
	FROM DISK = N''' + @backupfile + ''' 
	WITH  
	FILE = 1,'

	-- Переименуем файлы базы данных на исходные
	-- Новый цикл по всем файлам базы данных
	DECLARE fnc CURSOR LOCAL FAST_FORWARD FOR 
	(
		SELECT
			t_From.name,
			t_To.physical_name
		FROM sys.master_files as t_To 
			join sys.master_files as t_From 
			on t_To.file_id = t_From.file_id
		WHERE t_To.database_id = DB_ID(@DBName_To) 
			and t_From.database_id = DB_ID(@DBName_From)
	)
	OPEN fnc;
	FETCH fnc INTO @logicalName, @physicalName;
	WHILE @@FETCH_STATUS=0
		BEGIN
			SET @SQLString = @SQLString + '
			MOVE N''' + @logicalName + ''' TO N''' + @physicalName + ''','
			FETCH fnc INTO @logicalName, @physicalName;
		END;
	CLOSE fnc;
	DEALLOCATE fnc;

	SET @SQLString = @SQLString + '
	NORECOVERY,
	REPLACE,
	STATS = 5'

	-- Выводим и выполняем полученную инструкцию
	PRINT @SQLString
	EXEC sp_executesql @SQLString
	SET @error = @@error
	IF @error <> 0
		BEGIN
			-- Если были ошибки, то восстановить полную копию не удалось
			SET @subject = 'ОШИБКА ВОССТАНОВЛЕНИЯ базы данных ' + @DBName_To
			SET @finalmassage = 'Ошибка восстановления полной резервной копии для базы данных ' + @DBName_To + CHAR(13) + CHAR(13)
				+ 'Код ошибки: ' + CAST(@error as NVARCHAR(10)) + CHAR(13) + CHAR(13)
				+ 'Текст T-SQL: ' + CHAR(13) + @SQLString
		END
	ELSE 
		BEGIN

		-- 2. Загружаем разностный бэкап и журналы транзакций
		FETCH bkf INTO @backupfile;
		WHILE @@FETCH_STATUS=0
			BEGIN
			set @SQLString = 
			N'RESTORE DATABASE ' + @DBName_To + '
			FROM DISK = ''' + @backupfile + '''
			WITH
			FILE = 1,
			NORECOVERY,
			STATS = 5'
			
			-- Выводим и выполняем полученную инструкцию
			PRINT @SQLString	
			EXEC sp_executesql @SQLString
			SET @error = @@error
			IF @error <> 0
				BEGIN
					-- Если ошибка, прерываем цикл
				BREAK
				END
			ELSE
				BEGIN
					-- Ошибок нет, обрабатываем следующий элемент цикла
					FETCH bkf INTO @backupfile;
				END
			END;
		
		IF @error <> 0
			BEGIN
				-- Во время цикла, была ошибка
				SET @subject = 'ОШИБКА ВОССТАНОВЛЕНИЯ базы данных ' + @DBName_To
				SET @finalmassage = 'Ошибка восстановления резервной копии для базы данных ' + @DBName_To + CHAR(13) + CHAR(13)
					+ 'Код ошибки: ' + CAST(@error as NVARCHAR(10)) + CHAR(13) + CHAR(13)
					+ 'Текст T-SQL: ' + CHAR(13) + @SQLString
			END
		ELSE
			BEGIN

			-- 3. Переводим базу в оперативный режим
			SET @SQLString = 
			N'RESTORE DATABASE ' + @DBName_To + '
			WITH RECOVERY'
			
			-- Выводим и выполняем полученную инструкцию
			PRINT @SQLString	
			EXEC sp_executesql @SQLString
			SET @error = @@error
			IF @error <> 0
				BEGIN
					-- Ошибка перевода базы в оперативный режим
					SET @subject = 'ОШИБКА ВОССТАНОВЛЕНИЯ базы данных ' + @DBName_To
					SET @finalmassage = 'Ошибка перевода в оперативный режим базы данных ' + @DBName_To + CHAR(13) + CHAR(13)
						+ 'Код ошибки: ' + CAST(@error as NVARCHAR(10)) + CHAR(13) + CHAR(13)
						+ 'Текст T-SQL: ' + CHAR(13) + @SQLString
				END
			ELSE	
				BEGIN

				-- Переводим базу в простую модель восстановления
				SET @SQLString = 
					'ALTER DATABASE ' + @DBName_To + ' SET RECOVERY SIMPLE;'
				
				-- Выводим и выполняем полученную инструкцию
				PRINT @SQLString	
				EXEC sp_executesql @SQLString
				SET @error = @@error
				IF @error <> 0
					BEGIN
						-- Ошибка перевода базы в простую модель восстановлеия
						SET @subject = 'ОШИБКА ВОССТАНОВЛЕНИЯ базы данных ' + @DBName_To
						SET @finalmassage = 'Ошибка перевода в простую модель восстановления базы данных ' + @DBName_To + CHAR(13) + CHAR(13)
							+ 'Код ошибки: ' + CAST(@error as NVARCHAR(10)) + CHAR(13) + CHAR(13)
							+ 'Текст T-SQL: ' + CHAR(13) + @SQLString
					END
				ELSE
					BEGIN

					-- Запускаем сжатие базы данных
					SET @SQLString = 
						'DBCC SHRINKDATABASE(N''' + @DBName_To + ''');'
					
					-- Выводим и выполняем полученную инструкцию
					PRINT @SQLString
					EXEC sp_executesql @SQLString
					SET @error = @@error
					IF @error <> 0
						BEGIN
							-- Ошбика сжатия базы данных
							SET @subject = 'ОШИБКА ВОССТАНОВЛЕНИЯ базы данных ' + @DBName_To
							SET @finalmassage = 'Ошибка сжатия базы данных ' + @DBName_To + CHAR(13) + CHAR(13)
								+ 'Код ошибки: ' + CAST(@error as NVARCHAR(10)) + CHAR(13) + CHAR(13)
								+ 'Текст T-SQL: ' + CHAR(13) + @SQLString
						END
					ELSE
						BEGIN
							-- Успешное выполнение всех операций
							SET @subject = 'Успешное восстановление базы данных ' + @DBName_To
							SET @finalmassage = 'Успешное восстановление базы данных ' + @DBName_To + ' из резервной копии базы данных ' + @DBName_From + ' на момент времени ' + Replace(CONVERT(nvarchar, @BackupTime, 126),':','-')
						END
					END
				END
			END
		END			
	END

-- Завершаем цикл
CLOSE bkf;
DEALLOCATE bkf;

-- Удаляем временные таблицы
drop table #BackupFiles
drop table #FullBackup
drop table #DiffBackup
drop table #LogBackup
drop table #BackupFilesFinal

-- Если задан профиль электронной почты, отправим сообщение
IF @profile_name <> ''
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = @profile_name,
    @recipients = @recipients,
    @body = @finalmassage,
    @subject = @subject;

-- Выводим сообщение о результате
SELECT
	@subject as massage
	
GO

Эти и другие скрипты доступны также в репозитории: https://github.com/Tavalik/SQL_TScripts

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

Все вебинары по скриптам для SQL:

Автоматизируем перезаливку баз (Часть 1): //infostart.ru/public/799857/
Регламентные операции с индексами в MS SQL Server (Часть 2): //infostart.ru/public/803209/
Еще немного полезных SQL-скриптов (Часть 3): //infostart.ru/public/807843/

+164 –

См. также

Автоматическое резервное копирование любой клиент-серверной базы 1С в формате DT с удалением сеансов, архивацией, изменением расширения (8.3.14+, расширение)

Архивирование (backup) Инструменты администратора БД Платформа 1С v8.3 Управляемые формы Конфигурации 1cv8 Платные (руб)

Данная разработка позволит решить вопрос с резервным копированием Ваших баз в автоматическом режиме, расположенных на сервере 1С. Система умеет ставить блокировки на вход, блокировать фоновые задания, принудительно отключать сеансы пользователей. И все это система делает в автоматически при создании бэкапа (или через команду). Выгрузка происходит в родной формат 1С - .dt. Так же система умеет архивировать данные выгрузки с установкой пароля. Умеет менять расширение файла zip или dt на любое указанное вами, что позволит сохранить выгрузки от шифровальщика. Может удалять старые копии выгрузок, оставляя указанное количество резервных копий, начиная с самой поздней. Только для WINDOWS!

6000 руб.

06.11.2012 72783 625 45

BackUPv8 - система резервного копирования баз 1С

Архивирование (backup) Системный администратор Платформа 1С v8.3 Конфигурации 1cv8 Платные (руб)

Программа позволяет выполнять автоматическое создание копий файловых и серверных информационных баз 1С Предприятие 8 и размещение копий в облаке Яндекс.Диск, локальном или сетевом ресурсе.

1200 руб.

03.09.2014 15696 21 6

Резервное копирование журнала транзакций, наконец-то!

Архивирование (backup) Администрирование СУБД Системный администратор Россия Бесплатно (free)

Постараюсь объяснить, зачем нужно резервное копирование именно журнала транзакций, а не только базы данных, и почему я словно сбросил груз, настроив его - как, покажу, естественно. Кстати, будут скрипты T-SQL (с подробными комментариями) - отличный способ сделать администрирование базы более уютным.

04.12.2023 9415 n_mezentsev 15

Копии баз данных и размер БД. Проблемы и пути решения

Администрирование СУБД Архивирование (backup) Системный администратор Платформа 1С v8.3 Конфигурации 1cv8 Бесплатно (free)

Столкнулся с проблемой быстрого роста объема БД. Статья о том, как решал эту проблему.

30.11.2022 3326 DrMih 5

Резервное копирование и восстановление 1С баз на PostgreSQL в Windows с помощью pgAdmin, bat-файлов и планировщика

Архивирование (backup) Системный администратор Программист Платформа 1С v8.3 Конфигурации 1cv8 Бесплатно (free)

В данной инструкции будет описано, как с помощью pgAdmin, bat-файлов и планировщика заданий Windows организовать резервное копирование, восстановление и хранение копий баз данных.

07.10.2022 28571 sapervodichka 37

147

Cкрипт для выгрузки DT (детешников) утилитой ibcmd 1С+MSSQL

Архивирование (backup) Системный администратор Платформа 1С v8.3 Конфигурации 1cv8 Россия Бесплатно (free)

1С+MSSQL Выгрузка информационной базы в файл dt ibcmd infobase dump.

26.09.2022 5173 user1752096 6

Архивирование базы в dt и дамп postgres

Архивирование (backup) Системный администратор Платформа 1С v8.3 Конфигурации 1cv8 Россия Абонемент ($m)

Захотелось клиентам выгрузку архива баз, и выгрузку в дт, готовые скрипты с сети не заработали. Может, кому-то поможет. Релиз 8.3.18.1741.

1 стартмани

25.08.2022 5730 3 Gnom-Gluck 6

Утилита копирования баз данных 1С

Архивирование (backup) Системный администратор Программист Платформа 1С v8.3 Абонемент ($m)

Небольшая утилита для копирования файловых баз данных 1С.

1 стартмани

02.06.2022 4687 3 Giblarium 12

Комментарии

Подписаться на ответы Инфостарт бот

Свернуть все

1. Armando 1401 16.03.18 23:14 Сейчас в теме

Еще частый сценарий это создание резервной копии БД на одном сервере и восстановление в БД на другом сервере.
Например, рабочие базы на своих серверах, а тестовые на отдельном. И надо загрузить копию рабочей базы в тестовую базу.

2. JohnyDeath 302 17.03.18 23:02 Сейчас в теме

(1) https://github.com/Stepa86/1C-Deploy-and-CopyDB

3. Armando 1401 17.03.18 23:25 Сейчас в теме

(2) Жесть какая. Почему-то я скептически отношусь к oscript.
В принципе у нас все тоже самое, но на bat файлах.

5. webester 26 18.03.18 06:45 Сейчас в теме

(3) Действительно жесть.
- Это все авно!
- Почему?
- Ну я так решил...

13. пользователь 15.05.18 05:47

Сообщение было скрыто модератором.

...

21. romankoav 4 09.03.23 17:11 Сейчас в теме

(1) А это можно сделать в рамках одного скрипта?

22. Armando 1401 09.03.23 19:37 Сейчас в теме

(21) у нас это был один скрипт + хранимая процедура. В скрипте было создание резервной копии и копирование ее на тестовый сервер, а там вызывалась хранимая процедура, в которой были шаги по восстановлению, очистка объемных таблиц и что-то ещё. Но в принципе можно было и одним сделать, просто это не за один присест рождалось.

4. nvv1970 18.03.18 01:59 Сейчас в теме

Блин, ну это вообще детский сад какой-то...
Где например определение имени последнего бэкапа, если бэкапы идут с разными именами?

SEL ECT  @@Servername AS ServerName ,
        d.Name AS DBName ,
        b.Backup_finish_date ,  b.[type],
        bmf.Physical_Device_name
FR OM    sys.databases d
        INNER JOIN msdb..backupset b ON b.database_name = d.name AND b.[type] = 'D'
        INNER JOIN msdb.dbo.backupmediafamily bmf ON b.media_set_id = bmf.media_set_id
ORDER BY d.NAME , b.Backup_finish_date DESC;

6. German 413 20.03.18 06:50 Сейчас в теме

Лучше этого еще ничего не встречал
https://www.red-gate.com/products/dba/sql-backup/ дорого, но оно того стоит

7. Mi11er 99 20.03.18 12:20 Сейчас в теме

(6) спс за ссылку. Глянем

8. ershz 21.03.18 16:57 Сейчас в теме

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

DECLARE @SQLString1 NVARCHAR(4000)
-------------------------------------------

BEGIN TRY 
 set @SQLstring1= 'alt er   database [' +@DBName_To+ ']  SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
 exec @SQLstring1
	EXEC sp_executesql @SQLString

set @SQLstring1= 'alt er   database [' +@DBName_To+ ']  SET MULTI_USER ' 
exec @SQLstring1

END TRY

Показать

18. user1406861 23.12.21 09:28 Сейчас в теме

(8) Ваш вариант нерабочий, но спасибо за наводку. Я его немного исправил и теперь действительно можно перезаливать базу, если в ней есть активные сеансы (они будут завершены)

P.S. форма кода почему-то автоматически разделяет команду "ALTER" на "ALT ER" - нужно удалить пробел при копировании кода!

	-- tweak: ЗАВЕРШЕНИЕ АКТИВНЫХ СЕАНСОВ В БАЗЕ путем перевода в однопользовательский режим и обратно
	BEGIN TRY
		SET @SQLstring= 'ALT ER     DATABASE ' +@DBName_To+ ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;'
		EXEC sp_executesql @SQLString
		SET @SQLstring= 'ALT ER     DATABASE ' +@DBName_To+ ' SET MULTI_USER;' 
		EXEC sp_executesql @SQLString
	END TRY
	BEGIN CATCH  
		-- Ошбика выполнения операции
		SET @subject = 'ОШИБКА ВОССТАНОВЛЕНИЯ базы данных ' + @DBName_To
		SET @finalmassage = 'Ошибка перевода базы в одно- или многопользовательский режим ' + @DBName_To + CHAR(13) + CHAR(13)
			+ 'Код ошибки: ' + CAST(ERROR_NUMBER() as nvarchar(10)) + CHAR(13) + CHAR(13)
			+ 'Текст ошибки: ' + ERROR_MESSAGE()  + CHAR(13) + CHAR(13)
			+ 'Текст T-SQL:' + CHAR(13) + @SQLString  
	END CATCH;

Показать

9. bforce 482 02.05.18 17:21 Сейчас в теме

Хорошо, когда разработчик делает что-то своими руками. Это повышает компетентность и это здорово!
Но, как обычно, все уже придумано до нас. Ну и на github есть https://github.com/olahallengren/sql-server-maintenance-solution.

10. пользователь 02.05.18 20:04

Сообщение было скрыто модератором.

...

11. blackhole321 1314 09.05.18 16:45 Сейчас в теме

Ну вот же нормальный подход, и без всяких там консольных утилит с параметрами ;)

12. Tavalik 3409 11.05.18 06:55 Сейчас в теме

(11)
Спасибо, конечно. Только там эти же скрипты, с той лишь разницей, что запуск из командной строки.

14. isaev2016 04.02.20 10:47 Сейчас в теме

finalmassage понравилось

15. user1308398 21.05.20 00:04 Сейчас в теме

Если в имени базы есть "-" то выдает ошибку

Сообщение 102, уровень 15, состояние 1, строка 1
Incorrect syntax near '-'.
Сообщение 319, уровень 15, состояние 1, строка 2
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Как лечить?
А так скрипт, очень выручит.

16. jem 83 24.08.20 09:04 Сейчас в теме

Мне помогло. Спасибо! Даже если это велосипед, то дает скил для дальнейшего развития. Таким образом мы учимся. Комменты тоже полезны.

17. finist7490 08.10.20 23:06 Сейчас в теме

Спасибо, полезные скрипты! А как такую же перезаливку на PostgreSQL сделать?

19. user1406861 23.12.21 10:16 Сейчас в теме

Кстати, автор, почему у тебя в условиях стоит какая-то двойка ?

IF @subject = '2'

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

IF @subject = ''

20. rrustam11983 17.01.22 11:48 Сейчас в теме

Сам писал скрипты по восстановлению. Сейчас жалею что не нашел тогда вашу статью. Всё четко, лаконично и именно так как надо. Хочу сказать спасибо большое автору за проделанную работу.