Script to create a script to move all transaction logs

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.

Jim

Answer 1

sys.master_files looks to be what you're after

Answer 2

Nice, thanks, Ryan

http://www.jasonstrate.com/2012/07/lost-in-translation-deprecated-system-tables-sysaltfiles/

(I searched for the above after I re-visited http://forum.lessthandot.com/viewtopic.php?f=22&t=8401&sid=ddcf48fe969f3c2e3f40118516e214a5 which I always use when I need a similar script).

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

My TechNet articles

Answer 3

Hi goswell,

Please refer:

http://www.sqlmatters.com/Articles/Moving%20database%20files%20to%20a%20different%20location.aspx 

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-master-files-transact-sql 

https://dba.stackexchange.com/questions/52007/how-do-i-move-sql-server-database-files

Thanks,
Xi Jin.

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 4

Thank!  That's just what I need.

Answer 5

Just make sure you ignore tempdb and master. For tempdb, this is the model information (how tempdb is created at startup), not the current info. For master, the location is read through the registry. In short, take care with special databases like these. Obvious, but just to point it out...

---

Tibor Karaszi, SQL Server MVP (Web Blog)