Un plan de mantenimiento completo para SQL Server 2008
Optimización de bases de datos
Esta sección está dedicada a la optimización de las bases de datos. En este ejemplo, vamos a configurar el código para hacer lo siguiente (ver un diagrama aproximado de cómo están conectados los servidores):
Copia de seguridad de la base de datos desde el servidor activo a una carpeta de copia de seguridad compartida
Volver a indexar la base de datos
Cortar todas las conexiones y restaurar la base de datos en el servidor de informes.
Servidores enlazados será en un artículo separado pronto
El envío de logs se tratará en otro artículo próximamente
Cuando se hace a través del servidor, tendrás que permitir el acceso a la cuenta que se está ejecutando en SQL Server al sistema de archivos de los servidores de destino.
Todo el código SQL se ejecuta desde una base de datos de utilidades y el esquema se establece en "maint".
Hay una serie de otros artículos que hemos escrito a continuación que pueden ser de tu interés.
Plan de mantenimiento completo
Ahora podemos gestionar todos estos segmentos de código separados con otro procedimiento que los llamará en el orden correcto, esto se pondrá en el servidor en vivo. El código se ejecuta de la siguiente manera:
Calcula la hora
Si es de noche, vuelve a indexar la base de datos
Ejecutar una copia de seguridad de la base de datos (añadir donde sea necesario)
Si es de noche, crea una copia de seguridad de las otras bases de datos (añadir donde sea necesario)
Corta las conexiones y restaura la base de datos (añadir donde sea necesario)
Este código ha sido probado durante meses sin ningún problema.
Ten en cuenta que la reducción de archivos de registro y archivos de base de datos debe mantenerse al mínimo, al ejecutar una copia de seguridad estás vaciando el registro (aunque conservará el espacio utilizado). Si es necesario cuando se debe ejecutar fuera de las horas normales de trabajo.
SQL Code
Use [utilities]GOCREATE PROC [maint].MaintenancePlan AS BEGINDECLARE @BackupType VARCHAR(1)='E'IF DATEPART(HOUR,GETDATE()) BETWEEN 5 AND 21 BEGINSET @BackupType='D'END--EXEC ('USE TempDb; DBCC SHRINKFILE(templog, 0)');--This is only needed when space is at a premium!--Re-index LiveIF @BackupType='E' EXEC [maint].DatabaseReIndex 'dbname'--Create BackupBACKUP DATABASE TO DISK=N'{backuplocation}{dbname}.bak'WITH NOFORMAT, INIT, NAME =N'{dbname}', SKIP, NOREWIND, NOUNLOAD, STATS= 10;--EXEC ('USE ; DBCC SHRINKFILE(_log, 0)');--This is only needed when space is at a premium!--Backup Other Files at NightIF @BackupType='E' BEGIN EXEC [maint].DatabaseReIndex 'dbname' --Backup Others BACKUP DATABASE [databasename] TO DISK=N'{backuplocation}{dbname2}.bak' WITH FORMAT,INIT, NAME =N'{dbname2}',SKIP, NOREWIND, NOUNLOAD, STATS= 10END--Restore Backups on other serverEXEC [server].[utilities].[maint].KillConnections 'dbname';EXEC [server].[utilities].[maint].RestoreDatabase_{dbname};--Restore Backups on other server for db_2 etcIF @BackupType='E' BEGIN EXEC [server].[utilities].[maint].KillConnections 'dbname2'; EXEC [server].[utilities].[maint].RestoreDatabase_{dbname2};ENDENDGOCopia de seguridad de la base de datos
Para obtener el código de copia de seguridad de la base de datos, lo más fácil es ejecutar el código desde SSMS.
Sigue el proceso que usarías normalmente, y luego selecciona "Script Action to New Query Window".
Copia este código en el plan de mantenimiento.
Volver a indexar la base de datos
A continuación podemos añadir algo de código para re-indexar nuestra base de datos, esto de nuevo es un procedimiento almacenado compartido donde sólo necesitas decirle al sistema el nombre.
Para evitar la duplicación de código, puedes leerlo en el siguiente enlace.
Eliminar conexiones
Cuando se realiza una restauración en la base de datos, sólo se puede tener una conexión a la misma (el proceso que realiza la restauración), por lo tanto podemos crear un procedimiento almacenado para cerrar todas las conexiones aparte del proceso actual. De nuevo hemos creado un artículo aparte para esto.
Restaurar base de datos
Este código también puede ser programado desde SQL Server Management Studio. Si añades este código a un procedimiento almacenado, puedes llamarlo desde otros procesos e incluso desde otros servidores muy fácilmente. Hemos movido esto a un artículo separado para cubrir más opciones.
