Script to create a script to move all transaction logs RRS feed

  • Question

  • I'm an Oracle DBA who just got handed the MSSQL servers as well, so I'm green.

    Working through a performance issue, I found all the data and log files are in the same directory.  I'm planning on moving them to a different directory using a command similar to this:

    ALTER DATABASE AdventureWorks2014 MODIFY FILE ( NAME = AdventureWorks2014_Log , FILENAME = 'c:\mssqllogs\AdventureWorks2014_Log.ldf' ) 

    After all the log files are moved, I'll <g class="gr_ gr_25 gr-alert gr_spell gr_inline_cards gr_disable_anim_appear ContextualSpelling" data-gr-id="25" id="25">shutdown</g> the MSSQL server, move the files and start it back up.

    I'm hoping to use t-<g class="gr_ gr_52 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="52" id="52">sql</g> to output a script I can use to move all the logs.  There are over 100 databases that need to be moved.

    I have something like this started but I'm stuck now:

    select + 'alter database ' + name + ' modify file ( NAME= ' from sys.databases;

    I'm trying to find a view that contains all the log file names and locations?

    Any help is appreciated.


    • Edited by goswell Tuesday, June 13, 2017 9:38 PM
    Tuesday, June 13, 2017 9:35 PM


  • sys.master_files looks to be what you're after
    • Proposed as answer by Naomi N Tuesday, June 13, 2017 10:35 PM
    • Marked as answer by goswell Wednesday, June 14, 2017 12:20 PM
    Tuesday, June 13, 2017 10:27 PM

All replies