Видео в формате вебинара с подробным разбором всех скриптов:
Ну и, непосредственно, сами разобранные в вебинаре скрипты под спойлерами ниже. Все переменные собраны в соответствующем блоке в начале скриптов. Для отправки электронной почты используется предварительно настроенный профиль электронной почты компоненты 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/
