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:

Existe un backup de tipo completo (full) que está almacenado en un solo archivo de backup
El archivo de backup se encuentra en la ubicación original
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)
Los backup se han realizado usando las herramientas estándar de SQL Server
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:

--variables de proceso
DECLARE 
  @NombreBaseDatos varchar(255),    -->-- Base de datos que se desea restaurar
  @Sufijo varchar(20),              -->-- Sufije que se añade al nombre de la base de datos restaurada
  @Archivo varchar(250),	    -->-- Para almacenar el nombre del último backup
  @Sentencia nvarchar(4000),	    -->-- Para almacenar la sentecnia del backup
  @NumeroArchivos tinyint,	    -->-- Para definir iteraciones del loop de archivos a restaurar
  @Contador tinyint=1,		    -->-- Para controlar el loop de archivos a restaurar (Default = 1)
  @BackupSetId int,		    -->-- Para almacenar el identificador del backup que queremos restaurar
  @DiasBackup tinyint=0;	    -->-- Para definir los días de antiguedad del backup (Default = 0)

--Definimos el nombre de la base de datos a restaurar
SET @NombreBaseDatos = 'MiBaseDatos';
SET @Sufijo = 'CopiaDiaria';
  
USE Master;

--*************************************************************************
--PASO 1 - Verifica existencia de la base de datos a restaurar y la elimina
--*************************************************************************
IF EXISTS(SELECT * FROM sys.databases WHERE name = @NombreBaseDatos + @Sufijo)
  BEGIN 
    -- nos aseguramos que se cierren las conexiones existentes
    SET @Sentencia = 'ALTER DATABASE '+@NombreBaseDatos + @Sufijo+' SET SINGLE_USER WITH ROLLBACK IMMEDIATE' 
    EXEC sys.sp_executesql @Command1=@Sentencia
    --Eliminamos la base de datos existente
    SET @Sentencia = 'DROP DATABASE '+@NombreBaseDatos + @Sufijo 
    EXEC sys.sp_executesql @Command1=@Sentencia
  END;

--*************************************************************************
--PASO 2 - Armamos dinámicamente la sentencia de restore
--         Se usa MOVE TO para cambiar de nombre los archivos
--*************************************************************************
-- Obtenemos el id del último backup ejecutado de la tabla msdb.dbo.backupset
SELECT @BackupSetId = backup_set_id
FROM msdb.dbo.backupset
WHERE CAST(backup_start_date AS DATE) = CAST(GETDATE()-@DiasBackup AS DATE)
AND Type = 'D';

--Obtenemos la ubicación y nombre físico del archivo de backup 
SELECT @Archivo = bm.physical_device_name
 FROM msdb.dbo.backupset bs
   INNER JOIN msdb.dbo.backupmediafamily bm
     ON bs.media_set_id=bm.media_set_id
WHERE backup_set_id = @BackupSetId;

/* Armamos una sentencia dinámica con el nombre del backup obtenido   
   y con la opción MOVE TO para cambiar el nombre de los archivos*/
SET @Sentencia = 'RESTORE DATABASE ' + @NombreBaseDatos + @Sufijo ;
SET @Sentencia = @Sentencia + ' FROM DISK = '''+@Archivo+''' WITH';

--Obtenemos el numero total de archivos en el backup para el loop
SELECT @NumeroArchivos = MAX(file_number)
 FROM msdb.dbo.backupfile bf
WHERE backup_set_id = @BackupSetId;

/*Loop para generar la línea de sentencia que cambia de nombre 
  a los archivos usando MOVE TO*/
WHILE @Contador<=@NumeroArchivos
  BEGIN
   SELECT @Sentencia=@Sentencia+' MOVE '''+logical_name+''' TO '''+LEFT(physical_name,LEN(physical_name)-4)+@Sufijo+RIGHT(physical_name,4)+'''' 
   FROM msdb.dbo.backupfile WHERE backup_set_id = @BackupSetId AND file_number = @Contador
   
   IF @Contador<@NumeroArchivos
     SELECT @Sentencia=@Sentencia+','
   SET @Contador+=1
  END;

--OPCIONAL: Para pintar la sentencia dinámica para depuración
--print @Sentencia

--Ejecucuón de la sentencia mediante el procedimiento almacenado del sistema sp_executesql 
EXEC sys.sp_executesql @Command1=@Sentencia;

--*************************************************************************
--PASO 3 - Cambios en la configuración y mantenimiento del log
--*************************************************************************
/* En caso la BD original estuviera en modelo de recuperaicón FULL, la camibamos a SIMPLE
   Para simplificar la administración y reducir el tamaño del log. 
   Al ser una BD de prueba o desarrollo podemos mantener un log simple*/

--Cambiamos a modelo de recuperación simple
SET @Sentencia = 'ALTER DATABASE '+ @NombreBaseDatos + @Sufijo +' SET RECOVERY SIMPLE'
EXEC sys.sp_executesql @Command1=@Sentencia;

/*Debido a que vamos a usar USE, hay que ejecutar la soperaciones en un solo paso
  1.Movemos la conexión a la base de datos recién restaurada 
  2.Ejecutamos un checkpoint para sincronizar las páginas de datos
  3.Obtenemos el nombre lógico del archivo de log (Asumimos que la BD tiene solo un archivo de log)
*/
SET @Sentencia = 'USE '+ @NombreBaseDatos + @Sufijo + ';' + ASCII(10)
SET @Sentencia = @Sentencia + 'CHECKPOINT;'

SELECT @Archivo = logical_name
 FROM msdb.dbo.backupfile bf
WHERE backup_set_id = @BackupSetId
AND file_type='L'

--Armamos la sentencia con el nombre de archivo y reducimos el archivo de log
--Se esta reduciendo a 1024MB (1GB) el número se puede cambiar según se requiera
SET @Sentencia = @Sentencia + 'DBCC SHRINKFILE ('''+@Archivo+''', 1024)'
EXEC sys.sp_executesql @Command1=@Sentencia;
GO

100

101

102

103

104

--variables de proceso

DECLARE

@NombreBaseDatos varchar(255), -->-- Base de datos que se desea restaurar

@Sufijo varchar(20), -->-- Sufije que se añade al nombre de la base de datos restaurada

@Archivo varchar(250), -->-- Para almacenar el nombre del último backup

@Sentencia nvarchar(4000), -->-- Para almacenar la sentecnia del backup

@NumeroArchivos tinyint, -->-- Para definir iteraciones del loop de archivos a restaurar

@Contador tinyint=1, -->-- Para controlar el loop de archivos a restaurar (Default = 1)

@BackupSetId int, -->-- Para almacenar el identificador del backup que queremos restaurar

@DiasBackup tinyint=0; -->-- Para definir los días de antiguedad del backup (Default = 0)

--Definimos el nombre de la base de datos a restaurar

SET @NombreBaseDatos = 'MiBaseDatos';

SET @Sufijo = 'CopiaDiaria';

USE Master;

--*************************************************************************

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

--*************************************************************************

IF EXISTS(SELECT * FROM sys.databases WHERE name = @NombreBaseDatos + @Sufijo)

BEGIN

-- nos aseguramos que se cierren las conexiones existentes

SET @Sentencia = 'ALTER DATABASE '+@NombreBaseDatos + @Sufijo+' SET SINGLE_USER WITH ROLLBACK IMMEDIATE'

EXEC sys.sp_executesql @Command1=@Sentencia

--Eliminamos la base de datos existente

SET @Sentencia = 'DROP DATABASE '+@NombreBaseDatos + @Sufijo

EXEC sys.sp_executesql @Command1=@Sentencia

END;

--*************************************************************************

--PASO 2 - Armamos dinámicamente la sentencia de restore

-- Se usa MOVE TO para cambiar de nombre los archivos

--*************************************************************************

-- Obtenemos el id del último backup ejecutado de la tabla msdb.dbo.backupset

SELECT @BackupSetId = backup_set_id

FROM msdb.dbo.backupset

WHERE CAST(backup_start_date AS DATE) = CAST(GETDATE()-@DiasBackup AS DATE)

AND Type = 'D';

--Obtenemos la ubicación y nombre físico del archivo de backup

SELECT @Archivo = bm.physical_device_name

FROM msdb.dbo.backupset bs

INNER JOIN msdb.dbo.backupmediafamily bm

ON bs.media_set_id=bm.media_set_id

WHERE backup_set_id = @BackupSetId;

/* Armamos una sentencia dinámica con el nombre del backup obtenido

y con la opción MOVE TO para cambiar el nombre de los archivos*/

SET @Sentencia = 'RESTORE DATABASE ' + @NombreBaseDatos + @Sufijo ;

SET @Sentencia = @Sentencia + ' FROM DISK = '''+@Archivo+''' WITH';

--Obtenemos el numero total de archivos en el backup para el loop

SELECT @NumeroArchivos = MAX(file_number)

FROM msdb.dbo.backupfile bf

WHERE backup_set_id = @BackupSetId;

/*Loop para generar la línea de sentencia que cambia de nombre

a los archivos usando MOVE TO*/

WHILE @Contador<=@NumeroArchivos

BEGIN

SELECT @Sentencia=@Sentencia+' MOVE '''+logical_name+''' TO '''+LEFT(physical_name,LEN(physical_name)-4)+@Sufijo+RIGHT(physical_name,4)+''''

FROM msdb.dbo.backupfile WHERE backup_set_id = @BackupSetId AND file_number = @Contador

IF @Contador<@NumeroArchivos

SELECT @Sentencia=@Sentencia+','

SET @Contador+=1

END;

--OPCIONAL: Para pintar la sentencia dinámica para depuración

--print @Sentencia

--Ejecucuón de la sentencia mediante el procedimiento almacenado del sistema sp_executesql

EXEC sys.sp_executesql @Command1=@Sentencia;

--*************************************************************************

--PASO 3 - Cambios en la configuración y mantenimiento del log

--*************************************************************************

/* En caso la BD original estuviera en modelo de recuperaicón FULL, la camibamos a SIMPLE

Para simplificar la administración y reducir el tamaño del log.

Al ser una BD de prueba o desarrollo podemos mantener un log simple*/

--Cambiamos a modelo de recuperación simple

SET @Sentencia = 'ALTER DATABASE '+ @NombreBaseDatos + @Sufijo +' SET RECOVERY SIMPLE'

EXEC sys.sp_executesql @Command1=@Sentencia;

/*Debido a que vamos a usar USE, hay que ejecutar la soperaciones en un solo paso

1.Movemos la conexión a la base de datos recién restaurada

2.Ejecutamos un checkpoint para sincronizar las páginas de datos

3.Obtenemos el nombre lógico del archivo de log (Asumimos que la BD tiene solo un archivo de log)

SET @Sentencia = 'USE '+ @NombreBaseDatos + @Sufijo + ';' + ASCII(10)

SET @Sentencia = @Sentencia + 'CHECKPOINT;'

SELECT @Archivo = logical_name

FROM msdb.dbo.backupfile bf

WHERE backup_set_id = @BackupSetId

AND file_type='L'

--Armamos la sentencia con el nombre de archivo y reducimos el archivo de log

--Se esta reduciendo a 1024MB (1GB) el número se puede cambiar según se requiera

SET @Sentencia = @Sentencia + 'DBCC SHRINKFILE ('''+@Archivo+''', 1024)'

EXEC sys.sp_executesql @Command1=@Sentencia;

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:

Comparte Este artículo:

Deja una respuesta Cancelar la respuesta