MORE ABOUT ME
Welcome! I’m Thomas…
Resources

Changing Default Database File Locations in SQL Server 2014

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.”

One Pingback/Trackback

  • 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.

  • Andrew G

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

    • ThomasLaRock

      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.

      • Cody

        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?

        • ThomasLaRock

          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.

          • Cody

            Good enough for me!

  • Pingback: Microsoft SQL Server DBA Setup Checklist and Recommendations | Question Driven()

  • ZincKidd

    Is there some way through TSQL that the default paths can be altered?

    • ThomasLaRock

      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.

  • agentmi6

    I cant change the paths im getting this error:

    http://i.stack.imgur.com/brszG.png

    • ThomasLaRock

      Looks like a permissions error, you aren’t allowed to edit registry settings.

      • agentmi6

        But i am, Permission are set to allow everyone…

        • agentmi6

          nvm i got it now, had to enable permissions on the last folder in regedit

      • agentmi6

        the thing is, now they are reverting to the old locations…