Această temă descrie cum să implementați un plan de întreținere a bazei de date (DBMP)
folosind comenzi T-SQL. DBMP este mai bun decât un simplu backup deoarece verifică
integritatea bazei de date, corectează erorile mici și optimizează performanța.
Crearea unui DBMP
 |
Cu această interogare trebuie doar să schimbați variabilele globale
și este gata de rulat. |
folosiți master
începeți tranzacția
declarați
@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)
-- declarați variabile globale
setați @sDBName = 'BigDB' -- numele bazei de date / schimbați după cum este necesar
setați @sPlanName = 'Plan de întreținere BigDB' -- numele planului de întreținere / schimbați după cum este necesar
setați @sLogDir = 'C:\MSSQL7\LOG' -- calea către log-ul serverului / schimbați după cum este necesar
setați @sBackupDir = 'C:\MSSQL7\BACKUP' -- calea către backup / schimbați după cum este necesar
setați @iStartDate = 20020425 -- data când planul de întreținere începe să se aplice (YYYYMMDD)
setați @iBackupTime = 20000 -- ora de început pentru a face backup (2am)
setați @iOptiTime = 10000 -- ora de început pentru optimizare (1am)
-- obțineți ID-ul planului și inserați-l în tabelul PLANS
select @sPlanID = NEWID()
insert msdb.dbo.sysdbmaintplans (plan_id, plan_name, max_history_rows, remote_history_server, max_remote_history_rows)
values (@sPlanID, @sPlanName, 1000, '', 0)
-- începeți joburile
--!! select @PlanID = N'1403015E-AAC4-4481-81EF-84BCAF0CBC91'
setați @sJobName = 'Job de backup DB pentru Plan de întreținere DB '''+@sPlanName+''''
exec @ReturnCode = msdb.dbo.sp_add_job
@job_id = @JobID4 output,
@job_name = @sJobName,
@enabled = 1,
@category_id = 3
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
setați @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'Step 1',
@subsystem = N'TSQL',
@on_success_action = 1,
@on_fail_action = 2,
@command = @sParam,
@flags = 4
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
exec @ReturnCode = msdb.dbo.sp_add_jobschedule
@job_id = @JobID4,
@name = N'Schedule 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
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
exec @ReturnCode = msdb.dbo.sp_add_jobserver
@job_id = @JobID4,
@server_name = N'(local)'
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
setați @sJobName = 'Job de backup al jurnalului de tranzacții pentru Plan de întreținere DB '''+@sPlanName+''''
exec @ReturnCode = msdb.dbo.sp_add_job
@job_id = @JobID6 output,
@job_name = @sJobName,
@enabled = 1,
@category_id = 3
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
setați @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'Step 1',
@subsystem = N'TSQL',
@on_success_action = 1,
@on_fail_action = 2,
@command = @sParam,
@flags = 4
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
exec @ReturnCode = msdb.dbo.sp_add_jobschedule
@job_id = @JobID6,
@name = N'Schedule 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
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
exec @ReturnCode = msdb.dbo.sp_add_jobserver
@job_id = @JobID6,
@server_name = N'(local)'
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
setați @sJobName = 'Job de verificare a integrității pentru Plan de întreținere DB '''+@sPlanName+''''
exec @ReturnCode = msdb.dbo.sp_add_job
@job_id = @JobID2 output,
@job_name = @sJobName,
@enabled = 1,
@category_id = 3
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
setați @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'Step 1',
@subsystem = N'TSQL',
@on_success_action = 1,
@on_fail_action = 2,
@command = @sParam,
@flags = 4
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
exec @ReturnCode = msdb.dbo.sp_add_jobschedule
@job_id = @JobID2,
@name = N'Schedule 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
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
exec @ReturnCode = msdb.dbo.sp_add_jobserver
@job_id = @JobID2,
@server_name = N'(local)'
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
setați @sJobName = 'Job de optimizări pentru Plan de întreținere DB '''+@sPlanName+''''
exec @ReturnCode = msdb.dbo.sp_add_job
@job_id = @JobID0 output,
@job_name = @sJobName,
@enabled = 1,
@category_id = 3
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
setați @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'Step 1',
@subsystem = N'TSQL',
@on_success_action = 1,
@on_fail_action = 2,
@command = @sParam,
@flags = 4
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
exec @ReturnCode = msdb.dbo.sp_add_jobschedule
@job_id = @JobID0,
@name = N'Schedule 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
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
exec @ReturnCode = msdb.dbo.sp_add_jobserver
@job_id = @JobID0,
@server_name = N'(local)'
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
if (select Count(*)
from msdb.dbo.sysdbmaintplan_databases
where plan_id = @sPlanID AND database_name = @sDBName) < 1
insert msdb.dbo.sysdbmaintplan_databases (plan_id, database_name) values (@sPlanID, @sDBName)
delete msdb.dbo.sysdbmaintplan_jobs where plan_id = @sPlanID
insert msdb.dbo.sysdbmaintplan_jobs
values (@sPlanID, @JobID0)
insert msdb.dbo.sysdbmaintplan_jobs
values (@sPlanID, @JobID2)
insert msdb.dbo.sysdbmaintplan_jobs
values (@sPlanID, @JobID4)
insert msdb.dbo.sysdbmaintplan_jobs
values (@sPlanID, @JobID6)
commit tranzacția
goto EndSave
QuitWithRollback: if (@@TRANCOUNT > 0) rollback tranzacția
EndSave:
Vizualizați DBMP-urile
Pentru a vedea o listă a planurilor de întreținere active, rulați următoarea interogare:
select *
from msdb.dbo.sysdbmaintplans