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