Script – automatizar restauración de una base de datos de prueba

En muchos clientes nos toca mantener en línea una copia de la base de datos de producción, para que se use como entorno de pruebas o de Aseguramiento de la Calidad. Por épocas necesitamos realizar a diario esa operación.

Este script es un proceso estándar que permite restaurar cualquier base de datos en el mismo servidor en el que se encuentra la base de datos de producción pero cambiando tanto el nombre de la base de datos como el nombre de los archivos de base de datos. Se puede programar un trabajo del Agente SQL Server (job) para que lo ejecute periódicamente

El script tiene los siguientes pasos:

  • · PASO 1 – Verifica existencia de la base de datos a restaurar y la elimina
  • · PASO 2 – Arma dinámicamente la sentencia de restauración de la base de datos
  • · PASO 3 – (Opcional) Cambios en la configuración y mantenimiento del log de la base de datos restaurada

Vamos a ampliar la explicación por cada paso. Este artículo requiere un conocimiento intermedio administración de SQL Server ya que utilizaremos varias sentencias de administración de bases de datos. No vamos a explicar detalladamente cada una, pero incluiremos el respectivo vínculo que nos dirige a la información detallada.

Este script ha sido diseñado teniendo en cuenta el siguiente escenario:

  1. Existe un backup de tipo completo (full) que está almacenado en un solo archivo de backup
  2. El archivo de backup se encuentra en la ubicación original
  3. Los nombre físicos de los archivos de base de datos usan las extensiones estándar de SQL Server (MDF y NDF para archivos de datos; LDF para log de transacciones)
  4. Los backup se han realizado usando las herramientas estándar de SQL Server
  5. La restauración se va a llevar a cabo en la misma ubicación física en la que se encuentra la base de datos original

PASO 1 – Verifica existencia de la base de datos a restaurar y la elimina

Para trabajar sobre una versión limpia de la base de datos, el script valida su existencia y la elimina. Aquí realizamos una operación de ALTER DATABASE, en la que cambiamos la base de datos a modo de usuario único, para cerrar cualquier conexión existente, de modo que la base de datos se pueda eliminar.

NOTA: Atención con esta operación, porque podría cortar alguna prueba que se esté ejecutando en ese momento.

PASO 2 – Arma dinámicamente la sentencia de restauración de la base de datos

Este es el paso más complejo, ya que implica obtener información de las tablas de historial de los backups para identificar el archivo que se desea restaurar y se ejecuta un loop para incluir cada uno de los archivo de la base de datos.

Primero vamos a identificar las tablas de historial de backup usadas:

  • Tabla backupset.- Esta tabla contiene información general de la copia de seguridad. Es como la cabecera de la información de backups. Aquí identificamos el backup que deseamos restaurar según el nombre de la base de datos y la fecha
  • Tabla backupmediafamily.- Contiene detalle del medio de almacenamiento. Contiene información detallada del archivo de backup. Aquí obtenemos el nombre exacto y ruta en la que se encuentra ubicado el backup
  • Tabla backupfile.- Esta tabla contiene información de los archivos de base de datos que están contenidos en el backup. Aquí obtenemos la ubicación y nombre de los archivos de base de datos, a los cuales se les va a cambiar el nombre. Sobre esta tabla se hace el loop, ya que una base de datos tiene por lo menos dos archivos: uno de datos y otro de transaction log.

PASO 3 – (Opcional) Cambios en la configuración y mantenimiento del log de la base de datos restaurada

Este paso es opcional. No es requerido para operar la base de datos restaurada pero dado que la base de datos restaurada se utilizará para calidad o pruebas, podemos configurar la administración del archivo de transacciones para que use el modelo de recuperación simple. También se aplica un CHECKPOINT para sincronizar las páginas de datos y el comando DBCC SHRINKFILE para reducir el tamaño del log

A continuación presentamos el script. Más abajo algunos aspectos a tener en cuenta:

Algunos aspectos a tener en cuenta:

  • Observemos que se usan variables para que el script sea lo más paramétrico posible
  • Debemos establecer el nombre de la base de datos que deseamos restaurar e indicar un sufijo que se añadirá al nombre de la base de datos restaurada, para así poder diferenciarlas
  • Otra variable importante es @DiasBackup que determina la antigüedad del backup que deseamos identificar
  • La sentencia MOVE TO permite cambiar el nombre a los archivos de base de datos para diferenciarlos de la base de datos original
  • Todas las ejecuciones de sentencias dinámicas se ejecutan con el procedimiento almacenado del sistema sp_executesql

Los animamos a que prueben el script y le hagan los cambios necesarios para adaptarlo a sus necesidades. Aun si no requieren este tipo de funcionalidad, recomendamos estudiarlo por que abarca varios conceptos clave de administración de bases de datos con SQL Server

Aquí algunos artículos relacionados que podemos encontrar en el blog:

Deja un comentario

Tu dirección de correo electrónico no será publicada.