Видео в формате вебинара:
Ну и, непосредственно, сами разобранные в вебинаре скрипты под спойлерами ниже. Все переменные собраны в соответствующем блоке в начале скриптов. Для отправки электронной почты используется предварительно настроенный профиль электронной почты компоненты DataBase Mail. О настройке компоненты можно прочитать, например, здесь. Все скрипты много раз опробованы в бою и протестированы на версиях MS SQL 2008, 2012, 2016.
Скрипт, очищающий таблицы указанной базы данных:
---------------------------------------------
-- НАСТРАИВАЕМЫЕ ПАРАМЕТРЫ
-- Текущая база данных
USE WorkBase
-- Имена таблиц для очистки (необходимо добавить имена таблиц в таблицу @Table)
DECLARE @Table table(TablName varchar(100));
INSERT INTO @Table VALUES ('_Table1')
INSERT INTO @Table VALUES ('_Table2')
-- Имя почтового профиля, для отправки электонной почты
DECLARE @profilename as nvarchar(100) = 'ОсновнойПрофиль'
-- Получатели сообщений электронной почты, разделенные знаком ";"
DECLARE @recipients as nvarchar(500) = 'admin@mydomen.com'
-------------------------------------------
-- СЛУЖЕБНЫЕ ПЕРЕМЕННЫЕ
DECLARE @SQLString NVARCHAR(4000)
DECLARE @TableName varchar(100)
DECLARE @subject as NVARCHAR(1000) = '' -- тема сообщения
DECLARE @finalmassage as NVARCHAR(4000) = '' -- текст сообщения
-------------------------------------------
-- ТЕЛО СКРИПТА
-- Цикл по всем указанным таблицам
DECLARE TableCursor CURSOR FOR
(
SELECT * FROM @Table
)
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
-- Удаляем таблицу, если такая есть
SET @SQLString = 'IF NOT OBJECT_ID(N''[' + @TableName + ']'',''U'') IS NULL TRUNCATE TABLE [dbo].[' + @TableName + '];'
PRINT @SQLString
BEGIN TRY
EXEC sp_executesql @SQLString
SET @finalmassage = @finalmassage + 'Успешная очистка таблицы ' + @TableName + ' в базе данных ' + DB_NAME() + CHAR(13) + CHAR(13)
END TRY
BEGIN CATCH
-- Ошбика выполнения операции
SET @subject = 'БЫЛИ ОШИБКИ при очистке таблиц в базе данных ' + DB_NAME()
SET @finalmassage = @finalmassage + 'ОШИБКА очистки таблицы ' + @TableName + ' в базе данных ' + DB_NAME() + 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;
-- Следующий элемент цикла
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor;
DEALLOCATE TableCursor;
-- Формируем сообщение об успешном или не успешном выполнении операций
IF @subject = ''
BEGIN
-- Успешное выполнение всех операций
SET @subject = 'Успешная очистка таблиц в базе данных ' + DB_NAME()
END
-- Если задан профиль электронной почты, отправим сообщение
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 @namelike varchar(100) = 'WorkBase%'
-- Имена таблиц для очистки (необходимо добавить имена таблиц в таблицу @Table)
DECLARE @Table table(TablName varchar(100));
INSERT INTO @Table VALUES ('_Table1')
INSERT INTO @Table VALUES ('_Table2')
-- Имя почтового профиля, для отправки электонной почты
DECLARE @profilename as nvarchar(100) = 'ОсновнойПрофиль'
-- Получатели сообщений электронной почты, разделенные знаком ";"
DECLARE @recipients as nvarchar(500) = 'admin@mydomen.com'
-------------------------------------------
-- СЛУЖЕБНЫЕ ПЕРЕМЕННЫЕ
DECLARE @SQLString NVARCHAR(4000)
DECLARE @DBName varchar(100)
DECLARE @TableName varchar(100)
DECLARE @subject as NVARCHAR(1000) = '' -- тема сообщения
DECLARE @finalmassage as NVARCHAR(4000) = '' -- текст сообщения
-------------------------------------------
-- ТЕЛО СКРИПТА
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 -- база должна содержать указанное слово
)
-- Цикл по всем указанным таблицам
DECLARE TableCursor CURSOR FOR
(
SELECT * FROM @Table
)
-- Цикл по всем базам, попавшим в выборку
OPEN DBcursor
FETCH NEXT FROM DBcursor INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT N'----------------------------------------------------------'
PRINT N'USE [' + @DBName + N']'
-- Цикл по всем указанным таблицам
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
-- Удаляем таблицу, если такая есть
SET @SQLString = 'USE [' + @DBName + '] IF NOT OBJECT_ID(N''[' + @TableName + ']'',''U'') IS NULL TRUNCATE TABLE [dbo].[' + @TableName + '];'
-- Выполняем инструкцию
PRINT @SQLString
BEGIN TRY
EXEC sp_executesql @SQLString
SET @finalmassage = @finalmassage + 'Успешная очистка таблицы ' + @TableName + ' в базе данных ' + @DBName + CHAR(13) + CHAR(13)
END TRY
BEGIN CATCH
-- Ошбика выполнения операции
SET @subject = 'БЫЛИ ОШИБКИ при очистке таблиц '
SET @finalmassage = @finalmassage + 'ОШИБКА очистки таблицы ' + @TableName + ' в базе данных ' + @DBName + 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;
-- Следующий элемент цикла по таблицам
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor;
-- Следующий элемент цикла по базам данных
FETCH NEXT FROM DBcursor INTO @DBName
END
DEALLOCATE TableCursor;
CLOSE DBcursor;
DEALLOCATE DBcursor;
-- Формируем сообщение об успешном или не успешном выполнении операций
IF @subject = ''
BEGIN
-- Успешное выполнение всех операций
SET @subject = 'Успешная очистка таблиц '
END
-- Если задан профиль электронной почты, отправим сообщение
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 varchar(100) = 'TestBase'
-- Имя почтового профиля, для отправки электонной почты
DECLARE @profilename as nvarchar(100) = 'ОсновнойПрофиль'
-- Получатели сообщений электронной почты, разделенные знаком ";"
DECLARE @recipients as nvarchar(500) = 'admin@mydomen.com'
-------------------------------------------
-- СЛУЖЕБНЫЕ ПЕРЕМЕННЫЕ
DECLARE @SQLString NVARCHAR(4000)
DECLARE @subject as NVARCHAR(1000) = ''
DECLARE @finalmassage as NVARCHAR(4000) = ''
-------------------------------------------
-- ТЕЛО СКРИПТА
-- Переводим базы в простую модель восстановления
SET @SQLString = 'ALTER DATABASE ' + @DBName + ' SET RECOVERY SIMPLE WITH NO_WAIT;'
PRINT @SQLString
BEGIN TRY
EXEC sp_executesql @SQLString
END TRY
BEGIN CATCH
-- Ошбика выполнения операции
SET @subject = 'ОШИБКА перевода базы данных ' + @DBName + ' в простую модель восстановоления '
SET @finalmassage = 'ОШИБКА перевода базы данных ' + @DBName + ' в простую модель восстановоления ' + 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 @SQLString = 'DBCC SHRINKDATABASE(N''' + @DBName + ''');'
PRINT @SQLString
BEGIN TRY
EXEC sp_executesql @SQLString
END TRY
BEGIN CATCH
-- Ошбика выполнения операции
SET @subject = 'ОШИБКА сжатия базы данных ' + @DBName
SET @finalmassage = 'ОШИБКА сжатия базы данных ' + @DBName + 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
-- Формируем сообщение об успешном выполнении операций
IF @subject = ''
BEGIN
-- Успешное выполнение всех операций
SET @subject = 'Успешный перевод в простую модель восстановления и сжатие базы данных ' + @DBName
SET @finalmassage = 'Успешный перевод в простую модель восстановления и сжатие базы данных ' + @DBName
END
-- Если задан профиль электронной почты, отправим сообщение
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 @namelike varchar(100) = '%fto%'
-- Имя почтового профиля, для отправки электонной почты
DECLARE @profilename as nvarchar(100) = 'ОсновнойПрофиль'
-- Получатели сообщений электронной почты, разделенные знаком ";"
DECLARE @recipients as nvarchar(500) = 'admin@mydomen.com'
-------------------------------------------
-- СЛУЖЕБНЫЕ ПЕРЕМЕННЫЕ
DECLARE @SQLString NVARCHAR(4000)
DECLARE @DBName varchar(100)
DECLARE @subject as NVARCHAR(1000) = ''
DECLARE @finalmassage as NVARCHAR(4000) = ''
-------------------------------------------
-- ТЕЛО СКРИПТА
-- Отбоерем базы для выполнения операций
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 @SQLString = 'ALTER DATABASE ' + @DBName + ' SET RECOVERY SIMPLE WITH NO_WAIT;'
PRINT @SQLString
BEGIN TRY
EXEC sp_executesql @SQLString
END TRY
BEGIN CATCH
-- Ошбика выполнения операции
SET @finalmassage = @finalmassage + 'ОШИБКА перевода базы данных ' + @DBName + ' в простую модель восстановоления ' + 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;
-- Запускаем сжатие базы данных
SET @SQLString = 'DBCC SHRINKDATABASE(N''' + @DBName + ''');'
PRINT @SQLString
BEGIN TRY
EXEC sp_executesql @SQLString
END TRY
BEGIN CATCH
-- Ошбика выполнения операции
SET @finalmassage = @finalmassage + 'ОШИБКА сжатия базы данных ' + @DBName + ' в простую модель восстановоления ' + 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;
-- Следующий элемент цикла
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 @namelike varchar(100) = '%fto%'
-- Имя почтового профиля, для отправки электонной почты
DECLARE @profilename as nvarchar(100) = 'ОсновнойПрофиль'
-- Получатели сообщений электронной почты, разделенные знаком ";"
DECLARE @recipients as nvarchar(500) = 'admin@mydomen.com'
-------------------------------------------
-- СЛУЖЕБНЫЕ ПЕРЕМЕННЫЕ
DECLARE @SQLString NVARCHAR(4000)
DECLARE @DBName varchar(100)
DECLARE @DBLogName varchar(100)
DECLARE @subject as NVARCHAR(1000) = ''
DECLARE @finalmassage as NVARCHAR(4000) = ''
-------------------------------------------
-- ТЕЛО СКРИПТА
-- Отберем базы для выполнения операций
DECLARE DBcursor CURSOR FOR
(
SELECT
t_Name.name as DatabaseName,
t_LogName.name as DatabaseLogName
FROM sys.databases as t_Name
Inner join sys.master_files as t_LogName
on t_Name.database_id = t_LogName.database_id
WHERE t_Name.database_id > 4
AND t_Name.state_desc = 'ONLINE' -- база должна быть в сети
AND t_Name.name like @namelike -- база должна содержать указанное слово
AND t_LogName.type = 1
)
-- Цикл по всем базам, попавшим в выборку
OPEN DBcursor
FETCH NEXT FROM DBcursor INTO @DBName, @DBLogName
WHILE @@FETCH_STATUS = 0
BEGIN
-- Переводим базы в простую модель восстановления
SET @SQLString = 'ALTER DATABASE ' + @DBName + ' SET RECOVERY SIMPLE WITH NO_WAIT;'
PRINT @SQLString
BEGIN TRY
EXEC sp_executesql @SQLString
END TRY
BEGIN CATCH
-- Ошбика выполнения операции
SET @finalmassage = @finalmassage + 'ОШИБКА перевода базы данных ' + @DBName + ' в простую модель восстановоления ' + 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;
-- Запускаем сжатие лога базы данных
SET @SQLString = 'USE [' + @DBName + ']; DBCC SHRINKFILE(' + @DBLogName + ', 0, truncateonly);'
PRINT @SQLString
BEGIN TRY
EXEC sp_executesql @SQLString
END TRY
BEGIN CATCH
-- Ошбика выполнения операции
SET @finalmassage = @finalmassage + 'ОШИБКА сжатия базы данных ' + @DBName + ' в простую модель восстановоления ' + 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;
-- Следующий элемент цикла
FETCH NEXT FROM DBcursor
INTO @DBName, @DBLogName
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
Скрипт, показывающий все соединения с сервером SQL:
-------------------------------------------
-- ТЕЛО СКРИПТА
SELECT
program_name,
net_transport
FROM sys.dm_exec_sessions AS sessions
left join sys.dm_exec_connections AS connections
ON sessions.session_id = connections.session_id
WHERE
not sessions.program_name is null
ORDER BY
program_name
GO
Скрипт, показывающий версию сервера SQL:
-------------------------------------------
-- ТЕЛО СКРИПТА
SELECT
SERVERPROPERTY('MachineName') AS ComputerName,
SERVERPROPERTY('ServerName') AS InstanceName,
SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel;
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/
Вступайте в нашу телеграмм-группу Инфостарт
