Here is a script I wrote to create a script on the "old" SQL server (with all the proper logical file names, etc) so I could then export the databases, and just run a few scripts on the new server, completing a migration.
Be sure to read my other blog entry regarding Migrating your SQL users from a SQL2000 to SQL2005 server.. this prevents the orphans!
create table #RES
(db varchar(1000),
fileid nvarchar(20),
logfilenam nvarchar(1000),
filenam nvarchar(1000),
script nvarchar(2000))
go
Create table #s
(
db nvarchar(1000),
ROWSS nvarchar(1000),
LOGSS nvarchar(1000),
ResultScript nvarchar(1000)
)
GO
EXEC sp_msForEachDb "insert into #RES SELECT '?' as DB, fileid, name, filename,'' as A
FROM sysaltfiles WHERE dbid = DB_ID(N'?')"
GO
EXEC sp_msForEachDb "insert into #s SELECT '?' as DB,''as a,'' as b,'' as c
FROM sysaltfiles WHERE dbid=DB_ID(N'?') and fileid=N'2'"
GO
DECLARE @SOURCEFOLDER VARCHAR(2000)
-- Source where the .bak files will be when you run the restore on the new server
-- All DB files should be in this folder, named like: databasename.bak
SET @SOURCEFOLDER = 'D:\Migration'
update #RES set script = 'RESTORE DATABASE ['+[db]+'] FROM DISK = N"'+@SOURCEFOLDER+'\'+[db]+'.bak" WITH FILE =1, MOVE N"'+Replace([logfilenam],' ','')+'" TO N"'+replace([filenam],' ','')+'"'
where fileid='1'
GO
update #RES set script = ', MOVE N"'+Replace([logfilenam],' ','')+'" TO N"'+Replace([filenam],' ','')+'", NOUNLOAD, REPLACE, STATS = 20'
where fileid='2'
GO
update #s set ROWSS=#RES.script
from #s join #RES on #s.db=#RES.db
where fileid='1'
GO
update #s set LOGSS=#RES.script
from #s join #RES on #s.db=#RES.db
where fileid='2'
GO
DECLARE @DEST VARCHAR(2000),@SOURCE VARCHAR(2000)
-- To replace paths in case they changed between one server and the next
SET @SOURCE = 'D:\MSSQL\DATA'
SET @DEST = 'D:\MSSQL.1\MSSQL\DATA'
update #s set ROWSS=replace(ROWSS,@SOURCE,@DEST)
update #s set LOGSS=replace(LOGSS,@SOURCE,@DEST)
GO
set quoted_identifier off
select replace(ROWSS+LOGSS,'"',CHaR(39)) as ResultScript from #s
where db not in ('Master','Model','msdb','tempdb')
go
drop table #RES
drop table #s
Now, save the results to a txt file, you would run this on the new server when you are ready and all the databases will be re-created. (again, read the other blog about transferring the users, and do that first)
(See other blog entry for a similar script that works on SQL2005)