move db files to different location dynamically

  • 1974lg

    Old Hand

    Points: 343

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

  • Bobby Russell

    SSCrazy

    Points: 2695

    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

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

  • 1974lg

    Old Hand

    Points: 343

    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 3 months, 3 weeks ago by  1974lg.

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

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