Ruta mas rapida para restaurar una base de datos

Enviado por kenneth en

Imagen de kenneth

Votar: 

Average: 5 (2 votes)

Esta es la pregunta que por lo general cuando enfrentamos un evento en el que necesitamos recuperar la base de datos nos hacen:

  1. ¿Cuánto va a durar restaurando la base de datos?
  2. ¿Es la forma más rápida de restaurarla?
  3. ¿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:

  1. La estrategia de restore está definida por:
    1. Full, Full para el script se considera sobre la base de datos completa, no full sobre filegroups/files Individuales
    2. Diferenciales y log
  2. 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)
  3. El script no va a verificar la existencia del archivo, este va únicamente a tomar la información existente en msdb.
  4. 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.

  1. USE MSDB;
  2. ;
  3. 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 */)
  4.           AS ( SELECT   bs.backup_set_id ,
  5.  
  6.                         bs.database_name ,
  7.                         bs.first_lsn ,
  8.                         bs.last_lsn ,
  9.                         DATEDIFF(SECOND, bs.backup_start_date,
  10.                                  bs.backup_finish_date) AS duration_sec ,
  11.                         1 AS restore_sequence ,
  12.                         bs.type ,
  13.                         DENSE_RANK() OVER ( PARTITION BY bs.database_name ORDER BY bs.last_lsn DESC ) AS ExecPath ,
  14.                         bs.backup_start_date ,
  15.                         bs.backup_finish_date ,
  16.                         bs.backup_size / 1024 / 1024 ,
  17.                         bs.compressed_backup_size / 1024 / 1024 ,
  18.                         DATEDIFF(SECOND, bs.backup_start_date,
  19.                                  bs.backup_finish_date) AS Individual_duration_sec
  20.                FROM     ( SELECT    database_name ,
  21.                                     server_name ,
  22.                                     backup_set_id ,
  23.                                     first_lsn ,
  24.                                     last_lsn ,
  25.                                     checkpoint_lsn ,
  26.                                     database_backup_lsn ,
  27.                                     [type] ,
  28.                                     differential_base_lsn ,
  29.                                     DENSE_RANK() OVER ( PARTITION BY database_name ORDER BY first_lsn DESC ) AS position ,
  30.                                     backup_start_date ,
  31.                                     backup_finish_date ,
  32.                                     backup_size ,
  33.                                     compressed_backup_size ,
  34.                                     database_creation_date ,
  35.                                     recovery_model ,
  36.                                     USER_NAME
  37.                           FROM      backupset
  38.                           WHERE     type = 'D'
  39.                                     AND is_copy_only = 0
  40.                         ) AS bs
  41.                WHERE    bs.position = 1
  42.                UNION ALL
  43.                SELECT   bs.backup_set_id ,
  44.                         bs.database_name ,
  45.                         bs.first_lsn ,
  46.                         bs.last_lsn ,
  47.                         DATEDIFF(SECOND, bs.backup_start_date,
  48.                                  bs.backup_finish_date) + cte.duration_sec AS duration_sec ,
  49.                         cte.restore_sequence + 1 AS restore_sequence ,
  50.                         bs.type ,
  51.                         cte.ExecPath
  52.                         + ( ROW_NUMBER() OVER ( PARTITION BY bs.database_name ORDER BY bs.last_lsn DESC ) )
  53.                         - 1 AS ExecPath ,
  54.                         bs.backup_start_date ,
  55.                         bs.backup_finish_date ,
  56.                         bs.backup_size / 1024 / 1024 ,
  57.                         bs.compressed_backup_size / 1024 / 1024 ,
  58.                         DATEDIFF(SECOND, bs.backup_start_date,
  59.                                  bs.backup_finish_date) AS Individual_duration_sec
  60.                FROM     backupset bs
  61.                         INNER JOIN cte_backup_FullAndDiff cte ON bs.database_name = cte.database_name
  62.                                                               AND ( ( bs.differential_base_lsn = cte.first_lsn
  63.                                                               AND bs.type = 'I'
  64.                                                               )
  65.                                                               OR ( bs.last_lsn > cte.last_lsn
  66.                                                               AND bs.type = 'L'
  67.                                                               )
  68.                                                               )
  69.                WHERE    ( bs.type = 'I'
  70.                           AND cte.backuptype = 'D'
  71.                         )
  72.                         OR ( bs.type = 'L'
  73.                              AND NOT EXISTS ( SELECT    1
  74.                                               FROM      backupset bs_in
  75.                                               WHERE     bs_in.database_name = cte.database_name
  76.                                                         AND bs_in.last_lsn > cte.last_lsn
  77.                                                         AND bs_in.last_lsn < bs.last_lsn
  78.                                                         AND bs_in.type = 'L' )
  79.                            )
  80.              ),
  81.         cte_backup_FastPath ( backup_set_id, database_name, ResultOrder, backup_type, backup_size_MB, compressed_backup_size_MB, Duration_Seconds )
  82.           AS ( SELECT   MAX(BackupFasterPath.backup_set_id) AS backup_set_id ,
  83.                         CASE WHEN ( ( GROUPING(BackupFasterPath.database_name) = 1 )
  84.                                     AND ( GROUPING(BackupFasterPath.backuptype) = 1 )
  85.                                   ) THEN 'FULL+DIFF+LOG'
  86.                              WHEN ( GROUPING(BackupFasterPath.database_name) = 1 )
  87.                              THEN CASE BackupFasterPath.backuptype
  88.                                     WHEN 'D' THEN ' ALL_FULL'
  89.                                     WHEN 'I' THEN ' ALL_Diff'
  90.                                     WHEN 'L' THEN ' ALL_Log'
  91.                                     ELSE 'delete'
  92.                                   END
  93.                              ELSE ISNULL(BackupFasterPath.database_name,
  94.                                          'delete')
  95.                         END AS database_name ,
  96.                         CASE WHEN ( ( GROUPING(BackupFasterPath.database_name) = 1 )
  97.                                     AND ( GROUPING(BackupFasterPath.backuptype) = 1 )
  98.                                   ) THEN 2
  99.                              WHEN ( GROUPING(BackupFasterPath.database_name) = 1 )
  100.                              THEN 1
  101.                              ELSE 3
  102.                         END AS ResultOrder ,
  103.                         CASE WHEN ( GROUPING(BackupFasterPath.database_name) = 1 )
  104.                              THEN 'ALL'
  105.                              ELSE CASE BackupFasterPath.backuptype
  106.                                     WHEN 'D' THEN 'Full'
  107.                                     WHEN 'I' THEN 'Differential'
  108.                                     WHEN 'L' THEN 'Log'
  109.                                     ELSE 'delete'
  110.                                   END
  111.                         END AS backup_type ,
  112.                         SUM(CONVERT(DECIMAL(24, 2), BackupFasterPath.backup_size_MB)) AS backup_size_MB ,
  113.                         SUM(CONVERT(DECIMAL(24, 2), BackupFasterPath.compressed_backup_size_MB)) AS compressed_backup_size_MB ,
  114.                         SUM(BackupFasterPath.Individual_duration_sec) AS Duration_Seconds --,
  115.                FROM     ( SELECT    * ,
  116.                                     DENSE_RANK() OVER ( PARTITION BY database_name ORDER BY Total_Duration_Secs ASC, Total_stept ASC, execPath ASC ) AS FasterPath
  117.                           FROM      ( SELECT    * ,
  118.                                                 MAX(duration_sec) OVER ( PARTITION BY database_name,
  119.                                                               ExecPath ) AS Total_Duration_Secs ,
  120.                                                 MAX(restore_sequence) OVER ( PARTITION BY database_name,
  121.                                                               ExecPath ) AS Total_stept
  122.                                       FROM      cte_backup_FullAndDiff
  123.                                     ) AS BackUpsWithMax
  124.                         ) AS BackupFasterPath
  125.                         INNER JOIN [dbo].[backupmediafamily] bf ON bf.media_set_id = BackupFasterPath.backup_set_id
  126.                WHERE    1 IN ( BackupFasterPath.FasterPath,
  127.                                BackupFasterPath.Total_stept )
  128.                GROUP BY CUBE(BackupFasterPath.database_name,
  129.                              BackupFasterPath.backuptype)
  130.                HAVING   CASE WHEN ( GROUPING(BackupFasterPath.database_name) = 1 )
  131.                              THEN 'ALL'
  132.                              ELSE CASE BackupFasterPath.backuptype
  133.                                     WHEN 'D' THEN 'Full'
  134.                                     WHEN 'I' THEN 'Differential'
  135.                                     WHEN 'L' THEN 'Log'
  136.                                     ELSE 'delete'
  137.                                   END
  138.                         END <> 'delete'
  139.              )
  140.     SELECT  @@SERVERNAME AS SERVERNAME,
  141.             cte.database_name ,
  142.             bs.backup_start_date ,
  143.             bs.backup_finish_date ,
  144.             cte.backup_type ,
  145.             cte.backup_size_MB ,
  146.             cte.compressed_backup_size_MB ,
  147.             cte.Duration_Seconds ,
  148.             bs.database_creation_date ,
  149.             bs.recovery_model ,
  150.             bf.physical_device_name ,
  151.             bs.USER_NAME
  152.     FROM    cte_backup_FastPath cte
  153.             LEFT OUTER JOIN dbo.backupset bs ON bs.media_set_id = cte.backup_set_id
  154.                                                 AND bs.database_name = cte.database_name
  155.             LEFT OUTER JOIN [dbo].[backupmediafamily] bf ON bf.media_set_id = bs.backup_set_id
  156.     ORDER BY ResultOrder ASC ,
  157.             bs.recovery_model DESC
  158. OPTION  ( MAXDOP 2, Maxrecursion 10000 );
  159. 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