Revertir su base de datos SQL Server a un punto específico en el tiempo (4290180)

Introducción

Hay ciertas circunstancias en las que usted puede necesitar retrotraer su base de datos a un punto específico del tiempo. Pueden haber varias razones para que esto sea necesario, pero usualmente está relacionado a la ejecución accidental o maliciosa de sentencias DML o DDL. Por ejemplo:

• Alguien ejecutó una actualización sin una cláusula WHERE
• Alguien accidentalmente borró una tabla
• Su intento de actualización de la base de datos falló, y dejó a la base de datos en un estado desconocido

SQL Server provee dos mecanismos para que usted se recupere de estos eventos:

• Snapshots de la Base de Datos SQL Server
• Restauración a un punto de tiempo

Snapshots de la Base de Datos SQL Server

Si usted sabe que va a realizar una tarea que podría dañar sus datos, sería prudente tomar un snapshot de la base de datos antes de hacerlo.

Los snapshots de las bases de datos son fáciles y rápidos de crear, y muy fáciles para retrotraer. Por supuesto si usted no anticipó el evento dañino, o usted no usa snapshots de bases de datos como parte de su estrategia de protección de datos, esto puede no ser una opción para usted, y se verá forzado a usar el método de restauración a un punto del tiempo. Puede leer más acera de esto en el artículo en línea Using SQL Server database snapshots to protect yourself against accidental data modification.

Restauración a un punto del tiempo

Es importante saber que volver a un punto de tiempo específico no siempre es una opción, dado que es completamente dependiente de 2 cosas:

1. El modelo de recuperación de la base de datos y
2. Su cadena de transacciones

Modelos de Recuperación de SQL Server

SQL Server ofrece 3 modelos re recuperación:

• Simple
• Por medio de registros de operaciones masivas
• Completa

Elegir su modelo de recuperación es una decisión primordial porque esencialmente define cuántos datos usted está preparado para perder en el caso de un desastre.

En el caso de un modelo de recuperación simple, usted esencialmente está acordando que está bien perder todos los cambios hechos en su base de datos después de la última copia de seguridad completa. La ventaja de este modelo de recuperación es por supuesto que toma muy poca administración. Usted no tiene que preocuparse acerca de cosas como una cadena de registros o toneladas de copias de seguridad de registros, o incluso cuando el registro esté truncado. Obviamente esto nunca será suficientemente bueno para bases de datos de misiones críticas, pero definitivamente tiene su lugar y sus usuarios.

El propósito del modelo de registros de operaciones masivas es permitirle realizar operaciones masivas escribiendo cada transacción al registro de transacciones, y como tal mejorar el desempeño de su operación masiva. La desventaja de esto es por supuesto que no cada transacción es registrada en el registro, y como tal no le permite hacer una restauración a un punto de tiempo.

Para poder restaurar a un punto de tiempo específico, usted necesita tener su base de datos configurada para usar el modelo de recuperación completa. Esto significa que cada evento que toma lugar en la base de datos es escrito en el registro, lo cual es hace posible para usted restaurar a un punto específico. Pero sólo tener la base de datos configurada con el modelo de recuperación completa no es suficiente. A menos que usted tenga una cadena de registro completa, usted aún no podrá restaurar su base de datos al punto en el tiempo que requiere.

La cadena de registro

La cadena de registro inicia cuando usted crea una copia de seguridad completa de una base de datos que está en el modelo de recuperación completa. Esto significa que entre esta copia de seguridad completa y la siguiente copia de seguridad completa, cada operación que ocurrió en la base de datos será guardada en la copia de seguridad completa, una copia de seguridad diferencia o una copia de seguridad del registro.

Esto asegura que cuando usted necesita restaurar su base de datos a un punto específico de tiempo o a una transacción específica, todas las entradas del registro requeridas estarán ahí para hacer esto posible.

Mientras la cadena de registro no esté rota por, por ejemplo, una instancia que cambia el modelo de recuperación a un modelo de recuperación diferente y viceversa luego, o retrotraerla a un snapshot de base de datos, usted podrá recuperar su base de datos a un punto de tiempo específico. Incidentalmente, tomar otra copia de seguridad completa entre las copias de seguridad del registro no rompe la cadena de registro.

Si su cadena de registro se rompe, usted puede reiniciar simplemente creando una nueva copia de seguridad completa o diferencial.

Realizando una restauración a un punto de tiempo con SQL Server

SQL Server le permite realizar la restauración a un punto de tiempo usando T-SQL o SSMS.

Para poder realizar una restauración a un punto de tiempo usted necesita restaurar la copia de seguridad completa terminada antes del punto al que quiere restaurar, y luego restaurar todas las copia de seguridad de registros incluyendo el que contiene el punto al que quiere ir.

Usando SQL Server Management Studio

Para usar SQL Server Management Studio, usted puede seguir el siguiente procedimiento:

1. Haga clic derecho en la base de datos que desea revertir hacia un punto de tiempo

2. Seleccione Task/Restore/Database

   

   SSMS verificará automáticamente todas las copias de seguridad disponibles comenzando de la última copia de seguridad completa

3. En el diálogo restore database seleccione la opción Timeline

4. Ingrese el punto exacto de tiempo al que desea restaurar la base de datos, ya sea arrastrando el deslizador al punto deseado o seleccionándolo usando los campos de fecha y hora.

   

   Note que en SQL Server 2012, automáticamente creará una copia de seguridad de la cola del registro antes de comenzar la restauración.

5. Haga clic en OK
6. Haga clic en OK de nuevo

Esto iniciará la restauración.

Usando Transact SQL

Cualquier punto en el tiempo requiere una cadena de registro completa, sin importar el método usado para realizar la restauración. Esto significa que usted necesita restaurar la última copia de seguridad completa y todas las copias de seguridad de registros incluyendo aquella que contiene el punto al cual le gustaría restaurar.

El punto en el tiempo es siempre restaurado desde una copia de seguridad de registro. Lo cual significa que usted necesita usar la sentencia RESTORE LOG, con la cláusula Stop AT para restaurar la base de datos a un punto en el tiempo. La operación a la que se restauraría es la última transacción que ha sido exitosa consolidada previamente al punto especificado.

Aquí está un ejemplo de script:

1. USE master
   
   RESTORE DATABASE AdventureWorks2012
   FROM DISK = 'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\
   MSSQL\Backup\AW2012.bak'
   WITH FILE = 3
   	,REPLACE
   	,NORECOVERY;
   

   En el ejemplo anterior nosotros restauraremos la copia de seguridad completa desde el archivo ‘D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\AW2012.bak’. Dado que este archivo contiene múltiples copias de seguridad, necesitamos especificar qué ARCHVO usar.

   Usted puede identificar el número del archivo mirando a la columna Position cuando se ejecuta este comando:

   RESTORE HEADERONLY
   FROM DISK = 'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\
   MSSQL\Backup\AW2012.bak'
   

   La opción REPLACE indica que sobrescribirá la base de datos existente. De la misma manera que hubiera sobrescrito siguiendo los pasos en SSMS.

2. Luego restaure cada registro, especificando la fecha y la hora para usar STOP AT. Esto necesita ser especificado en cada sentencia RESTORE LOG.

   RESTORE LOG AdventureWorks2012
   FROM DISK = 'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\
   MSSQL\Backup\AW2012.bak'
   WITH FILE = 4 ,NORECOVERY ,STOPAT = '2013-09-28 10:16:28.873’;
   
   RESTORE LOG AdventureWorks2012
   FROM DISK = 'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\
   MSSQL\Backup\AW2012.bak'
   WITH FILE = 5 ,NORECOVERY ,STOPAT = '2013-09-28 10:16:28.873’;
   

3. Y finalmente recupere la base de datos

   RESTORE DATABASE AdventureWorks2012 WITH RECOVERY;
   

Restaurar a un punto en el tiempo con SQL Server puede tomar una significativa cantidad de tiempo, dependiendo del tamaño de su base de datos. SQL Server está básicamente haciendo una restauración completa desde la última copia de seguridad y todas las copias de seguridad de registros hasta el punto al cual usted quiere ir.

Aparte del tiempo que toma realizar la restauración, otra desventaja es que la base de datos no es accesible durante este periodo.

Si usted tiene una base de datos muy grande y realmente sólo quiere deshacer el último par de transacciones, puede ser más fácil usar una herramienta como ApexSQL Log para identificar qué eran esas últimas pocas transacciones y cómo deshacerlas.

Usando ApexSQL Log o ApexSQL Recover para revertir a un punto en el tiempo específico

ApexSQL Log es un lector de registros de transacciones SQL Server, el cual puede ser usado para identificar las últimas transacciones realizadas al leer el registro de transacciones en línea de SQL Server.ApexSQL Log puede entonces también crear un script deshacer para ayudarle a deshacer estos cambios.

ApexSQL Recover es una herramienta de recuperación de base de datos SQL Server que lee información de los archivos ldf y mdf y permite la recuperación desde operaciones de descarte, truncado y eliminación, reversión de operaciones, extracción directamente desde la copia de seguridad y más.

Ambas herramientas comparten la funcionalidad para leer archivos de registro de transacciones y volver a un punto específico en el tiempo

Aquí está cómo hacerlo:

1. Abra la aplicación e inicie una nueva sesión apuntando a la base de datos que desea revertir a un punto en el tiempo específico y haga clic en el botón Next

   

2. Luego usted necesita añadir todas las copias de seguridad o copias de seguridad de registro que contienen los datos que usted desea retrotraer. Esto significa: Todos los datos que ocurren después del evento no deseado

   

3. Por el bien de esta demostración elegiremos Abrir los resultados en la vista de cuadrícula. Esto nos permitirá ver y elegir qué transacciones queremos revertir. Esto es especialmente útil cuando no está 100% seguro de que punto necesita retroceder

   

4. Filtre las transacciones del registro basado en el tiempo. En este caso, dado que usted sólo quiere deshacer transacciones que ocurrieron después de un tiempo específico, usted sólo necesita especificar el “from date”, el cual es un punto al que desea revertir la base de datos.

   

5. Revise las transacciones mostradas en la cuadrícula como una verificación final. Usted puede indicar qué operaciones incluir resaltándolas o seleccionándolas. En este caso, deseamos retrotraer todo después de la fecha que elegimos.

6. Haga clic en el botón Select All en la cinta principal, o haga clic derecho en los resultados y elija la opción Select All en el menú contextual

   

7. Una vez que se seleccionan todas las operaciones, seleccione la opción Undo en la cinta principal

   

   El script se creará para usted en un editor de scripts SQL Server, que le permitirá ejecutar el script inmediatamente o guardar el script y ejecutarlo desde SSMS u otra herramienta similar más adelante.

   

Hay un par de ventajas al usar este enfoque:

1. La base de datos puede permanecer en línea y accesible mientras usted está deshaciendo operaciones
2. ApexSQL Log y ApexSQL Recover incorporan lógica para eliminar operaciones redundantes. Cada operación no es directamente aplicada a la base de datos, sino que es procesada y sólo el script deshacer final que aplicó
3. Deshacer un par de operaciones de esta forma es mucho más rápido que tener que restaurar la base de datos entera

Hoy, cuando el tiempo es tan caro, es importante identificar qué necesita ser hecho, y entonces usar la mejor herramienta para hacerlo. Si la mitad de su base de datos se corrompió, sería lo mejor hacer una restauración completa desde una copia de seguridad. Si usted ha identificado un par de operaciones que necesitan se deshechas, o si usted necesita hacerlo en sólo un día o dos, ApexSQL Log o ApexSQL Recover son definitivamente el camino a seguir, ahorrando tiempo dinero y tiempo de inactividad.

Descargas

Descargue las secuencias de comandos asociadas con este artículo en nuestro repositorio de GitHub.

Por favor contáctenos para cualquier problema o pregunta con los scripts.