move db files to different location dynamically

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

  • 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?

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

  • 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 years ago by  1974lg.
  • This was removed by the editor as SPAM

  • 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 6 (of 6 total)

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