Mes: diciembre 2023

Ayer vimos con detalle cómo restaurar una base de datos en SQL Server, sin embargo, en muchas ocasiones lo que necesitaremos es restaurar solo una tabla. El motivo más común para tener que restaurar una copia de seguridad es por un fallo humano a la hora de modificar datos, por lo que necesitaremos restaurar una o varias tablas pero no toda nuestra base de datos. Lamentablemente SQL Server no ha desarrollado aún (finales de 2023) ninguna funcionalidad para hacer esto de forma fácil y eficiente. Mientras esto no pase, vamos a ver paso a paso cómo podemos hacer para recuperar nuestra tabla a un momento anterior.

Preparación del entorno

Para las pruebas voy a usar la base de datos StackOverflow2013. Esta es una base de datos de ejemplo, pero podéis usar cualquier base de datos de pruebas que tengáis.

Tomaré como ejemplo esta tabla

Aplicaré estas 2 sentencias para cambiar los datos:

delete from [dbo].[VoteTypes] where id > 3
update [dbo].[VoteTypes] set Name = 'Closed'

Restaurar: Paso 1

Como hemos dicho lo primero que tenemos que hacer es restaurar una copia de la base de datos de antes de haber cometido el fallo. No sabemos que más operaciones legítimas se han hecho en la base de datos por lo que no podemos sobrescribir los datos. Restauraremos la base de datos con otro nombre para no pisar nada. Todo esto lo vimos en el post de ayer por lo que no nos vamos a detener más en este punto.

Restaurar: Paso 2

Una vez tengamos nuestra copia restaurada con otro nombre tendremos que mover los datos de la tabla a la base de datos original. Para eso podemos hacerlo con el asistente de copia de datos o mediante script, veámoslo.

Asistente de copia de datos

Nos situaremos sobre la base de datos original y abriremos el asistente desde el menú del botón derecho del ratón. Elegiremos como origen la base de datos restaurada, como destino la original y copiaremos los datos de la tabla borrando lo que contiene actualmente. Como la tabla del ejemplo contiene un campo IDENTITY nos aseguraremos de marcar el check para poder insertar en esos campos.

Script INSERT INTO

Para esta técnica también vamos a vaciar la tabla original. A continuación habilitaremos la inserción de campos IDENTITY para despues insertar los datos desde la tabla de la base de datos restaurada.

TRUNCATE TABLE [StackOverflow2013].[dbo].[VoteTypes]
GO

SET IDENTITY_INSERT [StackOverflow2013].[dbo].[VoteTypes] ON
GO

INSERT INTO [StackOverflow2013].[dbo].[VoteTypes] (id, name)
SELECT id, name FROM [StackOverflow2013_RESTORE].[dbo].[VoteTypes]
GO

Script MERGE

Muchas veces, solo unos pocos datos estarán mal y borrar y cargar la tabla entera no será una solución eficiente. En estos casos podremos usar MERGE para solo insertar los registros que faltan o actualizar solo los que no estén bien. Esta sería la sintaxis en nuestro caso:

SET IDENTITY_INSERT [StackOverflow2013].[dbo].[VoteTypes] ON
GO

MERGE [StackOverflow2013].[dbo].[VoteTypes] AS Destino
USING [StackOverflow2013_RESTORE].[dbo].[VoteTypes] AS Origen
ON Destino.ID = Origen.ID
WHEN NOT MATCHED BY Target THEN
INSERT (id, Name)
VALUES (Origen.ID, Origen.Name)
WHEN MATCHED AND Destino.Name <> Origen.Name THEN
UPDATE SET Destino.Name = Origen.Name ;
GO

Restaurar: Paso 3

Una vez que nuestra tabla vuelva a tener los datos correctos avisaremos a los usuarios para que lo confirmen. Si todo está ok, ya solo nos restaría borrar la base de datos restaurada para liberar ese espacio.

Conclusiones

Como hemos visto, restaurar una tabla es un proceso manual que tenemos que controlar.  Habrás notado que durante este artículo he sido bastante crítico con el hecho de que no haya una forma de restaurar una tabla de un archivo de copia de seguridad y seguramente vosotros estéis en la misma situación. Es normal, a día de hoy las restauraciones de tablas son la segunda petición a Microsoft más votada por los usuarios en la web de feedback de usuarios. Hace un año Microsoft confirmó tenerlo como una tarea pendiente para futuras versiones de SQL Server. Aun así, mientras esto no llegue, tenemos que conocer el procedimiento de memoria y estar familiarizados con él ya que es una labor crítica.

Cuando se pierden unos datos de producción, estamos sometidos a mucha presión y tenemos que dar una solución rápida y eficaz por lo que os recomiendo practicar esto que hemos visto en diferentes situaciones. De esta manera, el día que os toque lidiar con ello, será todo mucho más sencillo. 

En el pasado post vimos todo lo que necesitamos saber sobre las copias de seguridad de SQL Server excepto una cosa, como restaurarlas en caso de necesidad. Como esto no puede ser, vamos a ponerle solución ahora mismo. Ya hemos comentado en varias ocasiones que restaurar copias de seguridad es una de las principales tareas de todo DBA. De hecho, es de las primeras cosas que suele aprender un DBA de SQL Server. 

Consideraciones antes de restaurar

Lo primero que tenemos que tener claro a la hora de restaurar una base de datos es la compatibilidad. Solo podremos restaurar una base de datos si viene de un servidor con una versión de SQL igual o inferior al servidor de destino.

¿Sobrescribir o no sobrescribir? Esa es la cuestión

Cuando confirmamos que es posible restaurar, valoraremos si vamos a sobrescribir datos existentes. En caso afirmativo, asegúrate de tener confirmación de los responsables de los datos. Una vez revertido el estado de la base de datos a un momento anterior se perderán todos los datos que no existieran al momento de hacer la copia. Si la base de datos que estamos restaurando no existe en este servidor no vamos a sobrescribir los datos. Si existe tendremos la opción de restaurar sobrescribiendo o crear una base de datos nueva con otro nombre.

Una vez que tengamos claro lo anterior es el momento de volverse a preguntar si realmente tenemos claro que vamos a sobrescribir datos. Puedo parecerte un pesado pero, confía en mí, TODOS nos hemos cargado una base de datos alguna vez por menospreciar este detalle. Revísalo varias veces y asegúrate de que los usuarios han entendido las implicaciones. Aun así, si no tenemos problemas de tiempo ni de espacio, es recomendable hacer una copia de seguridad de lo que hay antes de sobrescribir.

¿Ya lo tiene todo el mundo claro? Ok es el momento de mirar las copias de seguridad y ver cuál se adapta mejor a nuestras necesidades. Si tenemos suerte, será suficiente con una copia completa pero puede que además necesitemos una copia diferencial y varias copias del log.

Por último, si sabemos cuánto ocupa la base de datos, es el confirmaremos que tenemos espacio suficiente.

¿Cómo restaurar una base de datos SQL Server?

Ha llegado de ponernos manos a la obra, tenemos que restaurar nuestros datos y la operativa cambiará en función de las decisiones que hayamos tomado. En cualquiera de los casos tendremos la opción de restaurar desde el entorno gráfico o por script. Mi consejo es que inicies el proceso desde el entorno gráfico pero antes de finalizar saques el script. Esto te permitirá una última revisión de que todo está como quieres. 

Abriremos la ventana de restauración de bases de datos de nuestro SSMS, seleccionaremos restaurar desde dispositivo y seleccionaremos de una sola vez todos los ficheros que necesitemos (completa + diferencial + logs). Esta es la manera más sencilla, SQL se encargará de todo lo necesario para restaurar los ficheros en el orden correcto.
Elegiremos también la ruta donde se van a restaurar los ficheros de base de datos, nos propondrá la ruta por defecto y podremos cambiarla si es necesario. Las opciones principales que debemos seleccionar es si deseamos mantener o no la configuración de replicación en caso de existir y si queremos restringir el acceso de usuarios una vez restaurada. Además de la opción de sobrescribir en caso de ser necesario. Si la base de datos existe y vamos a sobrescribir podremos cerrar todas las conexiones existentes antes de restaurar, lo que pone la base de datos en SINGLE_USER, restaura y vuelve a configurar el MULTI_USER.

Con todo configurado podemos darle a OK, aunque como ya os he dicho yo soy partidario de sacar el script y verificar que todo está correcto. Si lo hacéis y tenéis más de un archivo para restaurar veréis que todos los ficheros menos el último se restauran con la opción NORECOVERY, lo que deja la base de datos inaccesible pero preparada para restaurar nuevos archivos. Esta opción también la podemos seleccionar a mano si lo deseamos.

Permisos

Si la base de datos que vamos a crear es nueva, necesitaremos permisos de CREATE DATABASE, este permiso está incluido en los roles de servidor SYSADMIN y DBCREATOR. Si la base de datos necesitaremos SYSADMIN ya que teniendo DB_OWNER en esa base de datos no será suficiente..

Próximos pasos

Si nuestra base de datos viene de otro servidor lo más probable es que una restaurada nuestra base de datos tengamos que asignar los permisos a los usuarios del servidor actual. Además si usamos SQL es probable que nos encontremos con un problema de usuarios huérfanos (en otro post lo veremos en detalle y su solución). 

Una de las tareas más importantes que debemos hacer como DBAs son las relacionadas con las copias de seguridad. Tenemos que saber cómo hacer copias de seguridad y cómo restaurarlas. Esto nos permitirá recuperar nuestras bases de datos a un momento anterior en caso de incidente que podría ir desde un borrado accidental de un dato hasta un fallo total del servidor.

Tipos de copias de seguridad

Existen 3 tipos principales de copias de seguridad: Completas, Diferenciales y de log de transacciones. Las copias de log de transacciones solo podremos hacerlas si tenemos nuestra base de datos en modo de recuperación completa. 

Copias Completas

Este tipo de copia de seguridad es el principal, no podremos realizar ninguno de los otros tipos de copias de seguridad si no existe previamente una de estas. Esta copia de seguridad almacena en un archivo todo lo que contiene la base de datos en ese momento. Este archivo será suficiente para restaurar la base de datos.

Copias Diferenciales

Esta copia de seguridad almacena todos los cambios que ha sufrido nuestra base de datos desde la última copia de seguridad completa. Para restaurarlo deberemos disponer de la copia base que deberemos restaurar primero.

Copias de los logs de transacciones

Cuando tenemos las bases de datos en modo de recuperación completa todas las transacciones se van almacenando en el log de transacciones. Se almacenan en el log de transacciones hasta que no se vuelquen en un archivo de copia de seguridad. Por lo tanto este archivo lo que almacena son las transacciones realizadas en la base de datos. Para restaurarlo debemos restaurar una copia completa y todos las copias de log, una a una y en orden hasta el punto exacto en el tiempo que deseemos.

Tipos especiales de copias

Dentro de estos tipos principales de copias de seguridad podemos encontrarnos con alguna variación que si bien es parecida tiene algunas particularidades. 

Copias de seguridad solo copia

Este es un tipo de copia especial que no afecta a la cadena de copias de seguridad convencional. Como hemos comentado, cuando queremos restaurar una copia diferencial debemos primero restaurar la copia base completa. Esta relación necesaria se conoce como cadena de copias de seguridad. En ocasiones necesitaremos hacer una copia de seguridad para otros motivos sin afectar a la cadena existente, para ello usaremos la opción solo copia. Las opción solo copia se puede usar para copias de seguridad completas o de log de transacciones. Las copias de seguridad solo copia no vacían el log de transacciones.

Copias de seguridad de archivos o grupos de archivos

Cuando tenemos más de un archivo de datos en nuestra base de datos (en el mismo o en distinto grupo de archivos) y la base de datos está en modo de recuperación completa podemos realizar copias de seguridad completas o diferenciales parciales de solo uno o varios archivos. Al trabajar con copias de seguridad parciales existe el riesgo de perder la consistencia de las bases de datos.

Opciones de copias de seguridad

A la hora de hacer una copia de seguridad tenemos una serie de opciones importantes que tenemos que conocer. Por un lado en el apartado de opciones de medios podremos elegir si en caso de existir el fichero de copia queremos sustituirlo por el nuevo o si queremos anexar la copia al archivo existente. Aquí tenemos también las opciones de verificar el backup al finalizar, hacer un checksum antes de escribir el archivo y si en caso de error deseamos continuar. 

Como opciones de la copia podremos definir un nombre, una descripción y una fecha de expiración. Una copia de seguridad se va a poder restaurar aunque se haya superado la fecha de expiración, esta solo se usa para saber cuándo se puede eliminar la copia por los procesos de limpieza. Como última opción,tenemos otra muy importante, la compresión o no compresión del archivo de copia de seguridad. 

Si quereis podemos ver esto en un caso práctico en otro artículo, ahora tenéis que saber que una copia comprimida llevará más consumo de CPU pero ocupará menos. Para una copia local, si tenemos espacio suficiente una copia sin comprimir (tanto para copiar como para restaurar) pero para una copia en una ubicación de red, ese extra de tamaño, penalizará tanto que, la mayoría de las veces, compensará comprimir nuestra copia.

¿Cómo hacer copias de seguridad en SQL Server?

Como ya habréis podido notar los que seguís el blog, hacer un manual paso a paso no es el objetivo de estos artículos. Aquí queremos que entendáis los conceptos y sepamos lo que estamos haciendo, de cómo se hace está internet lleno. Solo vamos a comentar que podemos hacer nuestras copias de seguridad desde el entorno gráfico, por script o con herramientas de terceros. Si queréis saber como hacer las copias por el entorno gráfico o por script podéis consultar la documentación oficial. Como herramientas de terceros hay muchas en el mercado aunque, personalmente, no me canso de recomendar la solución de OLA HALLENGREN.

Permisos necesarios

Para poder hacer copias de seguridad necesitarás los permisos de BACKUP DATABASE y BACKUP LOG. Estos permisos están incluidos en el rol de servidor sysadmin y de los roles de bases de datos db_owner y db_backupoperator.

Además de los permisos en SQL, el usuario de servicio de SQL deberá tener permisos de lectura y escritura en el directorio destino de las copias de seguridad. Si vamos a programar las copias en un job, el usuario de servicio del Agente de SQL también necesitará permisos.

Próximos pasos

Ahora que ya sabemos como funcionan las copias de seguridad en SQL Server os emplazo a leer el próximo artículo en el que hablaremos de sus restauraciones. Al igual que en este, veremos las opciones de restauración que podemos definir y en qué nos van a afectar.