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
October 30, 2021 at 3:43 pm
This reply has been reported for inappropriate content.
what is search engine optimization
Imitation, conformity only "initial stage" and its state should be to reach from the wave of the fashion trend of wave leaving no stone unturned, it extracted the essence and true meaning, to enrich their own aesthetic and taste to create their own exclusive The beauty "template."
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply