Massive SQL Server Database Moving Using Detach – Attach: The Complete Procedure

This is a complete configurable metascript, prepared to create detach statements, file move statement and re-attach statements for every database in your instance.
You have only to configure the final destination for datafiles and the database list you want to migrate.

The final generated script for every database selected will be something like this (remembar to use ALT-T to use text output mode in Management Studio)

============================================================
  DATABASE: myTestDB
============================================================
 
--  1) DETACH DATABASE
 
USE [master]
GO
ALTER DATABASE [myTestDB] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
EXEC master.dbo.sp_detach_db @dbname = N'myTestDB', @keepfulltextindexfile=N'false'
GO
 
--  2) DATAFILE OS MOVING
 
exec xp_cmdshell 'move "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmyTestDB_log.LDF" L:LogPath'
exec xp_cmdshell 'move "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmyTestDB.mdf" D:DataPath'
exec xp_cmdshell 'move "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmyTestDB_2.ndf" D:DataPath'
exec xp_cmdshell 'move "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmyTestDB_3.ndf" D:DataPath'
exec xp_cmdshell 'move "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmyTestDB_4.ndf" D:DataPath'
exec xp_cmdshell 'move "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmyTestDB_5.ndf" D:DataPath'
 
--  3) ATTACH DATABASE
 
CREATE DATABASE [myTestDB] ON 
( FILENAME = N'L:LogPathmyTestDB_log.LDF' ),
( FILENAME = N'D:DataPathmyTestDB.mdf' ),
( FILENAME = N'D:DataPathmyTestDB_2.ndf' ),
( FILENAME = N'D:DataPathmyTestDB_3.ndf' ),
( FILENAME = N'D:DataPathmyTestDB_4.ndf' ),
( FILENAME = N'D:DataPathmyTestDB_5.ndf' ) 
FOR ATTACH
GO

This is the complete metascript:

set nocount on
----------------------------------------------------
--CONFIGURE HERE
----------------------------------------------------
--     DESTINATION PATH
----------------------------------------------------
--data files destination 
    declare @pathdata nvarchar(max) 
    set @pathdata = 'D:DataPath'
--log files destination   
    declare @pathlog nvarchar(max) 
    set @pathlog = 'L:LogPath'
----------------------------------------------------
--     DATABASE LIST
----------------------------------------------------
    create table #dbs (dbname nvarchar(255))
    --insert into #dbs values('db1')
    --insert into #dbs values('db2')
    --insert into #dbs values('db3')
--Note: if you want to move ALL database simply insert all db names in #dbs table using:
Insert into #dbs select name from master..sysdatabases where name not in ('master','model','msdb','tempdb')
---------------------------------------------------

declare @aDB nvarchar(255)
declare @aFile nvarchar(255)
declare @aType nvarchar(255)
declare @file_n integer
declare @count integer
declare @sep nvarchar(1)

declare db_cur CURSOR FOR select * from #dbs
OPEN db_cur
FETCH NEXT FROM db_cur INTO @aDB
WHILE @@FETCH_STATUS = 0
BEGIN
	print '============================================================'
    print '  DATABASE: ' + @aDB
    print '============================================================'
    print ''
	
	print '--  1) DETACH DATABASE'
	print ''
	print 'USE [master]'
	print 'GO'
	print 'ALTER DATABASE [' + @aDB + '] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE'
	print 'GO'
	print 'EXEC master.dbo.sp_detach_db @dbname = N''' + @aDB +''', @keepfulltextindexfile=N''false'''
	print 'GO'
	print ''
	
	print '--  2) DATAFILE OS MOVING'	
	print ''
		
	declare file_cur CURSOR FOR select type_desc,physical_name from sys.master_files where db_name(database_id) = @aDB order by type_desc,physical_name
	OPEN file_cur
		
    FETCH NEXT FROM file_cur INTO @aType,@aFile
    WHILE @@FETCH_STATUS = 0
    BEGIN
		if (@aType='ROWS')
		BEGIN			
			print 'exec xp_cmdshell ''move "' + @aFile + '" ' + @pathdata + ''''
		END
		ELSE
		BEGIN			
			print 'exec xp_cmdshell ''move "' + @aFile + '" ' + @pathlog + ''''
		END
		set @count=@count+1
	FETCH NEXT FROM file_cur INTO @aType,@aFile
	END
	CLOSE file_cur
	DEALLOCATE file_cur	
	print ''
	print '--  3) ATTACH DATABASE'	
	print ''
	--Create ATTACH Statement
	set @count=1
	set @sep=','	
	PRINT 'CREATE DATABASE [' + @aDB + '] ON '
	declare file_cur CURSOR FOR select type_desc,physical_name from sys.master_files where db_name(database_id) = @aDB order by type_desc,physical_name
	select @file_n=count(*) from sys.master_files where db_name(database_id) = @aDB
	OPEN file_cur
    FETCH NEXT FROM file_cur INTO @aType,@aFile
    WHILE @@FETCH_STATUS = 0
    BEGIN
		if (@count=@file_n)
		BEGIN
			set @sep=' '
		END
		
		if (@aType='ROWS')
		BEGIN
			print '( FILENAME = N''' + @pathdata + RIGHT(@aFile, CHARINDEX('', REVERSE(@aFile))-1) +''' )' + @sep
		END
		ELSE
		BEGIN
			print '( FILENAME = N''' + @pathlog + RIGHT(@aFile, CHARINDEX('', REVERSE(@aFile))-1) +''' )' + @sep
		END
		set @count=@count+1
	FETCH NEXT FROM file_cur INTO @aType,@aFile
	END
	CLOSE file_cur
	DEALLOCATE file_cur
	
	print 'FOR ATTACH'
	print 'GO'
	print ''
FETCH NEXT FROM db_cur INTO @aDB
END
CLOSE db_cur
DEALLOCATE db_cur
drop table #dbs