Generar Snapshot Database Automaticamente

Rara vez tenemos que crear un Snapshot de una base de datos determinada, aun mas, cuando la misma posee múltiples Datafiles y tenemos que andar fijándonos donde están, como se llaman.etc..etc..

En esta ocasión, tuve que armar una script, para crear automáticamente un Snapshot de la base de datos a las 13Hs y a las 20hs respectivamente.
El problema fue armar un script lo suficientemente autonono para que, indistintamente del nombre de la base de datos que necesitemos, el mismo cree el Snapshot donde corresponda y no me interese saber si tiene 1 o 200 Datafiles.

Aquí esta dicho script.

--- Creamos la Base de Datos de Snapshot
DECLARE     @Str NVARCHAR(4000)
DECLARE		@LogicalFileName VARCHAR(500)
DECLARE		@Path VARCHAR(500)
DECLARE     @Fecha VARCHAR(30)
DECLARE     @DatabaseName VARCHAR(30)
DECLARE		@DB VARCHAR(30)
SET	@DB		= 'CS_INT64'

SET @Fecha = RIGHT(REPLICATE('0', 2)    + CAST(DATEPART(DD, GETDATE()) AS VARCHAR(2)), 2) + 
             RIGHT(REPLICATE('0', 2)   + CAST(DATEPART(MM, GETDATE()) AS VARCHAR(2)), 2) + '_' +
             RIGHT(REPLICATE('0', 2)   + CAST(DATEPART(HH, GETDATE()) AS VARCHAR(2)), 2) +
             RIGHT(REPLICATE('0', 2)   + CAST(DATEPART(MINUTE, GETDATE()) AS VARCHAR(2)), 2)

SET @DatabaseName = @DB + '_' + @Fecha
SET @Str = 'CREATE DATABASE ' + @DatabaseName + ' ON '
DECLARE dd CURSOR FOR
SELECT name AS LogicalFileName, physical_name
FROM sys.master_files AS mf
WHERE DB_NAME(database_id) = @DB
AND type_desc = 'ROWS'
AND state = 0
OPEN dd
FETCH NEXT FROM dd INTO @LogicalFileName, @Path
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @Str =  @Str +  '( NAME = ' + @LogicalFileName + ', FILENAME = ''' + SUBSTRING(@Path, 1, CHARINDEX(@DB, @Path) -1) + @LogicalFileName + '' +  '_' + @Fecha + '.ss' +'''' + '),'
FETCH NEXT FROM dd INTO @LogicalFileName, @Path
END
CLOSE dd
DEALLOCATE dd

SELECT @Str = REVERSE(SUBSTRING(REVERSE(@Str), CHARINDEX(',', REVERSE(@Str)) +1, DATALENGTH(@Str)))
SELECT @Str = @Str + ' AS SNAPSHOT OF ' + @DB 


EXEC (@Str)

Espero les sirva.

Inicializar Replicación Transaccional desde un Backup

En algunos escenarios de disaster recovery, tenemos que volver a añadir los subscriptores a las publicaciones corruptas y en muchas oportunidades, tanto por el tipo de replica transaccional como por el tamaño de la BBDD dan como resultado un grupo de variables que hacen que el tiempo de resolución sea mas extenso y en algunos casos, muy difícil de calcular.

Como lo dice el titulo de este post, vamos a presentar los pasos necesarios para restaurar un subscriptor mediante un Backup, sin necesidad de tener que añadirlo y generar nuevamente todo el Sanpshot, ya que truncaría todas las tablas del destino y pasaría de forma secuencial y transaccional registro por registro de cada uno de los artículos de nuestra replica.

En este ejemplo, se toma una replica que tiene alrededor de 500 artículos y un tamaño de BBDD superior a los 90GB

Pasemos a detallar los pasos:

1 – Eliminar el Subscriptor de la replica actual.
2 – Cambiar el valor de 0 a 1 del parámetro (allow initialize from backup) de nuestra replica actual.

EXEC sp_changepublication @publication = 'Nombre_Publicacion', @property = N'allow_initialize_from_backup', @value = true

3 – Regenerar el Snapshot, estoy no tendría que demorar mucho, porque ya se encuentra actualizado.
3 – Realizar un Backup FULL de la BBDD en replica.
4 – Restaurar dicho Backup en el server de subscripcion (el que eliminamos en el paso 1).
5 – Aplicar Script de Alta de Subscripcion:

DECLARE @publication AS sysname;
DECLARE @subscriber AS sysname;
DECLARE @subscriptionDB AS sysname;
SET @publication = N'NombrePublicacion';
SET @subscriber = 'SERVER_SUBSCRIPTOR';
SET @subscriptionDB = N'Nombre_BBDD';

--Add a push subscription to a transactional publication.
USE Nombre_BBDD
EXEC sp_addsubscription
  @publication = @publication,
  @subscriber = @subscriber,
  @destination_db = @subscriptionDB,
  @sync_type = 'initialize with backup',
  @backupdevicetype = 'disk',
  @backupdevicename = 'Z:\Backup\BBDD.BAK',
  @subscription_type = N'push';

--Add an agent job to synchronize the push subscription.
EXEC sp_addpushsubscription_agent
  @publication = @publication,
  @subscriber = @subscriber,
  @subscriber_db = @subscriptionDB,
  @job_login = 'SERVER_PUBLICADOR',
  @job_password = 'StrongPassword';
GO

Hay que tener como precaución, que si el tiempo que se demora en hacerse el backup, copiarlo, restaurarlo en el destino, etc..se puede llegar a generar algún Backup diferencia hora a hora, si es que lo tenemos habilitado para dicha BBDD.
En caso de que se genere algún backup, posterior al generado para re-generar la replica, el script del paso 5 dará un error, ya que el SID de la BBDD original, ya no coincide con el SID del backup generado de la BBDD.