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

Leave a Reply
You must be logged in to post a comment.