Skript na nastavení údržby databází

/* Tento skript je volně k dispozici bez jakýchkoliv záruk. */
/* Výrobce nenese žádnou zodpovědnost za škody způsobené použitím tohoto skriptu. */
/* */
/* Skript vytváří úlohy pro kontrolu, reindexaci, zálohování a oříznutí */
/* logu všech uživatelských databází. */
/* */
/* Popis parametrů je v zápatí skriptu */

use master
declare @MJob_id UNIQUEIDENTIFIER,
@MJob_id_old UNIQUEIDENTIFIER,
@MJob_jmeno sysname,
@Cesta_backup varchar(256),
@PrikazJobu varchar(4000),
@SmazatStareZalohy int,
@DBName sysname,

@DBBack_freq_type int,
@DBBack_freq_interval int,
@DBBack_active_start_time int,
@DBBack_freq_recurrence_factor int,

@DBCheck_freq_interval int,
@DBCheck_freq_type int,
@DBCheck_active_start_time int,
@DBCheck_freq_recurrence_factor int,

@DBReind_freq_interval int,
@DBReind_freq_type int,
@DBReind_active_start_time int,
@DBReind_freq_recurrence_factor int,

@DBShrink_freq_interval int,
@DBShrink_freq_type int,
@DBShrink_active_start_time int,
@DBShrink_freq_recurrence_factor int

/**************************************/
/******** Nastavení skriptu ***********/
/**************************************/
SET @Cesta_backup = 'C:\SQLBackup' /*zde nastavit cestu k backup adresari*/
SET @SmazatStareZalohy = '14' /*Kolik dní staré zálohy chci smazat*/

/*Načasování zálohování*/
SET @DBBack_freq_type = 8 /*Tydne*/
SET @DBBack_freq_interval = 4 + 8 + 16 + 32 + 64 /*V utery, stredu, ctvrtek, patek, sobotu*/
SET @DBBack_active_start_time = 000001 /*v 00:01 rano*/
SET @DBBack_freq_recurrence_factor = 1 /*(kazdy 1 tyden (mesic) - v zavislosti na freq_type (8 tydne, 16 mesicne))*/

/*Načasování kontroly databází*/
SET @DBCheck_freq_type = 8 /*Tydne*/
SET @DBCheck_freq_interval = 2 + 4 + 8 + 16 + 32 /*V pondeli, utery, stredu, ctvrtek, patek*/
SET @DBCheck_active_start_time = 020000 /*ve 02:00 rano*/
SET @DBCheck_freq_recurrence_factor = 1 /*(kazdy 1 tyden (mesic) - v zavislosti na freq_type (8 tydne, 16 mesicne))*/

/*Načasování reindexací*/
SET @DBReind_freq_type = 8 /*Tydne*/
SET @DBReind_freq_interval = 4 + 8 + 16 + 32 + 64 /*V utery, stredu, ctvrtek, patek, sobotu*/
SET @DBReind_active_start_time = 033000 /*v 03:30 rano*/
SET @DBReind_freq_recurrence_factor = 1 /*(kazdy 1 tyden (mesic) - v zavislosti na freq_type (8 tydne, 16 mesicne))*/

/*Načasování ořezání logů*/
SET @DBShrink_freq_type = 8 /*Tydne*/
SET @DBShrink_freq_interval = 4 + 8 + 16 + 32 + 64 /*V utery, stredu, ctvrtek, patek, sobotu*/
SET @DBShrink_active_start_time = 060000 /*v 06:00 rano*/
SET @DBShrink_freq_recurrence_factor = 1 /*(kazdy 1 tyden (mesic) - v zavislosti na freq_type (8 tydne, 16 mesicne))*/

/**************************************/
/**************************************/
/**************************************/


/**************************************/
/********* Nastavení databází *********/
/**************************************/
/*Nacteni seznamu databazí pro jejich nastavení*/
declare DBNames cursor local for
select name
from master.dbo.sysdatabases
where name like 'Data%'
OR name like 'DMaj%'
OR name like 'DMzd%'
OR name like 'DOLE%'
OR name like 'DSED%'
OR name like 'DUct%'
OR name in ('Firmy')
/*Zde je samozrejme mozno specifikovat i dalsi zakazkove databaze*/
order by name
open DBNames
FETCH NEXT FROM DBNames INTO @DBName
WHILE @@FETCH_STATUS=0
BEGIN
-- nastavení modelu opravy databází
EXECUTE('ALTER DATABASE ' + @DBName + ' SET RECOVERY FULL')
-- vypnutí automatického zavírání databází po odpojení posledního uživatele
EXECUTE('ALTER DATABASE ' + @DBName + ' SET AUTO_CLOSE OFF')
-- vypnutí automatického ořezávání
EXECUTE('ALTER DATABASE ' + @DBName + ' SET AUTO_SHRINK OFF')
FETCH NEXT FROM DBNames INTO @DBName
END
close DBNames
deallocate DBNames
/**************************************/
/**************************************/
/**************************************/


/**************************************/
/******** Kontrola integrity **********/
/**************************************/
SET @MJob_id = NULL
SET @MJob_jmeno = 'Kontrola integrity'
SELECT @MJob_id_old = job_id from msdb.dbo.sysjobs where [name] = @MJob_jmeno
if not @MJob_id_old IS NULL
EXECUTE msdb.dbo.sp_delete_job @job_id = @MJob_id_old

EXECUTE msdb.dbo.sp_add_job @job_name = @MJob_jmeno,
@enabled = 1,
@category_name = 'Database Maintenance',
@job_id = @MJob_id OUTPUT

/*Pridani kroku Jobu*/
SET @PrikazJobu = 'spav_foreachuserdb ''DBCC CHECKDB'''
EXECUTE msdb.dbo.sp_add_jobstep @job_id = @MJob_ID,
@step_name = 'Kontrola integrity',
@step_id =1,
@subsystem = 'TSQL',
@command = @PrikazJobu,
@flags = 4

/*Pridani Serveru Jobu*/
EXEC msdb.dbo.sp_add_jobserver @job_id = @MJob_ID

/*Nacasovani Jobu*/
EXECUTE msdb.dbo.sp_add_jobschedule @job_id = @MJob_ID,
@name = 'Nacasovani Kontroly integrity',
@freq_type = @DBCheck_freq_type,
@freq_interval = @DBCheck_freq_interval,
@active_start_date = NULL,
@active_start_time = @DBCheck_active_start_time,
@freq_recurrence_factor = @DBCheck_freq_recurrence_factor
/**************************************/
/**************************************/
/**************************************/


/**************************************/
/************* Zálohování *************/
/**************************************/
SET @MJob_id = NULL
SET @MJob_jmeno = 'Záloha vario'
SELECT @MJob_id_old = job_id from msdb.dbo.sysjobs where [name] = @MJob_jmeno
if not @MJob_id_old IS NULL
EXECUTE msdb.dbo.sp_delete_job @job_id = @MJob_id_old

EXECUTE msdb.dbo.sp_add_job @job_name = @MJob_jmeno,
@enabled = 1,
@category_name = 'Database Maintenance',
@job_id = @MJob_id OUTPUT

/*Pridani kroku Jobu*/
SET @PrikazJobu = 'spav_dbbackup_all_dbs '''+ @Cesta_backup + ''', ' + cast(@SmazatStareZalohy as varchar(4))
EXECUTE msdb.dbo.sp_add_jobstep @job_id = @MJob_ID,
@step_name = 'Záloha',
@step_id =1,
@subsystem = 'TSQL',
@command = @PrikazJobu,
@flags = 4

/*Pridani Serveru Jobu*/
EXEC msdb.dbo.sp_add_jobserver @job_id = @MJob_ID

/*Nacasovani Jobu*/
EXECUTE msdb.dbo.sp_add_jobschedule @job_id = @MJob_ID,
@name = 'Nacasovani zalohy',
@freq_type = @DBBack_freq_type,
@freq_interval = @DBBack_freq_interval,
@active_start_date = NULL,
@active_start_time = @DBBack_active_start_time,
@freq_recurrence_factor = @DBBack_freq_recurrence_factor
/**************************************/
/**************************************/
/**************************************/


/**************************************/
/************* Reindexace *************/
/**************************************/
SET @MJob_id = NULL
SET @MJob_jmeno = 'Reindexace'
SELECT @MJob_id_old = job_id from msdb.dbo.sysjobs where [name] = @MJob_jmeno
if not @MJob_id_old IS NULL
EXECUTE msdb.dbo.sp_delete_job @job_id = @MJob_id_old

EXECUTE msdb.dbo.sp_add_job @job_name = @MJob_jmeno,
@enabled = 1,
@category_name = 'Database Maintenance',
@job_id = @MJob_id OUTPUT

/*Pridani kroku Jobu*/
SET @PrikazJobu = 'spav_dbreindex_all_dbs'
EXECUTE msdb.dbo.sp_add_jobstep @job_id = @MJob_ID,
@step_name = 'Reindexace',
@step_id =1,
@subsystem = 'TSQL',
@command = @PrikazJobu,
@flags = 4

/*Pridani Serveru Jobu*/
EXEC msdb.dbo.sp_add_jobserver @job_id = @MJob_ID

/*Nacasovani Jobu*/
EXECUTE msdb.dbo.sp_add_jobschedule @job_id = @MJob_ID,
@name = 'Nacasovani Reindexace',
@freq_type = @DBReind_freq_type,
@freq_interval = @DBReind_freq_interval,
@active_start_date = NULL,
@active_start_time = @DBReind_active_start_time,
@freq_recurrence_factor = @DBReind_freq_recurrence_factor
/**************************************/
/**************************************/
/**************************************/


/**************************************/
/************ Ořezání logů ************/
/**************************************/
SET @MJob_id = NULL
SET @MJob_jmeno = 'Ořezání logů'
SELECT @MJob_id_old = job_id from msdb.dbo.sysjobs where [name] = @MJob_jmeno
if not @MJob_id_old IS NULL
EXECUTE msdb.dbo.sp_delete_job @job_id = @MJob_id_old

EXECUTE msdb.dbo.sp_add_job @job_name = @MJob_jmeno,
@enabled = 1,
@category_name = 'Database Maintenance',
@job_id = @MJob_id OUTPUT

/*Pridani kroku Jobu*/
SET @PrikazJobu = 'spav_shrinklogs_all_dbs'
EXECUTE msdb.dbo.sp_add_jobstep @job_id = @MJob_ID,
@step_name = 'Ořezání logů',
@step_id =1,
@subsystem = 'TSQL',
@command = @PrikazJobu,
@flags = 4

/*Pridani Serveru Jobu*/
EXEC msdb.dbo.sp_add_jobserver @job_id = @MJob_ID

/*Nacasovani Jobu*/
EXECUTE msdb.dbo.sp_add_jobschedule @job_id = @MJob_ID,
@name = 'Nacasovani Ořezání logů',
@freq_type = @DBShrink_freq_type,
@freq_interval = @DBShrink_freq_interval,
@active_start_date = NULL,
@active_start_time = @DBShrink_active_start_time,
@freq_recurrence_factor = @DBShrink_freq_recurrence_factor
/**************************************/
/**************************************/
/**************************************/


/**************************************/
/******** Popis parametru *************/
/**************************************/
/*
@Cesta_backup - cesta k adresari se zalohami
@SmazatStareZalohy - Jak stare zalohy chci smazat. Zde je nutno zadat: cislo[minutes | hours | days | weeks | months], napriklad 5days
*/

/*
Parametry nacasovani
--------------------------
--------------------------
freq_type (typ frekvence):
--------------------------
--------------------------
1 Jednou
4 Denne
8 Tydne
16 Mesicne
--------------------------


--------------------------------------------------------------------------
--------------------------------------------------------------------------
Hodnota freq_type | Efekt hodnoty freq_interval
--------------------------------------------------------------------------
--------------------------------------------------------------------------
1 (jednou) | freq_interval je ignorovan.
--------------------------------------------------------------------------
4 (denne) | Kazdych 'freq_interval' dni pocinaje dnem
| spusteni skriptu
--------------------------------------------------------------------------
8 (tydne) | freq_interval je jedna z nasledujicich
| hodnot volitelne kombinovanych operatorem OR):
| 1 = Nedele
| 2 = Pondeli
| 4 = Utery
| 8 = Streda
| 16 = Ctvrtek
| 32 = Patek
| 64 = Sobota
--------------------------------------------------------------------------
16 (mesicne) | Ve dni cislo 'freq_interval' mesice.
--------------------------------------------------------------------------


-----------------------------------------------------------
-----------------------------------------------------------
active_start_time
-----------------------------------------------------------
-----------------------------------------------------------
cas ve formatu HHMMSS urcujici v jaky cas se ma job spustit
-----------------------------------------------------------


---------------------------------------------------------------------
---------------------------------------------------------------------
freq_recurrence_factor
---------------------------------------------------------------------
---------------------------------------------------------------------
Pocet tydnu nebo mesicu mezi spustenim ulohy
(napr. 1 - jednou za tyden (mesic), 2 - jednou za dva tydny (mesice))
Tento parametr je pouzivan procedurou sp_add_jobschedule pouze
kdyz freq_type je 8, 16 nebo 32(viz Books Online a sp_add_jobschedule).
V tomto skriptu je vsak povinny.
---------------------------------------------------------------------
*/
/**************************************/
/**************************************/
/**************************************/