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)
selectdbname,
--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 
