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

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

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

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

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

-------------------------------------------
-- НАСТРАИВАЕМЫЕ ПАРАМЕТРЫ
-- Имя базы данных для резервной копии
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'
-- Дата, на котороую собирается цепочка файлов резервных копий, в формате '20240315 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/

11 Comments

Armando 16.03.2018 at 23:14

Еще частый сценарий это создание резервной копии БД на одном сервере и восстановление в БД на другом сервере.

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

Reply ↓
JohnyDeath 17.03.2018 at 23:02

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

Reply ↓
Armando 17.03.2018 at 23:25

(2) Жесть какая. Почему-то я скептически отношусь к oscript.

В принципе у нас все тоже самое, но на bat файлах.

Reply ↓

nvv1970 18.03.2018 at 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;

Reply ↓

webester 18.03.2018 at 06:45

(3) Действительно жесть.

— Это все авно!

— Почему?

— Ну я так решил…

Reply ↓
German 20.03.2018 at 06:50

Лучше этого еще ничего не встречал

https://www.red-gate.com/products/dba/sql-backup/ дорого, но оно того стоит

Reply ↓
Mi11er 20.03.2018 at 12:20

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

Reply ↓

ershz 21.03.2018 at 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

Показать

Reply ↓

bforce 02.05.2018 at 17:21

Хорошо, когда разработчик делает что-то своими руками. Это повышает компетентность и это здорово!

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

Reply ↓
blackhole321 09.05.2018 at 16:45

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

Reply ↓
Tavalik 11.05.2018 at 06:55

(11)

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

Reply ↓

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

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

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

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

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

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

11 Comments

Leave a Comment Отменить ответ

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

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

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

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

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

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

Related Posts

11 Comments

Leave a Comment Отменить ответ