Integration Services, SQL Server, SQL Server 2012, SSIS

Utilizando SSIS para crear scripts de respaldo con la estructura de la base de datos (y 2)

En la primera entrega de este artículo realizábamos una introducción al desarrollo de proyectos de Integration Services desde el entorno de BIDS, con el objetivo de crear scripts de la estructura de las bases de datos de nuestro servidor utilizando Script Task, un componente de los paquetes de integración. Será en esta segunda parte, cuando nos centraremos en la generación de los mencionados archivos de script.

 

La clase ScriptingOptions, el método Script y la generación del archivo de script

Pasemos a la siguiente fase en el desarrollo de nuestro proceso, que consistirá en crear un archivo de script, que de momento solamente incluirá las sentencias de creación de la base de datos. Esta operación será realizada dentro del bucle que recorre la colección de bases de datos del servidor, obteniendo un archivo por cada base de datos. Previamente deberemos crear la carpeta «C:\EstructurasBBDDCopias», que será donde se generen los archivos.

La manera de especificar el archivo de script pasa por utilizar un objeto de la clase ScriptingOptions, que como adivinamos por su nombre, es la encargada de configurar las opciones relativas a la generación del archivo. Una vez instanciado dicho objeto, la propiedad FileName será la que utilicemos para indicar el nombre y ruta del archivo que se va a crear; mientras que el acto de generación lo llevaremos a cabo llamando al método Server.Script, pasándole como parámetro el objeto ScriptingOptions.

Dado que este es un proceso que probablemente ejecutaremos con cierta frecuencia, añadiremos las instrucciones necesarias para crear, dentro de la carpeta «C:\EstructurasBBDDCopias», una subcarpeta cuyo nombre sea la fecha de ejecución del paquete, siendo esta última, la carpeta en donde se depositarán los archivos de script. Para poder utilizar los objetos necesarios para la manipulación de carpetas, añadiremos a nuestro código la declaración del espacio de nombres System.IO.

using System.IO;
//....
Server oServer = new Server("WINTERFELL-PC");
bool bOtraVez = false;
 
string sRutaArchivo = @"C:EstructurasBBDDCopias" + DateTime.Today.ToString("yyyyMMdd") + @"";
 
if (Directory.Exists(sRutaArchivo))
{
  Directory.Delete(sRutaArchivo, true);
}
 
Directory.CreateDirectory(sRutaArchivo);
 
ScriptingOptions oScriptingOptions = new ScriptingOptions();
 
foreach (Database oDatabase in oServer.Databases)
{
  Dts.Events.FireInformation(0, string.Empty, "Procesando BBDD: " + oDatabase.Name, string.Empty, 0, ref bOtraVez);
  oScriptingOptions.FileName = sRutaArchivo + oDatabase.Name + ".sql";
  oDatabase.Script(oScriptingOptions);
}
//....

 

Después de ejecutar nuevamente el paquete, en la ruta especificada aparecerán los archivos recién creados, en cuyo interior encontraremos las sentencias de creación de la base de datos.

UtilizandoSSISCrearScriptsRespaldoEstructuraBD_17

 

CREATE DATABASE [Chinook]
CONTAINMENT = NONE
ON PRIMARY 
(NAME = N'Chinook', 
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Chinook.mdf',
SIZE = 5184KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB)
LOG ON
(NAME = N'Chinook_log', 
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Chinook_log.ldf',
SIZE = 1040KB,
MAXSIZE = 2048GB,
FILEGROWTH = 10%)
COLLATE Modern_Spanish_CI_AS
GO
ALTER DATABASE [Chinook] SET COMPATIBILITY_LEVEL = 110
GO
--....

 

Con toda seguridad habremos observado que se han generado archivos tanto para las bases de datos de usuario como de sistema, aunque probablemente en estas últimas no estemos interesados. Si este es el caso, podemos evitar su creación interrogando a la propiedad Database.IsSystemObject, de tipo bool, mediante la que averiguaremos si se trata de una base de datos de sistema o usuario.

Con las bases de datos del servidor de informes estamos en una situación parecida, pero como se trata de bases de datos de usuario, si no queremos generar script para ellas lo haremos comprobando el comienzo de su nombre.

//....
foreach (Database oDatabase in oServer.Databases)
{
    if ((!oDatabase.IsSystemObject) && (!oDatabase.Name.StartsWith("Report")))
    {
        Dts.Events.FireInformation(0, string.Empty, "Procesando BBDD: " + oDatabase.Name, string.Empty, 0, ref bOtraVez);
        oScriptingOptions.FileName = sRutaArchivo + oDatabase.Name + ".sql";
        oDatabase.Script(oScriptingOptions);
    }
}
//....

 

Modificación de valores a través de variables

Para especificar el nombre del servidor con el que trabajamos y la ruta en la que se crearán los archivos, actualmente utilizamos valores fijos de cadena, lo cual se convertirá en un inconveniente cuando tengamos que instalar el paquete en un servidor distinto del utilizado para el desarrollo, o bien queramos emplear una ruta diferente para la creación de los scripts.

Podemos remediar este problema gracias a la posibilidad de declarar variables en el diseñador del paquete, cuyos valores pueden ser recuperados desde el código fuente en el momento de su ejecución.

Para declarar una variable nos situaremos en la ventana «Variables» de Visual Studio y haremos clic en el botón «Add Variable» de su barra de herramientas, creándose la nueva variable vacía, cuyos campos de propiedad (Name, Data type y Value) completaremos con los valores oportunos. En la siguiente imagen vemos la creación de la variable RutaArchivos, de tipo cadena, con el valor «C:EstructurasBBDDCopias».

UtilizandoSSISCrearScriptsRespaldoEstructuraBD_18

 

En el caso de que esta ventana esté oculta, la mostraremos seleccionando la opción de menú de Visual Studio «SSIS | Variables».

Además de las variables creadas por el desarrollador, disponemos también de un conjunto de variables de sistema, que podemos visualizar haciendo clic en el botón «Show System Variables» de la barra herramientas de esta misma ventana. Para el ejemplo que estamos desarrollando, haremos uso de la variable «MachineName», que nos proporcionará el nombre del servidor en el que se ejecuta el paquete.

UtilizandoSSISCrearScriptsRespaldoEstructuraBD_19

 

El siguiente paso consistirá en preparar las variables para que puedan estar accesibles desde el código fuente del paquete. Para ello, abriremos la ventana «Script Task Editor» del objeto Script Task, y en la propiedad «ReadOnlyVariables», perteneciente a la categoría «Script», escribiremos los nombres de las variables como una lista separada por comas, o bien haremos clic en el botón de puntos suspensivos, que abrirá la ventana «Select Variables», en donde realizaremos la selección, lo que tendrá el mismo efecto.

UtilizandoSSISCrearScriptsRespaldoEstructuraBD_20

 

A continuación volveremos a la ventana del editor de código, donde para recuperar el valor de las variables emplearemos la colección «Variables» del objeto Dts, situando en el índice de la colección una cadena con el nombre de la variable a obtener, como vemos en el siguiente bloque de código.

//....
Server oServer = new Server(Dts.Variables["System::MachineName"].Value.ToString());
bool bOtraVez = false;
string sRutaArchivo = Dts.Variables["User::RutaArchivos"].Value.ToString() + DateTime.Today.ToString("yyyyMMdd") + @"";
//....

Para pasar valores a las variables del paquete, una vez éste ha sido importado al servidor de Integration Services, podemos utilizar la ventana «Execute Package Utility». En la categoría «Command Line» haremos clic en el RadioButton «Edit the command line manually», añadiendo al final de las opciones del comando DTExec la siguiente opción /SET, con la que cambiaremos la ruta en donde se crearán los scripts.

/SET «Package.Variables[User::RutaArchivos].Value»;»C:Pruebas\»

 

UtilizandoSSISCrearScriptsRespaldoEstructuraBD_21

 

Estos mismos valores serán los que usaremos en el caso de ejecutar el paquete desde la línea de comandos en una ventana «Símbolo del sistema» mediante la utilidad DTExec.exe.

UtilizandoSSISCrearScriptsRespaldoEstructuraBD_22

 

Incorporando las tablas al proceso

Los siguientes objetos que vamos a generar en el script serán las tablas, por lo que siguiendo la misma mecánica utilizada con las bases de datos, crearemos un bucle que recorrerá la colección Tables del objeto Database en el que estemos posicionados, añadiendo cada tabla al archivo por medio de su método Script.

//....
foreach (Database oDatabase in oServer.Databases)
{
  if ((!oDatabase.IsSystemObject) && (!oDatabase.Name.StartsWith("Report")))
  {
    Dts.Events.FireInformation(0, string.Empty, "Procesando BBDD: " + oDatabase.Name, string.Empty, 0, ref bOtraVez);
    oScriptingOptions.FileName = sRutaArchivo + oDatabase.Name + ".sql";
    oDatabase.Script(oScriptingOptions);
    foreach (Table oTable in oDatabase.Tables)
    {
      Dts.Events.FireInformation(0, string.Empty, "Procesando tabla: " + oDatabase.Name + '-' +
      oTable.Schema + '.' + oTable.Name, string.Empty, 0, ref bOtraVez);
      oTable.Script(oScriptingOptions);
    }
  }
}
//....

 

Al ejecutar el paquete los archivos serán generados de nuevo, pero si abrimos cualquiera de ellos nos llevaremos una desagradable sorpresa, ya que sólo contendrán una única sentencia de creación de tabla. Esto es debido a que, por defecto, el método Script, cada vez que es llamado, sobrescribe el contenido del archivo con la sentencia recién generada.

Igualmente nos encontraremos ante otra serie de inconvenientes como son la ausencia de las sentencias para la creación de los índices de las tablas, claves primarias, externas, etc.

Para evitar este comportamiento recurriremos nuevamente al objeto ScriptingOptions, asignando el valor true a las propiedades que describimos a continuación.

–AppendToFile. Las sentencias generadas mediante el método Script se irán agregando al archivo sobre el que estamos trabajando, evitando así que la última sentencia reemplace su contenido.

–IncludeDatabaseContext. Antes de cada sentencia de creación de tabla se creará una sentencia «USE NombreBBDD», que nos asegure que la tabla sea creada en la base de datos correcta.

–Indexes. Añade las sentencias para la creación de índices (agrupados / no agrupados) y claves primarias en las tablas.

–DriForeignKeys. Añade las sentencias para la creación de las claves externas de las tablas.

ScriptingOptions oScriptingOptions = new ScriptingOptions();
oScriptingOptions.AppendToFile = true;
oScriptingOptions.IncludeDatabaseContext = true;
oScriptingOptions.Indexes = true;
oScriptingOptions.DriForeignKeys = true;

 

Dentro del apartado de creación de índices, gracias a las propiedades DriPrimaryKey, ClusteredIndexes y NonClusteredIndexes, pertenecientes a la clase ScriptingOptions, vamos a tener la capacidad de ser más selectivos a la hora de elegir el tipo de índice a incluir en el script. Si por ejemplo, en el script solamente queremos añadir sentencias para crear los índices agrupados y las claves primarias, dejando fuera los índices no agrupados, en primer lugar asignaremos false a la propiedad Indexes, para impedir así la creación de todos los tipos de índices, y después realizaremos la asignación de las propiedades antes mencionadas, para que sean creados los tipos de índices elegidos.

oScriptingOptions.Indexes = false;
oScriptingOptions.DriPrimaryKey = true;
oScriptingOptions.ClusteredIndexes = true;
oScriptingOptions.NonClusteredIndexes = false;

 

Vistas, procedimientos almacenados, funciones y objetos del sistema

La inclusión en el proceso de otros objetos de la base de datos tales como vistas, procedimientos almacenados, funciones, etc., no reviste mayor complicación que la de añadir un nuevo bucle foreach, que recorra la colección correspondiente del objeto Database: Views, StoredProcedures, UserDefinedFunctions, etc.

Entre los tipos de objetos que acabamos de mencionar, además de los creados por el usuario, existen versiones del sistema, y adicionalmente, algunos de estos procedimientos almacenados y funciones del sistema se encuentran encriptados (los reconoceremos en SQL Server Management Studio porque su icono muestra un candado), provocando un error si intentamos incluirlos en el script. Es por ello que debemos evitar esta situación, consultando su propiedad IsEncrypted, como vemos en el siguiente bloque de código.

UtilizandoSSISCrearScriptsRespaldoEstructuraBD_23

 

//....
if ((!oDatabase.IsSystemObject) && (!oDatabase.Name.StartsWith("Report")))
{
    //....
    foreach (View oView in oDatabase.Views)
    {
        Dts.Events.FireInformation(0, string.Empty, "Procesando vista: " + oDatabase.Name + '-' + 
           oView.Schema + '.' + oView.Name, string.Empty, 0, ref bOtraVez);
        oView.Script(oScriptingOptions);
        }
 
    foreach (StoredProcedure oStoredProcedure in oDatabase.StoredProcedures)
    {
        if (!oStoredProcedure.IsEncrypted)
        {
            Dts.Events.FireInformation(0, string.Empty, "Procesando procedimiento almacenado: " + oDatabase.Name + '-' + 
                oStoredProcedure.Schema + '.' + oStoredProcedure.Name, string.Empty, 0, ref bOtraVez);
            oStoredProcedure.Script(oScriptingOptions);
        }
    }
 
    foreach (UserDefinedFunction oUserDefinedFunction in oDatabase.UserDefinedFunctions)
    {
        if (!oUserDefinedFunction.IsEncrypted)
        {
            Dts.Events.FireInformation(0, string.Empty, "Procesando función: " + oDatabase.Name + '-' + 
                oUserDefinedFunction.Schema + '.' + oUserDefinedFunction.Name, string.Empty, 0, ref bOtraVez);
            oUserDefinedFunction.Script(oScriptingOptions);
        }
    }
}
//....

 

Por otra parte, la inclusión de estos objetos del sistema hará que el archivo de script crezca hasta alcanzar un tamaño considerable, por lo que es posible que no deseemos añadirlos.

Si este es nuestro caso, el modo más sencillo de resolver el problema consiste en asignar false a la propiedad ScriptingOptions.AllowSystemObjects. De esta forma tampoco sería necesario comprobar si ciertos objetos están encriptados, obteniendo además un archivo de script con un tamaño más reducido.

ScriptingOptions oScriptingOptions = new ScriptingOptions();
//....
oScriptingOptions.AllowSystemObjects = false;
//....
foreach (View oView in oDatabase.Views)
{
    Dts.Events.FireInformation(0, string.Empty, "Procesando vista: " + oDatabase.Name + '-' + 
        oView.Schema + '.' + oView.Name, string.Empty, 0, ref bOtraVez);
    oView.Script(oScriptingOptions);
}
foreach (StoredProcedure oStoredProcedure in oDatabase.StoredProcedures)
{
    Dts.Events.FireInformation(0, string.Empty, "Procesando procedimiento almacenado: " + oDatabase.Name + '-' + 
        oStoredProcedure.Schema + '.' + oStoredProcedure.Name, string.Empty, 0, ref bOtraVez);
    oStoredProcedure.Script(oScriptingOptions);
}
 
foreach (UserDefinedFunction oUserDefinedFunction in oDatabase.UserDefinedFunctions)
{
    Dts.Events.FireInformation(0, string.Empty, "Procesando función: " + oDatabase.Name + '-' + 
        oUserDefinedFunction.Schema + '.' + oUserDefinedFunction.Name, string.Empty, 0, ref bOtraVez);
    oUserDefinedFunction.Script(oScriptingOptions);
}
//....

Sin embargo, el hecho de no añadir los objetos del sistema al script no va a suponer una mejora significativa en la duración del proceso, ya que a pesar de que el valor de la propiedad AllowSystemObjects sea false, las colecciones Views, StoredProcedures y UserDefinedFunctions siguen conteniendo los objetos del sistema.

Con el fin de agilizar estos tiempos de ejecución, las colecciones que utilizamos para construir el script deberían contener única y exclusivamente aquellos objetos que vayamos a incluir en el archivo. No obstante, esta es una tarea de optimización que queda fuera del ámbito del presente artículo, pero que abordaremos próximamente. Por el momento, confiamos en que la solución aquí propuesta  resulte de utilidad para todos aquellos lectores que precisen crear scripts con las estructuras de sus bases de datos.

Un saludo.

3 Comentarios

  1. kiquenet

    Muy bueno, me pone !!! buenos artículos , gracias 🙂

  2. lmblanco

    Hola Enrique

    Gracias a tí por el interés en el artículo, me alegra que te resulte de utilidad 😎

    Un saludo

  3. anonymous

    En el artículo "Utilizando SSIS para crear scripts de respaldo con la estructura de la base

Deja un comentario

Tema creado por Anders Norén