Database default locations

4

September 29, 2014 by Kenneth Fisher

One of the options you had when installing your instance was setting some default file locations.

  • Defaut data file location
  • Default log file location
  • Default backup file location

 
If you are new to installing SQL Server (and even if you aren’t) you might very well have skipped past these very useful settings. Not that the default values are bad mind you, and if you are careful they don’t make much of a difference at all. However they can save you some headaches and time along the way if set correctly. (Mileage may vary depending on your drive setup.)

Initial setup (SQL 2014 install)

SQL Server Default File Locations1

Modifying them after the fact

SQL Server Default File Locations2

Why are they so useful you may ask?

Defaut data and log file locations

These come into play mostly (but not only) when creating new databases. When you open up the new database wizard the paths for the data and log file are set to the associated default values. Unless you manually change them the data and log files will be put into those locations.

SQL Server Default File Locations3

When you use the CREATE DATABASE command and don’t specify locations for the data and log files they go to the default locations also. They even show up in some places when doing restores. So why does it matter? Well the default values are based on the install location. Typically this is going to be the C drive. On a production server generally the C drive is one of the smaller drives and is specifically set aside for the system files. You really don’t want your data or log files on this drive. In fact you generally want to make sure your data and log files are on separate drives. Assuming you only have one file location for your data files and another for your log files (not unreasonable for a relatively small instance) and you set your default locations correctly then you could run this command and the files would be in the correct location.

CREATE DATABASE NewDB

Generally an instance only has one drive for its log files but frequently there will be data files on more than one drive. Particularly for large databases that are split among multiple files and filegroups. In these cases I would set your default data drive to one of the data paths that you use frequently. This way if someone creates a new database and forgets to change the file locations at least they are an acceptable spot and not on your C drive.

Default backup file location

When not set correctly the default backup file location is potentially less dangerous but personally something I find much more aggravating. This is because I do a lot of pre-install manual backups.

When you open the backup screen initially the destination is the last place you did a backup. Typically you will want to select a new location/file to store your backup. Particularly since manual backups are frequently going to be copy only and it’s nice to include that in the name so there is no confusion later. So you hit the ADD button and it brings up a dialog for the file name you want to add. The default backup file location will be displayed as the initial directory.

SQL Server Default File Locations4

If you have it set correctly then you just need to type the name (and possibly a subdirectory) and off you go. Otherwise you have to remember and type in the correct path. Personally I deal with 70+ servers and more coming all the time. There is no possible way I’m going to be able to remember all of the backup paths. I have to look them up each time.

Again if you are using more than one drive for your backups this is less helpful. In this case I recommend setting the default to your full backup location. It will generally have the most space (in case of mistakes) and I find that I do more manual full backups than any other kind.

4 thoughts on “Database default locations

  1. Useful post, thanks Kenneth. Its also worth pointing out that the install process will also set the correct permissions for the folders you choose. Trying to alter the default backup path after-install can be a headache.

  2. Rob says:

    The only really useful purpose I’ve found for these is to prevent the ability to create random databases. By setting these to non-existing folders the default ‘create database’ is blocked. It enforces a level of planning where your file locations must be pre-allocated and specified during the database creation process.

    • Actually what I find the most handy is the default backup location. I am frequently doing manual one off backups and it saves me a lot of time looking up where I’m supposed to be sticking the backups for that particular server.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,135 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: