Memorias de un DBA

Un blog dedicado a contribuir a la comunidad SQL Server en español

Historial de Backups y Restores para una Base de Datos

with 5 comments

Hola, en esta ocación quisiera contribuir con un script que he desarrollado ya hace algun tiempo y me ha sido muy util al momento de adminstrar bases de datos, y es que hay veces en las que se requiere saber cuando fue el ultimo backup full o de log de una base de datos, o cuando fue la ultima vez que fue restaurada y que archivo se tomo para poder hacer la restauración. Este tipo de preguntas pueden ser un poco complicadas de responder cuando no se ha manejado la base de datos desde el comienzo (nacimiento) lo cual en muchos casos no pasa asi.

Con respecto a los backups, entre los datos mas relevantes del script se indica cuando fue ejecutado el backup, la duración de la operación de backup, el tamaño del archivo de backup generado, y el tipo de backup (Database, Diferencial y Log) y la ubicación del mismo. Con estos datos sera muy facil identificar la cadena de backups para una base de datos

Con respecto a las operaciones de restore, el scritp entre sus datos mas relevantes tiene: la fecha de la operación de  restore, el tamaño del backup restaurado, el tipo de backup que fue restaurado (Database, Diferencial y Log) y el servidor y la ubicación de donde proviene el backup. Es importante mencionar que para las operaciones de restore se muestra “Unknown” en la columna “Duration” debido a que no se tiene el dato de cuanto tiempo tardo realizar la operacion de restore.

Ahora para poder identificar el tipo de operacion mostrada en el historial es importante ver el valor de la columna “Operation_Type”, los posibles valores son “BACKUP” y “RESTORE”. Adicionalmente es importante mencionar que el script esta hecho para mostrar el historial de backups y restores de una sola base de datos al mismo tiempo, ademas no es necesario modificar el script para colocar el nombre de la base de datos de la cual se desea ver el historial, simplemente se debe de ejecutar sobre la base de datos que desea consultar, el script sacara el historial de la base de datos de la conexion que se uso para ejecutarlo.

Ahora sin mas preambulos el script:


select
bs.database_name as TargetDatabase
,bs.backup_start_date as Operation_Date
,cast(datediff(minute,bs.backup_start_date,bs.backup_finish_date)/60 as varchar) + ' hours ' +
cast(datediff(minute,bs.backup_start_date,bs.backup_finish_date)%60 as varchar) + ' minutes ' +
cast(datediff(second,bs.backup_start_date,bs.backup_finish_date)%60 as varchar) + ' seconds'
as [Duration]
,cast(bs.backup_size/1024/1024 as decimal(22,2)) as [BackupSize(MB)]
,'BACKUP' as Operation_Type
,case bs.type
when 'D' then 'Database'
when 'L' then 'Log'
when 'I' then 'Differential'
end as BackupType
,bs.user_name as [User]
,bmf.physical_device_name as BackupFile
,bs.server_name as ServerOrigin
,bs.recovery_model
,bs.begins_log_chain
,bs.is_copy_only
,bms.software_name as BackupSoftware
from msdb.dbo.backupset bs
inner join msdb.dbo.backupmediaset bms
on bs.media_set_id = bms.media_set_id
inner join msdb.dbo.backupmediafamily bmf
on bms.media_set_id = bmf.media_set_id
where bs.database_name = db_name()
and bs.server_name = serverproperty('servername')

union all

select
rh.destination_database_name
,rh.restore_date as operation_date
,'Unknown' as [Duration]
,cast(bs.backup_size/1024/1024 as decimal(22,2)) as [BackupSize(MB)]
,'RESTORE' as Operation_Type
,case rh.restore_type
when 'D' then 'Database'
when 'L' then 'Log'
when 'I' then 'Differential'
end as BackupType
,rh.user_name as [User]
,bmf.physical_device_name as BackupFile
,bs.server_name as ServerOrigin
,bs.recovery_model
,bs.begins_log_chain
,bs.is_copy_only
,bms.software_name as BackupSoftware
from msdb.dbo.backupset bs
inner join msdb.dbo.backupmediaset bms
on bs.media_set_id = bms.media_set_id
inner join msdb.dbo.backupmediafamily bmf
on bms.media_set_id = bmf.media_set_id
inner join msdb.dbo.restorehistory rh
on bs.backup_set_id = rh.backup_set_id
where rh.destination_database_name = db_name()
order by 2 desc

Espero les sea util. Hasta una proxima oportunidad.

Written by dbamemories

junio 21, 2013 a 11:34 pm

5 comentarios

Subscribe to comments with RSS.

  1. Excelente script amigo. Muy útil. Muchas gracias.

    Andres Segura

    agosto 1, 2013 at 11:12 pm

  2. Muy util, muchas gracias.

    Esteban Quijada Suazo

    marzo 21, 2014 at 1:36 pm

  3. Estimado, ya no realiza nuevos post? O en que otro blog lo ubicamos? Saludos,

    Bryan Rodriguez

    agosto 24, 2016 at 12:40 pm

  4. […] te encontrarás con esta problemática. Es por ello que basándonos en la query encontrada en dbamemories, hemos creado una query para obtener el historial de backups y restauraciones de todas las bases de […]

  5. […] te encontrarás con esta problemática. Es por ello que basándonos en la query encontrada en dbamemories, hemos creado una query para obtener el historial de backups y restauraciones de todas las bases de […]


Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: