Sencillamente Restaurar Base de Datosº

Hola a tod@s..!

Como hoy he estado un poco inspirado en las publicaciones en los blog.. pues me he atrevido a poneros un script de auto-ayuda 

Básicamente la necesidad era restaurar unas cuantas BBDD's que estaban en una SAN y hacerlo lo mas rápido en un SQL Server.. pues ahi va.. y espero os sea de ayuda.. en esos momentos de googleo.. :)

--**************************************************************+
--// Procedimiento que restura "n" desde una SAN indicada hacia un SQL Server.
--**************************************************************+
--

CREATE PROC [dbo].[PR_RestoreControl] @dbMediaBCK sysname, @RutaData sysname, @RutaLog sysname 
AS
DECLARE @QueryRestore varchar(999)
declare @sql4 varchar(200),@sql3 varchar(200),@sql2 varchar(200),@sql1 varchar(200),@NroFR int, @dbname sysname
declare @logicalname nvarchar(128), @PhyName nvarchar(260),@Type char(1),@FGroup nvarchar(128), @Fid bigint
declare @infoRestore TABLE
(
xLogicalName nvarchar(128), 
xPhysicalName nvarchar(260),
xType char(1), 
xFileGroupName nvarchar(128),
xSize numeric(20,0), 
xMaxSize numeric(20,0),
xFileID bigint,
xCreateLSN numeric(25,0), 
xDropLSN numeric(25,0),
xUniqueID uniqueidentifier, 
xReadOnlyLSN numeric(25,0),
xReadWriteLSN numeric(25,0),
xBackupSizeInBytes bigint,
xSourceBlockSize int,
xFileGroupID int,
xLogGroupGUID uniqueidentifier,
xDifferentialBaseLSN numeric(25,0),
xDifferentialBaseGUID uniqueidentifier,
xIsReadOnly bit,
xIsPresent bit,
xTDEThumbprint varbinary(32)
)
declare @infoDBRestore TABLE
(
xBackupName nvarchar(128),
xBackupDescription nvarchar(255),
xBackupType smallint,
xExpirationDate datetime,
xCompressed smallint,
xPosition smallint,
xDeviceType tinyint,
xUserName nvarchar(128),
xServerName nvarchar(128),
xDatabaseName nvarchar(128),
xDatabaseVersion int,
xDatabaseCreationDate datetime,
xBackupSize numeric(20,0),
xFirstLSN numeric(25,0),
xLastLSN numeric(25,0),
xCheckpointLSN numeric(25,0),
xDatabaseBackupLSN numeric(25,0),
xBackupStartDate datetime,
xBackupFinishDate datetime,
xSortOrder smallint,
xCodePage smallint,
xUnicodeLocaleId int,
xUnicodeComparisonStyle int,
xCompatibilityLevel tinyint,
xSoftwareVendorId int,
xSoftwareVersionMajor int,
xSoftwareVersionMinor int,
xSoftwareVersionBuild int,
xMachineName nvarchar(128),
xFlags int,
xBindingID uniqueidentifier,
xRecoveryForkID uniqueidentifier,
xCollation nvarchar(128),
xFamilyGUID uniqueidentifier,
xHasBulkLoggedData bit,
xIsSnapshot bit,
xIsReadOnly bit,
xIsSingleUser bit,
xHasBackupChecksums bit,
xIsDamaged bit,
xBeginsLogChain bit,
xHasIncompleteMetaData bit,
xIsForceOffline bit,
xIsCopyOnly bit,
xFirstRecoveryForkID uniqueidentifier,
xForkPointLSN numeric(25,0),
xRecoveryModel nvarchar(60),
xDifferentialBaseLSN numeric(25,0),
xDifferentialBaseGUID uniqueidentifier,
xBackupTypeDescription nvarchar(60),
xBackupSetGUID uniqueidentifier,
xCompressedBackupSize bigint NULL
)
/*Carga informacion de Medio de Backup */
BEGIN TRY
IF @RutaData is null
BEGIN
declare @vdefdata sysname
create table #vregistrod
(valor sysname,data sysname )
insert into #vregistrod
EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'DefaultData'
Select @vDefData = data from #vregistrod
Set @RutaData = @vdefdata
END
IF @RutaLog is null
BEGIN
declare @vdefLog sysname
create table #vregistrol
(valor sysname,data sysname )
insert into #vregistrol
EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'DefaultLog'
Select @vdefLog = data from #vregistrol
Set @RutaLog = @vdefLog
END
END TRY
BEGIN CATCH
RAISERROR('No se puede acceder a los parametros por defecto ',16,1);
END CATCH
IF EXISTS (select name from master.sys.backup_devices where name = ''+@dbMediaBCK+'')
BEGIN
BEGIN TRY
INSERT INTO @infoRestore
EXEC ('RESTORE FILELISTONLY FROM  ['+@dbMediaBCK+']')
INSERT INTO @infoDBRestore
EXEC ('RESTORE HEADERONLY FROM  ['+@dbMediaBCK+']')
END TRY
BEGIN CATCH
RAISERROR ('No se puede acceder Medio de BACKUP',16,1);
END CATCH
SELECT @dbname = xDatabaseName from @infoDBRestore
SET @sql1 ='RESTORE DATABASE ['+@dbname+'] FROM  ['+@dbMediaBCK+'] WITH  FILE = 1, '
SET @sql2 = ''
SET @sql3 = ''
SET @sql4 ='RECOVERY,  NOUNLOAD,  STATS = 10'
-- Construccion de Query para informacion de Datos que resturaremos...
DECLARE Cur_read CURSOR FOR 
Select xLogicalName,xPhysicalName,xType,xFileGroupName,xFileID from @infoRestore
OPEN Cur_read 
FETCH NEXT FROM Cur_read INTO @logicalname, @PhyName,@Type,@FGroup,@Fid;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @Type = 'D' 
BEGIN
IF @Fid = 1
BEGIN
SET @PhyName = @RutaData+'\'+@dbname+convert(char(1),@Fid)+'.mdf'
END
ELSE BEGIN
SET @PhyName = @RutaData+'\'+@dbname+convert(char(1),@Fid)+'.ndf'
END
SET @sql2 = @sql2 + 'MOVE N'''+@logicalname+''' TO N'''+@PhyName+''', '
END
IF @Type = 'L'
BEGIN
IF @Fid <> 1
BEGIN
SET @PhyName = @RutaLog+'\'+@dbname+convert(char(1),@Fid)+'.ldf'
END
SET @sql3 = @sql3 + 'MOVE N'''+@logicalname+''' TO N'''+@PhyName+''', '
END
FETCH NEXT FROM Cur_read INTO @logicalname, @PhyName,@Type,@FGroup,@Fid;
END
CLOSE Cur_read
DEALLOCATE Cur_read
SET @QueryRestore = @sql1+''+
@sql2+''+
@sql3+''+
@sql4
EXEC (@QueryRestore)
END
ELSE BEGIN
RAISERROR ('No Existe el Medio de Restore. ',16, 1)
END