I have been asked several times this week where the SharePoint 2010 content database files and log files are stored by default. Well, sort of hard to answer as everyone seems to install their SQLServer slightly differently and the locations of these files depends on how you approach the installation of SQLServer.
If you “click through” the installation, not really paying any attention and take the defaults, then your database files and log files are going to be created and stored in the default location. This typically is C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA.
If we create our databases in SQL Server Management Studio, we can specify the location of the MDF files and LOG files. However, we will have to go through some manipulation to use a pre-created content database in Central Administration – probably not the most streamlined approach. However, the pre-creation of content databases may be the optimal solution if your organization has DBAs that don’t want the SharePoint Administrators creating databases all over the place.
So, here is a quick tip and modification that you can give to your DBA so that the data and log files go in the location that they want and you don’t have to go through down-time or other configuration steps to have the default Central Administration Create Site Collection process do what you expect it to do.
The simple SQL script below can be executed to change the default location for MDF and LOG files:
EXEC xp_instance_regwrite N’HKEY_LOCAL_MACHINE’, N’Software\Microsoft\MSSQLServer\MSSQLServer’, N’DefaultData’, REG_SZ, N’D:\SQL_Data’
EXEC xp_instance_regwrite N’HKEY
EXEC xp_instance_regwrite N’HKEY_LOCAL_MACHINE’, N’Software\Microsoft\MSSQLServer\MSSQLServer’, N’DefaultData’, REG_SZ, N’Location’
— Log file
EXEC xp_instance_regwrite N’HKEY_LOCAL_MACHINE’, N’Software\Microsoft\MSSQLServer\MSSQLServer’, N’DefaultLog’, REG_SZ, N’Location’
Don’t forget that Sharepoint’s Central Administration interface nor powershell provide the ability to specify a location for the MDF and/or log files.