Database default locations4
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)
Modifying them after the fact
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.
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.
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.
Category: Backups, Microsoft SQL Server, Settings, SQLServerPedia Syndication, SSMS | Tags: backups, instance settings, microsoft sql server
4 thoughts on “Database default locations”
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
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.
That is useful to point out. I hadn’t thought about that but it makes a lot of sense.
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.