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.

Filed under: Backups, Microsoft SQL Server, Settings, SQLServerPedia Syndication, SSMS Tagged: backups, instance settings, microsoft sql server