TLDR: el trabajo de envío de registros predeterminado y el plan de mantenimiento eliminan archivos antiguos del disco, pero las copias de seguridad aún se muestran en SSMS, lo que hace que sea muy lento mostrar la ventana "Restaurar". ¿Cómo eliminar correctamente el antiguo .trn/.bak?
(Máquina virtual Azure, Windows Server 2016 Datacenter (10.0), Microsoft SQL Server Web (nivel de compatibilidad 2017), SMMS 17.3)
(las fechas mostradas están en formato AAAA-MM-DD o DD-MM-AAAA con reloj de 24 horas)
La administración de bases de datos no está en mis habilidades ni en la descripción de mi trabajo y, sin embargo, se me asignó la tarea de preparar copias de seguridad en MS Sql Server, así que trátenme como un profano en este tema.
Decidí crear copias de seguridad completas de la base de datos todos los días (copiadas en un almacenamiento externo, por supuesto), mantener algunas de ellas en el servidor para mayor comodidad, eliminar las antiguas y luego llenar los huecos con registros de transacciones para restaurarlas en un momento dado durante unos días. Después de buscar en Google un poco, hice lo siguiente en las bases de datos de prueba:
Se creó una copia de seguridad completa manual, ya que es necesaria para el envío de registros: aquí no hay nada que falle.
Se generó una tarea de programación de envío de registros de transacciones predeterminada con la eliminación de archivos más antiguos. Funciona bien, envía spam .trn cada minuto y elimina archivos antiguos del disco.
Configure un plan de mantenimiento para crear y eliminar copias de seguridad completas y antiguas de las bases de datos de prueba a medianoche; aquí tampoco hay problema.
Déjelo ejecutar de viernes a lunes, tanto los .trn como los .bak se crearon y eliminaron correctamente, por lo que tuve copias de seguridad de dos días para jugar:
Intenté restaurar a partir de copias de seguridad, lo que causó problemas debido a la falta de archivos de transacciones. Abrir Tasks\Restore\Database es muy, muy lento, SMMS está procesando el 100% del disco durante varios minutos para aproximadamente 10 MB de .trn de base de datos vacía. Para la base de datos sin copia de seguridad completa automática, enumera todos los .trn creados (y la copia de seguridad completa manual en primer lugar), obviamente acompañado de errores de "archivo no encontrado". Para la base de datos con copia de seguridad completa diaria, de forma predeterminada, el resultado limitado se establece nuevamente en La última copia de seguridad y restauración completas funcionaron correctamente, pero aún así fue muy lento para mostrar solo la lista de copias de seguridad. La línea de tiempo de navegación también es muy lenta (varios minutos de "no responder").
Encontré un script para recuperar el historial del script enhttps://www.mssqltips.com/sqlservertip/1601/script-to-retrieve-sql-server-database-backup-history-and-no-backups/(ligeramente modificado para devolver todas las copias de seguridad de la base de datos de interés)
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS backup_type,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE
--(CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)
--and
[database_name] = 'test'
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date
e incluye archivos .trn y .bak eliminados:
y así sucesivamente hasta este mismo minuto, para un total de 29682 puntos de respaldo.
Me gustaría que la ventana "Restaurar" vuelva a responder y tengo dos bases de datos de prueba para realizar pruebas libremente. ¿Puedo borrar la lista de respaldo de forma segura? ¿Cómo puedo hacerlo automático para que se adapte bien a mis trabajos programados actuales?
Tengo privilegios de administración completos tanto en db como en os, control creativo total y muchos recursos restantes en vm, por lo que casi no hay limitaciones para las posibles soluciones.
Respuesta1
Es posible utilizar elsp_delete_backuphistoryprocedimiento almacenado para borrar el historial anterior. Tenga en cuenta que no sabe lo que ha eliminado o no, pero borrará el historial anterior a la fecha X que le proporcionó.
Una vez que borre la gran cantidad de entradas de respaldo, debería volver a responder. Esto se puede incluir fácilmente en el trabajo de su agente llamando al SP con la fecha actual menos X número de días como último paso del trabajo.