Los parámetros de inicio del siguiente script son:
- dbs: Cursor con los nombres de ficheros de backup a restaurar (si se llama mibbdd.bak, pues mibbdd)
- pathFisicoMDF: Ruta donde acabarán los ficheros de datos
- pathFIsicoLDF: Ruta donde acabarán los ficheros de log
- pathToBak: Ruta donde buscar los ficheros de backup .bak
Solo una cosa mas,
/* Enrique Catala Bañuls */ declare @path nvarchar(max) DECLARE @dbName sysname DECLARE @logicalName sysname declare @physicalName nvarchar(max) declare @fileName nvarchar(max) declare @sSql nvarchar(max) declare @fileType char(1) declare @pathToBak nvarchar(max) declare @pathFisicoMDF nvarchar(max) declare @pathFisicoLDF nvarchar(max) /*********************************************************************************************/ /*********************************************************************************************/ -- -- FILL HERE DATA NEEDED TO THE RESTORNG PROCESS -- DECLARE dbs CURSOR READ_ONLY -- bases de datos restaurándose FOR select 'bdeap' select @pathFisicoMDF = N'e:datos', @pathFisicoLDF = N'd:logs', @pathToBak = 'E:fulldiaria' /*********************************************************************************************/ /*********************************************************************************************/ create table #tmp ( LogicalName sysname ,PhysicalName nvarchar(max) ,Type char(1) ,FileGroupName sysname NULL ,Size numeric(20,0) ,MaxSize numeric(20,0), Fileid tinyint, CreateLSN numeric(25,0), DropLSN numeric(25, 0), UniqueID uniqueidentifier, ReadOnlyLSN numeric(25,0) NULL, ReadWriteLSN numeric(25,0) NULL, BackupSizeInBytes bigint, SourceBlocSize int, FileGroupId int, LogGroupGUID uniqueidentifier NULL, DifferentialBaseLSN numeric(25,0) NULL, DifferentialBaseGUID uniqueidentifier, IsReadOnly bit, IsPresent bit, TDEthumbprint varbinary(32) NULL ) OPEN dbs FETCH NEXT FROM dbs INTO @dbName WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2) BEGIN SELECT @sSql = '', @path = 'restore filelistonly from disk = N'''+@pathToBak+@dbName+'.bak''' --PRINT @path insert #tmp EXEC (@path) --select * from #tmp DECLARE dbs2 CURSOR READ_ONLY FOR select logicalName,physicalName,type from #tmp OPEN dbs2 FETCH NEXT FROM dbs2 INTO @logicalName,@physicalName,@fileType WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2) BEGIN set @fileName= substring(@physicalName, 2+len(@physicalName)-charindex('',reverse(@physicalName)), charindex('',reverse(@physicalName))) if @fileType = 'D' set @sSql = @sSql+' MOVE N'''+@logicalName+''' TO N'''+@pathFisicoMDF+@fileName+''',' else set @sSql = @sSql + ' MOVE N'''+@logicalName+''' TO N'''+@pathFisicoLDF+@fileName+''',' --print @logicalName --print @pathFisicoMDF --print @sSql END FETCH NEXT FROM dbs2 INTO @logicalName,@physicalName,@fileType END CLOSE dbs2 DEALLOCATE dbs2 --print @dbName --print @pathToBak --print @sSql --SET @sSql = 'RESTORE DATABASE ['+@dbName+'] FROM DISK = N'''+ @pathToBak +'' +@dbName + '.bak'' SET @sSql = 'RESTORE DATABASE ['+@dbName+'] FROM DISK = N'''+ @pathToBak +'' +@dbName + '.bak'' WITH FILE = 1, ' +@sSql+ ' RECOVERY, NOUNLOAD, REPLACE, STATS = 10' --print @sSql exec(@sSql) truncate table #tmp END FETCH NEXT FROM dbs INTO @dbName END CLOSE dbs DEALLOCATE dbs GO drop table #tmp