Changing Default Database File Locations in SQL Server 2014

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 database file locations as defined in the server properties. You can see these properties for yourself by right-clicking on the instance name inside of SQL Server Management Studio (SSMS) and navigating to the ‘Database Settings’ tab:

server_properties

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%';

The second query returns the locations of our data and log files, which we see are the defaults:

results

Changing the defaults is easy enough, we can just update the file locations inside of SSMS. I will create two new folders (C:\SQL\Data\ and C:\SQL\Logs\) to store the data and log files for new databases. It is important to 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.

Let’s update the settings to point to the new directories:

server_properties_updated

Press OK, and we’ll run our create database script, modifying the database name, and the results now look like this:

results_updated

Whoa, what happened here?

First, like I said earlier, updating those defaults will not migrate existing files. You can see that in the first two rows of the result set. Second, setting the default locations requires a restart of the SQL instance for the changes to take effect. The reason why is because the default locations come from reading 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'Software\Microsoft\MSSQLServer\MSSQLServer'
        , N'DefaultData'
        , REG_SZ
        , N'C:\SQL\Data'
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE'
        , N'Software\Microsoft\MSSQLServer\MSSQLServer'
        , N'DefaultLog'
        , REG_SZ
        , N'C:\SQL\Logs'
GO

If we restart the instance and then run our create database script again. This time you should see the expected results:

really_updated

 

I’d 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:

install

By doing this during the installation you can avoid the need to restart the service at a later date 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! The syntax for that would be similar to this:

CREATE DATABASE [PorkChopExpress]
 ON  PRIMARY 
( NAME = N'PorkChopExpress', FILENAME = N'C:\SQL\Data\PorkChopExpress.mdf' , SIZE = 1048576KB , FILEGROWTH = 262144KB )
 LOG ON 
( NAME = N'PorkChopExpress_log', FILENAME = N'C:\SQL\Logs\PorkChopExpress_log.ldf' , SIZE = 262144KB , FILEGROWTH = 131072KB )
GO

I still prefer to set the default values, in order to help those that might not have good habits formed yet.

In the above examples you will note that I’ve placed everything on the C:\ drive. This is not a recommended best practice. Please don’t put your database files (and backups) onto the C:\ drive. You should have dedicated drives for data files, transaction logs, and backups. There are two main reasons why you want to have distinct drives. First is performance. But performance is becoming less of a concern as we transition away from spinning pieces of rust and into the world of flash arrays and virtual SANs.

The second reason, and to me the most important, is recovery. If you put your data, log, and backups onto the same drive and that drive fails, then your recovery options become much more limited. As a DBA I know my number one job is recovery, so I am less likely to take risks when it comes to recovery.

As Steve Jones (blog | @way0utwest) would remind us all: “Good resume. Good backup. You only need one.”

19 thoughts on “Changing Default Database File Locations in SQL Server 2014”

  1. I would like to see something like this for the In-Memory databases too. I found that if there are software/utilities that allow you to quiesce/attach database files elsewhere, then you cannot attach the files into a db unless you use scripts to attach the database.

    Example:
    AdventureWorks Files:
    C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATA
    C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLLog

    In-Memory Persist files:
    C:TEMPAdventureWorks2014_mod (this being a file group)
    If you use SSMS, there is no way for us to point to this last file group. (unless I missed something totally)
    I have suggested this on Microsoft’s community page as an enhancement.

    Reply
  2. How does this work if you have multiple SQL instances? It doesn’t seem to write to an instance specific key in the registry.

    Reply
    • Andrew,

      Thanks for the comment. It would appear there is a slight bug in the GUI that generates the code. It is pointing to the wrong registry value. If you use the GUI to perform the update then the correct registry key is updated, but the script points to a key that doesn’t exist.

      The correct key (for my default instance of SQL2014) appears to be: HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLServer

      For a named SQL2014 instance it might look like this:
      HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL12.YourInstanceNameHereMSSQLServer

      You can use regedit to verify your registry settings and update your T-SQL to reflect the correct path.

      Reply
      • Are you sure it’s wrong? xp_instance_regwrite is mean to replace the MSSQL portion with an instance specific path internally. Is it broken?

        Reply
        • Well, I was using SQL 2014 CU5 tools, and the GUI spit out that syntax, and when I ran it it said “0 rows affected”. I checked the registry and saw that the value was not changed. I then used the GUI to make the change and saw the registry was updated properly.

          Not exactly a scientific test, I know, but it was enough to get me thinking that something might be wrong.

          Reply
      • SQL 2014 Express doesn’t have the DefaultData and DefaultLog keys. It seems to have just a DataPath key. Will it use the DefaultData and DefaultPath keys if I add them or do I have to use the DataPath key and keep logs with databases?

        Reply
    • Yes, you could use T-SQL to update the registry directly, the script is in the post, it’s what get’s output from SSMS.

      Reply

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.