move db files to different location dynamically

,

Lately, I'm dealing with lots of DB migrations and came across situation that I have no proper dynamic script to move DB files to different location.

There are an option to proceed manually scripting one by one DB and execute it. Another option is detach attach DB - I'm not a big fan of this kind of operation, I assume DBAs understand why.

As we all do, I search on the net and didn't find anything that could answer my needs.

So I wrote one and here it is.

there is nothing complicated and you can merely see the output and then copy/paste for execution.

it also includes OFFLINE/ONLINE DB script which is necessary for this kind of operation.

If you find any bug, please let me know.

VLD

-- Get database file information for each database 
IF	 OBJECT_ID('TempDB..#holdforeachdb') IS NOT NULL
DROP TABLE #holdforeachdb;
 
create table #holdforeachdb
(	[databasename] [nvarchar](128) collate sql_latin1_general_cp1_ci_as not null, 
	[size] [int] not null, 
	[name] [nvarchar](128) collate sql_latin1_general_cp1_ci_as not null, 
	[filename] [nvarchar](260) collate sql_latin1_general_cp1_ci_as not null
)
    INSERT      
    INTO    #holdforeachdb exec sp_MSforeachdb 
				'select ''?'' as databasename,
				[?]..sysfiles.size, 
				[?]..sysfiles.name, 
				[?]..sysfiles.filename
				from [?]..sysfiles '
--NEW location of DB files
 DECLARE	@NewDataPath NVARCHAR(4000)='N:SQLDATA',	/*!!!!!!MODIFY ACCORDINGLY!!!!!!*/
			@NewTlogPath NVARCHAR(4000)='L:SQLTLOG'   /*!!!!!!MODIFY ACCORDINGLY!!!!!!*/

;WITH DataBasefiles (dbname, size_Gb, logical_name, Path, PhysFileName, FileType)
AS
(select 	databasename ,
		(size*8.00/1024/1024) size_Gb , 
		sf.name logical_name, 
		LEFT(FileName,LEN(FileName)-CHARINDEX('',REVERSE(FileName))+1) Path, 
		RIGHT(FileName,CHARINDEX('',REVERSE(FileName))-1) PhysFileName,
		SUBSTRING([filename], (LEN(filename)-2), 4) AS FileType
from #holdforeachdb sf
JOIN sys.databases db on db.name=sf.databasename)

select	dbname, 
		--size_Gb, 
		logical_name, 
		Path AS 'existing_path', 
		PhysFileName, 
		FileType,
		CASE 
		WHEN FileType = 'ldf'	THEN 'USE [master]; ALTER DATABASE '+QUOTENAME(dbname)+' SET OFFLINE WITH ROLLBACK IMMEDIATE;'
		ELSE '' END AS 'SET_DB_OFFLINE',
		'USE [master]; ALTER DATABASE '+QUOTENAME(dbname)+' MODIFY FILE (Name = '+logical_name+' , FileName = N'''+CASE
						WHEN FileType = 'mdf'	THEN @NewDataPath
						WHEN FileType = 'ndf'	THEN @NewDataPath
						WHEN FileType = 'ldf'	THEN @NewTlogPath
						END +''+PhysFileName+''');' AS 'MOVE_DB_FILES_CMD',
		CASE
		WHEN FileType = 'ldf'	THEN 'USE [master]; ALTER DATABASE '+QUOTENAME(dbname)+' SET ONLINE;'
		ELSE '' END AS 'SET_DB_ONLINE'
FROM DataBasefiles

Rate

3 (2)

Share

Share

Rate

3 (2)