Scripts automáticos para restaurar backups en SQL Server

En el artículo del pasado mes de marzo os mostré cómo crear un script para realizar backups de bases de datos. Hoy vamos a dar un paso más y trabajar en el proceso inverso: la generación automática de scripts T-SQL para restaurar esos backups.

Para ello he desarrollado un procedimiento almacenado llamado dbo.xp_GenerateRestoreScripts, que debe crearse en la base de datos master de la instancia de SQL Server correspondiente. La base de este script fue desarrollada originalmente por mis compañeros del Grupo RETAbet, Marta Conde y Luis Felipe Pérez, a quienes agradezco su trabajo. Sobre su versión he añadido algunas mejoras que lo hacen más flexible y fácil de usar.

🎯 Objetivo del procedimiento

El propósito de este stored procedure es sencillo pero muy potente: analizar un directorio raíz en busca de archivos de backup (FULL y DIFF) y generar de manera automática las sentencias necesarias para restaurarlos.

Además, soporta distintos escenarios de recuperación, permitiendo trabajar en tres modos principales:

1. Restaurar en la ubicación original.
2. Restaurar en la base de datos original pero moviendo los archivos a otra ruta.
3. Restaurar en una nueva base de datos, con redirección de archivos.

Como extra, también puede generar salidas en modo verbose, muy útil si lo queremos integrar en scripts de PowerShell.

⚙️ Parámetros de entrada

El procedimiento acepta varios parámetros que permiten personalizar su comportamiento. Los más relevantes son:

• @ModoRecuperación (TINYINT) → Define el modo de restauración (1, 2 o 3).
• @backupPath (NVARCHAR(500)) → Ruta raíz donde se encuentran los archivos de backup.
• @Path_MDF_Destination / @Path_NDF_Destination / @Path_LDF_Destination → Nuevas rutas de destino para los archivos físicos.
• @dbName_suffix (NVARCHAR(200)) → Sufijo que se añade al nombre de la base de datos y sus archivos al restaurar.
• @RecoveryON (BIT) → Indica si se debe incluir la cláusula WITH RECOVERY (1 por defecto).
• @VerboseMode (BIT) → Muestra información detallada para diagnóstico (0 por defecto).
• @GridResultSet (BIT) → Devuelve los comandos como conjunto de resultados (0 por defecto).
• @ScanSubdirectories (BIT) → Indica si debe buscar también en subdirectorios (1 por defecto).
• @databases (NVARCHAR(MAX)) → JSON con la colección de bases de datos a restaurar (ejemplo: [{«database_id»:5,»name»:»AdventureWorks2019″}]).

🧩 Funcionamiento paso a paso

El procedimiento se organiza en cuatro bloques principales:

Bloque 1 – Recorrido de carpetas
Se analiza el directorio indicado en @backupPath (y subdirectorios si aplica) utilizando el procedimiento del sistema master.sys.xp_dirtree. Con esa información se rellena la tabla @fileList con todos los archivos detectados.

BEGIN /*1. BLOQUE PARA IR RECORRIENDO LAS DISTINTAS CARPETAS QUE ENCUENTRE DE FORMA RECURSIVA Y TENER TODO LO QUE HAY EN EL ARBOL*/

	if @ScanSubdirectories=1 --esta hecho asi porque no se puede pasar depth como parametro
	begin
		INSERT #DirectoryTree (subdirectory,depth,isfile)
		EXEC master.sys.xp_dirtree @backupPath,0,1
	end
	else
	begin
		INSERT #DirectoryTree (subdirectory,depth,isfile)
		EXEC master.sys.xp_dirtree @backupPath,1,1 --SOLO BUSCA EN EL DIRECTORIO ACTUAL
	end

	;WITH fileparents (
		fileid
		,subdirectory
		,parentid
		,isfile
		)
	AS (
		SELECT c.fileid
			,c.subdirectory
			,(
				SELECT max(p.fileId)
				FROM #DirectoryTree p
				WHERE p.depth = c.depth - 1
					AND p.fileId < c.fileId
				) parentId
			,c.isfile
		FROM #DirectoryTree c
		)
		,DirListing (
		fileid
		,subdirectory
		,isfile
		)
	AS (
		SELECT fileid
			,subdirectory + '' subdirectory
			,isfile
		FROM fileparents
		WHERE parentid IS NULL
	
		UNION ALL
	
		SELECT f.fileid
			,d.subdirectory + '\' + f.subdirectory subdirectory
			,f.isfile
		FROM DirListing d
		INNER JOIN fileparents f ON f.parentid = d.fileid
		)
	insert into @fileList (IsFile, fullpath)
	SELECT l.isfile
		,@backupPath + l.subdirectory
	FROM DirListing l

END

Bloque 2 – Identificación de backups
Cada archivo de @fileList se evalúa con RESTORE HEADERONLY FROM DISK.

• Si el comando falla → no es un backup válido.
• Si funciona → se marca como archivo de backup.

En este paso también se filtra el listado según el JSON recibido en @databases, de modo que solo se consideren las bases de datos especificadas.

BEGIN /*2. COMPRUEBA TODOS LOS FICHEROS PARA SABER QUE SON BACKUPS Y SACAR SU INFORMACION*/
	WHILE EXISTS (SELECT TOP 1 1 FROM @fileList where Id > @fileListid)
	BEGIN
		SELECT TOP 1 
			@backupFile_fullpath = fullpath
			,@fileListid = id
		FROM @fileList where Id > @fileListid
		ORDER BY ID asc

		--añadimos bloque try catch por si encontramos algun fichero que no es un backup. lo marca y sigue para adelante (PARA NO BASARME EN LAS EXTENSIONES)
		begin try
			INSERT INTO #FilesInfo
			(
			BackupName
			, BackupDescription
			, BackupType
			, ExpirationDate
			, Compressed
			, Posición
			, DeviceType
			, UserName
			, ServerName
			, DatabaseName
			, DatabaseVersion
			, DatabaseCreationDate
			, BackupSize
			, FirstLSN
			, LastLSN
			, CheckpointLSN
			, DatabaseBackupLSN
			, BackupStartDate
			, BackupFinishDate
			, SortOrder
			, CodePage
			, UnicodeLocaleId
			, UnicodeComparisonStyle
			, CompatibilityLevel
			, SoftwareVendorId
			, SoftwareVersionMajor
			, SoftwareVersionMinor
			, SoftwareVersionBuild
			, MachineName
			, Marcas
			, BindingID
			, RecoveryForkID
			, Intercalación
			, FamilyGUID
			, HasBulkLoggedData
			, IsSnapshot	
			, IsReadOnly	
			, IsSingleUser	
			, HasBackupChecksums
			, IsDamaged	
			, BeginsLogChain	
			, HasIncompleteMetaData	
			, IsForceOffline	
			, IsCopyOnly		
			, FirstRecoveryForkID		
			, ForkPointLSN	
			, RecoveryModel	
			, DifferentialBaseLSN	
			, DifferentialBaseGUID	
			, BackupTypeDescription	
			, BackupSetGUID
			, CompressedBackupSize	
			, containment	
			, KeyAlgorithm	
			, EncryptorThumbprint	
			, EncryptorType	
			, LastValidRestoreTime --(2022)
			, TimeZone --(2022)
			, CompressionAlgorithm --(2022)
			)
			EXEC ('RESTORE HEADERONLY FROM DISK = ''' + @backupFile_fullpath + '''')

			update #FilesInfo set FullPath=@backupFile_fullpath where Id=(select max(Id) from #FilesInfo)

			--Ahora guardamos la informacion especifica de los ficheros de cada bbdd (sacado de su backup) para saber los mdf,ndf,ldf,etc.
			insert into #FileListDatabase
			EXEC('RESTORE FILELISTONLY FROM DISK =  ''' + @backupFile_fullpath + '''')

		end try
		begin catch
			DECLARE @ErrorMessage NVARCHAR(4000);  
			DECLARE @ErrorLine INT;  
			DECLARE @ErrorSeverity INT;  
			DECLARE @ErrorState INT;  

			select @ErrorMessage = ERROR_MESSAGE()
				,@ErrorLine = ERROR_LINE()
				,@ErrorSeverity = ERROR_SEVERITY()
				,@ErrorState = ERROR_STATE()
		
			-- para devolver el fichero concreto que ha dado error
			-- select @backupPath, @backupFile

			RAISERROR (@ErrorMessage, -- Message text.
						@ErrorLine, --Error Line 
						@ErrorSeverity, -- Severity.  
						@ErrorState -- State.  
						);  	
		end catch
	END	

	--Actualizamos todos los backupname a fullpath porque no lo mete bien si esta en subdirectorios
	UPDATE #FilesInfo SET BackupName=FullPath

	--Devolver ficheros que no son backups
	---------------------------------------------------------------------------------------------------------
	IF @VerboseMode=0
	BEGIN
		select l.*, 'No es backup'
		from @fileList l
			left join #FilesInfo i
				on l.fullpath=i.FullPath
		where i.FullPath is null
		and l.IsFile=1
	END

	--CARGA LAS BBDDs desde el Json
	insert into #databases (database_id, DatabaseName)
	SELECT *
	FROM OPENJSON (@databases)
	WITH
	(   database_id smallint '$.database_id'
		,DatabaseName sysname '$.name'
	) z

	--BBDD SIN NINGUN BACKUP FULL
	IF @VerboseMode=0
	BEGIN
		select d.*, 'SIN BACKUP COMPLETO', f.*
		from #databases d
			left join #FilesInfo f
				on d.DatabaseName=f.DatabaseName
				and f.BackupType = 1 -- Completo
		where f.BackupName is null
	END
END

Bloque 3 – Selección de backups válidos
Se distinguen backups completos (FULL) y diferenciales (DIFF), y se seleccionan los más recientes para cada base de datos.

BEGIN /*3. CON LA INFORMACION DE TODOS LOS BACKUPS SE ELIGE LOS QUE HAY QUE RESTAURAR PONIENDOLES  UN ORDEN A CADA UNO*/

	-- Añadir columna OrdenRestauracion smallint en la tabla @FilesInfo
	-- Añadir columna OrdenRestauracion_BBDD smallint en la tabla @FilesInfo para elegir el orden de cada bbdd
	ALTER TABLE #FilesInfo ADD OrdenRestauracion SMALLINT NULL, OrdenRestauracion_BBDD SMALLINT NULL

	--PRIMERO MARCO TODAS LAS BBDD CON UN ORDEN AUN SIN DETERMINAR!!!!. Se podria hacer por tamaño de la bbdd, por backup.. o a ver por que nos interesa
	update f set f.OrdenRestauracion_BBDD=d.database_id 
	from #FilesInfo f
		inner join #databases d
			on f.DatabaseName=d.DatabaseName

	--Doy las n vueltas una por cada BBDD
	while exists (select top 1 1 from #databases where Control=0)
	begin

		select top 1 @DBName = DatabaseName
		from #databases
		where Control=0

		-- Comprobar que hay un backup completo y actualizar OrdenRestauración 1 para el último backup encontrado
		IF EXISTS(SELECT TOP 1 1 FROM #FilesInfo WHERE BackupType = 1 and DatabaseName=@DBName)
		BEGIN
			SELECT TOP 1 
				@BackupName = BackupName
				, @LastLSN = LastLSN
			FROM
				#FilesInfo
			WHERE 
				BackupType = 1 -- Completo
				and DatabaseName=@DBName
			ORDER BY
				FirstLSN DESC	

			UPDATE FI
			SET	FI.OrdenRestauracion = 1
			FROM
				#FilesInfo AS FI
			WHERE
				FI.BackupType = 1 -- Completo
				AND FI.BackupName = @BackupName
				and fi.DatabaseName=@DBName
		END
		ELSE
		BEGIN
			update #databases set ErrorMessage='No hay un backup completo' where DatabaseName=@DBName
		END

		-- Comprobar si hay un backup diferencial y actualizar OrdenRestauración 2 para el último encontrado posterior al completo
		IF EXISTS(SELECT TOP 1 1 FROM #FilesInfo WHERE BackupType = 5 and DatabaseName=@DBName)
		BEGIN
			SET @BackupName = NULL	
	
			SELECT TOP 1 
				@BackupName = BackupName
				, @LastLSN = LastLSN
			FROM
				#FilesInfo
			WHERE 
				BackupType = 5 -- Diferencial
				AND FirstLSN >= @LastLSN
				and DatabaseName=@DBName
			ORDER BY
				FirstLSN DESC	

			-- Si hay backups diferenciales se establece el orden 2
			IF (@BackupName IS NOT NULL)
			BEGIN
				UPDATE FI
				SET	FI.OrdenRestauracion = 2
				FROM
					#FilesInfo AS FI
				WHERE
					FI.BackupType = 5 -- Diferencial
					AND FI.BackupName = @BackupName
					and fi.DatabaseName=@DBName
			END
		END

		--Controles para avanzar en el bucle de control a todas las bbdd
		update #databases set Control=1 where DatabaseName=@DBName

	end --Fin del while para el bucle de todas las bbdd
		
	IF @VerboseMode=0
	BEGIN
		--Devolvemos las bbdd que no tienen un completo
		select *
		from #databases
		where ErrorMessage is not null

		--Y el detalle de lo que se ha cogido para restaurar
		select f.BackupName, f.DatabaseName, f.BackupType, f.BackupTypeDescription, f.BackupSize, f.id, f.OrdenRestauracion, f.OrdenRestauracion_BBDD, f.*
		from #FilesInfo f
		where OrdenRestauracion is not null
		order by f.OrdenRestauracion_BBDD, f.OrdenRestauracion
	END
END

Bloque 4 – Generación de comandos
Aquí se construyen los comandos de restauración en función del modo de recuperación elegido y los parámetros recibidos.

BEGIN /*4. CREACION DE LOS COMANDOS DE RESTAURACION*/

	--Cargamos las unidades que hay con su espacio disponible
	insert into #Drives
	EXEC MASTER..xp_fixeddrives

	---------------------------------------------------------------------------------------------------------------------
	--Hacemos otro bucle para montar las sentencias para aquellas bbdd restaurables
	--Doy las n vueltas una por cada BBDD
	while exists (select top 1 1 from #databases where Control=1 and ErrorMessage is null)
	begin

		select top 1 @DBName = DatabaseName
		from #databases
		where ErrorMessage is null

		--REINICIO DE VARIABLES
		set @cmd=''
		set @cmdfilesmdf=''
		set @cmdfilesndf=''
		set @cmdfilesldf=''
		set @LogicalNamemdf=''
		set @LogicalNamendf=''
		set @LogicalNameldf=''

		-- 4.1 - Restaurar backup completo
		IF EXISTS(select top 1 * from #FilesInfo WHERE BackupType = 1 and DatabaseName=@DBName)
		BEGIN 

			SET @BackupName = NULL

			truncate table #FileListDatabase

			SELECT @BackupName = BackupName --+ '.Bak'
			FROM #FilesInfo  
			WHERE BackupType = 1 and DatabaseName=@DBName
				AND OrdenRestauracion is not null --(v.1.4.1)

			-- Restaurar en el original
			IF (@ModoRecuperacion = 1)
			BEGIN
				SET @cmd = 'RESTORE DATABASE [' + @DBName + '] FROM DISK = '''  
					+ @BackupName + ''' WITH NORECOVERY, STATS = 5, REPLACE'

				--Sacamos info de ficheros del backup para comprobar si existen las unidades y tienen espacio
				insert into #FileListDatabase
				EXEC ('RESTORE FILELISTONLY FROM DISK = ''' + @BackupName + '''')

				--Para devolver el tamaño total de la bbdd
				select @DatabaseSizeMB = sum(Size)/1048576.0
				from #FileListDatabase
				where Type='D'

				--Devolver los datos como texto o grid
				if @GridResultSet=0
				BEGIN
					PRINT @cmd 
				END
				ELSE
				BEGIN
					insert into @Results (DataBaseName, DataBaseSizeMB, ordercmd, cmd)
						select @DBName, @DatabaseSizeMB, 1, @cmd
				END

				if @VerboseMode=0
				BEGIN
					--Comprobacion por si la unidad donde va a restaurar no existe
					; with BackupSpace as
					(select LEFT(PhysicalName,CHARINDEX(':',PhysicalName,0)-1) as Drive, Size/1048576.0 as SizeMB
					from #FileListDatabase f)
					select 'Unidad ' + b.Drive + ': no existe para el backup ' + @BackupName + ' de ' + @DBName + ' y es necesaria'
					from BackupSpace b
						left join #Drives d
							on b.Drive=d.Drive
					where d.Drive is null

					--Comprobacion de si la unidad tiene espacio suficiente
					; with BackupSpace as
					(select LEFT(PhysicalName,CHARINDEX(':',PhysicalName,0)-1) as Drive, Size/1048576.0 as SizeMB
					from #FileListDatabase f)
					select 'Unidad ' + b.Drive + ': con ' + CAST(d.FreeMB AS varchar) +' libres y hacen falta ' + cast(b.SizeMB as varchar) + 'para el backup ' + @BackupName + ' de ' + @DBName
					from BackupSpace b
						inner join #Drives d
							on b.Drive=d.Drive
					where b.SizeMB>=d.FreeMB
				END					
			END

			-- Restaurar el original/nuevo con los archivos en otra ubicación
			IF (@ModoRecuperacion = 2) OR (@ModoRecuperacion=3)
			BEGIN

				IF @ModoRecuperacion=2 --Si es sobre la misma BBDD borramos suffix y añadimos el REPLACE
				BEGIN
					set @dbName_suffix=''
					set @Replace='REPLACE, '
				END

				--Sacamos info de ficheros del backup para comprobar los ficheros del backup (mdf, ndf y ldf)
				insert into #FileListDatabase
				EXEC ('RESTORE FILELISTONLY FROM DISK = ''' + @BackupName + '''')

				--Para devolver el tamaño total de la bbdd
				select @DatabaseSizeMB = sum(Size)/1048576.0
				from #FileListDatabase
				where Type='D'

				--Parte del mdf. solo hay uno (puede haber varios pero primario solo uno)
				----------------------MDF
				select @LogicalNamemdf=LogicalName
				from #FileListDatabase
				where Type='D' and CreateLSN=0

				set @cmdfilesmdf='MOVE N'''+ @LogicalNamemdf + ''' TO N'''+ @Path_MDF_Destination + @LogicalNamemdf + @dbName_suffix+'.mdf'','

				--ndf puede haber n asi que hacemos un bucle (pueden tener la extension que sea pero seran archivos secundarios)
				----------------------NDF
				SET @FileId = 1

				while exists (select top 1 1 from #FileListDatabase where Type='D' and CreateLSN<>0 and FileID>@FileId) --existe algun ndf sin procesar
				begin
					select top 1 @FileId=FileId, @LogicalNamendf=LogicalName
					from #FileListDatabase
					where Type='D' and CreateLSN<>0 and FileID>@FileId
					order by FileID

					select @cmdfilesndf = @cmdfilesndf +  'MOVE N''' + @LogicalNamendf + ''' TO N'''+ @Path_NDF_Destination + @LogicalNamendf+ @dbName_suffix +'.ndf'','

				end

				--ldf puede haber n asi que hacemos un bucle (pueden tener la extension que sea pero seran archivos de log)
				----------------------LDF
				set @FileId = 1

				while exists (select top 1 1 from #FileListDatabase where Type='L' and FileID>@FileId) --existe algun ndf sin procesar
				begin
					select top 1 @FileId=FileId, @LogicalNameldf=LogicalName
					from #FileListDatabase
					where Type='L' and FileID>@FileId
					order by FileID

					select @cmdfilesldf = @cmdfilesldf +  'MOVE N''' + @LogicalNameldf + ''' TO N'''+ @Path_LDF_Destination + @LogicalNameldf + @dbName_suffix +'.ldf'','

				end

				SET @cmd = 'RESTORE DATABASE [' + @DBName + @dbName_suffix + '] FROM DISK = '''  
					+ @BackupName + ''' WITH NORECOVERY, STATS = 5, ' + @Replace
					+ @cmdfilesmdf
					+ @cmdfilesndf
					+ @cmdfilesldf
					+ '  NORECOVERY,  NOUNLOAD,  STATS = 5' 

				IF @GridResultSet=0
				BEGIN
					PRINT @cmd 
				END
				ELSE
				BEGIN
					insert into @Results (DataBaseName, DataBaseSizeMB, ordercmd, cmd)
						select @DBName, @DatabaseSizeMB, 1, @cmd
				END
			END
		END

		-- 4.2 - Restaurar backup diferencial
		IF EXISTS(select top 1 * from #FilesInfo WHERE BackupType = 5 AND DatabaseName=@DBName)
		BEGIN  

			SET @BackupName = NULL

			SELECT @BackupName = BackupName --+ '.Bak'
			FROM #FilesInfo  
			WHERE BackupType = 5 and DatabaseName=@DBName
				AND OrdenRestauracion is not null --(v.1.4.1)

			-- Restaurar en el original o Restaurar el original con los archivos en otra ubicación
			IF ((@ModoRecuperacion = 1) OR (@ModoRecuperacion = 2))
			BEGIN
				SET @cmd = 'RESTORE DATABASE [' + @DBName + '] FROM DISK = '''  
					+ @BackupName + ''' WITH NORECOVERY, STATS = 5' 

				IF @GridResultSet=0
				BEGIN
					PRINT @cmd 
				END
				ELSE
				BEGIN
					insert into @Results (DataBaseName, DataBaseSizeMB, ordercmd, cmd)
						select @DBName, @DatabaseSizeMB, 2, @cmd
				END
			END	
	
			-- Restaurar en otra bbdd
			IF (@ModoRecuperacion = 3)
			BEGIN
				SET @cmd = 'RESTORE DATABASE [' + @DBName + @dbName_suffix + + '] FROM DISK = '''  
					+ @BackupName + ''' WITH NORECOVERY, STATS = 5' 

				IF @GridResultSet=0
				BEGIN
					PRINT @cmd 
				END
				ELSE
				BEGIN

					insert into @Results (DataBaseName, DataBaseSizeMB, ordercmd, cmd)
						select @DBName, @DatabaseSizeMB, 2, @cmd
				END
			END
		END

		-- 4.4 - Put database in a useable state 
		if @RecoveryON=1
		BEGIN
			-- Hacer un Restore 2
			IF (@ModoRecuperacion = 3)
			BEGIN
				SET @cmd = 'RESTORE DATABASE [' + @DBName + @dbName_suffix + '] WITH RECOVERY' 

				if @GridResultSet=0
				BEGIN
					PRINT @cmd 
				END
				ELSE
				BEGIN
					insert into @Results (DataBaseName, DataBaseSizeMB, ordercmd, cmd)
						select @DBName, @DatabaseSizeMB, 3, @cmd
				END
			END
			ELSE -- Restaurar en el original o Restaurar el original con los archivos en otra ubicación
			BEGIN
				SET @cmd = 'RESTORE DATABASE [' + @DBName + '] WITH RECOVERY' 
			
				if @GridResultSet=0
				BEGIN
					PRINT @cmd 
				END
				ELSE
				BEGIN
					insert into @Results (DataBaseName, DataBaseSizeMB, ordercmd, cmd)
						select @DBName, @DatabaseSizeMB, 3, @cmd
				END
			END
		END

		--Controles para avanzar en el bucle de control a todas las bbdd
		update #databases set ErrorMessage=' ' where DatabaseName=@DBName

	end --Fin del while para el bucle de todas las bbdd
END

📌 Resultado final

Como salida, el procedimiento devuelve los comandos T-SQL generados, listos para ser ejecutados en la restauración de las bases de datos.

	-- Retornamos los comandos
	select DataBaseName, DataBaseSizeMB, ordercmd, cmd
	from @Results

De esta forma, se obtiene un proceso rápido, automático y flexible que puede integrarse fácilmente en tareas de administración diaria o en scripts de automatización.

🧩 Ejemplo de uso

	-- recogemos en un Json las BBDDs no del sistema
	-- Otra opción seria pasar el listado que se quiere
	declare @databases nvarchar(max) 

	set @databases = (select 
		database_id
		, name 
	FROM sys.databases
	where name not in ('master','tempdb','model','msdb')
	FOR JSON PATH)
	
	EXEC master.dbo.xp_GenerateRestoreScripts
		@ModoRecuperacion  = 3
		,@backupPath ='C:\Backups\'
		,@Path_MDF_Destination = 'C:\Restores\'
		,@Path_NDF_Destination = 'C:\Restores\'
		,@Path_LDF_Destination = 'C:\Restores\'
		,@dbName_suffix = '_2'
		,@RecoveryON = 1
		,@VerboseMode = 1 
		,@GridResultSet = 1
		,@ScanSubdirectories = 1
		,@databases = @databases

📎 Recursos

Puedes consultar el código completo en mi repositorio de GitHub: [🔗 enlace aquí]

👉 En un post futuro integraré este script dentro de scripts de PowerShell para automatizar la restauración completa de una o N bases de datos.