Copias de Seguridad y Restauraciones en SQL Server 2005 y posteriores

Este artículo viene a completar el anterior que escribí hace un tiempo sobre como restaurar una base de datos en una nueva ubicación. Hoy vamos a detallar como hacer, mediante T-SQL, copias de seguridad y, luego, como restaurar esos backups.

Este artículo está casi "copy-paste" de un blog pero no recuerdo ahora cual. Solo quería aclarar que no era de mi autoría.

Copias de Seguridad (Backups)

Backups Completos

BACKUP DATABASE Nombre_BBDD TO DISK = 'Unidad:\ruta\ficherobackup.bak' WITH INIT

Con el parámetro WITH INIT nos aseguraremos de que el fichero de backup contiene una única copia de seguridad ya que, por defecto, el comando BACKUP lo añade al fichero existente. De esta forma nos aseguramos que el fichero se sobreescribe.

Backups diferenciales

La restauración de los backups del registro de transacciones tiende a ser una operación lenta, especialmente si nuestro backup completo es semanal, o incluso superior en su programación en el tiempo. Los backups diferenciales intentan decrementar el tiempo de recuperación. La cláusula T-SQL es:

BACKUP DATABASE Nombre_BD TO DISK = 'Unidad:\ruta\ficherobackup.dif' WITH DIFFERENTIAL, INIT

El backup "ficherobackup.dif" contiene todos los cambios realizados desde el último backup completo. Podemos utilizarlo durante el proceso de restauración en combinación a los backups del registro de transacciones. En primer lugar, restaurando el backup completo, seguido de la restauración del último diferencial, y a continuación restaurando cualquier log de transacciones posterior.

Backups del Log

El comando para realizar el backup del fichero de log es:

BACKUP LOG Nombre_Log_BBDD TO DISK = 'Unidad:\ruta\ficherobackup.trn'

Cada acción contra la base de datos se asigna a un Log Sequence Number (LSN). Para restaurar a un punto específico en el tiempo, debemos tener un continuo registro de LSNs.

Backups divididos (striped)

Algunas bases de datos son demasiado grandes para crear un backup completo en una única cinta LTO o en un array de discos. En estos casos, podemos hacer uso de los backup striped, también denominados multiplexados. La ventaja es que casa dispositivo utiliza la totalidad de su capacidad para crear el backup. Su desventaja es que, en caso de fallo, todas las cintas o ficheros se necesitarán para completar una restauración.
Para crear un backup striped utilizaremos:

BACKUP DATABASE Nombre_BD TO DISK = 'unidad1:\ruta1\fichero1.bak' , 'unidad2:\ruta2\fichero2.bak', 'unidad3:\ruta3\fichero3.bak' WITH INIT, CHECKSUM, CONTINUE_ON_ERROR

El backup será "extendido" a través de todos los ficheros indicados.


Restauraciones

Para realizar la restauración deberemos tener en cuenta que la ubicación de la base de datos, al tratarse de otro servidor, puede ser diferente. Para ello haremos uso de la opción MOVE que nos servirá para indicar la ubicación tanto del fichero de datos como del registro de transacciones. Para conocer la ubicación en disco exacta pulsaremos con el botón secundario del ratón sobre la BBDD sobre la que vayamos a realizar la restauración, y seleccionamos "Propiedades". En las pestañas "Archivo de datos" y "Registro de transacciones" de la ventana que se abre, podremos conocer la ubicación de estos dos ficheros o (si la base de datos se
 encuentra dañada o en modo sospechoso) ubicando los ficheros lógicos y físicos a traves de la master, consultando en sys.databases y sys.master_files.

En primer lugar deberemos poner nuestra base de datos en modo monousuario o exclusivo, para lo cual nos conectaremos al servidor destino mediante el analizador de consultas de SQL Server y ejecutaremos la sentencia:

ALTER DATABASE BaseDeDatos SET Single_User;

Podemos "expulsar" a los usuarios inmediatamente o tras un determinado tiempo haciendo de la opción ROLLBACK, para lo cual añadiremos a la sentencia anterior WITH
ROLLBACK AFTER segundos o WITH ROLLBACK IMMEDIATE.

 Y una vez realizado este paso, podemos comenzar el proceso de restauración. En primer lugar hacemos uso del Full Backup:

RESTORE DATABASE BaseDeDatos

FROM DISK = 'D:\backup\bk_BaseDeDatos.bak' WITH

    MOVE 'BaseDeDatos' TO 'D:\MSSQL\Data\BaseDeDatos_Data.mdf'

  , MOVE 'BaseDeDatos_log' TO 'D:\MSSQL\Data\BaseDeDatos_log.ldf', NORECOVERY

Indicando la opción NORECOVERY. En el caso de que tuviésemos incrementales, deberíamos ir haciendo restauraciones sucesivas de cada uno de estos backups.
A continuación, restauramos el registro de transacciones:

RESTORE LOG BaseDeDatos FROM DISK = 'D:\backup\bk_BaseDeDatos_log.trn' WITH RECOVERY

O si queremos hacer una restauración hasta un punto concreto en el tiempo podemos hacer uso de la opción STOPAT:

RESTORE LOG BaseDeDatos FROM DISK = 'D:\backup\bk_BaseDeDatos_log.trn' WITH STOPAT = N'9/19/2016 11:01:45 PM', RECOVERY

Una vez finalizado el proceso de restauración, ponemos la BD en modo multiusuario:

ALTER DATABASE BaseDeDatos SET Multi_User;

SQL Server - Restaurar una base de datos en una ubicación nueva y con un nombre nuevo (Transact-SQL)

-- -- --
-- -- Restaurar una base de datos en una ubicación nueva y con un nombre
-- -- nuevo (Transact-SQL)
--
USE master
GO
-- Primero habría que determinar (si no se sabe) los nombres lógicos de los
-- archivos de la copia de seguridad que se desea restaurar.
RESTORE FILELISTONLY
   FROM DISK = 'D:\MSSQL\Backup\bkp_DB_Sistema'


-- Luego, con los nombres lógicos de los datos (.mdf) y de registro (.ldf)
-- se puede hacer una restauración a una nueva ubicación con la opción MOVE.
RESTORE DATABASE DB_Nueva
   FROM DISK = 'D:\MSSQL\Backup\bkp_DB_Sistema'
WITH REPLACE,
   MOVE 'DB_Sistema_Data' TO 'D:\MSSQL\Data\DB_Nueva_Data.mdf',
   MOVE 'DB_Sistema_Log' TO 'D:\MSSQL\Data\DB_Nueva_Log.ldf'
GO


-- Usar REPLACE, de ser necesario. Si es que se tiene que
-- sobreescribir por ser diferente los nombres lógicos.

SQL Server - Usos no deseados de la operación UNION

Hace pocos días me topé en el trabajo con código SQL embebido, escrito por otro programador tiempo atrás, en donde hacía un uso indebido de la operación UNION. En el ejemplo en cuestión, se quería realizar el promedio de los valores de una columna TOTAL y, si bien todos los valores eran positivos, al sumarlos había que tener en cuenta la columna TIPO para sumarlos o restarlos al resultado, puesto que dependían de esta columna, para saber si eran de tipo créditos o débitos. Es decir, éstos valores dependían de una condición que los separaban en negativos y positivos.

Cuando veo como estaba escrito el código SQL me doy cuenta que el resultado no iba a ser el esperado porque se estaba separando la condición con una operación UNION 
Escribo la consulta en cuestión:

SELECT CantidadCasos = COUNT(*)
      ,Promedio = SUM(Total) / COUNT(*)
FROM Pedidos
UNION ALL
SELECT CantidadCasos = COUNT(*)
      ,Promedio = SUM(Total * (-1)) / COUNT(*)
FROM FACT0003
GO

El conjunto de resultados sería el siguiente:

Como se ve, esto nos da un registro por cada consulta (SELECT) en el caso que se den ambas condiciones (en el caso que la unión sea válida para ambos casos) y por ende, el promedio tampoco va a ser el correcto.

La forma correcta sería tratarlo con una expresión del tipo condicional, CASE o IF, teniendo en cuenta como se hace uso de las consultas de agregado (SUM(), COUNT(), etc) que es en donde muchos se equivocan o creen que no se puede realizar este tipo de consultas. Si prestan atención, el condicional siempre está dentro del agregado (en este caso el CASE dentro del SUM():

SELECT CantidadCasos = COUNT(*)
      ,Importe = SUM(CASE Tipo WHEN 'N' THEN Total * (-1) ELSE Total END) / COUNT(*)
FROM Pedidos
GO

El conjunto de resultados, para la consulta anterior, sería el siguiente:

Ahora sí, el resultado es el esperado.

SQL Server - Operaciones con Fechas y Horas II

Hace unos años publiqué (En el 2010... muchos ya.. tengo que publicar más seguido me parece :D) un primer post sobre operaciones con fechas y horas en SQL Server. Hoy, en el trabajo, se presentó una situación, que podría presentarse alguna otra vez en el futuro, y es bueno documentarlo para no estar pensando como resolverlo y/o googleando al cuete de nuevo.

Teníamos que averiguar el período (osea el intervalo de tiempo), en días, entre dos fechas para después usar los días para sumarlo a una fecha. Solo los días, manteniendo la hora como estaba. Combinando DATEDIFF() y DATEADD() lo podemos hacer.

Si tenemos dos parámetros de tipo DATETIME, @FechaDesde y @FechaHasta. Para averiguar la diferencia en días entre los dos hacemos:

SELECT DATEDIFF(DAY, @Fecha, @FechaLimite)

-- Si esto nos da una diferencia de 40 días, supongamos,
-- podemos luego, ir recorriendo todos los registros de la tabla 
-- que necesitabamos con las distintas fechas e ir sumando 40 a la variable @Fecha

SELECT DATEADD(DAY, 40, @Fecha)

Lo bueno cuando trabajamos con la funciones es que, si no las formateamos con CAST() y CONVERT(), no perdemos la hora que tenga grabada nuestra fecha de inicio (si viene de un campo DATETIME con hora y todo). Al sumarle días, nos va a mantener la hora como estaba.