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: - Create new server with SQL 2008 - Shutdown SQL Services on old server and copy databases to new one - Shutdown old server and rename new server to the same name as the old one. - 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

Accepted Answer

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

Attach multiple databases using T-SQL

Solution

Problem