What Are the Log File Names? I moved my database files. When I go attach the MDF file by itself, SQL Server will tell me that it can't find the log files. How do I go about asking the MDF what files it is expecting before attaching it?

More background information: I have a bunch of SAN backup volumes. I attached them to Windows through iSCSI, and now half the drive letters are messed up. Moreover, I multiple volumes should be mapped to the save drive letter, so I can't restore the correct drive letters. 

I know that the files are all there, but I don't know how many and which LDF/NDFs I should be attaching with each MDF.
If you have the information for all of the backups in the backupset table, you can build all of the RESTORE FILELISTONLY commands this way, then insert the results into a #temp table, then use a cursor to build the eventual CREATE DATABASE ... FOR ATTACH commands. I still think it would be faster to suck it up and just restore the backups already, but to each his own I suppose...

First, create a #temp table to hold the RESTORE FILELISTONLY output:

SET NOCOUNT ON;

CREATE TABLE #x
(
  ln SYSNAME, [path] NVARCHAR(512), Type CHAR(1), fgn NVARCHAR(128), 
  size BIGINT, msize BIGINT, FileId INT, lsn1 BIGINT, lsn2 BIGINT, 
  id UNIQUEIDENTIFIER, lsn3 BIGINT, lsn4 BIGINT, bsize BIGINT, bs INT, 
  fg INT, lgid UNIQUEIDENTIFIER, lsn5 BIGINT, bid UNIQUEIDENTIFIER, 
  ro BIT, p BIT, TDEThumbprint VARBINARY(32)
); 


Now, based on the backups stored in msdb..backupset, generate the commands themselves and run them:

DECLARE @sql NVARCHAR(MAX) = N'';

;WITH x AS 
(
  SELECT *,
    rn = ROW_NUMBER() OVER (PARTITION BY database_name 
     ORDER BY backup_start_date DESC)
  FROM msdb..backupset
)
SELECT @sql += '
  INSERT #x(ln) SELECT ''' + x.database_name + ''';
  INSERT #x EXEC sp_executesql N''RESTORE FILELISTONLY '
  + ' FROM DISK = ''''' + mf.physical_device_name + ''''''
  + ' WITH FILE = ' + RTRIM(x.position) + ';'';'
FROM x
INNER JOIN msdb..backupmediaset AS ms 
  ON x.media_set_id = ms.media_set_id
INNER JOIN msdb..backupmediafamily AS mf 
  ON ms.media_set_id = mf.media_set_id
WHERE x.rn = 1;

EXEC sp_executesql @sql;


Now, some of those might fail, if the .bak files have been moved. Also note that the position value is important, in case you have backed up multiple times to the same file. You want to be sure you have the most recent version that reflects the current file structure of the database (but there is still no guarantee that the last backup reflects reality).

Now, add a column to serve as a row number. Warning: this relies on "natural" sort order of a heap. If this table is super large this may not work.

ALTER TABLE #x ADD rn INT;
GO

;WITH x AS (SELECT *, r = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM #x)
UPDATE x SET rn = r
OPTION (MAXDOP 1);
GO


Once you have the paths in there, you will see that they reflect the old locations and drive letters of the database files. You may want to run a query that replaces the known old locations with the new ones. For example, if you know that databases previously located at C:\MSSQL\Data\ are not located at D:\MyInstance\SQLData\, you can run:

UPDATE #x SET [path] = REPLACE([path], 'C:\MSSQLData\', 'D:\MyInstance\SQLData\')
  WHERE [path] LIKE 'C:\MSSQLData\%';


Wash, rinse, repeat until all of the [path] values accurately reflect the new location of the files. 

Now, we're ready to roll up our sleeves. This could very well serve as a bad practices, do as I say but not as I do post. We need a cursor to build the CREATE DATABASE ... FOR ATTACH commands. The following cursor relies on the rn column we added and builds commands that accurately handle multiple data files, multiple log files, or both.

DECLARE @ln SYSNAME, @rn INT, @ubound INT, @cmd NVARCHAR(MAX);

DECLARE c CURSOR LOCAL FAST_FORWARD
 FOR SELECT ln, rn FROM #x WHERE [path] IS NULL;

OPEN c;

FETCH NEXT FROM c INTO @ln, @rn;

WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT @ubound = MIN(rn) FROM #x WHERE [path] IS NULL AND rn &gt; @rn;

  SET @ubound = COALESCE(@ubound, 2000000000);

  SELECT @cmd = N'CREATE DATABASE ' + QUOTENAME(@ln) + '
    ON '
    + STUFF((SELECT ',' + '(NAME = ''' + ln + ''',FILENAME=''' 
      + [path] + ''')' FROM #x WHERE rn &gt; @rn AND rn &lt; @ubound 
      AND [Type] = 'D' ORDER BY FileId 
      FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'),1,1,'')
    + ' 
    LOG ON 
    ' + STUFF((SELECT ',' + '(NAME = ''' + ln + ''',FILENAME=''' 
      + [path] + ''')' FROM #x WHERE rn &gt; @rn AND rn &lt; @ubound 
      AND [Type] = 'L' ORDER BY FileId 
      FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'),1,1,'')
    + '
      FOR ATTACH;';

  PRINT @cmd;

  FETCH NEXT FROM c INTO @ln, @rn;
END

CLOSE c;
DEALLOCATE c;


This will print a bunch of individual CREATE DATABASE ... FOR ATTACH commands. However they will only be as reliable as (a) the data in msdb..backupset, (b) the presence of the actual backup files, (c) how accurate the backup files are in terms of current database file structures, and (d) your re-mapping of old paths -> new paths.