
Votar:
Esta es la pregunta que por lo general cuando enfrentamos un evento en el que necesitamos recuperar la base de datos nos hacen:
- ¿Cuánto va a durar restaurando la base de datos?
- ¿Es la forma más rápida de restaurarla?
- ¿Vamos a recuperar todos los datos?
En el siguiente post vamos a darle respuesta a las primeras dos preguntas, la tercera va a depender de la capacidad de poder realizar un tail log backup.
Lo que este script asume:
-
La estrategia de restore está definida por:
- Full, Full para el script se considera sobre la base de datos completa, no full sobre filegroups/files Individuales
- Diferenciales y log
- Para la definición de ruta más rápida se va a tomar la ruta que haya durado menos creado el backup(se asume que debería de durar menos restaurando)
- El script no va a verificar la existencia del archivo, este va únicamente a tomar la información existente en msdb.
- El Script se ejecuta de SQL server 2008 y superior
Y acá es donde además se le puede dar respuesta a la pregunta de
¿Para qué sirve MSDB?
Entre otras cosas, MSDB guarda la meta data de Backus realizados.
¿Es importante respaldar MSDB?
Si absoluto, por ejemplo, si por alguna razón perdieras el servidor completo, si tienes un respaldo de MSDB, lo puedes restaurar en otro servidor, con otro nombre de base de datos, y el script que publico en este post, te puede ayudar a darte una idea de donde comenzar con la ardua labor de restaurar la operación.
-
USE MSDB;
-
;
-
WITH cte_backup_FullAndDiff ( backup_set_id, /*server_name, */ database_name, first_lsn, last_lsn, duration_sec, restore_sequence, backuptype, ExecPath, backup_start_date, backup_finish_date, backup_size_MB, compressed_backup_size_MB, Individual_duration_sec /*, database_creation_date, recovery_model, user_name */)
-
AS ( SELECT bs.backup_set_id ,
-
-
bs.database_name ,
-
bs.first_lsn ,
-
bs.last_lsn ,
-
DATEDIFF(SECOND, bs.backup_start_date,
-
bs.backup_finish_date) AS duration_sec ,
-
1 AS restore_sequence ,
-
bs.type ,
-
DENSE_RANK() OVER ( PARTITION BY bs.database_name ORDER BY bs.last_lsn DESC ) AS ExecPath ,
-
bs.backup_start_date ,
-
bs.backup_finish_date ,
-
bs.backup_size / 1024 / 1024 ,
-
bs.compressed_backup_size / 1024 / 1024 ,
-
DATEDIFF(SECOND, bs.backup_start_date,
-
bs.backup_finish_date) AS Individual_duration_sec
-
FROM ( SELECT database_name ,
-
server_name ,
-
backup_set_id ,
-
first_lsn ,
-
last_lsn ,
-
checkpoint_lsn ,
-
database_backup_lsn ,
-
[type] ,
-
differential_base_lsn ,
-
DENSE_RANK() OVER ( PARTITION BY database_name ORDER BY first_lsn DESC ) AS position ,
-
backup_start_date ,
-
backup_finish_date ,
-
backup_size ,
-
compressed_backup_size ,
-
database_creation_date ,
-
recovery_model ,
-
USER_NAME
-
FROM backupset
-
WHERE type = 'D'
-
AND is_copy_only = 0
-
) AS bs
-
WHERE bs.position = 1
-
UNION ALL
-
SELECT bs.backup_set_id ,
-
bs.database_name ,
-
bs.first_lsn ,
-
bs.last_lsn ,
-
DATEDIFF(SECOND, bs.backup_start_date,
-
bs.backup_finish_date) + cte.duration_sec AS duration_sec ,
-
cte.restore_sequence + 1 AS restore_sequence ,
-
bs.type ,
-
cte.ExecPath
-
+ ( ROW_NUMBER() OVER ( PARTITION BY bs.database_name ORDER BY bs.last_lsn DESC ) )
-
- 1 AS ExecPath ,
-
bs.backup_start_date ,
-
bs.backup_finish_date ,
-
bs.backup_size / 1024 / 1024 ,
-
bs.compressed_backup_size / 1024 / 1024 ,
-
DATEDIFF(SECOND, bs.backup_start_date,
-
bs.backup_finish_date) AS Individual_duration_sec
-
FROM backupset bs
-
INNER JOIN cte_backup_FullAndDiff cte ON bs.database_name = cte.database_name
-
AND ( ( bs.differential_base_lsn = cte.first_lsn
-
AND bs.type = 'I'
-
)
-
OR ( bs.last_lsn > cte.last_lsn
-
AND bs.type = 'L'
-
)
-
)
-
WHERE ( bs.type = 'I'
-
AND cte.backuptype = 'D'
-
)
-
OR ( bs.type = 'L'
-
AND NOT EXISTS ( SELECT 1
-
FROM backupset bs_in
-
WHERE bs_in.database_name = cte.database_name
-
AND bs_in.last_lsn > cte.last_lsn
-
AND bs_in.last_lsn < bs.last_lsn
-
AND bs_in.type = 'L' )
-
)
-
),
-
cte_backup_FastPath ( backup_set_id, database_name, ResultOrder, backup_type, backup_size_MB, compressed_backup_size_MB, Duration_Seconds )
-
AS ( SELECT MAX(BackupFasterPath.backup_set_id) AS backup_set_id ,
-
CASE WHEN ( ( GROUPING(BackupFasterPath.database_name) = 1 )
-
AND ( GROUPING(BackupFasterPath.backuptype) = 1 )
-
) THEN 'FULL+DIFF+LOG'
-
WHEN ( GROUPING(BackupFasterPath.database_name) = 1 )
-
THEN CASE BackupFasterPath.backuptype
-
WHEN 'D' THEN ' ALL_FULL'
-
WHEN 'I' THEN ' ALL_Diff'
-
WHEN 'L' THEN ' ALL_Log'
-
ELSE 'delete'
-
END
-
ELSE ISNULL(BackupFasterPath.database_name,
-
'delete')
-
END AS database_name ,
-
CASE WHEN ( ( GROUPING(BackupFasterPath.database_name) = 1 )
-
AND ( GROUPING(BackupFasterPath.backuptype) = 1 )
-
) THEN 2
-
WHEN ( GROUPING(BackupFasterPath.database_name) = 1 )
-
THEN 1
-
ELSE 3
-
END AS ResultOrder ,
-
CASE WHEN ( GROUPING(BackupFasterPath.database_name) = 1 )
-
THEN 'ALL'
-
ELSE CASE BackupFasterPath.backuptype
-
WHEN 'D' THEN 'Full'
-
WHEN 'I' THEN 'Differential'
-
WHEN 'L' THEN 'Log'
-
ELSE 'delete'
-
END
-
END AS backup_type ,
-
SUM(CONVERT(DECIMAL(24, 2), BackupFasterPath.backup_size_MB)) AS backup_size_MB ,
-
SUM(CONVERT(DECIMAL(24, 2), BackupFasterPath.compressed_backup_size_MB)) AS compressed_backup_size_MB ,
-
SUM(BackupFasterPath.Individual_duration_sec) AS Duration_Seconds --,
-
FROM ( SELECT * ,
-
DENSE_RANK() OVER ( PARTITION BY database_name ORDER BY Total_Duration_Secs ASC, Total_stept ASC, execPath ASC ) AS FasterPath
-
FROM ( SELECT * ,
-
MAX(duration_sec) OVER ( PARTITION BY database_name,
-
ExecPath ) AS Total_Duration_Secs ,
-
MAX(restore_sequence) OVER ( PARTITION BY database_name,
-
ExecPath ) AS Total_stept
-
FROM cte_backup_FullAndDiff
-
) AS BackUpsWithMax
-
) AS BackupFasterPath
-
INNER JOIN [dbo].[backupmediafamily] bf ON bf.media_set_id = BackupFasterPath.backup_set_id
-
WHERE 1 IN ( BackupFasterPath.FasterPath,
-
BackupFasterPath.Total_stept )
-
GROUP BY CUBE(BackupFasterPath.database_name,
-
BackupFasterPath.backuptype)
-
HAVING CASE WHEN ( GROUPING(BackupFasterPath.database_name) = 1 )
-
THEN 'ALL'
-
ELSE CASE BackupFasterPath.backuptype
-
WHEN 'D' THEN 'Full'
-
WHEN 'I' THEN 'Differential'
-
WHEN 'L' THEN 'Log'
-
ELSE 'delete'
-
END
-
END <> 'delete'
-
)
-
SELECT @@SERVERNAME AS SERVERNAME,
-
cte.database_name ,
-
bs.backup_start_date ,
-
bs.backup_finish_date ,
-
cte.backup_type ,
-
cte.backup_size_MB ,
-
cte.compressed_backup_size_MB ,
-
cte.Duration_Seconds ,
-
bs.database_creation_date ,
-
bs.recovery_model ,
-
bf.physical_device_name ,
-
bs.USER_NAME
-
FROM cte_backup_FastPath cte
-
LEFT OUTER JOIN dbo.backupset bs ON bs.media_set_id = cte.backup_set_id
-
AND bs.database_name = cte.database_name
-
LEFT OUTER JOIN [dbo].[backupmediafamily] bf ON bf.media_set_id = bs.backup_set_id
-
ORDER BY ResultOrder ASC ,
-
bs.recovery_model DESC
-
OPTION ( MAXDOP 2, Maxrecursion 10000 );
-
GO
SERVERNAME | database_name | backup_start_date | backup_finish_date | backup_type | backup_size_MB | compressed_backup_size_MB | Duration_Seconds | database_creation_date | recovery_model | physical_device_name | user_name |
---|---|---|---|---|---|---|---|---|---|---|---|
KENNETH-VAIO | ALL_FULL | NULL | NULL | ALL | 564.26 | 459.91 | 25 | NULL | NULL | NULL | NULL |
KENNETH-VAIO | ALL_Diff | NULL | NULL | ALL | 2.08 | 2.08 | 0 | NULL | NULL | NULL | NULL |
KENNETH-VAIO | ALL_Log | NULL | NULL | ALL | 0.08 | 0.08 | 0 | NULL | NULL | NULL | NULL |
KENNETH-VAIO | FULL+DIFF+LOG | NULL | NULL | ALL | 566.42 | 462.07 | 25 | NULL | NULL | NULL | NULL |
KENNETH-VAIO | Credit | 8/26/2008 | 8/26/2008 | Full | 157.09 | 52.74 | 24 | 8/26/2008 | SIMPLE | C:\SQLServerDatabases\backups\CreditBackup100.bak | CHICAGO\Administrator |
KENNETH-VAIO | profiler | 7/4/2014 | 7/4/2014 | Full | 79.08 | 79.08 | 1 | 1/13/2014 | FULL | C:\del\backups\profiler_full.bak | Kenneth-Vaio\Kenneth |
KENNETH-VAIO | profiler | 7/4/2014 | 7/4/2014 | Differential | 2.08 | 2.08 | 0 | 1/13/2014 | FULL | C:\del\backups\profiler_diff2.bak | Kenneth-Vaio\Kenneth |
KENNETH-VAIO | profiler | 7/4/2014 | 7/4/2014 | Log | 0.08 | 0.08 | 0 | 1/13/2014 | FULL | C:\del\backups\profiler_log3.bak | Kenneth-Vaio\Kenneth |