--**************************************
-- Name: RESTORE ALL DATABASES OF FOLDER ( WITH LOGICAL NAMES!! )
-- Description:
 Its a script to restore all databases, with the logical names of the databases
-- By: Jose Vicente Rocamora Ballestero
--
-- Inputs:the folder in / out
--
-- Returns:a log, with the results
--
-- Side Effects:
 nothing, but be careful
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=1247&lngWId=5--for details.--**************************************

-------------------------------------------------------------------------------------------------------------------
-- Jose Vicente Rocamora Ballestero jvrocamora@gmail.com 
-- 2008
-- only for your eyes ;-)
-- do you like ????.
-------------------------------------------------------------------------------------------------------------------
DECLARE @pathDBOrigen as varchar(2000)
DECLARE @pathDBDestino as varchar(2000)
DECLARE @cmdShellExecute as varchar(4000)
-- THE SOURCE FOLDER WITH THE DATABASE .BAK
Set @pathDBOrigen = 'D:\bbdd\' 
-- THE DESTINATION FOLDER ( MDF AND LDF )
Set @pathDBDestino = 'D:\Archivos de programa\Microsoft SQL Server\MSSQL.1\MSSQL\Data\'
--Set @pathDBDestino = 'C:\program files\Microsoft SQL Server\MSSQL.1\MSSQL\data\'
--Set @pathDBDestino = 'C:\program files\Microsoft SQL Server\MSSQL.1\MSSQL\data\'
Set @cmdShellExecute = 'dir /b /o-d ' + @pathDBOrigen 
-- FILES TO RESTORE
CREATE TABLE #result (output varchar(255) null)
INSERT #result (output) EXEC xp_cmdshell @cmdShellExecute
-- LOGIC NAMES
CREATE TABLE #result2 
(
o1 varchar(255) null,o2 varchar(255) null,o3 varchar(255) null,o4 varchar(255) null,o5 varchar(255) null,o6 varchar(255) null,o7 varchar(255) null,o8 varchar(255) null,o9 varchar(255) null,o10 varchar(255) null,
o11 varchar(255) null,o12 varchar(255) null,o13 varchar(255) null,o14 varchar(255) null,o15 varchar(255) null,o16 varchar(255) null,o17 varchar(255) null,o18 varchar(255) null,o19 varchar(255) null,o20 varchar(255) null,
)
DECLARE @nombreDataBase AS VARCHAR(300)
DECLARE @nombreCompletoDataBase AS VARCHAR(300)
DECLARE @pathDataBase AS VARCHAR(4000)
DECLARE @CADENA AS NVARCHAR(4000)
DECLARE @CADAUX AS NVARCHAR(4000)
DECLARE @LOGICALFILENAME AS VARCHAR(400)
--------------------------------------------
-- INICIALIZACION DE VARIABLES
--------------------------------------------
set @nombreDataBase=''
set @pathDataBase=''
set @CADENA=''
set @CADAUX=''
--------------------------------------------
DECLARE restoreDB CURSOR FOR
select output from #result
OPEN restoreDB 
FETCH NEXT FROM restoreDB INTO @nombreCompletoDataBase
	WHILE @@FETCH_STATUS = 0
	BEGIN
			 
		 set @nombreDataBase = left(@nombreCompletoDataBase,len(@nombreCompletoDataBase)-4)
		 set @pathDataBase = @pathDBOrigen + @nombreDataBase 
		 set @cadaux= N' RESTORE FILELISTONLY FROM DISK = ''' + @pathDataBase + right(@nombreCompletoDataBase,4) + N''' ' 
		
		 set @CADENA = N' print '' [' + @nombreDataBase + N'] '' ' + CHAR(13)
				+ N';restore DATABASE [' + @nombreDataBase + N'] ' + CHAR(13)
				+ N'FROM DISK = '''+ @pathDataBase + N'.BAK'' ' + CHAR(13)
				+ N'WITH replace, ' + CHAR(13)
			set @cadaux= N' RESTORE FILELISTONLY FROM DISK = '''+ @pathDataBase + right(@nombreCompletoDataBase,4) + ''' ' 
			INSERT INTO #result2 EXEC sp_executesql @cadaux
			DECLARE LOGICALFILES CURSOR FOR 
				select o1 from #result2
					OPEN LOGICALFILES 	FETCH NEXT FROM LOGICALFILES INTO @logicalFileName
						WHILE @@FETCH_STATUS = 0
						BEGIN
							set @CADAUX=''
							Set @CADAUX=@CADAUX + N'MOVE ''' + @logicalFileName + N''' TO ''' + @pathDBDestino + @nombreDataBase + N'.mdf'', ' + CHAR(13)
							FETCH NEXT FROM LOGICALFILES INTO @logicalFileName
							Set @CADAUX=@CADAUX + N'MOVE ''' + @logicalFileName + N''' TO ''' + @pathDBDestino + @nombreDataBase + N'.ldf'' ' + CHAR(13)
							FETCH NEXT FROM LOGICALFILES INTO @logicalFileName
						END
			
			CLOSE LOGICALFILES 
			DEALLOCATE LOGICALFILES
				
			-- borramos los datos en la tabla
			exec('DELETE FROM #result2')
		
			set @CADENA=@CADENA + @CADAUX
		 
			PRINT @CADENA
/************************************************************************************************************/
		 EXEC sp_executesql @cadena
/************************************************************************************************************/
		
		FETCH NEXT FROM restoreDB INTO @nombreCompletoDataBase
	END
drop table #result
drop TABLE #result2 
CLOSE restoreDB 
DEALLOCATE restoreDB