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 > @rn;
SET @ubound = COALESCE(@ubound, 2000000000);
SELECT @cmd = N'CREATE DATABASE ' + QUOTENAME(@ln) + '
ON '
+ STUFF((SELECT ',' + '(NAME = ''' + ln + ''',FILENAME='''
+ [path] + ''')' FROM #x WHERE rn > @rn AND rn < @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 > @rn AND rn < @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.
