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

Using the SQLCMD mode, you can easily script this:

:setvar dbname YourDatabaseName
:setvar dbfile N'E:\DATA\YourDatabase.mdf'
:setvar logfile N'E:\TLOG\YourDatabase_log.ldf'
USE [master]
GO

CREATE DATABASE $(dbname) ON 
( FILENAME = $(dbfile) ),
( FILENAME = $(logfile) )
FOR ATTACH
GO

This works either from sqlcmd.exe from the command line (you can even supply the values for the variables dbname, dbfile, logfile from the command line, too), or it works in SQL Server Management Studio if you enabled Tools > Options > Query Execution > by default, open new queries in SQLCMD mode .

Read more about the SQLCMD utility and all its parameters on MSDN.

PS: of course, this approach with a SQLCMD enabled script also works for BACKUP/RESTORE cycles :-) (as recommended by Aaron)

PPS: if you have a good naming convention, and the data file is always $(dbname).mdf and the log file is always $(dbname)_log.ldf, you could also use this shortened SQLCMD script:

:setvar dbname YourDatabaseName
USE [master]
GO

CREATE DATABASE $(dbname) ON 
( FILENAME = N'E:\DATA\$(dbfile).mdf' ),
( FILENAME = N'E:\TLOG\$(logfile)_log.ldf' )
FOR ATTACH
GO

and then just call this from the command line:

C:\>  sqlcmd.exe -S yourserver -E -i attach.sql -v dbname=YourDb1

and so forth, once for each database you need to re-attach.

PPPS: if you want to restore backups, it's just slightly more complicated :

:setvar dbname YourDatabaseName
USE [master]
GO

RESTORE DATABASE $(dbname)
FROM DISK = N'E:\Backup\$(dbname).bak' 
WITH FILE = 1,  
MOVE N'$(dbname)' TO N'E:\DATA\$(dbname).mdf',  
MOVE N'$(dbname)_Log' TO N'E:\TLOG\$(dbname)_Log.ldf',  
NOUNLOAD, REPLACE
GO

This works, as long as you name your .bak files the same as your database name, and you put them in a fixed location (I presumed E:\Backup here - adapt as needed).

Answer 2

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...