Attach multiple databases using T-SQL

Question

We are migrating one of our servers from SQL Server 2005 to SQL Server 2008. This server has about 50 small databases on it.

The migration path we are taking goes as follows:

1. Create new server with SQL 2008
2. Shutdown SQL Services on old server and copy databases to new one
3. Shutdown old server and rename new server to the same name as the old one.
4. Attach the 50 databases

Is there a fast way using t-sql to attach the 50 databases to the new server?

All the data files are going to be located at E:\DATA and transaction logs are going to be located at E:\TLOG

Answer 1

To re-iterate my comment, I suggest a backup/restore approach as opposed to a detach/attach approach (my reasons are outlined here and here).

And while I like @marc_s's SQLCMD approach, I prefer to pull this stuff from the metadata directly. This way I can inspect all of the output, copy and paste the parts I want to execute in batches instead of all at once, etc. Something like:

SET NOCOUNT ON;
DECLARE @folder nvarchar(512) = N'\\fileshare\folder\'; -- 'backup location

SELECT N'BACKUP DATABASE ' + QUOTENAME(name) 
  + N' TO DISK = N''' + @folder + name + N'.BAK'' WITH INIT;
  ALTER DATABASE ' + QUOTENAME(name) + N' SET OFFLINE;'
FROM sys.databases 
WHERE database_id > 4 -- AND other filter criteria

SELECT N'RESTORE DATABASE ' + QUOTENAME(d.name) 
  + N' FROM DISK = N''' + @folder + d.name + N'.BAK'' WITH ' 
  + STUFF(
    (SELECT N', 
      MOVE N''' + f.name + N''' TO ''E:\DATA\' + f.name + '.mdf''' 
      FROM master.sys.master_files AS f 
      WHERE f.database_id = d.database_id 
      AND type_desc = N'ROWS'
      FOR XML PATH(''), 
      TYPE).value(N'./text()[1]', N'nvarchar(max)'), 1, 1, N'') 
  + N', ' + STUFF(
    (SELECT N',
      MOVE N''' + f.name + N''' TO N''E:\TLOG\' + f.name + N'.mdf'''
      FROM master.sys.master_files AS f 
      WHERE f.database_id = d.database_id 
      AND type_desc = 'LOG'
      FOR XML PATH(''), 
      TYPE).value(N'./text()[1]', N'nvarchar(max)'), 1, 1, N'') + N';
  ALTER DATABASE ' + QUOTENAME(d.name) 
  + N' SET COMPATIBILITY_LEVEL = 100;'
FROM sys.databases AS d 
WHERE database_id > 4; -- AND other filter criteria

(This assumes that you only have data/log files, no filestream etc. and that you can backup to / restore from a common location accessible to both instances.)

To clarify, you would generate both sets of commands on the 2005 server, copy & run the backup commands there (and perhaps set them to offline immediately afterward), then copy & run the restore commands on the 2008 server.

You'll also want to update statistics on your key tables, otherwise you may be in for some serious head-scratching when your performance goes down the tubes as the plan cache on the new server gets primed...