A simple way to get the default SQL Server data and log path
For things like automated SQL Server build scripts (and probably many other reasons) finding the root data and log file paths is essential.
Prior to SQL Server 2012 there were all sorts of options ranging from e.g. running sp_helpfile against the master database (which is not foolproof) or constructing a string to establish the right registry key to search (which can be a pain for named instance) and then running an undocumented extended stored procedure called xp_instance_regread to get this information.
Fortunately, this has all now changed. Gone are the calls to undocumented extended procs or other home-brewed solutions because now (for those of us working on SQL Server 2012 onward), we can just run a straight t-sql function:
SELECT SERVERPROPERTY(‘INSTANCEDEFAULTDATAPATH’) as [Default_data_path], SERVERPROPERTY(‘INSTANCEDEFAULTLOGPATH’) as [Default_log_path];
I found this entirely by chance whilst tracing SSMS in order to update a server build script.
The SERVERPROPERTY function has been around for a while and provides really useful information about that SQL Server instance, but for some reason the SQL Server 2012 documentation has not been updated to reflect these two new additions to this function, which is a bit of a shame so I’ve decided to plug it in this post, as well as submitting a connect bug on this, as I’m hoping this is just an oversight and it’s not been deliberately left as an undocumented command.
Incidentally, if you’re wondering how to change the default data path or the default log path, connect to the server in SSMS, right click on the server in Object Explorer and select Properties. On the Server Properties dialog box look for Database Settings in the object list and make the necessary modifications under Database default locations.
If you’re after the default error log folder check out this post.
(EDIT: I did get a response the same day I logged the connect bug and this post, but it got short shrift and closed as a “Won’t fix” which I cannot understand, so if more people vote on it maybe they’ll change their mind…)
