title: Need help to restore multiple databases/bak files from one folder
body: <p>In SQL server 2008R2, I am trying to restore multiple databases/BAK files located in one folder only, using a SQL query given here - 
  <a href="http://www.karaszi.com/sqlserver/code/sp_RestoreFromAllFilesInDirectory_2008sp1.txt" rel="nofollow noreferrer">http://www.karaszi.com/sqlserver/code/sp_RestoreFromAllFilesInDirectory_2008sp1.txt</a></p>
  
  <p>It uses a stored procedure like this, the code for which is given at the end - </p>
  
  <pre><code>exec sp_RestoreFromAllFilesInDirectory 'C:\Mybakfiles\', 
  'D:\Mydatabasesdirectory\' , 
  'C:\MylogDirectory\'
  </code></pre>
  
  <p>Folders invloved - 'C:\Mybakfiles\', 'D:\Mydatabasesdirectory\' , 'C:\MylogDirectory\' </p>
  
  <p>You should know the path to the first folder. The second and third can be found 
  with a query or by SSMS. For the query, refer to alex aza's answer at - <a href="https://stackoverflow.com/questions/3574616/what-is-the-most-efficient-way-to-restore-multiple-databases-in-sql-2008">What is the most efficient way to restore multiple databases in SQL 2008</a> . I have also given his query at the end.</p>
  
  <p>I copied all my BAK files to - C:\Program Files\Microsoft SQL Server\MSSQL10_50.SS2008\MSSQL\Backup\</p>
  
  <p>Then, I entered executed the SP as follows - </p>
  
  <pre><code>exec sp_RestoreFromAllFilesInDirectory 'C:\Program Files\
  Microsoft SQL Server\MSSQL10_50.SS2008\MSSQL\Backup\', 
  'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SS2008\MSSQL
  \DATA\' , 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SS2008\
  MSSQL\DATA\'
  </code></pre>
  
  <p>and got the following sample output -</p>
  
  <pre><code>RESTORE DATABASE AdventureWorksDW FROM DISK = 'C:\Program Files\
  Microsoft SQL Server\MSSQL10_50.SS2008\MSSQL\Backup\AdventureWorksDW.bak' 
  WITH MOVE 'AdventureWorksDW_Data' TO 'C:\Program Files\Microsoft SQL 
  Server\MSSQL10_50.SS2008\MSSQL\DATA\AdventureWorksDW_Data.mdf', MOVE 
  'AdventureWorksDW_Log' TO 'C:\Program Files\Microsoft SQL Server\
  MSSQL10_50.SS2008\MSSQL\DATA\AdventureWorksDW_Log.ldf'
  </code></pre>
  
  <p><strong>Problem -</strong> I refreshed my server in management studio and saw no new databases. Why ? Have I restored my Databases correctly ? If not, how do I do it using the code I have provided below ? If you think that code is not good, is there any other reliable way 
  to do it ?</p>
  
  <p>PS - Its a shame that MS does not let you do this easily.</p>
  
  <hr>
  
  <p><strong>EXTRA INFORMATION -</strong> </p>
  
  <p><strong>Here is the sp to restore multiple databases -</strong> </p>
  
  <pre><code>CREATE PROC [dbo].[sp_RestoreFromAllFilesInDirectory] 
  @SourceDirBackupFiles nvarchar(200), @DestDirDbFiles 
  nvarchar(200),@DestDirLogFiles nvarchar(200) 
  AS 
  --Originally written by Tibor Karaszi 2004. Use at own risk. 
  --Restores from all files in a certain directory. Assumes that: 
  --  There's only one backup on each backup device. 
  --  Each database uses only two database files and the mdf file 
  is returned first from the RESTORE FILELISTONLY command. 
  --Sample execution: 
  -- EXEC sp_RestoreFromAllFilesInDirectory 'C:\Mybakfiles\', 
  'D:\Mydatabasesdirectory\' ,’C:\MylogDirectory\’ 
  SET NOCOUNT ON 
  
  --Table to hold each backup file name in 
  CREATE TABLE #files(fname varchar(200),depth int, file_ int) 
  INSERT #files 
  EXECUTE master.dbo.xp_dirtree @SourceDirBackupFiles, 1, 1 
  
  --Table to hold the result from RESTORE HEADERONLY. Needed to get 
  the database name out from 
  CREATE TABLE #bdev( 
   BackupName nvarchar(128) 
  ,BackupDescription nvarchar(255) 
  ,BackupType smallint 
  ,ExpirationDate datetime 
  ,Compressed tinyint 
  ,Position smallint 
  ,DeviceType tinyint 
  ,UserName nvarchar(128) 
  ,ServerName nvarchar(128) 
  ,DatabaseName nvarchar(128) 
  ,DatabaseVersion int 
  ,DatabaseCreationDate datetime 
  ,BackupSize numeric(20,0) 
  ,FirstLSN numeric(25,0) 
  ,LastLSN numeric(25,0) 
  ,CheckpointLSN numeric(25,0) 
  ,DatabaseBackupLSN numeric(25,0) 
  ,BackupStartDate datetime 
  ,BackupFinishDate datetime 
  ,SortOrder smallint 
  ,CodePage smallint 
  ,UnicodeLocaleId int 
  ,UnicodeComparisonStyle int 
  ,CompatibilityLevel tinyint 
  ,SoftwareVendorId int 
  ,SoftwareVersionMajor int 
  ,SoftwareVersionMinor int 
  ,SoftwareVersionBuild int 
  ,MachineName nvarchar(128) 
  ,Flags int 
  ,BindingID uniqueidentifier 
  ,RecoveryForkID uniqueidentifier 
  ,Collation nvarchar(128) 
  ,FamilyGUID uniqueidentifier 
  ,HasBulkLoggedData int 
  ,IsSnapshot int 
  ,IsReadOnly int 
  ,IsSingleUser int 
  ,HasBackupChecksums int 
  ,IsDamaged int 
  ,BegibsLogChain int 
  ,HasIncompleteMetaData int 
  ,IsForceOffline int 
  ,IsCopyOnly int 
  ,FirstRecoveryForkID uniqueidentifier 
  ,ForkPointLSN numeric(25,0) 
  ,RecoveryModel nvarchar(128) 
  ,DifferentialBaseLSN numeric(25,0) 
  ,DifferentialBaseGUID uniqueidentifier 
  ,BackupTypeDescription nvarchar(128) 
  ,BackupSetGUID uniqueidentifier 
  ,CompressedBackupSize nvarchar(128)
  ) 
  
  --Table to hold result from RESTORE FILELISTONLY. Need to 
  generate the MOVE options to the RESTORE command 
  CREATE TABLE #dbfiles( 
   LogicalName nvarchar(128) 
  ,PhysicalName nvarchar(260) 
  ,Type char(1) 
  ,FileGroupName nvarchar(128) 
  ,Size numeric(20,0) 
  ,MaxSize numeric(20,0) 
  ,FileId int 
  ,CreateLSN numeric(25,0) 
  ,DropLSN numeric(25,0) 
  ,UniqueId uniqueidentifier 
  ,ReadOnlyLSN numeric(25,0) 
  ,ReadWriteLSN numeric(25,0) 
  ,BackupSizeInBytes int 
  ,SourceBlockSize int 
  ,FilegroupId int 
  ,LogGroupGUID uniqueidentifier 
  ,DifferentialBaseLSN numeric(25) 
  ,DifferentialBaseGUID uniqueidentifier 
  ,IsReadOnly int 
  ,IsPresent int 
  ,TDEThumbprint nvarchar(128)
  ) 
  
  
  DECLARE @fname varchar(200) 
  DECLARE @dirfile varchar(300) 
  DECLARE @LogicalName nvarchar(128) 
  DECLARE @PhysicalName nvarchar(260) 
  DECLARE @type char(1) 
  DECLARE @DbName sysname 
  DECLARE @sql nvarchar(1000) 
  
  DECLARE files CURSOR FOR 
  SELECT fname FROM #files 
  
  DECLARE dbfiles CURSOR FOR 
  SELECT LogicalName, PhysicalName, Type FROM #dbfiles 
  
  OPEN files 
  FETCH NEXT FROM files INTO @fname 
  WHILE @@FETCH_STATUS = 0 
  BEGIN 
  SET @dirfile = @SourceDirBackupFiles + @fname 
  
  --Get database name from RESTORE HEADERONLY, assumes there's 
  only one backup on each backup file. 
  TRUNCATE TABLE #bdev 
  INSERT #bdev 
  EXEC('RESTORE HEADERONLY FROM DISK = ''' + @dirfile + '''') 
  SET @DbName = (SELECT DatabaseName FROM #bdev) 
  
  --Construct the beginning for the RESTORE DATABASE command 
  SET @sql = 'RESTORE DATABASE ' + @DbName + ' FROM DISK = ''' + 
  @dirfile + ''' WITH MOVE ' 
  
  --Get information about database files from backup device into temp table 
  TRUNCATE TABLE #dbfiles 
  INSERT #dbfiles 
  EXEC('RESTORE FILELISTONLY FROM DISK = ''' + @dirfile + '''') 
  
  OPEN dbfiles 
  FETCH NEXT FROM dbfiles INTO @LogicalName, @PhysicalName, @type 
  --For each database file that the database uses 
  WHILE @@FETCH_STATUS = 0 
  BEGIN 
  IF @type = 'D' 
  SET @sql = @sql + '''' + @LogicalName + ''' TO ''' + 
  @DestDirDbFiles + @LogicalName  + '.mdf'', MOVE ' 
  ELSE IF @type = 'L' 
  SET @sql = @sql + '''' + @LogicalName + ''' TO ''' + 
  @DestDirLogFiles + @LogicalName  + '.ldf''' 
  FETCH NEXT FROM dbfiles INTO @LogicalName, @PhysicalName, @type 
  END 
  
  --Here's the actual RESTORE command 
  PRINT @sql 
  --Remove the comment below if you want the procedure to 
  actually execute the restore command. 
  --EXEC(@sql) 
  CLOSE dbfiles 
  FETCH NEXT FROM files INTO @fname 
  END 
  CLOSE files 
  DEALLOCATE dbfiles 
  DEALLOCATE files 
  </code></pre>
  
  <p><strong>Query to get the folder locations for DATA and LOG files -</strong> </p>
  
  <pre><code>declare @DefaultData nvarchar(512)
  exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', 
  N'Software\Microsoft\MSSQLServer\MSSQLServer', 
  N'DefaultData', @DefaultData output
  
  declare @DefaultLog nvarchar(512)
  exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', 
  N'Software\Microsoft\MSSQLServer\MSSQLServer', 
  N'DefaultLog', @DefaultLog output
  
  declare @MasterData nvarchar(512)
  exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\
  Microsoft\MSSQLServer\MSSQLServer\Parameters', N'SqlArg0', @MasterData output
  select @MasterData=substring(@MasterData, 3, 255)
  select @MasterData=substring(@MasterData, 1, len(@MasterData) - 
  charindex('\', reverse(@MasterData)))
  
  declare @MasterLog nvarchar(512)
  exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\
  Microsoft\MSSQLServer\MSSQLServer\Parameters', N'SqlArg2', @MasterLog output
  select @MasterLog=substring(@MasterLog, 3, 255)
  select @MasterLog=substring(@MasterLog, 1, len(@MasterLog) - 
  charindex('\', reverse(@MasterLog)))
  
  select 
      isnull(@DefaultData, @MasterData) DefaultData, 
      isnull(@DefaultLog, @MasterLog) DefaultLog
  </code></pre>
  
  <p>Use SSMS to find DATA and LOG folders - </p>
  
  <p>SSMS > Your Server > Right click > Properties > Database Settings node.</p>
  
  <p><img src="https://i.stack.imgur.com/Q0pEb.jpg" alt="enter image description here"></p>