06 octubre, 2009

Planes de mantenimiento para el DBA pobre

Buscando un poco encontré en el sitio de descargas de Microsoft un conjunto muy útil de scripts de mantenimiento. Según dice en la documentación incluida, estos scripts son usados por el equipo de operaciones de bases de datos de Microsoft.

Se incluyen los siguientes scripts:

  • SQLBackupAll – Performs Full database backup for non-excluded databases by SQL native or Litespeed backup commands. The job has parameters to specify the desired backup location and compress (SQL Litespeed backup) or nocompress (SQL native backup) , whether to backup or not to backup read only database etc.
  • SQLBackupDiffAll - Performs Differential database backup for non-excluded databases by SQL native or Litespeed backup commands. This job is disabled by default. This can be enabled based on the customer’s needs for the recovery requirements.
  • SQLBackupTranAll – Performs Transaction Log backup for non-excluded databases by SQL native or Litespeed backup commands. The job has parameters to specify the desired backup location and compress (SQL Litespeed backup) or nocompress (SQL native backup)
  • SQLBackupChecker – Checks for backups that have not occurred in more than the specified number of hours in the job. There is a parameter to specify the desired number of hours to specify for the alert.
  • SQLDBCCAll - Performs database consistency checks on all system and user databases.
  • SQLIndexDefragAll – Performs Index defrag maintenance for non-excluded SQL 2000 databases. This job is disabled by default. It can be enabled based on the customer’s needs. This job helps to run the index defrag for all the databases in the SQL instance or any particular database or any particular table in a database.
  • SQLUpdateStatistics - Runs Update Statistics maintenance for non-excluded databases.This job is disabled by default. This job is disabled by default. It can be enabled based on the customer’s needs. This job helps to run the update statistics for all the databases in the SQL instance or any particular database or any particular table in a database.
  • SQLCleanupMsdbBackupHistory – Cleans up the backup history information from msdb database.
  • SQLCycleErrorLog - Cycle the SQL Error log periodically so the size is manageable for viewing through SQL tools.

En el paquete se incluye un documento Word con las instrucciones y detalles. Brevemente:

1. Expandir el paquete en una carpeta C:\AITSCRIPTS

2. En dicha carpeta ejecutar el script de línea de comandos: jobsinstall servidor\instancia

EJEMPLO:

C:\AITScripts>jobsinstall (LOCAL)\SQLEXPRESS2008
C:\AITScripts>C:\AITScripts\CMDS\JOBSinstall.cmd (LOCAL)\SQLEXPRESS2008
C:\AITScripts>REM **********************************
C:\AITScripts>REM Pass the server name as a parameter, this proc will install procedures and jobs on a sql server
C:\AITScripts>REM then execute each job for the first run.
C:\AITScripts>REM **********************************
C:\AITScripts>REM ****** INSTALL PROCEDURES and OBJECTS **************
C:\AITScripts>osql -S(LOCAL)\SQLEXPRESS2008 -E -dmaster -n -i"C:\Aitscripts\PROCS\SQLDBExclusions.sql"
C:\AITScripts>osql -S(LOCAL)\SQLEXPRESS2008 -E -dmaster -n -i"C:\Aitscripts\PROCS\SQLDBDBCCExclusions.sql"
C:\AITScripts>osql -S(LOCAL)\SQLEXPRESS2008 -E -dmaster -n -i"C:\Aitscripts\PROCS\SQLDBTranExclusions.sql"
C:\AITScripts>osql -S(LOCAL)\SQLEXPRESS2008 -E -dmaster -n -i"C:\Aitscripts\PROCS\SQLSpecifyDBExclusions.sql"
C:\AITScripts>osql -S(LOCAL)\SQLEXPRESS2008 -E -dmaster -n -i"C:\Aitscripts\PROCS\SQLSpecifyDBTranExclusions.sql"
C:\AITScripts>osql -S(LOCAL)\SQLEXPRESS2008 -E -dmaster -n -i"C:\Aitscripts\PROCS\SQLSpecifyDBDBCCExclusions.sql"
C:\AITScripts>osql -S(LOCAL)\SQLEXPRESS2008 -E -dmaster -n -i"C:\Aitscripts\PROCS\SQLSpecifyDBIndexDefragExclusions.sql"
C:\AITScripts>osql -S(LOCAL)\SQLEXPRESS2008 -E -dmaster -n -i"C:\Aitscripts\PROCS\SQLSpecifyDBUpdateStatsExclusions.sql"
C:\AITScripts>osql -S(LOCAL)\SQLEXPRESS2008 -E -dmaster -n -i"C:\Aitscripts\PROCS\SQLDBUpdateStatsExclusions.sql"
C:\AITScripts>osql -S(LOCAL)\SQLEXPRESS2008 -E -dmaster -n -i"C:\Aitscripts\PROCS\SQLMostRecentBackup.sql"
C:\AITScripts>osql -S(LOCAL)\SQLEXPRESS2008 -E -dmaster -n -i"C:\Aitscripts\PROCS\SQLBackupChecker.sql"
C:\AITScripts>osql -S(LOCAL)\SQLEXPRESS2008 -E -dmaster -n -i"C:\Aitscripts\PROCS\SQLBackupAll01.sql"
C:\AITScripts>osql -S(LOCAL)\SQLEXPRESS2008 -E -dmaster -n -i"C:\Aitscripts\PROCS\SQLBackupAll02.sql"
C:\AITScripts>osql -S(LOCAL)\SQLEXPRESS2008 -E -dmaster -n -i"C:\Aitscripts\PROCS\SQLBackupAll03.sql"
C:\AITScripts>osql -S(LOCAL)\SQLEXPRESS2008 -E -dmaster -n -i"C:\Aitscripts\PROCS\SQLBackupDiffAll01.sql"
C:\AITScripts>osql -S(LOCAL)\SQLEXPRESS2008 -E -dmaster -n -i"C:\Aitscripts\PROCS\SQLBackupDiffAll02.sql"
C:\AITScripts>osql -S(LOCAL)\SQLEXPRESS2008 -E -dmaster -n -i"C:\Aitscripts\PROCS\SQLRenameTlogBackupFile.sql"
C:\AITScripts>osql -S(LOCAL)\SQLEXPRESS2008 -E -dmaster -n -i"C:\Aitscripts\PROCS\SQLBackupTranAll01.sql"
C:\AITScripts>osql -S(LOCAL)\SQLEXPRESS2008 -E -dmaster -n -i"C:\Aitscripts\PROCS\SQLBackupTranAll02.sql"
C:\AITScripts>osql -S(LOCAL)\SQLEXPRESS2008 -E -dmaster -n -i"C:\Aitscripts\PROCS\SQLDbccAll.sql"
C:\AITScripts>osql -S(LOCAL)\SQLEXPRESS2008 -E -dmaster -n -i"C:\Aitscripts\PROCS\SQLIndexDefragAll_SQL2000.sql"
C:\AITScripts>osql -S(LOCAL)\SQLEXPRESS2008 -E -dmaster -n -i"C:\Aitscripts\PROCS\SQLIndexDefragAll_SQL2005.sql"
C:\AITScripts>osql -S(LOCAL)\SQLEXPRESS2008 -E -dmaster -n -i"C:\Aitscripts\PROCS\SQLIndexDefragAll.sql"
C:\AITScripts>osql -S(LOCAL)\SQLEXPRESS2008 -E -dmaster -n -i"C:\Aitscripts\PROCS\SQLUpdateStatsAll.sql"
C:\AITScripts>osql -S(LOCAL)\SQLEXPRESS2008 -E -dmaster -n -i"C:\Aitscripts\PROCS\SQLRODBStatus.sql"
C:\AITScripts>osql -S(LOCAL)\SQLEXPRESS2008 -E -dmaster -n -i"C:\Aitscripts\PROCS\SQLMostRecentBackup_RODB.sql"
C:\AITScripts>osql -S(LOCAL)\SQLEXPRESS2008 -E -dmaster -n -i"C:\Aitscripts\PROCS\SQLfncsvtodatabase.sql"
C:\AITScripts>osql -S(LOCAL)\SQLEXPRESS2008 -E -dmaster -n -i"C:\Aitscripts\PROCS\SQLfncsvtotable.sql"
C:\AITScripts>REM ****** INSTALL JOBS **************
C:\AITScripts>osql -S(LOCAL)\SQLEXPRESS2008 -E -dmaster -n -i"C:\Aitscripts\JOBS\JOB_SQLBackupAll.sql"SQLServerAgent is not currently running so it cannot be notified of thisaction.
C:\AITScripts>osql -S(LOCAL)\SQLEXPRESS2008 -E -dmaster -n -i"C:\Aitscripts\JOBS\JOB_SQLBackupDiffAll.sql"SQLServerAgent is not currently running so it cannot be notified of thisaction.
C:\AITScripts>osql -S(LOCAL)\SQLEXPRESS2008 -E -dmaster -n -i"C:\Aitscripts\JOBS\JOB_SQLBackupTranAll.sql"SQLServerAgent is not currently running so it cannot be notified of thisaction.
C:\AITScripts>osql -S(LOCAL)\SQLEXPRESS2008 -E -dmaster -n -i"C:\Aitscripts\JOBS\JOB_SQLDBCCAll.sql"SQLServerAgent is not currently running so it cannot be notified of thisaction.
C:\AITScripts>osql -S(LOCAL)\SQLEXPRESS2008 -E -dmaster -n -i"C:\Aitscripts\JOBS\JOB_SQLIndexDefragAll.sql"SQLServerAgent is not currently running so it cannot be notified of thisaction.
C:\AITScripts>osql -S(LOCAL)\SQLEXPRESS2008 -E -dmaster -n -i"C:\Aitscripts\JOBS\JOB_SQLUpdateStatsAll.sql"SQLServerAgent is not currently running so it cannot be notified of thisaction.
C:\AITScripts>osql -S(LOCAL)\SQLEXPRESS2008 -E -dmaster -n -i"C:\Aitscripts\JOBS\JOB_SQLBackupChecker.sql"SQLServerAgent is not currently running so it cannot be notified of thisaction.
C:\AITScripts>osql -S(LOCAL)\SQLEXPRESS2008 -E -dmaster -n -i"C:\Aitscripts\JOBS\JOB_SQLCleanupMsdbBackupHistory.sql"SQLServerAgent is not currently running so it cannot be notified of thisaction.
C:\AITScripts>osql -S(LOCAL)\SQLEXPRESS2008 -E -dmaster -n -i"C:\Aitscripts\JOBS\JOB_SQLCycleErrorLog.sql"SQLServerAgent is not currently running so it cannot be notified of thisaction.
C:\AITScripts>osql -S(LOCAL)\SQLEXPRESS2008 -E -dmaster -n -i"C:\Aitscripts\JOBS\JOB_SQLBackupSingleDBwithCompression.sql"SQLServerAgent is not currently running so it cannot be notified of thisaction.C:\AITScripts>

Finalizada la instalación, los procedimientos fueron creados en la base de sistema MsDb.

NOTA: Observar los errores al tratar de configurar al SQLServerAgent en un Express 2008.

Descargar los scripts de mantenimiento de bases de datos.

No hay comentarios:

Publicar un comentario

Nota: solo los miembros de este blog pueden publicar comentarios.