SQL Server 2005 Express y Visual Studio 2008 : Restaurar Respaldo

En uno de los proyectos en los que trabajo, tuve la necesidad de modificar el esquema de la base de datos.  Aún cuando Visual Studio Team Suite 2008 Database Edition GDR R2 facilita el trabajo con bases de datos de SQL Server, hay que programar rutinas para la migración de datos.  Además, creo que es bastante común el utilizar un respaldo de la base de datos del ambiente de producción en nuestro ambiente de prueba o de representación. Este artículo trata de cómo generar una rutina para automatizar la restauración del respaldo como parte del proceso de construcción del proyecto en Visual Studio 2008.

Ya está publicada la nota sobre la integración de esta rutina en el proceso de construcción de Visual Studio

El ambiente de desarrollo

En mi caso particular tengo la siguiente configuración:

• Ambiente de Desarrollo
  • Visual Studio 2008 Team System SP1
  • Visual Studio 2008 Team Suite Database Edition GDR R2
• Servidor de Base de Datos:
  • SQL Server 2005 Express Edition SP3

La solución está compuesta por varios proyectos, incluyendo dos de base de datos:

• Proyecto de Base de Datos de Servidor - Incluye la base de datos 'master' y la definición de un usuario y su respectiva cuenta utilizada por la aplicación para conectarse a la base de datos.
• Proyecto de Base de Datos de la Aplicación - Este es el proyecto en el que se define el esquema de la base de datos de la aplicación en cuestión.  Tiene una referencia a la base de datos del servidor y es en este proyecto en el que agregué las rutinas de migración de datos y de generación de rutina para restaurar el respaldo.

La base de datos de la aplicación está configurada con el modelo de recuperación completa (FULL RECOVERY) y se tiene calendarizada la ejecución de rutinas de respaldo para generar un respaldo completo de la base de datos a las 2:00 a.m. todos los días y generar un respaldo de las bitácoras de transacciones (TRANSACTION LOGS) a partir de las 2:30 horas, cada hora.  Los respaldos se almacenan en un archivo correspondiente al día del respaldo. Debido a la naturaleza de los cambios al esquema de la base de datos, se requiere la creación de rutinas de migración de datos.  Para poder probar estas rutinas, se me ocurrió restaurar un respaldo de la base de datos del ambiente de producción en el ambiente de prueba.  Al principio, hice el proceso manualmente. Pero al estar depurando las rutinas de migración de datos, se hizo evidente que necesitaba automatizar el proceso e integrarlo al proceso de construcción/compilación de la solución en Visual Studio. El primer reto fue averiguar cómo generar la rutina para restaurar el respaldo.  Utilizando SQL Server Management Studio Express, se puede generar el código en Transact-SQL con algunos clics.  Y se obtiene algo como:

RESTORE DATABASE [LaBaseDeDatosDesarrollo]
FROM DISK = N'X:\Ruta\Al\Archivo\De\Respaldo.bak' WITH FILE = 3,
MOVE N'BaseDeDatosProduccion.mdf' TO N'X:\Ruta\Al\Archivo\Restaurado1.mdf',
MOVE N'BaseDeDatosProduccionBitacora.ldf' TO N'X:\Ruta\Al\Archivo\Restaurado1.ldf',
NORECOVERY, NOUNLOAD, REPLACE, STATS = 10;
GO
RESTORE LOG [LaBaseDeDatosDesarrollo]
FROM DISK = N'X:\Ruta\Al\Archivo\De\Respaldo.bak' WITH FILE = 4,
NORECOVERY, NOUNLOAD, STATS = 10;
GO
RESTORE LOG [LaBaseDeDatosDesarrollo]
FROM DISK = N'X:\Ruta\Al\Archivo\De\Respaldo.bak' WITH FILE = 5,
NORECOVERY, NOUNLOAD, STATS = 10;
GO
RESTORE LOG [LaBaseDeDatosDesarrollo]
FROM DISK = N'X:\Ruta\Al\Archivo\De\Respaldo.bak' WITH FILE = 6,
NOUNLOAD, STATS = 10;
GO

Sin embargo, yo quería que fuera un proceso totalmente automático y que se ejecutara como parte de la acción "desplegar" (deploy) en Visual Studio.  Entonces, pensé: Si SQL Server Management Studio Express puede generar el código de Transact-SQL, debe haber una manera en la que yo pueda hacer lo mismo en Transact-SQL.

La base de datos msdb

SQL Server guarda la información de los respaldos en las tablas [backupfile], [backupset], [backupmediafamily], [backupmediaset] de la base de datos [msdb].  Ya que el motor de SQL Server está diseñado para cubrir una amplia gama de necesidades en aplicaciones de todos los tamaños, muchas veces el control interno puede parecer muy rebuscado y complejo.  Sin embargo, leyendo un poco de la documentación podemos entender lo suficiente para nuestro propósito.

No voy a repetir aquí la documentación de SQL Server.  Lo único que nos interesa es lo siguiente:

• El nombre del archivo en el que se almacenó el respaldo está en la columna [physical_device_name] de la tabla [msdb]..[backupmediafamily]
• La tabla [msdb]..[backupset] contiene un registro por cada respaldo que se generó con mucha información del mismo.  Las columnas que nos interesan son:
  • [position] - La posición dentro del archivo de respaldo
  • [database_name] - El nombre de la base de datos que se respaldó
  • [type] - 'D' si fue respaldo de base de datos o 'L' si fue respaldo de la bitácora de transacciones
  • [backup_set_id] - Número de identificación del conjunto
• La tabla [msdb]..[backupfile] contiene un registro por cada archivo de base de datos que se respaldó en un conjunto.  Las columnas que nos interesan son:
  • [logical_name] - Nombre lógico del archivo
  • [file_type] - Tipo de archivo ('D' si es de base de datos o 'L' si es archivo de la bitácora de transacciones)
  • [create_lsn] - Número de secuencia de creación de la base de datos
  • [backup_set_id] - Número de identificación del conjunto al que pertenece
• El comando RESTORE y sus variantes:
  • RESTORE DATABASE
  • RESTORE LOG

El Código

Armado con la información anterior, hice la siguiente rutina:

DECLARE @backup_set_id INT;
DECLARE @physical_device_name NVARCHAR(260);
DECLARE @batch_text VARCHAR(MAX);
DECLARE @last_position INT;

-- Obtener el valor de [backup_set_id] para el último
-- respaldo completo de la base de datos del ambiente
-- de producción
SELECT @backup_set_id = MAX([backup_set_id])
FROM [msdb]..[backupset]
WHERE [database_name]='$(ProductionDatabaseName)'
AND [type]='D';

-- Obtener el valor de [physical_device_name] de
-- la tabla [backupmediafamily] asociado al [backupset]
-- identificado por el comando anterior
SELECT
@physical_device_name = [mf].[physical_device_name]
FROM
[msdb]..[backupset] AS [bs] INNER JOIN
[msdb]..[backupmediafamily] AS [mf]
ON [bs].[media_set_id] = [mf].[media_set_id]
WHERE
[bs].[backup_set_id] = @backup_set_id;

-- Obtener el valor de [position] para el último
-- respaldo de la bitácora de transacciones realizado
-- después del último respaldo de base de datos
SELECT @last_position = MAX([position])
FROM [msdb]..[backupset]
WHERE [database_name]='$(ProductionDatabaseName)'
AND [type]='L'
AND [backup_set_id] > @backup_set_id;

-- Comenzar a generar el texto de la rutina
SET @batch_text =
'PRINT ''RESTAURANDO BASE DE DATOS DE DESARROLLO '
+ '$(DatabaseName)...'';' + CHAR(13) + CHAR(10)
+ 'GO' + CHAR(13) + CHAR(10)
+ 'USE [master]' + CHAR(13) + CHAR(10)
+ 'GO' + CHAR(13) + CHAR(10);

-- Agregar el comando para restaurar el último
-- respaldo de base de datos
SELECT @batch_text = @batch_text +
'RESTORE DATABASE [$(DatabaseName)] FROM DISK = N'''
+ @physical_device_name + ''' WITH FILE = ' 
+ CONVERT(VARCHAR, [position]) + ',' + CHAR(13) + CHAR(10)
FROM [msdb]..[backupset]
WHERE [backup_set_id] = @backup_set_id;

-- Agregar cada uno de los archivos en el conjunto de
-- respaldo, indicando su nueva ruta de localización
SELECT @batch_text = @batch_text +
CHAR(9) + 'MOVE N''' + [logical_name]
+ ''' TO N''$(RestorePath)\$(DatabaseName)'
+ CONVERT(VARCHAR, ROW_NUMBER() OVER
(PARTITION BY [file_type] ORDER BY [create_lsn]))
+ CASE WHEN file_type='D' THEN
'.mdf'
ELSE
'_log.ldf'
END + ''',' + CHAR(13) + CHAR(10)
FROM [msdb]..[backupfile]
WHERE [backup_set_id] = @backup_set_id;

-- Terminar el comando de restauración de base de datos
SET @batch_text = @batch_text + CHAR(9)
+ 'NORECOVERY, NOUNLOAD, REPLACE, STATS = 10;'
+ CHAR(13) + CHAR(10)
+ 'GO' + CHAR(13) + CHAR(10);

-- Para cada respaldo de la bitácora de transacciones en el
-- conjunto de respaldos, agregar un comando que restaure
-- dicho respaldo
SELECT @batch_text = @batch_text
+ 'RESTORE LOG [$(DatabaseName)] FROM DISK = N'''
+ @physical_device_name + ''' WITH FILE = '
+ CONVERT(VARCHAR, [position]) + ','
+ CASE WHEN [position] < @last_position THEN
' NORECOVERY,'
ELSE
''
END
+ ' NOUNLOAD, STATS = 10;' + CHAR(13) + CHAR(10)
+ 'GO' + CHAR(13) + CHAR(10)
FROM [msdb]..[backupset]
WHERE [backup_set_id] > @backup_set_id
AND [POSITION] <= @last_position
AND [database_name]='$(ProductionDatabaseName)';

-- Finalmente terminar con un mensaje 
SET @batch_text = @batch_text
+ 'PRINT ''BASE DE DATOS DE DESARROLLO RESTAURADA...'';'
+ CHAR(13) + CHAR(10)
+ 'GO' + CHAR(13) + CHAR(10)
+ 'USE [$(DatabaseName)]' + CHAR(13) + CHAR(10)
+ 'GO';

-- Escribir el resultado a la salida estándar
PRINT @batch_text;

Nótese la utilización de variables de sustitución (todas las cadenas con el patrón “$(identificador)”) para la ejecución con SQLCMD. De esta manera podemos pasar parámetros a nuestra rutina de acuerdo a valores de variables del proyecto de Visual Studio, la idea es que se ejecute esta rutina como parte de la construcción del proyecto. En la siguiente parte del artículo utilizaremos los eventos de pre-construcción y post-construcción para ejecutar esta rutina.