Move MS SQL Databases between servers

Standard

Found a nice little script here to detach databases.

-- Script to Detach All User Databases
-- Orignal by Vidhya Sagar
-- Additions by Mike Trebilcock

SET NOCOUNT ON

DECLARE
@dbName varchar(80),
@ServerName varchar(20),
@attachScript varchar(max),
@logfilename varchar(200),
@dbfilename varchar(200)

SELECT @ServerName = @@servername
SET @attachScript=''
DECLARE dbCursor CURSOR FOR
SELECT db.name, SUBSTRING(row.physical_name, CHARINDEX('\data\',LOWER(row.physical_name)) +6, len(row.physical_name)) as DataFile,
SUBSTRING(logs.physical_name, CHARINDEX('\data\',LOWER(logs.physical_name)) +6, len(logs.physical_name)) as LogFile
FROM master.dbo.sysdatabases db inner join sys.master_files row on db.dbid=row.database_id and row.type_desc='ROWS'
inner join sys.master_files logs on db.dbid=logs.database_id and logs.type_desc='LOG'
WHERE db.name NOT IN ( 'model', 'master', 'msdb', 'tempdb', 'distribution', 'repldata' )

OPEN dbCursor

FETCH NEXT FROM dbCursor INTO @dbName, @dbfilename, @logfilename

IF ( @@FETCH_STATUS <> 0 )
PRINT 'No User databases found!!!'

WHILE ( @@FETCH_STATUS = 0 )
BEGIN
DECLARE @SQLStr varchar(8000)
SET @SQLStr =
'DECLARE
@SPIDStr varchar(8000),
@ConnKilled smallint
SELECT
@ConnKilled = 0,
@SPIDStr = ''''
SELECT @SPIDStr = coalesce( @SPIDStr, '', '' ) + ''KILL '' + convert( varchar, spid ) + ''; ''
FROM master.dbo.sysprocesses
WHERE dbid = db_id( ''' + @dbName + ''' )
IF LEN( @SPIDStr ) > 0
BEGIN
EXECUTE( @SPIDStr )
SELECT @ConnKilled = COUNT(1)
FROM master..sysprocesses
WHERE dbid = db_id( ''' + @dbName + ''' )
END' + char(10) + ';' + char(10) +
'EXECUTE sp_detach_db ' + @dbName
EXECUTE ( @SQLStr )
--PRINT @attachSCript
SET @attachScript = @attachScript + char(10)+
'CREATE DATABASE ['+@dbName+'] ON ' + char(10)+
'( FILENAME = N''S:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\'+@dbfilename+''' ), ' + char(10)+
'( FILENAME = N''L:\Logs\'+@logfilename+''' ) ' + char(10)+
' FOR ATTACH ' + char(10) +
' GO ' +char(10)

PRINT @SQLStr
PRINT 'Detach of ' + upper( @dbName ) + ' Database Successfully Completed'
PRINT ''
FETCH NEXT FROM dbCursor INTO @dbName, @dbfilename, @logfilename
END

print @attachScript
CLOSE dbCursor
DEALLOCATE dbCursor

PRINT ' '
PRINT upper( @ServerName ) + ' --> All User Databases Successfully Detached'