SQL Server- Restore database scripts to handle multiple logical files..primary files, data files and index files

When our database backup has multiple logical files and physical file names like mdf,ndf  and ldf files, our normal restore backup query will not work..to restore files from those backups we need to write restore query to handle the move logic dynamically, this code below works magically.



DECLARE @drivelocation varchar(25)
DECLARE @directoryname varchar(2000)
DECLARE @databasename as varchar(128)

SET @drivelocation= "D:/Test"
SET @directoryname = @drivelocation  + '\New' +  '.bak';

SET @databasename = 'NewDatabase'

IF (EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = @databasename))
BEGIN
EXEC('DROP DATABASE ' + @databasename)
END
 -- Create a table to hold the logical names of the source database we are restoring from
  CREATE TABLE #t
 (id int identity,
 LogicalName VARCHAR(128)
,PhysicalName VARCHAR(260)
,Type CHAR(1)
 ,FileGroupName NVARCHAR(128)
 ,Size NUMERIC(20,0)
 ,MaxSize NUMERIC(20,0)
 ,FileID BIGINT
 ,CreateLSN NUMERIC(25,0)
 ,DropLSN NUMERIC(25,0) NULL
 ,UniqueID UNIQUEIDENTIFIER
 ,ReadOnlyLSN NUMERIC(25,0) NULL
 ,ReadWriteLSN NUMERIC(25,0) NULL
 ,BackupSizeInBytes BIGINT
 ,SourceBlockSize INT
 ,FileGroupID INT
 ,LogGroupGUID UNIQUEIDENTIFIER NULL
 ,DifferentialBaseLSN NUMERIC(25,0) NULL
 ,DifferentialBaseGUID UNIQUEIDENTIFIER
 ,IsReadOnly BIT
 ,IsPresent BIT
 ,TDEThumbprint CHAR(1)
 )

 DECLARE @stmt nVARCHAR(1024)
 SET @stmt = 'RESTORE FILELISTONLY FROM DISK=' + '''' + @directoryname + ''''
 INSERT INTO #t EXEC(@stmt)

print @stmt

DECLARE @id int;
DECLARE @Max int;
DECLARE @string1 varchar(max);
DECLARE @logicalname as varchar(128);
DECLARE @physicalname as varchar(128);


SET @id = 0
SET @string1=''
SELECT @Max = max(id) from #t

WHILE @id < @Max
   BEGIN
     SET @id = @id + 1   
     IF @id =1 and @id <@max
SET @logicalname='';
SET @physicalname='';

SELECT @logicalname=logicalname,@physicalname=physicalname FROM #t WHERE id=@id
IF CHARINDEX('mdf',@physicalname,1)> 0
SET @physicalname = @drivelocation+'EHI_ExportDir\Data_dev\' +@databasename + '.mdf'
ELSE IF CHARINDEX('ndf',@physicalname,1)> 0
SET @physicalname = @drivelocation+'EHI_ExportDir\Data_dev\' +@databasename +  '.ndf'
ELSE IF CHARINDEX('ldf',@physicalname,1)> 0
SET @physicalname = @drivelocation+'EHI_ExportDir\Log_dev\' +@databasename +  '.ldf'

IF @id =1
SET @string1 = @string1 + ' WITH MOVE ' + '''' + @logicalname + '''' + ' TO ' + '''' +  @physicalname + '''' + ','
ELSE
SET @string1 = @string1 + '  MOVE ' + '''' + @logicalname + '''' + ' TO ' + '''' +  @physicalname + '''' + ','

END

print @string1

 SET @stmt = 'USE MASTER;' + 'RESTORE DATABASE ' + @databasename  + 
  ' FROM DISK = N''' + @directoryname + ''''  + @string1 +
 ' REPLACE, FILE = 1,  NOUNLOAD'
 print (@stmt)
 drop table #t
 print (@stmt)