1.1. Открыть утилиту Enterprise Manager.
1.2. Правой кнопкой мыши щелкнуть по базе данных, выбрать пункт Все задачи из контекстного меню, а затем выбрать пункт Detach Database.
1.3. Если вы видите надпись STATUS: Database is ready to be detached, то нажмите на кнопку OK. В противном случае сначала отключите всех пользователей от базы данных.
1.4. После отсоединения базы данных скопировать ее файл журнала транзакций и/или файл данных в новое физическое местоположение (на другой диск).
1.5. В утилите Enterprise Manager щелкнуть правой кнопкой по контейнеру Databases, в контекстном меню выбрать пункт Все задачи, затем – Attach Database.
1.6. В открывшемся окне (см. Рисунок 4) в поле MDF file of database to attach с помощью файл-менеджера, который открывается при нажатии кнопки […], найти первичный файл данных в новом физическом местоположении.
Рисунок Рисунок 4. Окно Attach Database
1.7. В поле Current File(s) Location необходимо вручную ввести полный путь к новому физическому местоположению файла журнала транзакций.
1.8. Из раскрывающегося списка Specify database owner выбрать владельца базы данных (рекомендуется выбрать пользователя sa), см. Рисунок 5.
Рисунок Рисунок 5. Окно Attach Database
6. Изменение величины приращения и ограничение размера файла данных и файла журнала транзакций
1.9. Открыть утилиту Enterprise Manager.
1.10. Выбрать базу данных, щелкнуть по ней правой кнопкой мыши и выбрать пункт Свойства.
1.11. В открывшемся окне Properties (см. Рисунок 6) перейти на закладку Data Files.
1.12. В разделе File properties установить для каждого файла данных переключатель By percent и для каждого файла данных ввести значение 35.
Рисунок Рисунок 6. Окно Properties
1.13. Перейти на закладку Transaction Log.
1.14. В разделе File properties установить для файла журнала переключатель By percent и ввести значение 45.
Рисунок Рисунок 7. Окно Properties
1.15. Нажать OK.
7. Ограничение размера файла данных и файла журнала транзакций
1.16. Проанализировать размер свободного дискового пространства и количество файлов данных, которые будут храниться на этом диске. На основании анализа принять решение о размере каждого файла данных.
1.17. Открыть утилиту Enterprise Manager.
1.18. Выбрать базу данных, щелкнуть по ней правой кнопкой мыши и выбрать пункт Свойства.
1.19. В открывшемся окне (см. Рисунок 8) перейти на закладку Data Files.
1.20. В разделе Maximum file size установить переключатель Restrict file growth (MB).
Рисунок Рисунок 8. Окно Properties
1.21. Ввести ранее определенный размер файла данных.
1.22. Перейти на закладку Transaction Log.
1.23. Повторить пункты 3.5 – 3.6 для файла журнала транзакций.
8. Создание вторичного файла базы данных
1.24. Открыть утилиту Enterprise Manager.
1.25. Выбрать базу данных, щелкнуть по ней правой кнопкой мыши и выбрать пункт Свойства.
1.26. В открывшемся окне Properties перейти на закладку Data Files (см. Рисунок 9).
Рисунок Рисунок 9. Окно Properties
1.27. В поле File Name ввести имя нового файла данных (например, secfile).
1.28. В поле Location ввести путь к тому диску, который был создан для хранения нового файла данных, а также физическое имя нового файла данных, например, secfile. ndf.
1.29. В поле Filegroup ввести имя новой группы файлов (например, SEC1), которой будет принадлежать новый файл данных.
1.30. Нажать кнопку OK.
9. Перемещение некластерных индексов таблицы
1.31. Открыть утилиту Enterprise Manager.
1.32. Выбрать базу данных, раскрыть ее и выбрать раздел Tables.
1.33. В правом окне щелкнуть правой кнопкой мыши по таблице EXTREM и из контекстного меню выбрать Все задачи -> Manage Indexes.
1.34. В открывшемся окне (см. Рисунок 10) выбрать первый индекс, у которого в столбце Clustered стоит значение No.
Рисунок Рисунок 10. Окно Manage Indexes
1.35. Нажать кнопку Edit.
1.36. Из раскрывающегося списка File group выбрать ту группу файлов, которая была создана в качестве вторичной группы файлов (в нашем случае SEC1).
Рисунок Рисунок 11. Окно Edit Existing Indexes
1.37. Нажать кнопку OK.
1.38. Повторить п. п. 5.4 – 5.7 для всех оставшихся индексов, у которых в столбце Clustered стоит значение No.
1.39. Проделать операции, описанные в пунктах 5.3 – 5.8, для таблиц REM3_INCOME, REM1, REM2 и REM3.
10. Изменение параметров базы данных
1.40. Открыть утилиту Enterprise Manager.
1.41. Выбрать базу данных, щелкнуть по ней правой кнопкой мыши и выбрать пункт Свойства.
1.42. В открывшемся окне (см. Рисунок 12) перейдите на закладку Options.
1.43. Установите все настройки базы данных следующим образом:
Рисунок Рисунок 12. Окно Properties
Приложение 4. Резервное копирование и восстановление баз данных
1. Создание устройств резервного копирования
1.1. Создать на компьютере, отличном от компьютера, на котором установлен SQL Server 2000, сетевой диск, и дать к этому сетевому диску полный доступ учетной записи Windows, под которой запускается SQL Server 2000.
1.2. Открыть утилиту Enterprise Manager.
1.3. Развернуть папку Management и щелкнуть левой кнопкой мыши по значку Backup.
1.4. В правом окне щелкнуть правой кнопкой мыши и выбрать из выпадающего меню пункт New Backup Device
1.5. В открывшемся окне (см. Рисунок 13) ввести имя для создаваемого устройства резервного копирования, например, DBFullBackup, а также ввести полный путь UNC (с указанием имени файла резервной копии) к созданному сетевому диску, на который будет осуществляться резервное копирование.
Рисунок Рисунок 13. Окно Backup Device Properties
1.6. Нажать кнопку OK
1.7. В примере выше было создано устройство резервного копирования для последующего создания полной резервной копии базы данных. Чтобы создать устройство резервного копирования для резервной копии журнала транзакций, повторите пункты 1.2 – 1.6. Имя устройства резервного копирования и сетевой путь нужно задать другие, например, DBLogBackup и \\backupserver\sqlbackup\logdb. bak
11. Создание операторов
1.8. Открыть утилиту Enterprise Manager.
1.9. Развернуть папку Management.
1.10. Развернуть папку SQL Server Agent и щелкнуть левой кнопкой мыши по значку Operators.
1.11. В правом окне щелкнуть правой кнопкой мыши и выбрать из выпадающего меню пункт New Operator.
1.12. В открывшемся окне (см. Рисунок 14) ввести имя для создаваемого оператора, адрес электронной почты оператора, онлайновый пейджер (если есть) и адрес для отсылки сообщений командой NET SEND.
Рисунок Рисунок 14. Окно Properties
1.13. Для проверки адреса команды NET SEND нажать кнопку Test. Оператору должно прийти сообщение по сети следующего вида (см. Рисунок 15):
Рисунок Рисунок 15. Окно сообщения
1.14. Нажать на кнопку OK.
12. Создание хранимых процедур для обслуживания базы данных
1.15. Открыть утилиту Query Analyzer
1.16. Выполнить следующий сценарий:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
USE master
GO
IF OBJECTPROPERTY(OBJECT_ID('[dbo].[sp_reindex]'),'IsProcedure')=1
DROP PROCEDURE [dbo].[sp_reindex]
GO
CREATE PROCEDURE [dbo].[sp_reindex]
AS
/*Переиндексация всех пользовательских таблиц текущей базы данных*/
SET NOCOUNT ON
SET XACT_ABORT ON
SET ARITHABORT ON
DECLARE
@sql VARCHAR(1000),
@table CHAR(32),
@owner CHAR(32),
@msg VARCHAR(1000)
DECLARE cur CURSOR FOR
SELECT so.[name] AS [table],su.[name] AS [owner]
FROM dbo. sysobjects AS so
INNER JOIN dbo. sysusers AS su ON so. uid=su. uid
WHERE so. type='U'
OPEN cur
FETCH NEXT FROM cur INTO @table, @owner
WHILE @@FETCH_STATUS=0
BEGIN
SET @sql='DBCC DBREINDEX('''+LTRIM(RTRIM(@owner))+'.'+LTRIM(RTRIM(@table))+''')'
EXEC (@sql)
FETCH NEXT FROM cur INTO @table, @owner
END
CLOSE cur
DEALLOCATE cur
/*Исправление несоответствий в таблице sysindexes*/
DBCC UPDATEUSAGE(0)
GO
IF OBJECTPROPERTY(OBJECT_ID('[dbo].[sp_filedefrag]'),'IsProcedure')=1
DROP PROCEDURE [dbo].[sp_filedefrag]
GO
CREATE PROCEDURE [dbo].[sp_filedefrag]
AS
/*Дефрагментация файлов данных текущей базы данных*/
SET NOCOUNT ON
SET XACT_ABORT ON
DECLARE
@sql VARCHAR(1000),
@name SYSNAME,
@msg VARCHAR(1000)
CREATE TABLE #t (
[name] SYSNAME,
fileid SMALLINT,
[filename] NCHAR(260),
[filegroup] SYSNAME NULL,
[size] NVARCHAR(18),
[maxsize] NVARCHAR(18),
growth NVARCHAR(18),
usage VARCHAR(9)
)
INSERT INTO #t EXEC sp_helpfile
DECLARE cur CURSOR FOR SELECT [name] FROM #t WHERE [filegroup] IS NOT NULL
OPEN cur
FETCH NEXT FROM cur INTO @name
WHILE @@FETCH_STATUS=0
BEGIN
SET @sql='DBCC SHRINKFILE('''+LTRIM(RTRIM(@name))+''',NOTRUNCATE)'
EXEC(@sql)
FETCH NEXT FROM cur INTO @name
END
CLOSE cur
DEALLOCATE cur
GO
IF OBJECTPROPERTY(OBJECT_ID('[dbo].[sp_updatestat]'),'IsProcedure')=1
DROP PROCEDURE [dbo].[sp_updatestat]
GO
CREATE PROCEDURE [dbo].[sp_updatestat]
AS
/*Полное обновление статистики всех пользовательских таблиц БД*/
SET NOCOUNT ON
SET XACT_ABORT ON
SET ARITHABORT ON
DECLARE
@sql VARCHAR(1000),
@table CHAR(32),
@owner CHAR(32),
@msg VARCHAR(1000)
DECLARE cur CURSOR FOR
SELECT so.[name] AS [table],su.[name] AS [owner]
FROM dbo. sysobjects AS so
INNER JOIN dbo. sysusers AS su ON so. uid=su. uid
WHERE so. type='U'
OPEN cur
FETCH NEXT FROM cur INTO @table,@owner
WHILE @@FETCH_STATUS=0
BEGIN
SET @sql='UPDATE STATISTICS ['+LTRIM(RTRIM(@owner))+'].['+LTRIM(RTRIM(@table))+'] WITH FULLSCAN'
EXEC (@sql)
FETCH NEXT FROM cur INTO @table,@owner
END
CLOSE cur
DEALLOCATE cur
GO
IF OBJECTPROPERTY(OBJECT_ID('[dbo].[sp_indexdefrag]'),'IsProcedure')=1
DROP PROCEDURE [dbo].[sp_indexdefrag]
GO
CREATE PROCEDURE [dbo].[sp_indexdefrag]
AS
/*Дефрагментация индексов всех пользовательских таблиц текущей базы данных*/
SET NOCOUNT ON
SET XACT_ABORT ON
SET ARITHABORT ON
--Объявление переменных
DECLARE
@sql VARCHAR(1000),
@table CHAR(32),
@owner CHAR(32),
@msg VARCHAR(1000),
@objectid INT,
@indexid INT
--Таблица для результатов команды DBCC SHOWCONTIG
IF OBJECT_ID('tempdb..#fraglist') IS NOT NULL DROP TABLE #fraglist
CREATE TABLE #fraglist(
ObjectName CHAR(255),
ObjectId INT,
IndexName CHAR(255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL
)
--Выборка имен пользовательских таблиц текущей базы данных
DECLARE cur CURSOR FOR
SELECT so.[name] AS [table],su.[name] AS [owner]
FROM dbo. sysobjects AS so
INNER JOIN dbo. sysusers AS su ON so. uid=su. uid
WHERE so. type='U'
--Заполнение таблицы #fraglist
OPEN cur
FETCH NEXT FROM cur INTO @table,@owner
WHILE @@FETCH_STATUS=0
BEGIN
SET @sql='DBCC SHOWCONTIG('''+LTRIM(RTRIM(@owner))+'.'+LTRIM(RTRIM(@table))+''') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS'
INSERT INTO #fraglist EXEC(@sql)
FETCH NEXT FROM cur INTO @table,@owner
END
CLOSE cur
DEALLOCATE cur
--Дефрагментация индексов пользовательских таблиц
DECLARE cur CURSOR FOR
SELECT ObjectId, IndexId FROM #fraglist
WHERE INDEXPROPERTY(ObjectId, IndexName,'IndexDepth')>0
OPEN cur
FETCH NEXT FROM cur INTO @objectid,@indexid
WHILE @@FETCH_STATUS=0
BEGIN
SET @sql='DBCC INDEXDEFRAG (0,'+RTRIM(@objectid)+','+RTRIM(@indexid)+')'
EXEC (@sql)
FETCH NEXT FROM cur INTO @objectid,@indexid
END
CLOSE cur
DEALLOCATE cur
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
13. Создание заданий для резервного копирования баз данных
1.17. Создание серверного задания для обслуживания и полного резервного копирования пользовательской базы данных
Прежде чем будет создана полная резервная копия базы данных, необходимо быть уверенным в том, что:
– целостность базы данных не нарушена
– индексы таблиц перестроены или дефрагментированы
– статистика распределения таблиц обновлена
– файлы данных дефрагментированы
Выполнение всех вышеуказанных мер позволит в случае необходимости восстановить из резервной копии работоспособную и оптимизированную базу данных.
Особое внимание следует обратить на переиндексацию таблиц. Переиндексация таблиц может быть выполнена с помощью нескольких методов. В данном документе рассматриваются два метода: перестройка индексов и дефрагментация индексов.
Метод, использующий перестройку индексов, приведен в тексте хранимой процедуры sp_reindex. Метод, использующий дефрагментацию индексов, приведен в тексте хранимой процедуры sp_indexdefrag. Перестройка индексов осуществляется путем создания новых индексов и удаления старых. Дефрагментация индексов осуществляется путем перемещения («перетасовки») индексных страниц. Каждый из методов имеет свои преимущества и недостатки. Сравнение двух методов переиндексации приведено в таблице 8.
Таблица 8. Сравнение двух методов переиндексации таблиц
Метод | Преимущество | Недостаток |
Перестройка индексов | Операция создания новых индексов и удаление старых приводит к логической и физической дефрагментации индексов, то есть к более полной дефрагментации индексов. | Создание новых индексов и последующее удаление старых приводит к фрагментации файла данных. Поэтому после использования метода перестройки индексов файл данных желательно дефрагментировать. |
При перестройке индексов осуществляется автоматическое обновление статистики распределения данных в индексированных столбцах. | Операция перестройки индексов является ресурсоемкой, поэтому ее желательно проводить во внерабочие часы, чтобы не препятствовать нормальной работе пользователей. | |
Дефрагментация индексов | Дефрагментация индексов не приводит к фрагментации файла данных. | Дефрагментация индексов устраняет только логическую фрагментацию индексов, поэтому менее эффективна, чем операция перестройки индексов. |
Операция дефрагментации индексов является ресурсоемкой только в случае большой фрагментации индекса. Если довольно часто устранять фрагментацию индекса, и не допускать, чтобы индекс стал сильно фрагментированным, то операцию дефрагментации можно проводить в рабочее время, не мешая работе пользователей | Дефрагментация индексов не обновляет автоматически статистику распределения данных в индексированных столбцах, поэтому после дефрагментации индексов необходимо провести обновление статистики. |
Ниже описывается создание серверного задания обслуживания и создания полной резервной копии базы данных
– открыть Query Analyzer
– скопировать в окно Query Analyzer текст следующего сценария T-SQL:
BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb. dbo. syscategories WHERE name = N'[Uncategorized (Local)]') < 1
EXECUTE msdb. dbo. sp_add_category @name = N'[Uncategorized (Local)]'
SELECT @JobID = job_id
FROM msdb. dbo. sysjobs
WHERE (name = N'Резервное копирование и сопровождение БД TestDB')
IF (@JobID IS NOT NULL)
BEGIN
IF (EXISTS (SELECT *
FROM msdb. dbo. sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
RAISERROR (N'Unable to import job ''Резервное копирование и сопровождение БД TestDB'' since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
EXECUTE msdb. dbo. sp_delete_job @job_name = N'Резервное копирование и сопровождение БД TestDB'
SELECT @JobID = NULL
END
BEGIN
EXECUTE @ReturnCode = msdb. dbo. sp_add_job @job_id = @JobID OUTPUT,
@job_name = N'Резервное копирование и сопровождение БД TestDB', @owner_login_name = N'sa', @description = N'Регламентные процедуры по сопровождению базы данных TestDB', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 2, @notify_level_eventlog = 2, @delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb. dbo. sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Проверка целостности', @command = N'DBCC CHECKDB(''TestDB'')
', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb. dbo. sp_add_jobstep @job_id = @JobID, @step_id = 2, @step_name = N'Переиндексация пользовательских таблиц', @command = N'EXEC sp_reindex', @database_name = N'TestDB', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb. dbo. sp_add_jobstep @job_id = @JobID, @step_id = 3, @step_name = N'Обновление статистики пользовательских таблиц', @command = N'EXEC sp_updatestat', @database_name = N'TestDB', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb. dbo. sp_add_jobstep @job_id = @JobID, @step_id = 4, @step_name = N'Дефрагментация файлов данных', @command = N'EXEC sp_filedefrag', @database_name = N'TestDB', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb. dbo. sp_add_jobstep @job_id = @JobID, @step_id = 5, @step_name = N'Полное резервное копирование', @command = N'BACKUP DATABASE [TestDB] TO [TestDB Full Backup] WITH INIT, NOUNLOAD, NAME=N''TestDB backup'', NOSKIP, STATS= 10, NOFORMAT
DECLARE @i INT
select @i = position from msdb..backupset where database_name=''TestDB''and type!=''F'' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=''TestDB'')
RESTORE VERIFYONLY FROM [TestDB Full Backup] WITH FILE = @i
', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb. dbo. sp_add_jobstep @job_id = @JobID, @step_id = 6, @step_name = N'Резервное копирование журнала транзакций', @command = N'BACKUP LOG [TestDB] TO [TestDB Log Backup] WITH INIT, NOUNLOAD, NAME = N''TestDB backup'', NOSKIP, STATS = 10, NOFORMAT
DECLARE @i INT
select @i = position from msdb..backupset where database_name=''TestDB''and type!=''F'' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=''TestDB'')
RESTORE VERIFYONLY FROM [TestDB Log Backup] WITH FILE = @i
', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb. dbo. sp_update_job @job_id = @JobID, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb. dbo. sp_add_jobschedule @job_id = @JobID, @name = N'Sch1', @enabled = 1, @freq_type = 4, @active_start_date = , @active_start_time = 0, @freq_interval = 1, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = , @active_end_time = 235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb. dbo. sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
– в сценарии T-SQL заменить текст TestDB Full Backup на имя устройства резервного копирования для полной резервной копии базы данных, которое было создано в п.1
– в сценарии T-SQL заменить текст TestDB Log Backup на имя устройства резервного копирования для резервной копии журнала транзакций, которое было создано в п.1
– в сценарии T-SQL заменить текст TestDB на имя вашей базы данных, для которой будет проводиться обслуживание и резервное копирование
– выполнить откорректированный сценарий T-SQL
В результате выполнения сценария T-SQL должно быть создано серверное задание для обслуживания и полного резервного копирования базы данных, которое будет запускаться по расписанию каждый день в 12 часов ночи.
Это задание можно просмотреть (см. Рисунок 16) и при необходимости откорректировать с помощью утилиты Enterprise Manager.
Рисунок Рисунок 16. Окно «Enterprise Manager»
1.18. Создание серверного задания для резервного копирования журнала транзакций пользовательской базы данных
Периодическое создание резервной копии журнала транзакций позволяет максимально полно восстановить базу данных в случае краха системы. Кроме того, периодически создавая резервную копию журнала транзакций, можно восстановить данные на определенный период времени. Это может пригодиться в случае непреднамеренного удаления или неверного изменения данных пользователем.
Создание резервной копии журнала транзакций возможно только в том случае, если модель восстановления базы данных Full или Bulk-Logged.
Ниже описывается создание серверного задания для резервного копирования журнала транзакций.
– открыть Query Analyzer
– скопировать в окно Query Analyzer текст следующего сценария T-SQL:
BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb. dbo. syscategories WHERE name = N'[Uncategorized (Local)]') < 1
EXECUTE msdb. dbo. sp_add_category @name = N'[Uncategorized (Local)]'
SELECT @JobID = job_id
FROM msdb. dbo. sysjobs
WHERE (name = N'Резервная копия журнала БД TestDB')
IF (@JobID IS NOT NULL)
BEGIN
IF (EXISTS (SELECT * FROM msdb. dbo. sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
RAISERROR (N'Unable to import job ''Резервная копия журнала БД TestDB'' since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
EXECUTE msdb. dbo. sp_delete_job @job_name = N'Резервная копия журнала БД TestDB'
SELECT @JobID = NULL
END
BEGIN
EXECUTE @ReturnCode = msdb. dbo. sp_add_job @job_id = @JobID OUTPUT, @job_name = N'Резервная копия журнала БД TestDB', @owner_login_name = N'sa', @description = N'No description available.', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 2, @notify_level_eventlog = 2, @delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb. dbo. sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Резервное копирование журнала транзакций', @command = N'BACKUP LOG [TestDB] TO [TestDB Log Backup] WITH NOINIT, NOUNLOAD, NAME = N''TestDB backup'', NOSKIP, STATS = 10, NOFORMAT
DECLARE @i INT
select @i = position from msdb..backupset where database_name=''TestDB''and type!=''F'' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=''TestDB'')
RESTORE VERIFYONLY FROM [TestDB Log Backup] WITH FILE = @i
', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb. dbo. sp_update_job @job_id = @JobID, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb. dbo. sp_add_jobschedule @job_id = @JobID, @name = N'Sch1', @enabled = 1, @freq_type = 4, @active_start_date = , @active_start_time = 20000, @freq_interval = 1, @freq_subday_type = 8, @freq_subday_interval = 1, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = , @active_end_time = 230000
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb. dbo. sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
– в сценарии T-SQL заменить текст TestDB Log Backup на имя устройства резервного копирования для резервной копии журнала транзакций, которое было создано в п.1
– в сценарии T-SQL заменить текст TestDB на имя вашей базы данных, для которой будет проводиться обслуживание и резервное копирование
– выполнить откорректированный сценарий T-SQL
В результате выполнения сценария T-SQL должно быть создано серверное задание для резервного копирования журнала транзакций, которое будет запускаться по расписанию через каждый час, начиная с 2 часов ночи и заканчивая 11 часами вечера.
Это задание можно просмотреть (см. Рисунок 17) и при необходимости откорректировать с помощью утилиты Enterprise Manager.
Рисунок Рисунок 17. Окно «Enterprise Manager»
1.19. Мониторинг работы заданий
Администратор баз данных должен каждый день проверять, как отработало задание. Он может это делать:
– вручную с помощью утилиты Enterprise Manager
– назначить заданию оператора, который будет уведомляться при неудачной отработке задания
– использовать оба вышеуказанных способа
Вручную с помощью утилиты Enterprise Manager можно просмотреть результаты выполнения задания следующим образом:
– открыть Enterprise Manager
– визуально определить статус выполнения задания: синий цвет – успех, красный – неудача
– щелкнуть по заданию правой кнопкой мыши и выбрать пункт View Job History
– появится следующее окно (см. Рисунок 18):
Рисунок Рисунок 18. Окно Job History
В столбце Result будет стоять значение Successful в случае, если задание отработало без ошибок; в противном случае можно отметить пункт Show step details и посмотреть, на каком шаге задания произошла ошибка, и какая именно ошибка.
Назначить заданию оператора для уведомления можно следующим образом:
– открыть Enterprise Manager
– щелкнуть по заданию два раза левой кнопкой мыши и в открывшемся окне перейти на закладку Notifications
– отметить пункт Net send operator
– из раскрывающегося списка выбрать оператора, которому нужно отправлять уведомления (см. Рисунок 19).
Рисунок Рисунок 19. Окно «Резервное копирование и сопровождение»
14. Создание оповещения о проценте заполнения журнала транзакций
1.20. Открыть утилиту Enterprise Manager.
1.21. Развернуть папку Management.
1.22. Развернуть папку SQL Server Agent и щелкнуть левой кнопкой мыши по значку Alerts.
1.23. В правом окне щелкнуть правой кнопкой мыши и выбрать из выпадающего меню пункт New Alert
1.24. В открывшемся окне ввести имя оповещения, например, LogIsFull.
1.25. В выпадающем списке Type выбрать SQL Server performance condition alert.
1.26. В выпадающем списке Object выбрать SQL Server: Databases.
1.27. В выпадающем списке Counter выбрать Percent Log Used.
1.28. В выпадающем списке Instance выбрать базу данных, для журнала которой создается оповещение.
1.29. В выпадающем списке Alert if counter выбрать rises above.
1.30. В поле Value поставить значение 85 (см. Рисунок 20).
Рисунок Рисунок 20. Окно «New Allert Properties»
1.31. Перейти на закладку Response.
1.32. Установить флаг Execute job и из выпадающего списка выбрать задание резервного копирования журнала транзакций, которое было создано в пункте 4.
1.33. Выбрать оператора, которому будет отправлено оповещение о заполнении журнала транзакций на 85%.
1.34. В поле Additional notification message to send можно ввести дополнительное сообщение, которое будет отправляться оператору, например: «Журнал транзакций заполнился на 85%» (см. Рисунок 21).
Рисунок Рисунок 21. Окно «New Allert Properties»
1.35. Нажать OK.
1.36. Таким образом, мы создали оповещение, которое будет уведомлять оператора о заполнении журнала транзакций на 85%, причем это оповещение будет еще и запускать задание резервного копирования журнала транзакций, которое будет этот журнал усекать, и, тем самым, освобождать в нем место.
15. Мониторинг файла данных
1.37. Открыть Enterprise Manager.
1.38. Щелкнуть по базе данных правой кнопкой мыши и в выпадающем меню выбрать Вид, затем – Taskpad (см. Рисунок 22).
|
Из за большого объема этот материал размещен на нескольких страницах:
1 2 3 4 |
