Cómo realizar una copia de seguridad SQL y restaurar la limpieza del historial

SQL Server almacena un historial completo de todas las operaciones de restauración y respaldo de SQL, y otras actividades históricas como Correo Electrónico de la Base de Datos, Trabajos, Transvase del Registro, Políticas, Planes de Mantenimiento, etc. en una instancia de servidor en la base de datos msdb.

Todos estos datos, SQL Server los inserta en la copia de seguridad de la base de datos msdb y restaura las tablas históricas. Cada una de estas tablas contiene una fila de datos para una acción diferente:

backupfile – una fila para cada archivo de datos o registro que es respaldado.

backupfilegroup – una fila para cada grupo de archivos que es respaldado.

backupmediafamily – Una fila para cada familia de medios. En caso de que una familia de medios está localizada en un conjunto de medios espejo, la familia de medios tiene una fila separada para cada conjunto espejo en la tabla de historial.

backupmediaset – una fila por cada copia de seguridad de conjunto de medios.

backupset – una fila por cada conjunto de copias de seguridad.

restorefile – una fila para cada archivo restaurado incluyendo archivos restaurados indirectamente por un nombre de grupo de archivos.

restorefilegroup – una fila para cada grupo de archivos restaurado.

restorehistory – una fila para cada operación de restauración.

Tener un gran número de bases de datos y realizar frecuentemente copias de seguridad SQL y operaciones de restauración, resulta en una base de datos msdb grande y es preferible realizar la copia de seguridad SQL y restaurar la limpieza del historial de las tablas de historial de la base de datos msdb.

Por ejemplo, tener una instancia SQL Server que está restaurando las copias de seguridad del registro de transacciones para una gran cantidad de bases de datos cada 15 minutos cada día, cada vez que el evento ocurre, realiza una inserción en una tabla de sistema msdb. Como resultado, los datos históricos en un año obtienen hasta unos pocos millones de filas.

Esto resulta de una base de datos msdb muy grande, y estos datos permanecerán en la base de datos msdb y tienen que ser manualmente removidos.

Remover la copia de seguridad SQL y restaurar el historial desde la base de datos msdb desde SQL Server Management Studio

Una de las opciones para borrar el historial de respaldo y restauración es usar la casilla Delete backup and restore history information for databases.

Para usar esta opción:

1. Haga clic derecho en una base de datos en Object Explorer y seleccione la opción Delete:

   

2. En el diálogo Delete Object, seleccione la opción Delete backup and restore history information for databases.

   

   Note que usar esta opción no sólo borrará y restaurará el historial, también eliminará la base de datos completa.

Remover la copia de seguridad SQL y restaurar el historial desde la base de datos msdb usando procedimientos almacenados del sistema

Para borrar la copia de seguridad y restaurar el historial para una base de datos específica, use el siguiente script:

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'AdventureWorks2014'
GO

El procedimiento almacenado sp_delete_database_backuphistory borrará la información de la copia de seguridad y restaurará las tablas de historial para la base de datos especificada. Tiene que ser ejecutado desde la base de datos msdb.

Para revisar si hay alguna copia de seguridad SQL huérfana y restaurar las filas de historial dejadas, use la siguiente consulta:

SELECT database_name AS [Database], 
COUNT(backup_set_id) AS Orphans
FROM   backupset
WHERE  database_name NOT IN (SELECT name FROM master.dbo.sysdatabases)
GROUP BY database_name

Si la ejecución de esta consulta retorna cualquier resultado, significa que hay bases de datos que tienen copias de seguridad SQL e historial de restauración sin una referencia física en una tabla de historial de respaldo.

Para eliminar el historial de respaldo más antiguo que una fecha especificada desde el respaldo de la base de datos msdb y restaurar las tablas de historial, use el siguiente script:

USE msdb;
GO
EXEC sp_delete_backuphistory @oldest_date = '01/01/2013';

Realizar una copia de seguridad SQL y restaurar la operación de limpieza usando este método puede ser muy lento para una base de datos msdb grande. Correr este comando en un gran conjunto de datos sin usar índices puede tomar horas o incluso días para completarse. Otro problema es que esto bloqueará las copias de seguridad, ya que bloqueará las tablas de sistema de msdb.

En términos de escalabilidad, las tablas del sistema se desempeñarán pobremente si no son correctamente indexadas; de todas maneras, la copia de seguridad de la base de datos msdb y las tablas de rastreo de recuperación no tienen índices. Crear índices en la copia de seguridad de msdb y las tablas de historial de recuperación manejará los problemas de desempeño. El siguiente script crea índices en las tablas de historial msdb:

USE msdb
GO

-- create indexes on the backupset column

CREATE INDEX  IX_backupset_backup_set_iid ON backupset(backup_set_id)
GO
CREATE INDEX IX_backupset_backup_set_uuiid ON backupset(backup_set_uuid)
GO
CREATE INDEX IX_backupset_media_set_iid ON backupset(media_set_id)
GO
CREATE INDEX IX_backupset_backup_finish_date_i ON backupset(backup_finish_date)
GO
CREATE INDEX IX_backupset_backup_start_date_i ON backupset(backup_start_date)
GO

-- create index on the backupmediaset column 

CREATE INDEX IX_backupmediaset_media_set_iid ON backupmediaset(media_set_id)
GO

-- create index on the backupfile column 

CREATE INDEX IX_backupfile_backup_set_iid ON backupfile(backup_set_id)
GO

-- create index on the backupmediafamily column 

CREATE INDEX IX_backupmediafamily_media_set_iid ON backupmediafamily(media_set_id)
GO

-- create indexes on the restorehistory column

CREATE INDEX IX_restorehistory_restore_history_iid ON restorehistory(restore_history_id)
GO
CREATE INDEX IX_restorehistory_backup_set_iid ON restorehistory(backup_set_id)
GO

-- create index on the restorefile column

CREATE INDEX IX_restorefile_restore_history_iid ON restorefile(restore_history_id)
GO

--create index on the restorefilegroup column

CREATE INDEX IX_restorefilegroup_restore_history_iid ON restorefilegroup(restore_history_id)
GO

Remover la copia de seguridad SQL y el historial de recuperación de la base de datos msdb usando la Tarea de Limpieza de Historial

Remover la copia de seguridad histórica y restaurar la información desde tablas históricas en la base de datos msdb puede ser logrado usando el diálogo History Cleanup Task. El diálogo History Cleanup Task soporta la eliminación de copias de seguridad SQL y el historial de restauración, el historial de SQL Server Agent Job y el historial del plan de mantenimiento.

Para eliminar la copia de seguridad y el historial de restauración:

1. Haga clic en Maintenance Plans debajo del nodo Management en Object Explorer, y seleccione la opción New Maintenance Plan:

   

2. Ingrese un nombre para el plan en el diálogo New Maintenance Plan:

   

3. Seleccione History Cleanup Task desde la barra de herramientas Maintenance Plan Tasks:

   

4. En el editor de plan, haga doble clic en el plan seleccionado y elija la opción Backup and restore history:

   

   Hay también una opciçon para ejecutar el plan ad hoc o usar el programador de SQL Server Agent.

   Como los métodos previos, este método también puede ser muy lento para bases de datos msdb.

Traductor: Daniel Calbimonte