Тази тема описва как да се реализира план за поддръжка на база данни (DBMP)
с команди T-SQL. DBMP е по-добър от простото архивиране, тъй като проверява
целостта на базата данни, коригира малки грешки и оптимизира производителността.
Създаване на DBMP
 |
С тази заявка просто трябва да промените глобалните променливи
и е готова за изпълнение. |
използвайте master
започнете транзакция
декларирайте
@sPlanID nchar(36),
@sPlanName varchar(100),
@sLogDir varchar(100),
@sBackupDir varchar(100),
@sJobName varchar(255),
@sParam varchar(1000),
@sDBName varchar(100),
@iStartDate int,
@iBackupTime int,
@iOptiTime int,
@ReturnCode INT ,
@JobID0 nchar(36),
@JobID1 nchar(36),
@JobID2 nchar(36),
@JobID3 nchar(36),
@JobID4 nchar(36),
@JobID5 nchar(36),
@JobID6 nchar(36),
@JobID7 nchar(36),
@JobIDD nchar(36)
-- декларирайте глобални променливи
задайте @sDBName = 'BigDB' -- име на базата данни / променете при необходимост
задайте @sPlanName = 'План за поддръжка на BigDB' -- име на плана за поддръжка / променете при необходимост
задайте @sLogDir = 'C:\MSSQL7\LOG' -- път до лог файла на сървъра / променете при необходимост
задайте @sBackupDir = 'C:\MSSQL7\BACKUP' -- път до архива / променете при необходимост
задайте @iStartDate = 20020425 -- дата, когато планът за поддръжка започва да се прилага (YYYYMMDD)
задайте @iBackupTime = 20000 -- време за започване на архивиране (2am)
задайте @iOptiTime = 10000 -- време за започване на оптимизация (1am)
-- получаване на ID на плана и вмъкване в таблицата PLANS
изберете @sPlanID = NEWID()
вмъкнете msdb.dbo.sysdbmaintplans (plan_id, plan_name, max_history_rows, remote_history_server, max_remote_history_rows)
стойности (@sPlanID, @sPlanName, 1000, '', 0)
-- стартиране на задачи
--!! изберете @PlanID = N'1403015E-AAC4-4481-81EF-84BCAF0CBC91'
задайте @sJobName = 'Задача за архивиране на БД за план за поддръжка на БД '''+@sPlanName+''''
exec @ReturnCode = msdb.dbo.sp_add_job
@job_id = @JobID4 output,
@job_name = @sJobName,
@enabled = 1,
@category_id = 3
ако (@@ERROR <> 0 ИЛИ @ReturnCode <> 0) отидете QuitWithRollback
задайте @sParam = 'exec master.dbo.xp_sqlmaint N''-PlanID '+@sPlanID+' -Rpt "'+@sLogDir+'\DBMP (Backup) '+@sDBName+'.txt" -DelTxtRpt 1WEEKS -WriteHistory -VrfyBackup -BkUpOnlyifClean -CkDBRepair -BkUpMedia DISK -BkUpDB -UseDefDir -DelBkUps 1 -BkExt ""'''
exec @ReturnCode = msdb.dbo.sp_add_jobstep
@job_id = @JobID4,
@step_id = 1,
@step_name = N'Стъпка 1',
@subsystem = N'TSQL',
@on_success_action = 1,
@on_fail_action = 2,
@command = @sParam,
@flags = 4
ако (@@ERROR <> 0 ИЛИ @ReturnCode <> 0) отидете QuitWithRollback
exec @ReturnCode = msdb.dbo.sp_add_jobschedule
@job_id = @JobID4,
@name = N'График 1',
@enabled = 1,
@freq_type = 4,
@active_start_date = @iStartDate,
@active_start_time = @iBackupTime,
@freq_interval = 1,
@freq_subday_type = 1,
@freq_subday_interval = 0,
@freq_relative_interval = 0,
@freq_recurrence_factor = 1,
@active_end_date = 99991231,
@active_end_time = 235959
ако (@@ERROR <> 0 ИЛИ @ReturnCode <> 0) отидете QuitWithRollback
exec @ReturnCode = msdb.dbo.sp_add_jobserver
@job_id = @JobID4,
@server_name = N'(local)'
ако (@@ERROR <> 0 ИЛИ @ReturnCode <> 0) отидете QuitWithRollback
задайте @sJobName = 'Задача за архивиране на транзакционен лог за план за поддръжка на БД '''+@sPlanName+''''
exec @ReturnCode = msdb.dbo.sp_add_job
@job_id = @JobID6 output,
@job_name = @sJobName,
@enabled = 1,
@category_id = 3
ако (@@ERROR <> 0 ИЛИ @ReturnCode <> 0) отидете QuitWithRollback
задайте @sParam = 'exec master.dbo.xp_sqlmaint N''-PlanID '+@sPlanID+' -Rpt "'+@sLogDir+'\DBMP (Log Backup) '+@sDBName+'.txt" -DelTxtRpt 1WEEKS -WriteHistory -VrfyBackup -BkUpOnlyifClean -CkDBRepair -BkUpMedia DISK -BkUpLog -UseDefDir -DelBkUps 1 -BkExt ""'''
exec @ReturnCode = msdb.dbo.sp_add_jobstep
@job_id = @JobID6,
@step_id = 1,
@step_name = N'Стъпка 1',
@subsystem = N'TSQL',
@on_success_action = 1,
@on_fail_action = 2,
@command = @sParam,
@flags = 4
ако (@@ERROR <> 0 ИЛИ @ReturnCode <> 0) отидете QuitWithRollback
exec @ReturnCode = msdb.dbo.sp_add_jobschedule
@job_id = @JobID6,
@name = N'График 1',
@enabled = 1,
@freq_type = 8,
@active_start_date = 0,
@active_start_time = 0,
@freq_interval = 126,
@freq_subday_type = 1,
@freq_subday_interval = 0,
@freq_relative_interval = 0,
@freq_recurrence_factor = 1,
@active_end_date = 99991231,
@active_end_time = 235959
ако (@@ERROR <> 0 ИЛИ @ReturnCode <> 0) отидете QuitWithRollback
exec @ReturnCode = msdb.dbo.sp_add_jobserver
@job_id = @JobID6,
@server_name = N'(local)'
ако (@@ERROR <> 0 ИЛИ @ReturnCode <> 0) отидете QuitWithRollback
задайте @sJobName = 'Задача за проверки на целостта за план за поддръжка на БД '''+@sPlanName+''''
exec @ReturnCode = msdb.dbo.sp_add_job
@job_id = @JobID2 output,
@job_name = @sJobName,
@enabled = 1,
@category_id = 3
ако (@@ERROR <> 0 ИЛИ @ReturnCode <> 0) отидете QuitWithRollback
задайте @sParam = 'exec master.dbo.xp_sqlmaint N''-PlanID '+@sPlanID+' -Rpt "'+@sLogDir+'\DBMP (Integrity) '+@sDBName+'.txt" -DelTxtRpt 1WEEKS -WriteHistory -CkDBRepair '''
exec @ReturnCode = msdb.dbo.sp_add_jobstep
@job_id = @JobID2,
@step_id = 1,
@step_name = N'Стъпка 1',
@subsystem = N'TSQL',
@on_success_action = 1,
@on_fail_action = 2,
@command = @sParam,
@flags = 4
ако (@@ERROR <> 0 ИЛИ @ReturnCode <> 0) отидете QuitWithRollback
exec @ReturnCode = msdb.dbo.sp_add_jobschedule
@job_id = @JobID2,
@name = N'График 1',
@enabled = 1,
@freq_type = 4,
@active_start_date = @iStartDate,
@active_start_time = 0,
@freq_interval = 1,
@freq_subday_type = 1,
@freq_subday_interval = 0,
@freq_relative_interval = 0,
@freq_recurrence_factor = 1,
@active_end_date = 99991231,
@active_end_time = 235959
ако (@@ERROR <> 0 ИЛИ @ReturnCode <> 0) отидете QuitWithRollback
exec @ReturnCode = msdb.dbo.sp_add_jobserver
@job_id = @JobID2,
@server_name = N'(local)'
ако (@@ERROR <> 0 ИЛИ @ReturnCode <> 0) отидете QuitWithRollback
задайте @sJobName = 'Задача за оптимизации за план за поддръжка на БД '''+@sPlanName+''''
exec @ReturnCode = msdb.dbo.sp_add_job
@job_id = @JobID0 output,
@job_name = @sJobName,
@enabled = 1,
@category_id = 3
ако (@@ERROR <> 0 ИЛИ @ReturnCode <> 0) отидете QuitWithRollback
задайте @sParam = 'exec master.dbo.xp_sqlmaint N''-PlanID '+@sPlanID+' -Rpt "'+@sLogDir+'\DBMP (Optimisations) '+@sDBName+'.txt" -DelTxtRpt 1WEEKS -WriteHistory -RebldIdx 10 -RmUnusedSpace 50 10 '''
exec @ReturnCode = msdb.dbo.sp_add_jobstep
@job_id = @JobID0,
@step_id = 1,
@step_name = N'Стъпка 1',
@subsystem = N'TSQL',
@on_success_action = 1,
@on_fail_action = 2,
@command = @sParam,
@flags = 4
ако (@@ERROR <> 0 ИЛИ @ReturnCode <> 0) отидете QuitWithRollback
exec @ReturnCode = msdb.dbo.sp_add_jobschedule
@job_id = @JobID0,
@name = N'График 1',
@enabled = 1,
@freq_type = 4,
@active_start_date = @iStartDate,
@active_start_time = @iOptiTime,
@freq_interval = 1,
@freq_subday_type = 1,
@freq_subday_interval = 0,
@freq_relative_interval = 0,
@freq_recurrence_factor = 1,
@active_end_date = 99991231,
@active_end_time = 235959
ако (@@ERROR <> 0 ИЛИ @ReturnCode <> 0) отидете QuitWithRollback
exec @ReturnCode = msdb.dbo.sp_add_jobserver
@job_id = @JobID0,
@server_name = N'(local)'
ако (@@ERROR <> 0 ИЛИ @ReturnCode <> 0) отидете QuitWithRollback
ако (изберете Count(*)
от msdb.dbo.sysdbmaintplan_databases
където plan_id = @sPlanID И ИМЕ_НА_БАЗАТА = @sDBName) < 1
вмъкнете msdb.dbo.sysdbmaintplan_databases (plan_id, database_name) стойности (@sPlanID, @sDBName)
изтрийте msdb.dbo.sysdbmaintplan_jobs където plan_id = @sPlanID
вмъкнете msdb.dbo.sysdbmaintplan_jobs
стойности (@sPlanID, @JobID0)
вмъкнете msdb.dbo.sysdbmaintplan_jobs
стойности (@sPlanID, @JobID2)
вмъкнете msdb.dbo.sysdbmaintplan_jobs
стойности (@sPlanID, @JobID4)
вмъкнете msdb.dbo.sysdbmaintplan_jobs
стойности (@sPlanID, @JobID6)
потвърдете транзакцията
отидете EndSave
QuitWithRollback: ако (@@TRANCOUNT > 0) отменете транзакцията
EndSave:
Преглед на DBMPs
За да видите списък на активните планове за поддръжка, изпълнете следната заявка:
изберете *
от msdb.dbo.sysdbmaintplans