When you create a database in SQL Server and do not specify a file location for your data and log files, SQL Server will rely on the default locations as defined in the server properties. You can see these properties for yourself by right-clicking on the instance name inside SQL Server Management Studio (SSMS) and navigating to the ‘Database Settings’ tab.
If we create a simple database, we can verify that the files are written to these directories:
CREATE DATABASE TestFileLoc
GO
SELECT filename
FROM sys.sysaltfiles
WHERE name LIKE 'TestFileLoc%'
This returns the locations of our data and log files, which we see are the defaults:
Changing the default is easy enough; we can just update the file locations inside of SSMS. I will create two new folders (C:SQLData and C:SQLLogs) to store the data and log files for new databases. Note that updating these locations will NOT migrate the current data and log files to the new directories. These changes will only apply to new databases created from this point forward.
It’s also important to note that storing your data and log files on the same disk is not recommended.
Let’s update the setting to point to the new directories.
Press OK, and we’ll run our create database script again (don’t forget to drop the original database first) to find this result set:
Whoa, what happened here?
Setting the default database locations requires you to restart the SQL instance for those changes to take effect. This is because the default locations are read from registry values.
In fact, after you make your change in the SSMS, hit the little button at the top that says ‘Script’ and check out what is being done behind the scenes.
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE'
, N'SoftwareMicrosoftMSSQLServerMSSQLServer'
, N'DefaultData'
, REG_SZ
, N'C:SQLData'
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE'
, N'SoftwareMicrosoftMSSQLServerMSSQLServer'
, N'DefaultLog'
, REG_SZ
, N'C:SQLLogs'
GO
Run that, restart the instance, and then run our create database script again. This time you should see the results:
I suggest that you configure these directories when you are installing SQL Server. You will find the option for this on the ‘Database Engine Configuration’ screen as follows:
By doing this during the installation, you can avoid needing to restart the service later to make this simple change.
Or you could specify the data and log file locations when creating your database, a good habit that everyone should have! I still prefer to set the default values, to help those that might not have good habits formed yet.
