June 24, 2020 at 4:25 pm
Comments posted to this topic are about the item move db files to different location dynamically
July 1, 2020 at 1:32 pm
Great script, thank you! I think you meant for this line:
RIGHT(FileName,CHARINDEX('',REVERSE(FileName))-1) PhysFileName
to be
RIGHT(FileName,CHARINDEX('\',REVERSE(FileName))-1) PhysFileName?
July 2, 2020 at 2:38 am
Hi Robby, you are completely right, it messed up over there somehow. good catch. updating it. thank you!
July 2, 2020 at 3:03 am
here is the updated script:
-- 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,
[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
WHERE db_id(''?'')>4'
--NEW location of DB files
DECLARE @NewDataPath NVARCHAR(4000)='N:\datafiles\DBDATA\', /*!!!!!!MODIFY ACCORDINGLY!!!!!!*/
@NewTlogPath NVARCHAR(4000)='L:\datafiles\DBLOG\' /*!!!!!!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)
/***DON'T FORGET TO MOVE DB FILES TO THE NEW LOCATION BEFORE RUNNING ALTER DATABASE DB SET ONLINE;****/
select dbname,
--size_Gb,
logical_name,
Path,
PhysFileName,
FileType,
CASE
WHEN FileType = 'ldf' THEN 'USE [master]; ALTER DATABASE '+'['+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 '+'['+dbname+']'+' SET ONLINE;'
ELSE '' END AS 'SET_DB_ONLINE'
FROM DataBasefiles
--where dbname='DBA' /*******add list of DBs within IN clause*******/
April 28, 2021 at 7:02 am
This was removed by the editor as SPAM
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply