Inicializar varias Bases de Datos desde una ubicación física determinada

En entornos multi instancias, en los cuales debemos mantener los mismos actualizados, o preparados para Deploy, Recovery, etc, es bueno contar con un mecanismo casi automático que inicialice el mismo y lo deje en las condiciones optimas.

Estos procesos normalmente incluyen el siguiente grupo de tareas:

Restaurar las Bases de Datos en un punto determinado
Inciializar determinados Jobs
Inicializar Distribuidores, Suscriptores y replicas en si

Todas estas tareas, pueden estar automatizadas en un «Job de Restauracion de Ambiente»
El mismo cuenta con pasos que realizar las taras anteriormente detalladas

En su momento, cuando se me ocurrio automatizar este procedimiento, vi que era sencillo para los Jobs, Replicas y ciertos Script de Inicializacion, no asi con las bases de datos.
El proceso de automatizar un Restore no es cosa complicada, pero cuando el mismo se tiene que hacer, aunque haya conexiones Lazy, siempre se debe hacer Base por Base, y no un conjunto de las mismas

En mi caso, tuve que pensar la forma de hacerlo con 8 base de datos, con lo cual, a menos que haga un Step por Base, el dia que tena una base de mas o de menos, me veia forzado a modificar el
Job de Inicializacion de Ambiente, agregando un mantenimiento extra a los ya existentes.

Para quitarme ese peso de encima, mantengo los Backup en una carpeta determinada (siempre la version que me interesa incializar o mantener ante una nueva inicializacion, o un Rollback arrojado por un Deploy incorrecto)

En si, la rutina recorre la carpeta especificada de archivos de Backup (con la excepcion de que deben tener el .BAK cada uno, y su nombre corresponderse al nombre de la base que va a restaurar)
Es decir, si necesito restaurar la base Accounts, mi archivo de backup se debe nomenclar Accounts.BAK, nada complicado.

A continuacion detalle el Store que realiza el Restore y mas abajo, el codigo del mismo para aplicar nuestra inicializacion.

CREATE PROC DBA_RestoreDynamicBackup            
   @BackupPathNameComplete NVARCHAR(1000),            
   @DataFileLocation VARCHAR(255),            
   @LogFileLocation VARCHAR(255)            
AS            
            
            
DECLARE @SQLString NVARCHAR(4000)            
            
--- Ejemplo            
/*            
SET @DataFileLocation = 'F:\SQLSERVER\DBSAFA2002'            
SET @LogFileLocation = 'L:\SQLSERVER\DBSAFA2002'            
SET @BackupPathNameComplete = 'F:\Backup BBDD Nivel 2\adserver_conso2.BAK'            
*/            
            
DECLARE @Bck AS TABLE (            
      LogicalName VARCHAR(255),            
      PhysicalName VARCHAR(255),            
      Type VARCHAR(255),            
      FileGroupName VARCHAR(255),            
      Size VARCHAR(255),            
      MaxSize VARCHAR(255),            
      FileId VARCHAR(255),            
      CreateLSN VARCHAR(255),            
      DropLSN VARCHAR(255),            
      UniqueId UNIQUEIDENTIFIER,            
      ReadOnlyLSN VARCHAR(255),            
      ReadWriteLSN VARCHAR(255),            
      BackupSizeInBytes VARCHAR(255),            
      SourceBlockSize VARCHAR(255),            
      FileGroupId VARCHAR(255),            
      FileGroupGUID VARCHAR(255),            
      DifferentialBaseLSN VARCHAR(255),            
      DifferentialBaseGUID UNIQUEIDENTIFIER,            
      IsReadOnly VARCHAR(255),            
      IsPresent VARCHAR(255),            
      TDEThumbprint VARCHAR(255)            
      )            
INSERT INTO @Bck            
EXEC ('RESTORE FILELISTONLY FROM  DISK = N'''+ @BackupPathNameComplete + ''' WITH  NOUNLOAD,  FILE = 1')            
            
            
DECLARE @LogicalName NVARCHAR(1000)            
DECLARE @PhysicalName NVARCHAR(1000)            
            
--- Primera parte del Restore            
SET @SQLString = 'ALTER DATABASE [' +             
           SUBSTRING(RIGHT(@BackupPathNameComplete,            
           CHARINDEX('\',REVERSE(@BackupPathNameComplete))-1), 1,             
           CHARINDEX('.', RIGHT(@BackupPathNameComplete,            
           CHARINDEX('\',REVERSE(@BackupPathNameComplete))-1))-1) +             
          ']' +            
     ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE '            
            
SET @SQLString = @SQLString +            
     'RESTORE DATABASE [' +             
           SUBSTRING(RIGHT(@BackupPathNameComplete,            
           CHARINDEX('\',REVERSE(@BackupPathNameComplete))-1), 1,             
           CHARINDEX('.', RIGHT(@BackupPathNameComplete,            
           CHARINDEX('\',REVERSE(@BackupPathNameComplete))-1))-1) +             
          ']' +            
     ' FROM DISK = N''' + @BackupPathNameComplete + '''' +            
     ' WITH FILE = 1, '            
            
--- Segunda parte con todos los Physical Files            
DECLARE cc CURSOR FOR             
SELECT LogicalName,            
  RIGHT(PhysicalName,CHARINDEX('\',REVERSE(PhysicalName))-1)            
FROM @Bck            
OPEN cc            
FETCH NEXT FROM cc INTO @LogicalName, @PhysicalName            
WHILE @@FETCH_STATUS = 0            
BEGIN            
	IF @LogicalName LIKE '%LOG%'
	BEGIN
		SET @SQLString = @SQLString + ' MOVE N'''+ @LogicalName + ''' TO N''' + @LogFileLocation + '\' + @PhysicalName + ''','              
	END ELSE
	BEGIN
         SET @SQLString = @SQLString + ' MOVE N'''+ @LogicalName + ''' TO N''' + @DataFileLocation + '\' + @PhysicalName + ''','              
	END            
FETCH NEXT FROM cc INTO @LogicalName, @PhysicalName            
END            
CLOSE cc            
DEALLOCATE cc            
            
--- Tercera parte con el tipo de Status del Backup            
SET @SQLString = @SQLString + ' NOUNLOAD,  REPLACE,  STATS = 10'            
PRINT @SQLString 
EXEC(@SQLString)            

El siguiente Script detalla como invocamos al Stored Procedure que recorre la totalidad de los archivos .BAK localizados en la carpeta ‘J:\BackUp_DBSAFA3001\BBDD’
El mismo aloja los DataFiles en ‘F:\DBO_DBSAFA3001’ y sus respectivos Log Files en ‘L:\DBO_DBSAFA3001’

DECLARE	@BackupPathNameComplete NVARCHAR(1000)
DECLARE	@DataFileLocation VARCHAR(255)
DECLARE	@LogFileLocation VARCHAR(255)
DECLARE	@BackupLocation NVARCHAR(500)
DECLARE	@FileName SYSNAME
DECLARE @dirContent TABLE(id INT IDENTITY(1,1), FileName SYSNAME NULL)
DECLARE  @cmd NVARCHAR(512)

SET @BackupLocation = 'J:\BackUp_DBSAFA3001\BBDD'
SET @DataFileLocation = 'F:\DBO_DBSAFA3001'
SET @LogFileLocation = 'L:\DBO_DBSAFA3001'
SET @cmd = 'DIR /b ' + @BackupLocation
 
INSERT INTO @dirContent
EXEC master..xp_cmdshell @cmd

DECLARE dd CURSOR FOR 
SELECT	FileName 
FROM	@dirContent
WHERE	FileName IS NOT NULL
AND		FileName NOT LIKE 'File%'
OPEN dd
FETCH NEXT FROM dd INTO @FileName
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @BackupPathNameComplete = @BackupLocation + '\' + @FileName
	EXEC DBA_RestoreDynamicBackup	@BackupPathNameComplete,
									@DataFileLocation,
									@LogFileLocation
FETCH NEXT FROM dd INTO @FileName
END
CLOSE dd
DEALLOCATE dd