MSSQL - Relocating Database Files
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.
Relocate the files (locations form step 1).
.. raw:: html| SELECT name, physical\_name AS LogicalName, FileName | FROM sys.master\_files WHERE database\_id = DB\_ID(N'REPLDB0'); .. raw:: html
.. 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
.. raw:: htmlStop MSSQL, using the paths already seen, move the files and restart MSSQL. Just don't screw it up. .. raw:: html