I was doing a small Proof-of-Concept with SharePoint Server 2007, using one server for the database (running SQL Server 2005) and another server for SharePoint Server. The problem is that the SysAdmin that installed SQL Server not only installed it on the C: drive but also let SQL creating new database on the C: drive. As you can imagine, the C: drive ended up dangerouly low on disk space. We needed to move the data files to another drive (yes, there was another drive that was supposed to be used for data). Here are the steps we used.
WARNING: These instructions are provided "AS IS" with no warranties, and confer no rights. To be on the safe side, always perform a full backup before any major configuration change. You will need downtime on the server to perform these operations. Follow these instructions at your own risk.
1 - We confirmed there were no users on the server and properly shutdown the SharePoint Server. This makes sure there's no application hitting the database while we're moving the files around. If you SharePoint Server and SQL Server are on the same box, you will need to shutdown IIS and all SharePoint services before proceeding. Check a previous post on this blog to identify those services.
2 - We detached all databases. We also needed to disconnect current users (although SharePoint services were shut down, some connections remained there). You can check a previous post on this blog to identify those 8 or more databases involved. Make sure you take note of those, since you will need them later.
3 - We created a new folder on the E: drive, where we were moving the files to. Then we moved those 16 or more files (there was an MDF and an LDF file for each one of the 8 databases). We did not move any of the system databases, just the SharePoint-related ones.
4 - We re-attached the databases, point to the new file locations at E:. SQL Server 2005 had no problem mounting the files from the new location.
5 - We reconfigured the default location for the SQL Server data and log files, so future database get created on the right location. We had to restart the SQL Server service for this configuration change to apply.
6 - We restarted SharePoint, which did not really care for the fact that the databases were now in a different drive. All SharePoint knows about is server and database names, and those did not change.
In the end, it was actually not that hard...