move db files to different location dynamically

Question

Post reply

move db files to different location dynamically

1974lg

Old Hand

Points: 374
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

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Bobby Russell SSCrazy Points: 2801 More actions · Answer 1

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: 374 More actions · Answer 2

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

1974lg Old Hand Points: 374 More actions · Answer 3

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 5 years ago by 1974lg.

yiuyiuya Newbie Points: 3 More actions · Answer 4

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."