move db files to different location dynamically

• 1974lg

  Old Hand

  Points: 343

  More actions

  June 24, 2020 at 4:25 pm

  #3766154

  Comments posted to this topic are about the item move db files to different location dynamically

• Bobby Russell

  SSCrazy

  Points: 2657

  More actions

  July 1, 2020 at 1:32 pm

  #3766711

  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?

• 1974lg

  Old Hand

  Points: 343

  More actions

  July 2, 2020 at 2:38 am

  #3766906

  Hi Robby, you are completely right, it messed up over there somehow. good catch. updating it. thank you!

• 1974lg

  Old Hand

  Points: 343

  More actions

  July 2, 2020 at 3:03 am

  #3766908

  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*******/

  • This reply was modified 2 weeks ago by  1974lg.