MSSQL - Relocating Database Files

Wed 21 May 2008

Filed under Misc

Tags Obvious

There are a ton of articles, esp from Microsoft, regarding moving SQL Server data and log files. After our storage expansion we wanted to move the data and logs for several databases, all of which participated in replication by one means or another.

I didn't find any articles specifically mentioning what I was doing, however, I decided the best approach was to test moving the files in the same way you relocate MSSQL System Databases. It worked, and I thought I would mention it here.

If you've never done this, it's quite simple.

  • Locate the files (and logical names)

  • Alter Database to change the new location on next startup.

  • Stop MSSQL

  • Relocate the files (locations form step 1).

  • Restart MSSQL

Locate

.. raw:: html

| SELECT name, physical\_name AS LogicalName, FileName | FROM sys.master\_files WHERE database\_id = DB\_ID(N'REPLDB0'); .. raw:: html

Alter

.. raw:: html

| ALTER DATABASE REPLDB0 MODIFY FILE ( NAME = 'REPLDB0\_Data', FILENAME = 'D:\\MSSQL\\Data\\REPLDB0.mdf' ); | ALTER DATABASE REPLDB0 MODIFY FILE ( NAME = REPLDB0\_Log', FILENAME = 'E:\\MSSQL\\Logs\\REPLDB0\_1.ldf' ); .. raw:: html

Relocate

.. raw:: html

Stop MSSQL, using the paths already seen, move the files and restart MSSQL. Just don't screw it up. .. raw:: html


Comments


Up To Something © Joshua M Schmidlkofer Powered by Pelican and Twitter Bootstrap. Icons by Font Awesome and Font Awesome More