Backups o copias de seguridad para recuperar tu información de SQL Server 2014

Las copias de seguridad o backups son sencillos. Sin embargo no todas las personas saben cifrar su backup, comprimirlo y peor aún hacer copias de respaldo incrementales o poder restaurar datos de una fecha y hora específica. En este tutorial paso a paso muestro estos datos y más:

Backup completo (Full backup)
Backup con compresión
Backup cifrado
Backup diferencial
Backup transaccional


Continuamos con esta serie de artículos acerca de SQL Server 2014. Anteriormente vimos cómo instalar SQL Server 2014 Express, luego vimos cómo crear tablas, vistas, procedimientos almacenados y funciones.


Requerimientos:

• SQL Server 2014 Express instalado (para instrucciones, consulta https://elpaladintecnologico.blogspot.com/2016/05/tutorial-de-instalacion-paso-paso-de.html). Los backups con compresión y cifrado están disponibles solamente en SQL Server 2014 Enterprise.
• La base de datos AdventureWorks2014, bájala aquí: https://msftdbprodsamples.codeplex.com/downloads/get/880661

Índice

Backup completo (Full backup)

Una copia de seguridad o backup completo es una copia completa de la base de datos en un momento dado junto con la totalidad de los registros, de manera que se puede restaurar la base de datos completa. Para crear un backup completo, sigue los siguientes pasos:

1. Abre SQL Server Management Studio (SSMS)
2. Expande ‘Bases de datos’
3. Clic derecho en AdventureWorks2014 > Tareas > Copia de seguridad…
copia-de-seguridad.jpg
4. Haz clic en ‘Quitar’ para quitar cualquier ruta que haya en el panel inferior.
5. Haz clic en ‘Agregar…’

6. Haz clic en el botó con tres puntos.

7. Elige una ruta donde estará tu copia de seguridad y el nombre para el backup. Yo elegí C:\testools y ‘TestBackup’.

8. Haz clic en ‘Aceptar’ dos veces
9. Verifica que ‘Tipo de copia de seguridad’ esté en ‘Completa’

10. Haz clic en ‘Aceptar’. Verás que un proceso se ejecuta.
Luego de un momento verás un mensaje de confirmación:

11. Abre la ruta que señalaste para guardar la copia de seguridad. Un nuevo archivo .bak debería estar ahí:


Backup con compresión

Un backup con compresión es simplemente un backup con la opción de compresión activada. Esta opción permite reducir el tamaño de la copia de seguridad a cambio de algo más de tiempo cuando se crea. Desafortunadamente, esta opción no está disponible para SQL Server 2014 Express. Puede comprobarlo siguiendo los pasos:

1. Abre SQL Server Management Studio (SSMS)
2. Expande ‘Bases de datos’
3. Clic derecho en AdventureWorks2014 > Tareas > Copia de seguridad…
4. Haz clic en ‘Quitar’ para quitar cualquier ruta que haya en el panel inferior.
5. Haz clic en ‘Agregar…’
6. Haz clic en el botó con tres puntos.
7. Elige una ruta donde estará tu copia de seguridad y el nombre para el backup. Yo elegí C:\testools y ‘TestBackupCompression’.
8. Haz clic en ‘Aceptar’ dos veces
9. Haz clic en ‘Opciones de copia de seguridad’

10. En ‘Establecer compresión de copia de seguridad’, elige ‘Comprimir copia de seguridad’

11. Haz clic en ‘Aceptar’. Verás un mensaje de error anunciando que la opción no está disponible en esta versión de SQL Server:

Sin embargo, revisaremos esta opción en SQL Server 2014 Enterprise. Para verlo, sigue los anteriores pasos del 1 al 11. Después de hacer clic en ‘Aceptar’, en lugar de ver un mensaje de error, verás una confirmación de que se creó una copia de seguridad exitosamente:

Para comprobar la compresión, toma una copia de seguridad siguiendo los pasos de la sección Backup completo (Full backup). Cuida que en el paso 7 elijas un nombre que reconozcas, como ‘NoCompressionBackup’.

Una vez que se cree el backup, compara los tamaños:

Se puede apreciar la alta diferencia de tamaño que podría tener mucho impacto en bases de datos grandes.

Backup cifrado

Para crear un backup cifrado:
1. Abre SQL Server Management Studio (SSMS)
2. Clic en ‘Nueva consulta’
3. Para crear una copia de seguridad cifrada primero necesitamos una clave maestra de la base de datos. Para esto, ejecutamos la siguiente consulta:

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘Control123’;
GO

En este caso estoy usando ‘Control123’ como password. Usa el que desees. No olvides que esta consulta se ejecuta contra la base de datos master (por eso el ‘USE master’ al principio).
4. Aparte de la clave maestra, también necesitamos un certificado de copia de seguridad. Para crearlo, ejecuta la siguiente consulta:

Use Master
GO
CREATE CERTIFICATE TestCert
   WITH SUBJECT = ‘TestCert’;
GO

5. Haz clic derecho en la base de datos ‘AdventureWorks2014’ > Tareas > Copia de seguridad…
6. Haz clic en ‘Quitar’ para quitar cualquier ruta que haya en el panel inferior.
7. Haz clic en ‘Agregar…’
8. Haz clic en el botó con tres puntos.
9. Elige una ruta donde estará tu copia de seguridad y el nombre para el backup. Yo elegí C:\testools y ‘TestBackupCifrado’.
10. Haz clic en ‘Aceptar’ dos veces
11. Haz clic en ‘Opciones de medios’ y deja las opciones como se ve en la siguiente captura:

12. Haz clic en ‘Opciones de copia de seguridad’.
13. Elije ‘Cifrar copia de seguridad’.
14. En ‘Certificado o clave asimétrica’, elige ‘TestCert (Certificado)’

15. Haz clic en ‘Aceptar’. Esta opción no está disponible en SQL Server 2014 Express. Si sigues los pasos hasta este punto en esa versión, verás el siguiente mensaje de error:

Los siguientes pasos están hechos en SQL Server 2014 Enterprise:
16. Verás el mensaje de confirmación de la creación de backup:

Ahora, si es que restauras la copia de seguridad no verás ninguna diferencia con restaurar una copia de seguridad normal. Eso es porque SQL Server ya tiene el certificado para des encriptar la copia de seguridad. Por tanto, vamos a borrar el certificado para verificar el encriptado, pero primero deberemos exportar el certificado y la clave asimétrica para restaurarlos después, como si lo hiciéramos a un servidor diferente. Hazlo con las siguientes consultas:

Exportar certificado y clave:

USE master;
GO
BACKUP CERTIFICATE TestCert
TO FILE = 'c:\testools\BackupCert.cer'
WITH PRIVATE KEY
(FILE = 'c:\testools\BackupKey.pvk',
ENCRYPTION BY PASSWORD = 'Control123')

Eliminar el certificado que tenemos actualmente:

USE master
DROP CERTIFICATE TestCert;

Ahora que no hay certificado, intenta restaurar la base de datos con la siguiente consulta:

USE master
RESTORE DATABASE AdventureWorks2014 FROM
DISK = N'C:\testools\TestBackupCifrado.bak'
WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5

Deberías ver un mensaje de error, que corresponde con la ausencia del certificado:

Restaurar el certificado a partir de los archivos exportados:

Ahora, para poder restaurar el backup, necesitas primero crear el certificado basado en los archivos exportados:

CREATE CERTIFICATE TestCert
FROM FILE = 'C:\testools\BackupCert.cer'  
WITH PRIVATE KEY (FILE = 'C:\testools\BackupKey.pvk',
DECRYPTION BY PASSWORD = 'Control123')
Ejecuta esa consulta SQL y seguidamente prueba de nuevo a restaurar con la siguiente consulta:
USE [master]
RESTORE DATABASE AdventureWorks2014 FROM
DISK = N'C:\testools\TestBackupCifrado.bak'
WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5

Después de ejecutar eso, deberías ver lo siguiente, que indica que la restauración fue exitosa:


Backup diferencial

Los backups diferenciales son copias de seguridad solamente de los últimos cambios en la base de datos desde la última copia de seguridad completa. Esto tiene una multitud de ventajas, entre ellas el ahorro en espacio y la velocidad de toma de backups. A modo de ejemplo, primero tomaremos 2 backups completas, una base y la otra después de crear una tabla grande.

1. Toma 1 backup completo. Puede consultar la sección Backup completo (Full backup) para instrucciones. Guarda el backup en C:\testing, y llámalo ‘Backup1.bak’.
2. Crea una tabla grande ejecutando la siguiente consulta:

USE AdventureWorks2014
CREATE TABLE testoolsTable
(
    ID int NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
    Name varchar(50) NOT NULL,
    Age int NOT NULL
)

INSERT testoolsTable (Name, Age)
    SELECT 'Juan', s1.number % 10 + 25
    FROM master.dbo.spt_values s1
    CROSS JOIN master.dbo.spt_values s2
    WHERE s1.type = 'P' AND s2.type = 'P'
    AND s2.number < 200


3. Toma otro backup completo llamado ‘Backup2.bak’.
4. Verifica el total de ambos backups:

En este caso, hay un tamaño combinado de 389 MB. Ahora, veamos el caso de la copia de seguridad diferencial:

1. Elimina todo el contenido de C:\testing
2. Ejecuta la siguiente consulta para dejar eliminar la tabla grande que creamos:

DROP TABLE testoolsTable

3. Toma una copia de seguridad completa en C:\testing, llámala ‘Backup1’
4. Crea una tabla grande ejecutando la siguiente consulta:

USE AdventureWorks2014
CREATE TABLE testoolsTable
(
    ID int NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
    Name varchar(50) NOT NULL,
    Age int NOT NULL
)

INSERT testoolsTable (Name, Age)
    SELECT 'Juan', s1.number % 10 + 25
    FROM master.dbo.spt_values s1
    CROSS JOIN master.dbo.spt_values s2
    WHERE s1.type = 'P' AND s2.type = 'P'
    AND s2.number < 200

5. Toma una copia de seguridad de la base de datos AdventureWorks2014, guárdala en C:\testing y llámala ‘BackupDiff.bak’. En ‘Tipo de copia de seguridad’ elige ‘Diferencial’.

3. Verifica el tamaño combinado de ambos backups

Ahora, el tamaño de ambos archivos es de 201 MB, una gran diferencia con los backups completos.

Backup transaccional

Para tomar una copia de seguridad transaccional, seguiremos los siguientes pasos:

1. Cambia el modelo de recuperación de la base de datos ejecutando la siguiente consulta:

USE AdventureWorks2014;
ALTER DATABASE AdventureWorks2014 SET RECOVERY FULL;

2.Toma un backup completo. Puedes consultar la sección Backup completo (Full backup). Guarda el backup en C:\testing y llámalo ‘BackupCompleto.bak’.
3. Toma una copia de seguridad del registro de transacciones:

BACKUP LOG AdventureWorks2014 TO DISK = 'C:\testing\AdventureWorks2.TRN'
GO

3. Elimina la tabla ‘testoos22’ con lo siguiente:

DROP TABLE testools22

4. Espera al menos unos 2 minutos y elimina ‘testools33’ con:

DROP TABLE testools33

5. Ahora toma un Backup con la siguiente consulta:

BACKUP LOG AdventureWorks2014 TO DISK = 'C:\testing\AdventureWorks2.TRN'
GO

Sí, debe sobrescribir ‘AdventureWorks2.TRN'

6.Clic derecho en Bases de Datos > Restaurar base de datos…
9.. Selecciona ‘Dispositivo’
10. Haz clic en el botón de 3 puntos.
11. Haz clic en ‘Agregar’
12. Selecciona el archivo en la ruta: 'C:\testing\AdventureWorks2.TRN'.
13. Clic en 'Aceptar'
14. Clic en 'Agregar' de nuevo
15. Selecciona 'C:\testing\BackupCompleto.bak'
16. Clic en 'Aceptar'
13. Clic en ‘Escala de tiempo…’
14. Selecciona ´Fecha y hora específicos’. Ingresa la hora más en el pasado y haz clic en ‘Aceptar’.
15. Clic en ‘Opciones’
16. Selecciona ‘Cerrar las conexiones existentes’
17. Clic en ‘Aceptar’. Una vez que termine la restauración de la base de datos, actualiza 'Tablas' y deberías ver solamente a testools33, ya que esta tabla no se eliminó aún en este punto de restauración: