Up to Something

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

SELECT name, physical_name AS LogicalName, FileName
FROM sys.master_files
WHERE database_id = DB_ID(N’REPLDB0’);

Alter

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’ );

Relocate

Stop MSSQL, using the paths already seen, move the files and restart MSSQL. Just don’t screw it up.