Типовой план ежедневного обслуживания пользовательских баз для MS SQL на основе ola hallengren

userdb-dailyplan.sql

declare @EmailFrom nvarchar(100) = 'ИмяПочтового профиля'
declare @EmailTo nvarchar(100) = 'адреса получателей через запятую'
declare @ErrorsQuery nvarchar(1000) = 'SELECT TOP 10 DatabaseName, StartTime, EndTime, ErrorNumber, ErrorMessage FROM master.dbo.CommandLog WHERE ErrorNumber <> 0 AND StartTime >= CONVERT (date, SYSDATETIME())'
declare @BackupPath nvarchar(100) = 'Каталог бэкапов'
declare @SiteName nvarchar(100) = 'SQL.' + @@SERVERNAME + '.' + DEFAULT_DOMAIN()
declare @Subject nvarchar(100)
declare @Result int;

/* Бэкап логов */
EXECUTE @Result = dbo.DatabaseBackup
	@Databases = 'USER_DATABASES, -%_dev%, -%_tmp%',
	@Directory = @BackupPath,
	@BackupType = 'LOG',
	@CleanupTime = 72,
	@LogToTable = 'Y'
IF @Result <> 0
	BEGIN
		SET @Subject = @SiteName + ': backup log failed'
		EXEC msdb.dbo.sp_send_dbmail
			@profile_name = @EmailFrom,
			@recipients = @EmailTo,
			@body = N'Последние 10 ошибок: ',
			@subject = @Subject,
			@query = @ErrorsQuery;
	END

/* Проверка целостности баз */
EXECUTE @Result = dbo.DatabaseIntegrityCheck
	@Databases = 'USER_DATABASES',
	@CheckCommands = 'CHECKDB',
	@LogToTable = 'Y'
IF @Result <> 0
	BEGIN
		SET @Subject = @SiteName + ': integrity check failed'
		EXEC msdb.dbo.sp_send_dbmail
			@profile_name = @EmailFrom,
			@recipients = @EmailTo,
			@body = N'Последние 10 ошибок: ',
			@subject = @Subject,
			@query = @ErrorsQuery;
	END

/* Индексы и статистика */
EXECUTE @Result = dbo.IndexOptimize
	@Databases = 'USER_DATABASES',
	@FragmentationLow = NULL,
	@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
	@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
	@FragmentationLevel1 = 5,
	@FragmentationLevel2 = 45,
	@UpdateStatistics = 'ALL',
	@OnlyModifiedStatistics = 'Y',
	@LogToTable = 'Y'
IF @Result <> 0
	BEGIN
		SET @Subject = @SiteName + ': reindex failed'
		EXEC msdb.dbo.sp_send_dbmail
			@profile_name = @EmailFrom,
			@recipients = @EmailTo,
			@body = N'Последние 10 ошибок: ',
			@subject = @Subject,
			@query = @ErrorsQuery;
	END

/* Создание и проверка полных бэкапов */
EXECUTE @Result = dbo.DatabaseBackup
	@Databases = 'USER_DATABASES, -%_dev%, -%_tmp%',
	@Directory = @BackupPath,
	@BackupType = 'FULL',
	@Verify = 'Y',
	@Compress = 'Y',
	@CheckSum = 'Y',
	@CleanupTime = 168,
	@LogToTable = 'Y'
IF @@ERROR <> 0
	BEGIN
		SET @Subject = @SiteName + ': backup failed'
		EXEC msdb.dbo.sp_send_dbmail
			@profile_name = @EmailFrom,
			@recipients = @EmailTo,
			@body = N'Последние 10 ошибок: ',
			@subject = @Subject,
			@query = @ErrorsQuery;
	END
ELSE
	BEGIN
		SET @Subject = @SiteName + ': done'
		EXEC msdb.dbo.sp_send_dbmail
			@profile_name = @EmailFrom,
			@recipients = @EmailTo,
			@body = N'Обслуживание успешно завершено',
			@subject = @Subject
	END